数据库版本：15.5

参考：基于PG 15搭建主备 - 墨天轮

安装步骤：

一、安装2台机器，

1、机器：

    1）1 172.25.136.192

    2）2 172.25.136.194

常用命令：

# 开机启动|启动|重启|状态|停止 命令
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
sudo systemctl restart postgresql-15
sudo systemctl status postgresql-15
sudo systemctl stop postgresql-15

2、操作：

安装固定版本（15.5）--开始：

1）从postgres网站下载，需要的版本安装文件

    地址：PostgreSQL: File Browserhttps://www.postgresql.org/ftp/source/v15.5/

2）上传文件到服务器

3）新建用户postgres

     adduser postgres

     设置密码

   passwd *****

4）tar -zxvf postgresql-15.5.tar.gz  --解压压缩包

5）yum install -y bison flex readline-devel zlib-devel zlib zlib-devel gcc  gcc-c++ openssl-devel  python3-devel python3    --下载安装数据库基本依赖包，Python依赖为可选项

cd postgresql-15.5

./configure --prefix=/home/postgres/pg15-5 --with-openssl --with-python #拟安装至/home/postgres/pg

  --创建装载所需文件夹

make && make install

chown -R postgres:postgres /home/postgres/pg --进行授权

vim /etc/profile    

--指定bin文件路径 确保准备   

--指定data文件路劲 在初始化时会将data装载这个路径

export PATH=/home/postgres/pg/bin:$PATH     

export PGDATA=/home/postgres/pg/data        

source /etc/profile  --加载环境变量内容

su - postgres  --切换用户

cd /home/postgres/pg/bin   --进入指令包

./initdb -D $PGDATA    --初始化数据库

pg_ctl  start  --启动数据库

pg_ctl status --查看数据库运行状态

监听所有主机

vim /home/postgres/pg/data/postgresql.conf

将原本的#listen_addresses = 'localhost'换成

listen_addresses = '*'

vim /home/postgres/pg/data/pg_hba.conf

增加

host    all             all             0.0.0.0/0               trust
使所有主机可访问

安装固定版本（15.5）--结束：

安装最新版本：

1）yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2）sudo yum install -y postgresql15-server

3）修改配置文件：/etc/profile

export PGSQL_HOME=/usr/pgsql-15
export PATH=$PATH:$PGSQL_HOME/bin

执行：source /etc/profile

4）启动：sudo systemctl start postgresql-15

5）查看启动状态：sudo systemctl status postgresql-15

root@iZuf6etvfng2acwulef5anZ data]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2024-01-14 20:20:21 CST; 18s ago
     Docs: https://www.postgresql.org/docs/15/static/
  Process: 2027 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 2033 (postmaster)
   CGroup: /system.slice/postgresql-15.service
           ├─2033 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
           ├─2036 postgres: logger 
           ├─2037 postgres: checkpointer 
           ├─2038 postgres: background writer 
           ├─2040 postgres: walwriter 
           ├─2041 postgres: autovacuum launcher 
           ├─2042 postgres: archiver 
           └─2043 postgres: logical replication launcher 

Jan 14 20:20:21 iZuf6etvfng2acwulef5anZ systemd[1]: Stopped PostgreSQL 15 database server.
Jan 14 20:20:21 iZuf6etvfng2acwulef5anZ systemd[1]: Starting PostgreSQL 15 database server...
Jan 14 20:20:21 iZuf6etvfng2acwulef5anZ postmaster[2033]: 2024-01-14 20:20:21.624 CST [2033] LOG:  redirecting log output to logging collector process
Jan 14 20:20:21 iZuf6etvfng2acwulef5anZ postmaster[2033]: 2024-01-14 20:20:21.624 CST [2033] HINT:  Future log output will appear in directory "log".
Jan 14 20:20:21 iZuf6etvfng2acwulef5anZ systemd[1]: Started PostgreSQL 15 database server.

3、主库操作

1）添加replication，用于复制

 [postgresql@pg1 ~]$ cat >> /postgresql/pgdata/pg_hba.conf <<"EOF" # add replication host replication all 0.0.0.0/0 md5 EOF

2）配置归档 

-- 建议该路径在从库也创建，方便后期主备切换
[postgresql@pg1 ~]$ mkdir -p /postgresql/archive
[postgresql@pg1 ~]$ chown -R postgres:postgres /postgresql/archive

[postgresql@pg1 ~]$ cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
wal_level='replica'
archive_mode='on'
archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'
restore_command='cp /postgresql/archive/%f %p'

# 指定wal日志发送进程的最大并发连接数
max_wal_senders=10
# 指定日志复制进程保留多少日志量,单位是MB，wal_keep_size = wal_keep_segments * wal_segment_size。旧版本使用wal_keep_segments参数，在PG13中已经取消。
wal_keep_size=512
# 设置流复制主机发送数据包的超时时间
wal_sender_timeout=60s
EOF

-- 重启PG服务
[postgresql@pg1 ~]$ pg_ctl restart
pg_ctl: PID file "/postgresql/pgdata/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2023-04-04 03:44:31.354 GMT [11872] LOG:  redirecting log output to logging collector process
2023-04-04 03:44:31.354 GMT [11872] HINT:  Future log output will appear in directory "pg_log".
 done
server started

