postgresql:replikacio
Tartalomjegyzék
PostgreSQL streaming replikáció
1. módszer (pg_basebackup)
Primary
[postgres@pg01 data]$ cat <<EOF | psql SHOW wal_level; SHOW max_wal_senders; SHOW max_replication_slots; SHOW wal_keep_size; SHOW archive_mode; SHOW archive_command; SHOW listen_addresses; EOF wal_level ----------- replica (1 row) max_wal_senders ----------------- 10 (1 row) max_replication_slots ----------------------- 10 (1 row) wal_keep_size --------------- 0 (1 row) archive_mode -------------- off (1 row) archive_command ----------------- (disabled) (1 row) listen_addresses ------------------ * (1 row)
Standby
[root@pg02 ~]# systemctl stop postgresql-18 [root@pg02 ~]# su - postgres [postgres@pg02 ~]$ rm -rf $PGDATA/* [postgres@pg02 ~]$ pg_basebackup -h 192.168.100.71 -U postgres -D /var/lib/pgsql/18/data -X stream -P -R 23747/23747 kB (100%), 1/1 tablespace [postgres@pg02 ~]$ ls -l $PGDATA/standby.signal -rw-------. 1 postgres postgres 0 Apr 21 03:55 /var/lib/pgsql/18/data/standby.signal [postgres@pg02 ~]$ cat $PGDATA/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=192.168.100.71 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
Ellenőrzés (primary)
[postgres@pg01 data]$ psql -x -c 'select * from pg_stat_replication' -[ RECORD 1 ]----+------------------------------ pid | 367 usesysid | 10 usename | postgres application_name | walreceiver client_addr | 192.168.100.72 client_hostname | client_port | 43906 backend_start | 2026-04-21 03:58:36.752383+02 backend_xmin | state | streaming sent_lsn | 0/E000060 write_lsn | 0/E000060 flush_lsn | 0/E000060 replay_lsn | 0/E000060 write_lag | 00:00:00.009987 flush_lag | 00:00:00.010273 replay_lag | 00:00:00.010329 sync_priority | 0 sync_state | async reply_time | 2026-04-21 03:58:36.766307+02
Ellenőrzés (standby)
[postgres@pg02 ~]$ psql -x -c 'SELECT * FROM pg_stat_wal_receiver' -[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ pid | 276 status | streaming receive_start_lsn | 0/E000000 receive_start_tli | 1 written_lsn | 0/E000168 flushed_lsn | 0/E000168 received_tli | 1 last_msg_send_time | 2026-04-21 04:01:44.521496+02 last_msg_receipt_time | 2026-04-21 04:01:44.521568+02 latest_end_lsn | 0/E000168 latest_end_time | 2026-04-21 04:00:44.499053+02 slot_name | sender_host | 192.168.100.71 sender_port | 5432 conninfo | user=postgres passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=192.168.100.71 port=5432 fallback_application_name=walreceiver sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
Replikáció típus váltás
Standby oldalon módosítani kell az appliction_name paramétert
[postgres@pg02 data]$ cat $PGDATA/postgresql.auto.conf.orig # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=192.168.100.71 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable' [postgres@pg02 data]$ cat $PGDATA/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=192.168.100.71 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable application_name=pg02' [postgres@pg02 data]$ psql -x -c 'SELECT pg_reload_conf()' -[ RECORD 1 ]--+-- pg_reload_conf | t
Ellenőrzés a primary oldalon
[postgres@pg01 data]$ psql -x -c 'select * from pg_stat_replication' -[ RECORD 1 ]----+------------------------------ pid | 388 usesysid | 10 usename | postgres application_name | pg02 client_addr | 192.168.100.72 client_hostname | client_port | 57966 backend_start | 2026-04-21 04:04:49.263132+02 backend_xmin | state | streaming sent_lsn | 0/E000168 write_lsn | 0/E000168 flush_lsn | 0/E000168 replay_lsn | 0/E000168 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2026-04-21 04:05:09.28512+02
Replikáció módosítása
[postgres@pg01 data]$ psql -c 'SHOW synchronous_standby_names' synchronous_standby_names --------------------------- (1 row) [postgres@pg01 data]$ psql -c "ALTER SYSTEM SET synchronous_standby_names = 'pg02'" ALTER SYSTEM [postgres@pg01 data]$ psql -c "SELECT pg_reload_conf();" pg_reload_conf ---------------- t (1 row) [postgres@pg01 data]$ psql -c 'SHOW synchronous_standby_names' synchronous_standby_names --------------------------- pg02 (1 row) [postgres@pg01 data]$ psql -x -c 'select * from pg_stat_replication' -[ RECORD 1 ]----+------------------------------ pid | 388 usesysid | 10 usename | postgres application_name | pg02 client_addr | 192.168.100.72 client_hostname | client_port | 57966 backend_start | 2026-04-21 04:04:49.263132+02 backend_xmin | state | streaming sent_lsn | 0/E000168 write_lsn | 0/E000168 flush_lsn | 0/E000168 replay_lsn | 0/E000168 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2026-04-21 04:12:48.93553+02
Teszteléshez állítsuk le a standby oldalon a PostgreSQL szerver, majd primary oldalon próbáljunk létrehozni egy új adatbázist.
Visszaállítás
[postgres@pg01 data]$ psql -c 'ALTER SYSTEM RESET synchronous_standby_names' ALTER SYSTEM [postgres@pg01 data]$ psql -c "SELECT pg_reload_conf();" pg_reload_conf ---------------- t (1 row) [postgres@pg01 data]$ psql -x -c 'select * from pg_stat_replication' -[ RECORD 1 ]----+------------------------------ pid | 463 usesysid | 10 usename | postgres application_name | pg02 client_addr | 192.168.100.72 client_hostname | client_port | 46046 backend_start | 2026-04-21 04:19:05.289065+02 backend_xmin | state | streaming sent_lsn | 0/E87E830 write_lsn | 0/E87E830 flush_lsn | 0/E87E830 replay_lsn | 0/E87E830 write_lag | 00:00:00.00049 flush_lag | 00:00:00.001067 replay_lag | 00:00:00.001073 sync_priority | 0 sync_state | async reply_time | 2026-04-21 04:19:39.612299+02
2. módszer (rsync)
[root@pg01 ~]# systemctl is-active postgresql-18 active
[root@pg02 ~]# systemctl stop postgresql-18 [root@pg02 ~]# su - postgres [postgres@pg02 ~]$ rm -rf $PGDATA/* [root@pg02 ~]# rsync -a root@pg01:/var/lib/pgsql/18/data/ /var/lib/pgsql/18/data/ receiving incremental file list sent 24,579 bytes received 116,074,568 bytes 21,108,935.82 bytes/sec total size is 115,970,939 speedup is 1.00
[root@pg01 ~]# systemctl stop postgresql-18 [root@pg01 ~]# systemctl is-active postgresql-18 inactive
[root@pg02 ~]# rsync -avc root@192.168.100.71:/var/lib/pgsql/18/data/ /var/lib/pgsql/18/data/ root@192.168.100.71's password: receiving incremental file list ./ global/pg_control log/postgresql-Tue.log pg_logical/ pg_stat/ pg_stat/pgstat.stat pg_wal/00000001000000000000000E sent 24,877 bytes received 196,380 bytes 63,216.29 bytes/sec total size is 116,101,403 speedup is 524.74
[root@pg01 ~]# systemctl start postgresql-18 [root@pg01 ~]# systemctl is-active postgresql-18 active
[root@pg02 ~]# chown -R postgres:postgres /var/lib/pgsql/18/data [root@pg02 ~]# cp -a /var/lib/pgsql/18/data/postgresql.auto.conf /var/lib/pgsql/18/data/postgresql.auto.conf.orig [root@pg02 ~]# echo "primary_conninfo = 'user=postgres host=pg01 port=5432 application_name=pg02'" >> /var/lib/pgsql/18/data/postgresql.auto.conf [root@pg02 ~]# touch /var/lib/pgsql/18/data/standby.signal [root@pg02 ~]# chmod 0600 /var/lib/pgsql/18/data/standby.signal [root@pg02 ~]# chown postgres:postgres /var/lib/pgsql/18/data/standby.signal [root@pg02 ~]# systemctl start postgresql-18
[root@pg01 ~]# su - postgres [postgres@pg01 ~]$ psql -x -c 'select * from pg_stat_replication' -[ RECORD 1 ]----+------------------------------ pid | 743 usesysid | 10 usename | postgres application_name | pg02 client_addr | 192.168.100.72 client_hostname | client_port | 47134 backend_start | 2026-04-21 04:42:27.779197+02 backend_xmin | state | streaming sent_lsn | 0/E87EB80 write_lsn | 0/E87EB80 flush_lsn | 0/E87EB80 replay_lsn | 0/E87EB80 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2026-04-21 04:45:18.963055+02
postgresql/replikacio.txt · legutóbbi módosítás: – Szerkesztő: riba.zoltan