PG12 primary and secondary stream replication, Point-in-Time Recovery

Contents:

(2)Create an archive directory

(3) Modify the archive parameters

(4) Start the database service

(5) Basic backup

(6) Manufacturing data

(7) Close the database service

(8) Replace data

(9) Create a logo file

(10) Modify recovery parameters

(11) Start the database service and complete the recovery

(12) The verification table has been restored and can be written

  1. Build primary and secondary stream replication

(1) The main end initializes the database

(2) Modify the parameters of the master

(4) The main end starts the database service

(5) Delete data on the standby end

(6) Basic backup of the standby end

[postgrespankajconnect2 pg12]$ pg_basebackup -h 192.168.90.220 -U postgres -Xs -Fp -R -Pv -D data
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot “pg_basebackup_35429”
24588/24588 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming …
pg_basebackup: syncing data to disk …
pg_basebackup: base backup completed
Note: The -R parameter is different from previous versions. The recovery.conf file is no longer generated, but the standby.signal flag file is generated and added to the postgresql.auto.conf file

primary_conninfo

View the generated logo file

(7) The standby end starts the database service

(8) Verify that the streaming replication is set up successfully
Query the active and standby processes

last line of logfile:
postgres 35347 1 0 17:44 ? 00:00:00 /home/postgres/pg12/bin/postgres
postgres 35349 35347 0 17:44 ? 00:00:00 postgres: checkpointer
postgres 35350 35347 0 17:44 ? 00:00:00 postgres: background writer
postgres 35351 35347 0 17:44 ? 00:00:00 postgres: walwriter
postgres 35352 35347 0 17:44 ? 00:00:00 postgres: autovacuum launcher
postgres 35353 35347 0 17:44 ? 00:00:00 postgres: stats collector
postgres 35354 35347 0 17:44 ? 00:00:00 postgres: logical replication launcher
postgres 35454 35347 0 17:55 ? 00:00:00 postgres: walsender postgres 192.168.90.222(65054) streaming 0/3000060

postgres 33828 1 0 17:55 ? 00:00:00 /home/postgres/pg12/bin/postgres
postgres 33829 33828 0 17:55 ? 00:00:00 postgres: startup recovering 000000010000000000000003
postgres 33830 33828 0 17:55 ? 00:00:00 postgres: checkpointer
postgres 33831 33828 0 17:55 ? 00:00:00 postgres: background writer
postgres 33832 33828 0 17:55 ? 00:00:00 postgres: stats collector
postgres 33833 33828 0 17:55 ? 00:00:00 postgres: walreceiver streaming 0/3000060

Master write data

The standby side queries and writes data

postgres=# insert into tb1 values (2,’pankajkk’);
2020–10–22 17:58:48.251 CST [33870] ERROR: cannot execute INSERT in a read-only transaction
2020–10–22 17:58:48.251 CST [33870] STATEMENT: insert into tb1 values (2,’sam’);
ERROR: cannot execute INSERT in a read-only transaction

2. The standby end is upgraded to the main end

(3) Verify that the standby end has been upgraded
Query the new master process

New master writes data

3. The original master is synchronized with the standby

(2) Modified parameters of the original master

(3) The original master executes pg_rewind
[postgres@pankajconnect1 ~]$ pg_rewind — target-pgdata pg12/data — source-server=’host=192.168.90.222 port=5432 user=postgres dbname=postgres’ -P
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/301A240 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/301A1C8 on timeline 1
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 51 MB (total source directory size is 71 MB)
53090/53090 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!

(7) Verify that the original primary end is the standby end
Query the new master process

Query the process of the new standby (original master)

New master writes data

The new standby end (original main end) queries and writes data

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS