1.安装/下载软件和初始化
205 groupadd postgres
206 useradd postgres -g postgres
207 yum install gcc
214 yum install -y readline-devel
216 yum install zlib-devel
200 mkdir /apps/data/pgsql137/
200 mkdir /apps/data/pgsql137/data/
200 mkdir /apps/data/pgsql137/log/
200 cd /apps/svr/postgresql-13.7/
217 ./configure --prefix=/apps/data/pgsql137/
221 make && make install
223 chown -R postgres:postgres /apps/data/pgsql137
224 su postgres
18 export
PGHOME=/apps/data/pgsql137
19 export
PGDATA=/apps/data/pgsql137/data
200 /apps/data/pgsql137/bin/pg_ctl -D /apps/data/pgsql137/data/ -l logfile start
20 /apps/data/pgsql137/bin/pg_ctl start -l /apps/data/pgsql137/log/pg_server.log
22 /apps/data/pgsql137/bin/psql -U postgres -d postgres
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
2.权限
一般会有一个postgres的用户初始化，无密码，超级用户，可直接登陆先，进去之后这个时候怎么加密码以及加其他用户呢？
Create user或者Create role 设置PASSWORD，完成后更改
pg_hba.conf 设置访问权限。
3.搭建主从配置
wal_level = hot_standby # 这个是设置主为wal的主机
max_wal_senders = 32
# 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目 max_keep_size 13.7是这个参数
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100
# 这个设置要注意下，从库的max_connections必须要大于主库的
4.从库设置
安装软件即可
5.基础备份，注意-R
-R, --write-recovery-conf
write configuration for replication
pg_basebackup -F p --progress -D /apps/data/pgsql137/data -hxx -p 5432 -U repl --password -R
6.完成后注意文件
[postgres@ip-xx data]$ more standby.signal
[postgres@ip-xx data]$ more postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo =
'user=repl password=xx channel_binding=disable host=''master_host'' port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv
1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
7.从库进程检查
[postgres@ip-xx data]$ ps -ef|grep postgres
root 4632 3385 0 06:33 pts/0 00:00:00 su - postgres
postgres 4633 4632 0 06:33 pts/0 00:00:00 -bash
root 6727 6686 0 07:09 pts/1 00:00:00 su postgres
postgres 6728 6727 0 07:09 pts/1 00:00:00 bash
root 10142 10120 0 08:11 pts/3 00:00:00 su - postgres
postgres 10143 10142 0 08:11 pts/3 00:00:00 -bash
root 13100 12118 0 09:01 pts/4 00:00:00 su - postgres
postgres 13102 13100 0 09:01 pts/4 00:00:00 -bash
postgres 13443 1 0 09:07 ? 00:00:00 /apps/data/pgsql137/bin/postgres
postgres 13444 13443 0 09:07 ? 00:00:00 postgres: startup recovering 00000001000000000000000F
postgres 13445 13443 0 09:07 ? 00:00:00 postgres: checkpointer
postgres 13446 13443 0 09:07 ? 00:00:00 postgres: background writer
postgres 13447 13443 0 09:07 ? 00:00:00 postgres: stats collector
postgres 13448 13443 0 09:07 ? 00:00:00 postgres: walreceiver streaming 0/F000060
postgres 13479 13102 0 09:07 pts/4 00:00:00 ps -ef
postgres 13480 13102 0 09:07 pts/4 00:00:00 grep --color=auto postgres
8.主库进程以及状态检查
[postgres@ip-xx ~]$ ps -ef|grep postgres
postgres 3799 1 0 Sep09 ? 00:00:33 /apps/data/pgsql137/bin/postgres
postgres 3801 3799 0 Sep09 ? 00:00:01 postgres: checkpointer
postgres 3802 3799 0 Sep09 ? 00:00:33 postgres: background writer
postgres 3803 3799 0 Sep09 ? 00:00:32 postgres: walwriter
postgres 3804 3799 0 Sep09 ? 00:00:37 postgres: autovacuum launcher
postgres 3805 3799 0 Sep09 ? 00:00:04 postgres: archiver last was 00000001000000000000000E.00000028.backup
postgres 3806 3799 0 Sep09 ? 00:00:57 postgres: stats collector
postgres 3807 3799 0 Sep09 ? 00:00:01 postgres: logical replication launcher
root 28882 28863 0 07:09 pts/3 00:00:00 su postgres
postgres 28883 28882 0 07:09 pts/3 00:00:00 bash
root 29432 29415 0 08:10 pts/6 00:00:00 su postgres
postgres 29433 29432 0 08:10 pts/6 00:00:00 bash
root 29621 29601 0 08:21 pts/0 00:00:00 su postgres
postgres 29622 29621 0 08:21 pts/0 00:00:00 bash
postgres 29720 29622 0 08:30 pts/0 00:00:00 /apps/data/pgsql137/bin/psql -U postgres -d postgres -h localhost -W
postgres 29721 3799 0 08:30 ? 00:00:00 postgres: postgres postgres 127.0.0.1(51594) idle
root 29893 29876 0 08:46 pts/7 00:00:00 su postgres
postgres 29894 29893 0 08:46 pts/7 00:00:00 bash
root 29997 29980 0 08:47 pts/8 00:00:00 su postgres
postgres 29998 29997 0 08:47 pts/8 00:00:00 bash
postgres 30105 29998 0 08:59 pts/8 00:00:00 /apps/data/pgsql137/bin/psql -U postgres -d postgres -h127.0.0.1 -W
postgres 30106 3799 0 08:59 ? 00:00:00 postgres: postgres postgres 127.0.0.1(34106) idle
postgres 30270 3799 0 09:07 ? 00:00:00 postgres: walsender repl 172.31.34.1(37046) streaming 0/F000060
root 30336 30319 0 09:08 pts/2 00:00:00 su - postgres
postgres 30337 30336 1 09:08 pts/2 00:00:00 -bash
postgres 30360 30337 0 09:08 pts/2 00:00:00 ps -ef
postgres 30361 30337 0 09:08 pts/2 00:00:00 grep --color=auto postgres
[postgres@ip-xx root]$ psql -h localhost -xc
"select * from pg_stat_replication"
could not change directory to
"/root": Permission denied
Password for user postgres:
-[ RECORD 1 ]----+------------------------------
pid | 30270
usesysid | 16394
usename | repl
application_name | walreceiver
client_addr | 172.31.34.1
client_hostname |
client_port | 37046
backend_start | 2022-10-12 09:07:14.54261+00
backend_xmin |
state | streaming
sent_lsn | 0/F000060
write_lsn | 0/F000060
flush_lsn | 0/F000060
replay_lsn | 0/F000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-10-12 09:10:14.878291+00
9.模拟主从切换
主库：pg_ctl stop -m fast
从库：pg_ctl promote
touch standby.signal
vim postgresql.auto.conf 写入新主库信息
