部署PG一主一从

1.准备数据库安装包
二进制包。解压
tar xvf postgresql.tar.gz
mkdir -p /data/pgsql/12/{archive,data,log} #归档目录 ,数据目录 ,日志文件目录

2.主备参数文件
vi postgresql.conf

listen_addresses = '0.0.0.0'
port = 5432
max_connections = 100
unix_socket_directories = '.'
superuser_reserved_connections = 10
 
ssl = off
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
 
#02 - Memory-Resource
#shared_buffers
shared_buffers = 1GB
maintenance_work_mem = 256MB
autovacuum_work_mem = 256MB
temp_buffers = 64MB
work_mem = 5592kB
huge_pages = try
dynamic_shared_memory_type = posix
 
bgwriter_delay = 10ms
effective_io_concurrency = 0
max_worker_processes = 20
max_parallel_workers = 20
old_snapshot_threshold = 3h
 
#03 - WAL-Checkpoint-Archiving
wal_level = replica
wal_log_hints = on
wal_buffers = 16MB
synchronous_commit = off
 
min_wal_size = 2GB
max_wal_size = 1GB
checkpoint_timeout = 30min
checkpoint_warning = 60s
checkpoint_completion_target = 0.9
 
archive_mode = always
archive_command = 'test ! -f /data/pgsql/12/archive/%f && cp %p /data/pgsql/12/archive/%f'
archive_timeout = 1800
 
#04 - Replication
max_wal_senders = 32
max_replication_slots = 10
wal_sender_timeout = 600s
track_commit_timestamp = on
 
#synchronous_standby_names = ''
hot_standby = on
hot_standby_feedback = on
 
#05 - Reporting-Logging
#log_destination = 'csvlog'
log_destination = 'stderr'
logging_collector = on
log_directory = '/data/pgsql/12/log'
log_filename = 'postgresql-%m-%d.log'
log_line_prefix = '%t:%r:%u@%d:[%p]: '
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_timezone = 'Asia/Shanghai'
 
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_statement = 'ddl'
log_replication_commands = on
log_temp_files = -1
cluster_name = 'kong-cluster'
 
#06 - QueryTuning-Statistics-Autovacuum
effective_cache_size = 1GB
default_statistics_target = 200
track_activities = on
track_counts = on
track_io_timing = on
track_functions = pl
track_activity_query_size = 10240
 
autovacuum = on
autovacuum_max_workers = 5
log_autovacuum_min_duration = 1000
 
#07 - Lock
deadlock_timeout = 5s
max_locks_per_transaction = 1024
 
#08 - Client Connection
timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
 
#09 - pg_stat_statements-auto_explain
shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.track_utility = true
pg_stat_statements.save = true
auto_explain.log_min_duration = 10s
auto_explain.log_nested_statements = on
auto_explain.log_analyze = true
auto_explain.log_verbose = true
auto_explain.log_timing = true
auto_explain.log_buffers = true
auto_explain.log_format = json
primary_conninfo = 'application_name=kong1 user=repl password=repl host=1x2.x1.1x.19 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'

vi pg_hba.conf

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
 
host replication repl 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5

3.初始化2个节点。

su - postgres
vi .bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/data/pgsql/12/bin
export PATH
export PGDATA=/data/pgsql/12/data

–初始化数据。两个节点都初始化。
initdb -D /postgresql/data -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8 -U postgres

创建复制用。
psql

create role repuser login encrypted password ‘repl’ replication;

4.删除从库，重做从库。
pg_ctl stop
rm -rf /data/

pg_basebackup -h 1x2.x1.1x.19 -p 5432 -U repl -W -X stream -F p -P -R -D /data/pgsql/12/data -l backup20250401

–启动从库。
pg_ctl start

5.检查主从是否同步 。

–主库检查

[root@host-1x2-x1-1x-19 ~]# ps -ef |grep postgres
postgres 14594 1 0 Mar26 ? 00:00:24 /data/pgsql/12/bin/postgres
postgres 14595 14594 0 Mar26 ? 00:00:05 postgres: kong-cluster: logger
postgres 14597 14594 0 Mar26 ? 00:00:00 postgres: kong-cluster: checkpointer
postgres 14598 14594 0 Mar26 ? 00:02:55 postgres: kong-cluster: background writer
postgres 14599 14594 0 Mar26 ? 00:00:09 postgres: kong-cluster: walwriter
postgres 14600 14594 0 Mar26 ? 00:00:12 postgres: kong-cluster: autovacuum launcher
postgres 14601 14594 0 Mar26 ? 00:00:01 postgres: kong-cluster: archiver last was 00000001000000000000000B # 数据库处于归档状态。
postgres 14602 14594 0 Mar26 ? 00:00:42 postgres: kong-cluster: stats collector
postgres 14603 14594 0 Mar26 ? 00:00:00 postgres: kong-cluster: logical replication launcher
postgres 65430 14594 0 11:53 ? 00:00:00 postgres: kong-cluster: walsender repl 1x2.x1.1x.41(54282) streaming 0/C000000 #有一个从库正在进行流复制。
postgres 66064 14594 0 13:38 ? 00:00:00 postgres: kong-cluster: kong kong 1x2.x1.1x.186(64780) idle
postgres 66068 14594 0 13:39 ? 00:00:00 postgres: kong-cluster: kong kong 1x2.x1.1x.1x6(4183) idle
postgres 66077 14594 0 13:41 ? 00:00:00 postgres: kong-cluster: kong kong 1x2.x1.1x.186(41246) idle
postgres 66081 14594 0 13:42 ? 00:00:00 postgres: kong-cluster: kong kong 1x2.x1.1x.1x6(10129) idle
postgres 66082 14594 0 13:42 ? 00:00:00 postgres: kong-cluster: kong kong 1x2.x1.1x.186(35745) idle
root 66100 66084 0 13:42 pts/0 00:00:00 grep --color=auto postgres