-- 检查归档参数
[postgresql@pg1 ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# 
postgres=# select * from pg_settings where name in ('wal_level','archive_mode','archive_command');
-[ RECORD 1 ]---+------------------------------------------------------------------
name            | archive_command
setting         | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f
unit            | 
category        | Write-Ahead Log / Archiving
short_desc      | Sets the shell command that will be called to archive a WAL file.
extra_desc      | This is used only if "archive_library" is not set.
context         | sighup
vartype         | string
source          | configuration file
min_val         | 
max_val         | 
enumvals        | 
boot_val        | 
reset_val       | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f
sourcefile      | /postgresql/pgdata/postgresql.conf
sourceline      | 10
pending_restart | f
-[ RECORD 2 ]---+------------------------------------------------------------------
name            | archive_mode
setting         | on
unit            | 
category        | Write-Ahead Log / Archiving
short_desc      | Allows archiving of WAL files using archive_command.
extra_desc      | 
context         | postmaster
vartype         | enum
source          | configuration file
min_val         | 
max_val         | 
enumvals        | {always,on,off}
boot_val        | off
reset_val       | on
sourcefile      | /postgresql/pgdata/postgresql.conf
sourceline      | 9
pending_restart | f
-[ RECORD 3 ]---+------------------------------------------------------------------
name            | wal_level
setting         | replica
unit            | 
category        | Write-Ahead Log / Settings
short_desc      | Sets the level of information written to the WAL.
extra_desc      | 
context         | postmaster
vartype         | enum
source          | configuration file
min_val         | 
max_val         | 
enumvals        | {minimal,replica,logical}
boot_val        | replica
reset_val       | replica
sourcefile      | /postgresql/pgdata/postgresql.conf
sourceline      | 8
pending_restart | f

postgres=# 

-- 手动切换归档
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/14D2208
(1 row)

[root@pg1 ~]# ll /postgresql/archive/
total 16384
-rw------- 1 postgresql postgresql 16777216 Apr  4 13:06 000000010000000000000001

归档配置完成，可以正常归档wal日志。

2）主库创建复制用户

postgres=# create user repl encrypted password 'repl' replication;
CREATE ROLE

4、从库操作

1、在从库对主库进行备份

[postgresql@pg2 backup]$ pg_basebackup -h 172.25.136.192 -p 5432 -U repl  -Fp -Xs -Pv -R -D /home/postgres/backup
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  skipping special file "./.s.PGSQL.5432"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_16304"
WARNING:  skipping special file "./.s.PGSQL.5432"
22312/22312 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/3000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

检查文件：standby.signal

[postgresql@pg2 backup]$ ll standby.signal
-rw------- 1 postgresql postgresql 0 Apr  4 13:09 standby.signal

2、还原从库

-- 关闭从库
[postgresql@pg2 backup]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

-- 删除从库的数据文件，将备份文件覆盖备库的数据文件
[postgresql@pg2 pgdata]$ rm -rf /postgresql/pgdata/*
[postgresql@pg2 pgdata]$ cp -r /home/postgresql/backup/* /postgresql/pgdata/

 3、修改备库primary_conninfo参数

-- 配置连接主库的ip、端口、用户
[postgresql@pg2 pgdata]$ cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
primary_conninfo='host=192.168.1.11 port=5432 user=repl password=repl'
EOF

-- 可选参数
archive_cleanup_command = 'pg_archivecleanup /mnt/server/archiverdir %r'
hot_standby_feedback=on

4 启动从库

[postgresql@pg2 pgdata]$ sudo systemctl start postgresql-15
waiting for server to start....2023-04-04 05:14:35.251 GMT [16365] LOG:  redirecting log output to logging collector process
2023-04-04 05:14:35.251 GMT [16365] HINT:  Future log output will appear in directory "pg_log".
 done
server started

5 查询主库复制状态

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 16544
usesysid         | 16389
usename          | repl
application_name | walreceiver
client_addr      | 192.168.1.12
client_hostname  | 
client_port      | 52848
backend_start    | 2023-04-04 05:14:35.280968+00
backend_xmin     | 
state            | streaming
sent_lsn         | 0/4000148
write_lsn        | 0/4000148
flush_lsn        | 0/4000148
replay_lsn       | 0/4000148
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async      -- 默认为异步复制模式
reply_time       | 2023-04-04 05:17:18.005466+00

6\ 从库查询wal日志接收状态

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 16374
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 1
written_lsn           | 0/4000148
flushed_lsn           | 0/4000148
received_tli          | 1
last_msg_send_time    | 2023-04-04 05:17:37.929886+00
last_msg_receipt_time | 2023-04-04 05:17:37.92991+00
latest_end_lsn        | 0/4000148
latest_end_time       | 2023-04-04 05:15:07.716782+00
slot_name             | 
sender_host           | 192.168.1.11
sender_port           | 5432
conninfo              | user=repl password=********

• 也可以查看pg_is_in_recovery，主库是f代表false ；备库是t，代表true

-- 主库
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

-- 备库
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

主库新增表及数据，从库查看

主库操作：

ostgres=# select * from temptable;
 name 
------
 zhw
 zbh
 lx
(3 rows)

从库操作：

postgres=# select * from temptable;
-[ RECORD 1 ]
name | zhw
-[ RECORD 2 ]
name | zbh
-[ RECORD 3 ]
name | lx

显示格式问题，实际数据是一致的

主库增加数据：

postgres=# insert into temptable values('20240114');
INSERT 0 1
postgres=# select * from temptable;
   name   
----------
 zhw
 zbh
 lx
 20240114
(4 rows)

从库查看数据：

postgres=# select * from temptable;
-[ RECORD 1 ]--
name | zhw
-[ RECORD 2 ]--
name | zbh
-[ RECORD 3 ]--
name | lx
-[ RECORD 4 ]--
name | 20240114

测试成功，撒花。