一、系统配置
1、配置hosts
172.16.220.151 Euler1 172.16.220.152 Euler2 172.16.220.153 Euler3复制
2、添加用户及环境变量
2节点添加
[root@Euler2 ~]# useradd postgres [root@Euler2 ~]# id postgres uid=1001(postgres) gid=1001(postgres) groups=1001(postgres) [postgres@Euler2 ~]$ vi .bash_profile # Source /root/.bashrc if user has one [ -f ~/.bashrc ] && . ~/.bashrc export PGHOME=/postgresql export PATH=$PGHOME/bin:$PATH export PATH=$PGHOME/bin:$PATH:$HOME/bin export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PGUSER=postgres export PGPORT=5432 export PGDATA=$PGHOME/data export MANPATH=/postgresql/share/man:$MANPATH复制
3节点添加
[root@Euler3 ~]# useradd postgres [root@Euler3 ~]# id postgres uid=1001(postgres) gid=1001(postgres) groups=1001(postgres) [postgres@Euler3 ~]$ vi .bash_profile # Source /root/.bashrc if user has one [ -f ~/.bashrc ] && . ~/.bashrc export PGHOME=/postgresql export PATH=$PGHOME/bin:$PATH export PATH=$PGHOME/bin:$PATH:$HOME/bin export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PGUSER=postgres export PGPORT=5432 export PGDATA=$PGHOME/data export MANPATH=/postgresql/share/man:$MANPATH ~复制
二、数据库配置
1、主库开启归档
[postgres@Euler1 data]$ vi postgresql.conf wal_level= replica archive_mode=on #archive_command='/postgresql/archive' archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f' max_wal_senders=10 wal_sender_timeout=60s复制
2、重启主库
[postgres@Euler1 data]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@Euler1 data]$ pg_ctl start waiting for server to start....2023-08-15 14:09:10.713 CST [224295] LOG: 00000: redirecting log output to logging collector process 2023-08-15 14:09:10.713 CST [224295] HINT: Future log output will appear in directory "log". 2023-08-15 14:09:10.713 CST [224295] LOCATION: SysLogger_Start, syslogger.c:715 done server started复制
实际操作中，重启可以等待主库全部操作完成后重启
3、检查确认参数
postgres=# show wal_level; wal_level ----------- replica (1 row) postgres=# show archive_mode; archive_mode -------------- on (1 row) postgres=# show archive_command; archive_command ------------------------------------------------------------------ test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/4C27168 (1 row)复制
5、主库创建复制用户
postgres=# create role repuser login encrypted password 'repuser' replication; CREATE ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- cy | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} repuser | Replication | {}复制
6、调整访问限制
# replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host all all 172.16.220.1/32 trust host replication repuser 0.0.0.0/0 md5复制
一定需要添加replication参数，如果使用all是无法复制的。
7、加载参数
[postgres@Euler1 data]$ pg_ctl reload server signaled复制
三、复制数据库
1、打包主库安装文件
如果标准化安装，data文件单独存放，不用打包data目录
[root@Euler1 tmp]# tar -zcvf postgres.tar.gz /postgresql tar: Removing leading `/' from member names复制
2、发送软件
[root@Euler2 tmp]# scp Euler1:/tmp/postgres.tar.gz ./ root@euler1's password: postgres.tar.gz复制
[root@Euler2 tmp]# cd / [root@Euler2 /]# tar -zxvf /tmp/postgres.tar.gz复制
[root@Euler3 /]# tar -zxvf postgres.tar.gz postgresql/ postgresql/bin/复制
3、需要删除主库数据
最简单的数据库克隆其实就可以直接复制数据库文件及软件
[root@Euler2 postgresql]# rm -rf data/ [root@Euler3 postgresql]# ls archive bin data gis include lib share [root@Euler3 postgresql]# rm -rf data/复制
4、开启主库
打包数据库软件时需要关闭主库
To run a command as administrator(user "root"),use "sudo <command>". [postgres@Euler1 ~]$ pg_ctl start waiting for server to start....2023-08-15 15:56:03.190 CST [242010] LOG: 00000: redirecting log output to logging collector process 2023-08-15 15:56:03.190 CST [242010] HINT: Future log output will appear in directory "log". 2023-08-15 15:56:03.190 CST [242010] LOCATION: SysLogger_Start, syslogger.c:715 done server started复制
5、复制数据库
类似rman duplicate
[postgres@Euler2 ~]$ pg_basebackup -D /postgresql/data -F p -P -R -h Euler1 -p 5432 -U repuser -l backup20230816 Password: 54727/54727 kB (100%), 1/1 tablespace [postgres@Euler2 ~]$复制
检查复制结果
[postgres@Euler2 data]$ ls -lsa total 396 4 drwx------ 20 postgres postgres 4096 Aug 16 18:16 . 4 drwxrwxr-x 9 postgres postgres 4096 Aug 16 18:16 .. 4 -rw------- 1 postgres postgres 214 Aug 16 18:16 backup_label 252 -rw------- 1 postgres postgres 256022 Aug 16 18:16 backup_manifest 4 drwx------ 7 postgres postgres 4096 Aug 16 18:16 base 4 -rw------- 1 postgres postgres 30 Aug 16 18:16 current_logfiles 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 global 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 log 4 -rw------- 1 postgres postgres 818 Aug 16 18:16 logfile.log 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_commit_ts 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_dynshmem 8 -rw------- 1 postgres postgres 4870 Aug 16 18:16 pg_hba.conf 4 -rw------- 1 postgres postgres 1636 Aug 16 18:16 pg_ident.conf 4 drwx------ 4 postgres postgres 4096 Aug 16 18:16 pg_logical 4 drwx------ 4 postgres postgres 4096 Aug 16 18:16 pg_multixact 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_notify 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_replslot 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_serial 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_snapshots 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_stat 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_stat_tmp 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_subtrans 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_tblspc 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_twophase 4 -rw------- 1 postgres postgres 3 Aug 16 18:16 PG_VERSION 4 drwx------ 3 postgres postgres 4096 Aug 16 18:16 pg_wal 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_xact 4 -rw------- 1 postgres postgres 324 Aug 16 18:16 postgresql.auto.conf 32 -rw------- 1 postgres postgres 30220 Aug 16 18:16 postgresql.conf 0 -rw------- 1 postgres postgres 0 Aug 16 18:16 standby.signal复制
复制的数据库会多出一个standby.signal文件
6、调整备库参数
备库配置文件需要添加主库的连接信息并启动备库
primary_conninfo = 'host=172.16.220.151 port=5432 user=repuser passowrd=repuser' ~ "postgresql.conf" 845L, 30304C written [postgres@Euler2 data]$ pg_ctl start waiting for server to start....2023-08-16 18:24:48.211 CST [121877] LOG: 00000: redirecting log output to logging collector process 2023-08-16 18:24:48.211 CST [121877] HINT: Future log output will appear in directory "log". 2023-08-16 18:24:48.211 CST [121877] LOCATION: SysLogger_Start, syslogger.c:715 done server started [postgres@Euler2 data]$复制
检查备库文件
[postgres@Euler2 data]$ ls -lsa total 404 4 drwx------ 20 postgres postgres 4096 Aug 16 18:24 . 4 drwxrwxr-x 9 postgres postgres 4096 Aug 16 18:16 .. 4 -rw------- 1 postgres postgres 214 Aug 16 18:16 backup_label.old 252 -rw------- 1 postgres postgres 256022 Aug 16 18:16 backup_manifest 4 drwx------ 7 postgres postgres 4096 Aug 16 18:16 base 4 -rw------- 1 postgres postgres 30 Aug 16 18:24 current_logfiles 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 global 4 drwx------ 2 postgres postgres 4096 Aug 16 18:24 log 4 -rw------- 1 postgres postgres 818 Aug 16 18:16 logfile.log 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_commit_ts 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_dynshmem 8 -rw------- 1 postgres postgres 4870 Aug 16 18:16 pg_hba.conf 4 -rw------- 1 postgres postgres 1636 Aug 16 18:16 pg_ident.conf 4 drwx------ 4 postgres postgres 4096 Aug 16 18:16 pg_logical 4 drwx------ 4 postgres postgres 4096 Aug 16 18:16 pg_multixact 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_notify 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_replslot 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_serial 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_snapshots 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_stat 4 drwx------ 2 postgres postgres 4096 Aug 16 18:24 pg_stat_tmp 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_subtrans 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_tblspc 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_twophase 4 -rw------- 1 postgres postgres 3 Aug 16 18:16 PG_VERSION 4 drwx------ 3 postgres postgres 4096 Aug 16 18:24 pg_wal 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_xact 4 -rw------- 1 postgres postgres 324 Aug 16 18:16 postgresql.auto.conf 32 -rw------- 1 postgres postgres 30304 Aug 16 18:24 postgresql.conf 4 -rw------- 1 postgres postgres 25 Aug 16 18:24 postmaster.opts 4 -rw------- 1 postgres postgres 76 Aug 16 18:24 postmaster.pid 0 -rw------- 1 postgres postgres 0 Aug 16 18:16 standby.signal复制
检查备库进程，多出了startup、walreceiver进程
[postgres@Euler2 ~]$ ps -ef|grep postgres postgres 121877 1 0 18:24 ? 00:00:00 /postgresql/bin/postgres postgres 121878 121877 0 18:24 ? 00:00:00 postgres: logger postgres 121879 121877 0 18:24 ? 00:00:00 postgres: checkpointer postgres 121880 121877 0 18:24 ? 00:00:00 postgres: background writer postgres 121881 121877 0 18:24 ? 00:00:00 postgres: startup recovering 000000010000000000000009 postgres 121882 121877 0 18:24 ? 00:00:00 postgres: walreceiver streaming 0/9000148 root 122796 81868 0 18:27 pts/0 00:00:00 su - postgres postgres 122797 122796 0 18:27 pts/0 00:00:00 -bash postgres 122854 122797 0 18:27 pts/0 00:00:00 ps -ef postgres 122855 122797 0 18:27 pts/0 00:00:00 grep --color=auto postgres复制
检查主库进程，存在archiver、walsender进程
[postgres@Euler1 ~]$ ps -ef|grep postgres root 241817 164074 0 15:55 pts/1 00:00:00 su - postgres postgres 241818 241817 0 15:55 pts/1 00:00:00 -bash postgres 242010 1 0 15:56 ? 00:00:00 /postgresql/bin/postgres postgres 242011 242010 0 15:56 ? 00:00:00 postgres: logger postgres 242012 242010 0 15:56 ? 00:00:00 postgres: checkpointer postgres 242013 242010 0 15:56 ? 00:00:00 postgres: background writer postgres 242015 242010 0 15:56 ? 00:00:00 postgres: walwriter postgres 242016 242010 0 15:56 ? 00:00:00 postgres: autovacuum launcher postgres 242017 242010 0 15:56 ? 00:00:00 postgres: archiver last was 000000010000000000000008.00000028.backup postgres 242018 242010 0 15:56 ? 00:00:00 postgres: logical replication launcher postgres 243765 242010 0 16:07 ? 00:00:00 postgres: walsender repuser 172.16.220.152(45884) streaming 0/9000148 postgres 244351 241818 0 16:11 pts/1 00:00:00 ps -ef postgres 244352 241818 0 16:11 pts/1 00:00:00 grep --color=auto postgres复制
7、检查复制状态
[postgres@Euler1 ~]$ psql psql (15.3) Type "help" for help. postgres=# \x Expanded display is on. postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 243765 usesysid | 37482 usename | repuser application_name | walreceiver client_addr | 172.16.220.152 client_hostname | client_port | 45884 backend_start | 2023-08-15 16:07:08.511256+08 backend_xmin | state | streaming sent_lsn | 0/9000148 write_lsn | 0/9000148 flush_lsn | 0/9000148 replay_lsn | 0/9000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-08-16 18:30:08.818949+08复制
8、测试
主库 postgres=# create table t as select * from pg_settings; SELECT 357 备库 postgres=# select count(*) from t; count ------- 357 (1 row)复制
四、切换
1、关闭主库
模拟主库宕机。没有switch over和fail over区分
[postgres@Euler1 ~]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@Euler1 ~]$复制
2、备库状态检查
此时in archive recovery。还处于备库状态
[postgres@Euler2 ~]$ pg_controldata pg_control version number: 1300 Catalog version number: 202209061 Database system identifier: 7260197151921927327 Database cluster state: in archive recovery pg_control last modified: Wed 16 Aug 2023 07:24:51 PM CST Latest checkpoint location: 0/903FCC0 Latest checkpoint's REDO location: 0/903FC88 Latest checkpoint's REDO WAL file: 000000010000000000000009 Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0:986 Latest checkpoint's NextOID: 45675 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 716 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 986 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint: Tue 15 Aug 2023 04:17:48 PM CST Fake LSN counter for unlogged rels: 0/3E8 Minimum recovery ending location: 0/A000000 Min recovery ending loc's timeline: 1 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no wal_level setting: replica wal_log_hints setting: off max_connections setting: 100 max_worker_processes setting: 10 max_wal_senders setting: 10 max_prepared_xacts setting: 0 max_locks_per_xact setting: 64 track_commit_timestamp setting: off Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float8 argument passing: by value Data page checksum version: 0 Mock authentication nonce: bb306323291b193b03c0e9cf5d5e45fdb471a34c38b0d8e029c726d940b768d6复制
3、切换
^ postgres=# select pg_promote(true,60); pg_promote ------------ t (1 row) postgres=#复制
4、切换后
此时in production，成为主库
[postgres@Euler2 ~]$ pg_controldata pg_control version number: 1300 Catalog version number: 202209061 Database system identifier: 7260197151921927327 Database cluster state: in production pg_control last modified: Wed 16 Aug 2023 07:26:33 PM CST Latest checkpoint location: 0/A000090 Latest checkpoint's REDO location: 0/A000058 Latest checkpoint's REDO WAL file: 00000002000000000000000A Latest checkpoint's TimeLineID: 2 Latest checkpoint's PrevTimeLineID: 2 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0:986 Latest checkpoint's NextOID: 45675 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 716 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 986 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint: Wed 16 Aug 2023 07:26:33 PM CST Fake LSN counter for unlogged rels: 0/3E8 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no wal_level setting: replica wal_log_hints setting: off max_connections setting: 100 max_worker_processes setting: 10 max_wal_senders setting: 10 max_prepared_xacts setting: 0 max_locks_per_xact setting: 64 track_commit_timestamp setting: off Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float8 argument passing: by value Data page checksum version: 0 Mock authentication nonce: bb306323291b193b03c0e9cf5d5e45fdb471a34c38b0d8e029c726d940b768d6复制
进程存在archiver、没有walsender
[postgres@Euler2 ~]$ ps -ef|grep postgres postgres 121877 1 0 18:24 ? 00:00:00 /postgresql/bin/postgres postgres 121878 121877 0 18:24 ? 00:00:00 postgres: logger postgres 121879 121877 0 18:24 ? 00:00:00 postgres: checkpointer postgres 121880 121877 0 18:24 ? 00:00:00 postgres: background writer root 124878 124306 0 18:37 pts/1 00:00:00 su - postgres postgres 124879 124878 0 18:37 pts/1 00:00:00 -bash postgres 134405 121877 0 19:26 ? 00:00:00 postgres: walwriter postgres 134406 121877 0 19:26 ? 00:00:00 postgres: autovacuum launcher postgres 134407 121877 0 19:26 ? 00:00:00 postgres: archiver last was 000000010000000000000009 postgres 134408 121877 0 19:26 ? 00:00:00 postgres: logical replication launcher postgres 135082 124879 0 19:29 pts/1 00:00:00 ps -ef postgres 135083 124879 0 19:29 pts/1 00:00:00 grep --color=auto postgres复制
5、测试读写
postgres=# create table t3 as select * from pg_settings; SELECT 357 postgres=#复制
[postgres@Euler2 data]$ ls -lsa total 404 4 drwx------ 20 postgres postgres 4096 Aug 16 19:26 . 4 drwxrwxr-x 9 postgres postgres 4096 Aug 16 18:16 .. 4 -rw------- 1 postgres postgres 214 Aug 16 18:16 backup_label.old 252 -rw------- 1 postgres postgres 256022 Aug 16 18:16 backup_manifest 4 drwx------ 7 postgres postgres 4096 Aug 16 18:16 base 4 -rw------- 1 postgres postgres 30 Aug 16 18:24 current_logfiles 4 drwx------ 2 postgres postgres 4096 Aug 16 18:32 global 4 drwx------ 2 postgres postgres 4096 Aug 16 18:24 log 4 -rw------- 1 postgres postgres 818 Aug 16 18:16 logfile.log 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_commit_ts 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_dynshmem 8 -rw------- 1 postgres postgres 4870 Aug 16 18:16 pg_hba.conf 4 -rw------- 1 postgres postgres 1636 Aug 16 18:16 pg_ident.conf 4 drwx------ 4 postgres postgres 4096 Aug 16 19:31 pg_logical 4 drwx------ 4 postgres postgres 4096 Aug 16 18:16 pg_multixact 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_notify 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_replslot 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_serial 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_snapshots 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_stat 4 drwx------ 2 postgres postgres 4096 Aug 16 18:24 pg_stat_tmp 4 drwx------ 2 postgres postgres 4096 Aug 16 18:29 pg_subtrans 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_tblspc 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_twophase 4 -rw------- 1 postgres postgres 3 Aug 16 18:16 PG_VERSION 4 drwx------ 3 postgres postgres 4096 Aug 16 19:31 pg_wal 4 drwx------ 2 postgres postgres 4096 Aug 16 18:16 pg_xact 4 -rw------- 1 postgres postgres 324 Aug 16 18:16 postgresql.auto.conf 32 -rw------- 1 postgres postgres 30304 Aug 16 18:24 postgresql.conf 4 -rw------- 1 postgres postgres 25 Aug 16 18:24 postmaster.opts 4 -rw------- 1 postgres postgres 76 Aug 16 19:26 postmaster.pid复制
五、故障修复
1、重建从库
将原主库调整为备库
调整pg_hba.conf
host replication repuser 0.0.0.0/0 md5复制
调整postgresql.conf参数
#primary_conninfo = 'host=172.16.220.151 port=5432 user=repuser passowrd=repuser' --注释复制
重建从库
[postgres@Euler1 postgresql]$ pg_basebackup -D /postgresql/data -F p -P -R -h Euler2 -p 5432 -U repuser -l backup20230817 Password: 54983/54983 kB (100%), 1/1 tablespace复制
[postgres@Euler1 data]$ ls -lrt total 392 -rw------- 1 postgres postgres 4870 Aug 15 17:27 pg_hba.conf -rw------- 1 postgres postgres 214 Aug 15 17:27 backup_label drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_twophase drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_stat_tmp drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_snapshots drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_replslot drwx------ 4 postgres postgres 4096 Aug 15 17:27 pg_logical drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_dynshmem -rw------- 1 postgres postgres 818 Aug 15 17:27 logfile.log -rw------- 1 postgres postgres 214 Aug 15 17:27 backup_label.old -rw------- 1 postgres postgres 30305 Aug 15 17:27 postgresql.conf -rw------- 1 postgres postgres 3 Aug 15 17:27 PG_VERSION drwx------ 4 postgres postgres 4096 Aug 15 17:27 pg_multixact drwx------ 3 postgres postgres 4096 Aug 15 17:27 pg_wal drwx------ 7 postgres postgres 4096 Aug 15 17:27 base drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_serial -rw------- 1 postgres postgres 1636 Aug 15 17:27 pg_ident.conf -rw------- 1 postgres postgres 30 Aug 15 17:27 current_logfiles -rw------- 1 postgres postgres 560 Aug 15 17:27 postgresql.auto.conf drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_tblspc drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_notify drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_commit_ts drwx------ 2 postgres postgres 4096 Aug 15 17:27 log drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_xact drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_subtrans drwx------ 2 postgres postgres 4096 Aug 15 17:27 pg_stat drwx------ 2 postgres postgres 4096 Aug 15 17:27 global -rw------- 1 postgres postgres 0 Aug 15 17:27 standby.signal -rw------- 1 postgres postgres 257316 Aug 15 17:27 backup_manifest复制
[postgres@Euler1 data]$ vi standby.signal standby_mode='on'复制
[postgres@Euler1 data]$ vi postgresql.conf primary_conninfo = 'host=172.16.220.152 port=5432 user=repuser passowrd=repuser'复制
[postgres@Euler1 data]$ pg_ctl start waiting for server to start....2023-08-15 17:31:08.817 CST [257727] LOG: 00000: redirecting log output to logging collector process 2023-08-15 17:31:08.817 CST [257727] HINT: Future log output will appear in directory "log". 2023-08-15 17:31:08.817 CST [257727] LOCATION: SysLogger_Start, syslogger.c:715 done server started复制
postgres=# \x Expanded display is on. postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 138168 usesysid | 37482 usename | repuser application_name | walreceiver client_addr | 172.16.220.151 client_hostname | client_port | 28304 backend_start | 2023-08-16 19:46:51.069946+08 backend_xmin | state | streaming sent_lsn | 0/D047E08 write_lsn | 0/D047E08 flush_lsn | 0/D047E08 replay_lsn | 0/D047E08 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-08-15 17:33:03.178873+08复制
2、恢复原主库
1、关闭所有主备库
[postgres@Euler1 data]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@Euler2 data]$ pg_ctl stop waiting for server to shut down.... done server stopped复制
2、修复主库
删除主库standby.signal,注释复制参数
[postgres@Euler1 data]$ rm standby.signal [postgres@Euler1 data]$ vi postgresql.conf #primary_conninfo = 'host=172.16.220.152 port=5432 user=repuser passowrd=repuser'复制
3、启动原主库为主库
[postgres@Euler1 data]$ pg_ctl start waiting for server to start....2023-08-15 17:42:06.182 CST [259331] LOG: 00000: redirecting log output to logging collector process 2023-08-15 17:42:06.182 CST [259331] HINT: Future log output will appear in directory "log". 2023-08-15 17:42:06.182 CST [259331] LOCATION: SysLogger_Start, syslogger.c:715 done server started复制
4、修复备库
将主库降为备库，创建standby.signal，添加主库访问参数
[postgres@Euler2 data]$ vi standby.signal standby_mode='on'复制
vi postgresql.conf primary_conninfo = 'host=172.16.220.151 port=5432 user=repuser passowrd=repuser'复制
[postgres@Euler2 data]$ pg_ctl start waiting for server to start....2023-08-16 20:02:19.737 CST [141542] LOG: 00000: redirecting log output to logging collector process 2023-08-16 20:02:19.737 CST [141542] HINT: Future log output will appear in directory "log". 2023-08-16 20:02:19.737 CST [141542] LOCATION: SysLogger_Start, syslogger.c:715 done复制
六、扩容
添加节点
1、复制主库数据
[postgres@Euler3 postgresql]$ pg_basebackup -D /postgresql/data -F p -P -R -h Euler1 -p 5432 -U repuser -l backup20230817 Password: 55372/55372 kB (100%), 1/1 tablespace [postgres@Euler3 postgresql]$复制
[postgres@Euler3 data]$ vi postgresql.conf primary_conninfo = 'host=172.16.220.151 port=5432 user=repuser passowrd=repuser'复制
[postgres@Euler3 data]$ pg_ctl start waiting for server to start....2023-08-16 20:12:30.412 CST [142711] LOG: 00000: redirecting log output to logging collector process 2023-08-16 20:12:30.412 CST [142711] HINT: Future log output will appear in directory "log". 2023-08-16 20:12:30.412 CST [142711] LOCATION: SysLogger_Start, syslogger.c:715 done server started复制
2、检查复制信息
多出了RECORD 2
postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 259918 usesysid | 37482 usename | repuser application_name | walreceiver client_addr | 172.16.220.152 client_hostname | client_port | 45970 backend_start | 2023-08-15 17:46:37.516353+08 backend_xmin | state | streaming sent_lsn | 0/10000148 write_lsn | 0/10000148 flush_lsn | 0/10000148 replay_lsn | 0/10000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-08-16 20:17:43.479349+08 -[ RECORD 2 ]----+------------------------------ pid | 262479 usesysid | 37482 usename | repuser application_name | walreceiver client_addr | 172.16.220.153 client_hostname | client_port | 44704 backend_start | 2023-08-15 18:00:48.3063+08 backend_xmin | state | streaming sent_lsn | 0/10000148 write_lsn | 0/10000148 flush_lsn | 0/10000148 replay_lsn | 0/10000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-08-16 20:18:02.86149+08复制
七、注意事项
- 1、自定义表空间，pg_basebackup复制主从会出错。
- 2、主从环境中，主库创建表空间目录，备库也需要手工创建。
八、同步与异步
- 异步：主库提交的事务不会等待备库接收并返回
- 同步：要求主库将WAL日志写入磁盘、复制至备库、写入备库磁盘才能返回commit结果，一旦所有备库故障，主库操作会挂起。
1、参数修改
synchronous_commit --synchronization level,off, local, remote_write, remote_apply, or on synchronous_standby_names --'*' = all,当参数为*表示所有，实际机制为保证一台备库同步即可，所以需要一主多备。复制
set synchronous_commit=off 可以会话级别取消同步复制 set synchronous_commit=on 不可以在当前会话开启同步复制复制
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【MySQL 30周年庆】MySQL 8.0 OCP考试限时免费！教你免费领考券
墨天轮小教习
2696次阅读
2025-04-25 18:53:11
2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
2457次阅读
2025-04-09 15:33:27
【DBA坦白局】第一期：在小城市和一线城市做DBA，是“躺”还是“卷”？
墨天轮编辑部
1478次阅读
2025-04-10 14:17:22
2025年4月国产数据库大事记：4个千万级中标项目诞生！2024年达梦净利3.6亿、金仓净利8006.6万……
墨天轮编辑部
1438次阅读
2025-04-30 17:39:54
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
1318次阅读
2025-04-21 16:58:09
数据库国产化替代深化：DBA的机遇与挑战
代晓磊
1136次阅读
2025-04-27 16:53:22
Oracle Concepts（Oracle 19c）：07 SQL
Ryan Bai
1047次阅读
2025-04-09 10:57:11
2025 DBA 薪资观察：做 DBA 还香吗？
墨天轮编辑部
966次阅读
2025-04-24 15:53:21
2025年3月国产数据库中标情况一览：TDSQL大单622万、GaussDB大单581万……
通讯员
816次阅读
2025-04-10 15:35:48
MySQL 30 周年庆！MySQL 8.4 认证免费考！这次是认真的。。。
严少安
803次阅读
2025-04-25 15:30:58
