部署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