从库查看

[postgres@host-1x2-x1-1x-41 ~]$ ps -ef |grep postgres
postgres 40043 1 0 11:53 ? 00:00:00 /data/pgsql/12/bin/postgres
postgres 40044 40043 0 11:53 ? 00:00:00 postgres: kong-cluster: logger #集群的名称叫 Kong-cluster
postgres 40045 40043 0 11:53 ? 00:00:00 postgres: kong-cluster: startup recovering 00000001000000000000000B #正在恢复状态。
postgres 40046 40043 0 11:53 ? 00:00:00 postgres: kong-cluster: checkpointer
postgres 40047 40043 0 11:53 ? 00:00:03 postgres: kong-cluster: background writer
postgres 40048 40043 0 11:53 ? 00:00:00 postgres: kong-cluster: archiver last was 00000001000000000000000A
postgres 40049 40043 0 11:53 ? 00:00:00 postgres: kong-cluster: stats collector
postgres 40050 40043 0 11:53 ? 00:00:08 postgres: kong-cluster: walreceiver streaming 0/C000000 #正在接收wal日志。

–从库日志查看 。

2025-04-01 11:53:41 CST:😡:[40045]: LOG: entering standby mode #备库模式。
2025-04-01 11:53:41 CST:😡:[40045]: LOG: redo starts at 0/A000028
2025-04-01 11:53:41 CST:😡:[40045]: LOG: consistent recovery state reached at 0/A000138 #恢复位置。
2025-04-01 11:53:41 CST:😡:[40043]: LOG: database system is ready to accept read only connections
2025-04-01 11:53:41 CST:😡:[40050]: LOG: started streaming WAL from primary at 0/B000000 on timeline 1
2025-04-01 11:56:31 CST:[local]:[unknown]@[unknown]:[40057]: LOG: connection received: host=[local]
2025-04-01 11:56:31 CST:[local]:postgres@postgres:[40057]: LOG: connection authorized: user=postgres database=postgres application_name=psql
2025-04-01 11:56:40 CST:[local]:[unknown]@[unknown]:[40058]: LOG: connection received: host=[local]
2025-04-01 11:56:40 CST:[local]:postgres@kong:[40058]: LOG: connection authorized: user=postgres database=kong application_name=psql
2025-04-01 11:56:40 CST:[local]:postgres@postgres:[40057]: LOG: disconnection: session time: 0:00:08.670 user=postgres database=postgres host=[local]
2025-04-01 11:56:47 CST:[local]:postgres@kong:[40058]: LOG: disconnection: session time: 0:00:07.108 user=postgres database=kong host=[local]
2025-04-01 12:23:41 CST:😡:[40046]: LOG: restartpoint starting: time
2025-04-01 12:23:43 CST:😡:[40046]: LOG: restartpoint complete: wrote 0 buffers (0.0%); 1 WAL file(s) added, 0 removed, 0 recycled; write=0.027 s, sync=0.000 s, total=1.924 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=16384 kB
2025-04-01 12:23:43 CST:😡:[40046]: LOG: recovery restart point at 0/B000060

–主库里面查看复制的从库。

[postgres@host-1x2-x1-1x-19 ~]$ psql
psql (12.3)
Type “help” for help.

postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
--------------±-----------
1x2.x1.1x.41 | async
(1 row)

postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------±---------±--------±-----------------±-------------±----------------±------------±------------------------------±-------------±----------±----------±----------±----------±-----------±----------±----------±-----------±--------------±-----------±------------------------------
65430 | 16386 | repl | kong-cluster | 1x2.x1.1x.41 | | 54282 | 2025-04-01 11:53:41.839507+08 |854 | streaming | 0/C000000 | 0/C000000 | 0/C000000 | 0/C000000 | | | | 0 | async| 2025-04-01 13:50:13.664137+08 #恢复时间。接近当前时间表示同步，与当前时间相差很远可能不同步。
(1 row)