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