【Postgresql】-01、Ubuntu20.40二进制安装部署postgresql

部署Postgresql数据库方式

postgresql部署方式如下，可根据需要选择不通的部署方式。
1、二进制安装部署
2、源码安装部署
3、centos系统通过yum部署，ubuntu系统通过apt部署安装

1、创建文件存储库配置

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

root@db01:~# echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

2、导入存储库签名密钥

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

root@db01:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK

3、更新包列表

apt-get update

4、安装PostgreSQL

安装最新版本PostgreSQL
apt-get -y install postgresql

推荐：
安装指定版本PostgreSQL
apt-get -y  install postgresql-12

5、查看PostgreSQL状态

 systemctl status postgresql.service


root@db01:/etc/apt# systemctl status postgresql.service
* postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2023-03-12 20:16:26 CST; 1min 32s ago
   Main PID: 699985 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 9466)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Mar 12 20:16:26 db01 systemd[1]: Starting PostgreSQL RDBMS...
Mar 12 20:16:26 db01 systemd[1]: Finished PostgreSQL RDBMS.
root@db01:/etc/apt#

6、查看PostgreSQL进程和端口

查看进程：
root@db01:/etc/apt# ps -ef|grep postgre
postgres  700276       1  0 20:16 ?        00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
postgres  700278  700276  0 20:16 ?        00:00:00 postgres: 12/main: checkpointer   
postgres  700279  700276  0 20:16 ?        00:00:00 postgres: 12/main: background writer   
postgres  700280  700276  0 20:16 ?        00:00:00 postgres: 12/main: walwriter   
postgres  700281  700276  0 20:16 ?        00:00:00 postgres: 12/main: autovacuum launcher   
postgres  700282  700276  0 20:16 ?        00:00:00 postgres: 12/main: stats collector   
postgres  700283  700276  0 20:16 ?        00:00:00 postgres: 12/main: logical replication launcher   
root      701785  697752  0 20:18 pts/0    00:00:00 grep --color=auto postgre
root@db01:/etc/apt# 

查看端口：
root@db01:~# netstat  -lntp|grep post
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      702118/postgres

7、PostgreSQL默认配置

egrep -v "^$|^#|^\s" /etc/postgresql/15/main/postgresql.conf

root@db01:/etc/apt# egrep -v "^$|^#|^\s" /etc/postgresql/12/main/postgresql.conf
data_directory = '/var/lib/postgresql/12/main'      # use data in another directory
hba_file = '/etc/postgresql/12/main/pg_hba.conf'    # host-based authentication file
ident_file = '/etc/postgresql/12/main/pg_ident.conf'    # ident configuration file
external_pid_file = '/var/run/postgresql/12-main.pid'           # write an extra PID file
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_line_prefix = '%m [%p] %q%u@%d '        # special values:
log_timezone = 'Asia/Shanghai'
cluster_name = '12/main'            # added to process titles if nonempty
stats_temp_directory = '/var/run/postgresql/12-main.pg_stat_tmp'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US'           # locale for system error message
lc_monetary = 'en_US'           # locale for monetary formatting
lc_numeric = 'en_US'            # locale for number formatting
lc_time = 'en_US'               # locale for time formatting
default_text_search_config = 'pg_catalog.english'
include_dir = 'conf.d'          # include files ending in '.conf' from
root@db01:/etc/apt#

8、连接PostgreSQL

su - postgres -c "psql -U postgres -p 5432"

root@db01:/etc/apt# su - postgres -c "psql -U postgres -p 5432"
psql (12.14 (Ubuntu 12.14-1.pgdg20.04+1))
Type "help" for help.

postgres=#

9、postgresql数据库的日常操作

数据库快捷操作指令
\l  ：查看数据库
\q  ： 退出

其他操作后续补充：

10、修改postgres密码

修改密码
postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=# 


测试连接
su - postgres -c "psql -U postgres -h 127.0.0.1 -p 5432"


root@db01:~# su - postgres -c "psql -U postgres -h 127.0.0.1 -p 5432"
Password for user postgres: 
psql (12.14 (Ubuntu 12.14-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

11、允许远程连接

postgresql.conf

root@db01:~# sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '0.0.0.0'@g"  /etc/postgresql/12/main/postgresql.conf

pg_hba.conf

root@db01:~# echo "host    all             all             0.0.0.0/0           md5" >> /etc/postgresql/12/main/pg_hba.conf

root@db01:~# echo "host    all             all             0.0.0.0/0            scram-sha-256" >> /etc/postgresql/12/main/pg_hba.conf

重启服务

root@db01:~# systemctl restart postgresql.service

测试远程连接

root@db01:/etc/postgresql/12/main#  su - postgres -c "psql -U postgres -h 172.21.209.40  -p 5432"
Password for user postgres: 
psql (12.14 (Ubuntu 12.14-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

12、PG的数据目录

root@db01:/var/lib/postgresql/12/main# pwd
/var/lib/postgresql/12/main
root@db01:/var/lib/postgresql/12/main# 
root@db01:/var/lib/postgresql/12/main# ll
total 92
drwx------ 19 postgres postgres 4096 Mar 12 20:52 ./
drwxr-xr-x  3 postgres postgres 4096 Mar 12 20:16 ../
drwx------  5 postgres postgres 4096 Mar 12 20:16 base/
drwx------  2 postgres postgres 4096 Mar 12 20:52 global/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_commit_ts/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_dynshmem/
drwx------  4 postgres postgres 4096 Mar 12 20:57 pg_logical/
drwx------  4 postgres postgres 4096 Mar 12 20:16 pg_multixact/
drwx------  2 postgres postgres 4096 Mar 12 20:52 pg_notify/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_replslot/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_serial/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_snapshots/
drwx------  2 postgres postgres 4096 Mar 12 20:52 pg_stat/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_stat_tmp/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_subtrans/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_tblspc/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_twophase/
-rw-------  1 postgres postgres    3 Mar 12 20:16 PG_VERSION
drwx------  3 postgres postgres 4096 Mar 12 20:16 pg_wal/
drwx------  2 postgres postgres 4096 Mar 12 20:16 pg_xact/
-rw-------  1 postgres postgres   88 Mar 12 20:16 postgresql.auto.conf
-rw-------  1 postgres postgres  130 Mar 12 20:52 postmaster.opts
-rw-------  1 postgres postgres  108 Mar 12 20:52 postmaster.pid
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容