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

Contents:

1. PITR steps
(1) Initialize the database
(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
Two, primary and secondary stream replication
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
(7) The standby end starts the database service
(8) Verify that the streaming replication is set up successfully
2. The standby end is upgraded to the main end
(1) The main end closes the database service
(2) Standby end upgrade
(3) Verify that the standby end has been upgraded
3. The original master is synchronized with the standby
(1) Modify parameters of the new master
(2) Modified parameters of the original master
(3) The original master executes pg_rewind
(4) The original master creates the logo file
(5) Modified parameters of the original master
(6) The original master starts the database service
(7) Verify that the original primary end is the standby end

1. PITR steps
(1) Initialize the database

[postgres@pankajconnect pg12]$ initdb data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_us.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "en_us.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D data -l logfile start

(2)Create an archive directory

[postgres@pankajconnect ~]$ mkdir archive
[postgres@pankajconnect ~]$ cd archive/
[postgres@pankajconnect archive]$ ls
[postgres@pankajconnect archive]$ pwd
/home/postgres/archive

(3) Modify the archive parameters

vi postgresql.conf
archive_mode = on 
archive_command = ‘cp %p /home/postgres/archive/%f’ 

(4) Start the database service

[postgres@pankajconnect data]$ pg_ctl start
waiting for server to start….2020–10–22 16:58:00.137 CST [34745] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–39), 64-bit
2020–10–22 16:58:00.140 CST [34745] LOG: listening on IPv6 address “::1”, port 5432
2020–10–22 16:58:00.140 CST [34745] LOG: listening on IPv4 address “127.0.0.1”, port 5432
2020–10–22 16:58:00.142 CST [34745] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2020–10–22 16:58:00.159 CST [34746] LOG: database system was shut down at 2020–10–22 16:53:45 CST
2020–10–22 16:58:00.162 CST [34745] LOG: database system is ready to accept connections
 done
server started

(5) Basic backup

[postgres@pankajconnect pg12]$ pg_basebackup -Pv -Ft -Xf -D basebackup
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000060 on timeline 1
40974/40974 kB (100%), 1/1 tablespace 
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: syncing data to disk …
pg_basebackup: base backup completed
[postgres@pankajconnect pg12]$
Note: -Ft means the input format is a tar file, the current command is to create a basebackup directory in the current directory, and then enter the base.tar file under that directory

(6) Manufacturing data

[postgres@pankajconnect pg12]$ psql
psql (12.0)
Type “help” for help.
postgres=# create table tb1 (c1 int,c2 char);
CREATE TABLE
postgres=# insert into tb1 values(2,’c’);
INSERT 0 1
postgres=# select now(); — Record, the database is restored to this point in time time1
 now 
 — — — — — — — — — — — — — — — -
 2020–10–22 17:05:35.023024+08
(1 row)
postgres=# drop table tb1 ;
DROP TABLE

(7) Close the database service

[postgres@pankajconnect pg12]$ pg_ctl stop
waiting for server to shut down….2020–10–22 17:06:51.193 CST [34745] LOG: received fast shutdown request
2020–10–22 17:06:51.194 CST [34745] LOG: aborting any active transactions
2020–10–22 17:06:51.195 CST [34745] LOG: background worker “logical replication launcher” (PID 34753) exited with exit code 1
2020–10–22 17:06:51.196 CST [34747] LOG: shutting down
2020–10–22 17:06:51.398 CST [34745] LOG: database system is shut down
 done
server stopped

(8) Replace data

[postgres@pankajconnect pg12]$ cd data/
[postgres@pankajconnect data]$ rm -rf * 
[postgres@pankajconnect data]$ cp ../basebackup/base.tar .
[postgres@pankajconnect data]$ tar xf base.tar 
[postgres@pankajconnect data]$ ls
backup_label global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact tablespace_map
base pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf
base.tar pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf

(9) Create a logo file

[postgres@pankajconnect data]$ touch recovery.signal

(10) Modify recovery parameters

vi postgresql.conf
restore_command = ‘cp /home/postgres/archive/%f %p’
recovery_target_time = ‘2020–10–22 17:05:35.023024+08’
recovery_target_action = ‘promote’
Note: recovery_targer_action controls the action of the database when recovering to the specified target. The default is pause, which means that the recovery will be paused and only read operations are allowed. You need to manually execute pg_wal_replay_resume() to recover. After setting to promote, the recovery process is completed and the server accepts the connection

(11) Start the database service and complete the recovery

[postgres@pankajconnect data]$ pg_ctl start
waiting for server to start….2020–10–22 17:17:00.376 CST [34988] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–39), 64-bit
2020–10–22 17:17:00.378 CST [34988] LOG: listening on IPv6 address “::1”, port 5432
2020–10–22 17:17:00.378 CST [34988] LOG: listening on IPv4 address “127.0.0.1”, port 5432
2020–10–22 17:17:00.380 CST [34988] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2020–10–22 17:17:00.392 CST [34989] LOG: database system was interrupted; last known up at 2020–10–22 16:59:34 CST
cp: Unable to get the file status of "/home/postgres/archive/00000002.history" (stat): No such file or directory
2020–10–22 17:17:00.403 CST [34989] LOG: starting point-in-time recovery to 2020–10–22 17:05:35.023024+08
2020–10–22 17:17:00.418 CST [34989] LOG: restored log file “000000010000000000000002” from archive
2020–10–22 17:17:01.036 CST [34989] LOG: redo starts at 0/2000060
2020–10–22 17:17:01.037 CST [34989] LOG: consistent recovery state reached at 0/2000138
2020–10–22 17:17:01.037 CST [34988] LOG: database system is ready to accept read only connections
 done
server started
[postgres@pankajconnect data]$ 2020–10–22 17:17:01.049 CST [34989] LOG: restored log file “000000010000000000000003” from archive
2020–10–22 17:17:01.512 CST [34989] LOG: recovery stopping before commit of transaction 488, time 2020–10–22 17:05:45.546195+08
2020–10–22 17:17:01.512 CST [34989] LOG: redo done at 0/3013528
2020–10–22 17:17:01.512 CST [34989] LOG: last completed transaction was at log time 2020–10–22 17:05:33.544826+08
cp: Unable to get the file status of "/home/postgres/archive/00000002.history" (stat): No such file or directory
2020–10–22 17:17:01.522 CST [34989] LOG: selected new timeline ID: 2
2020–10–22 17:17:01.944 CST [34989] LOG: archive recovery complete
cp: Unable to get the file status of "/home/postgres/archive/00000002.history" (stat): No such file or directory
2020–10–22 17:17:01.972 CST [34988] LOG: database system is ready to accept connections

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

[postgres@pankajconnect data]$ psql
psql (12.0)
Type “help” for help.
postgres=# select * from tb1 ;
 c1 | c2 
 — — + — — 
 2 | c
(1 row)
postgres=# insert into tb1 values (3,’c’);
INSERT 0 1

1. Build primary and secondary stream replication

(1) The main end initializes the database

[postgres@pankajconnect1 pg12]$ initdb data
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.
The database cluster will be initialized with locale “en_US.UTF-8”.
The default database encoding has accordingly been set to “UTF8”.
initdb: could not find suitable text search configuration for locale “en_US.UTF-8”
The default text search configuration will be set to “simple”.
Data page checksums are disabled.
creating directory data … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … PRC
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
initdb: warning: enabling “trust” authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
 — auth-local and — auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D data -l logfile start

(2) Modify the parameters of the master

vi data/postgresql.conf 
listen_addresses = ‘*’
vi data/pg_hba.conf
host replication all 192.168.90.222/32 trust

(4) The main end starts the database service

[postgres@pankajconnect1 pg12]$ pg_ctl start
waiting for server to start….2020–10–22 17:44:16.661 CST [35347] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–39), 64-bit
2020–10–22 17:44:16.663 CST [35347] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2020–10–22 17:44:16.663 CST [35347] LOG: listening on IPv6 address “::”, port 5432
2020–10–22 17:44:16.667 CST [35347] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2020–10–22 17:44:16.689 CST [35348] LOG: database system was shut down at 2020–10–22 17:39:52 CST
2020–10–22 17:44:16.692 CST [35347] LOG: database system is ready to accept connections
 done
server started

(5) Delete data on the standby end

[postgrespankajconnect2 pg12]$ ls
bin data include lib share
[postgrespankajconnect2 pg12]$ rm -rf data
[postgrespankajconnect2 pg12]$ ls
bin include lib share

(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

[postgrespankajconnect2 data]$ ls
backup_label.old pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf standby.signal
global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts

(7) The standby end starts the database service

[postgrespankajconnect2 data]$ pg_ctl start
waiting for server to start….2020–10–22 17:55:14.525 CST [33828] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–39), 64-bit
2020–10–22 17:55:14.529 CST [33828] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2020–10–22 17:55:14.529 CST [33828] LOG: listening on IPv6 address “::”, port 5432
2020–10–22 17:55:14.570 CST [33828] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2020–10–22 17:55:14.636 CST [33829] LOG: database system was interrupted; last known up at 2020–10–22 17:52:10 CST
2020–10–22 17:55:14.652 CST [33829] LOG: entering standby mode
2020–10–22 17:55:14.655 CST [33829] LOG: redo starts at 0/2000028
2020–10–22 17:55:14.657 CST [33829] LOG: consistent recovery state reached at 0/2000138
2020–10–22 17:55:14.658 CST [33828] LOG: database system is ready to accept read only connections
2020–10–22 17:55:14.665 CST [33833] LOG: started streaming WAL from primary at 0/3000000 on timeline 1
 done
server started

(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

[postgres@pankajconnect1 pg12]$ psql
psql (12.0)
Type “help” for help.
postgres=# create table tb1 (i int,t text);
CREATE TABLE
postgres=# insert into tb1 values (1,’pankaj’);
INSERT 0 1
postgres=#

The standby side queries and writes data

[postgrespankajconnect2 data]$ psql
psql (12.0)
Type “help” for help.
postgres=# select * from tb1 ; 
 i | t 
 — -+ — — — 
 1 | pankaj
(1 row)

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

(1) The main end closes the database service
[postgres@pankajconnect1 pg12]$ pg_ctl stop
waiting for server to shut down….2020–10–22 18:02:26.956 CST [35347] LOG: received fast shutdown request
2020–10–22 18:02:26.958 CST [35347] LOG: aborting any active transactions
2020–10–22 18:02:26.959 CST [35347] LOG: background worker “logical replication launcher” (PID 35354) exited with exit code 1
2020–10–22 18:02:26.959 CST [35349] LOG: shutting down
2020–10–22 18:02:26.978 CST [35347] LOG: database system is shut down
 done
server stopped

[postgrespankajconnect2 data]$ 2020–10–22 18:02:26.960 CST [33833] LOG: replication terminated by primary server
2020–10–22 18:02:26.960 CST [33833] DETAIL: End of WAL reached on timeline 1 at 0/301A240.
2020–10–22 18:02:26.960 CST [33833] FATAL: could not send end-of-streaming message to primary: no COPY in progress
2020–10–22 18:02:26.960 CST [33829] LOG: invalid record length at 0/301A240: wanted 24, got 0
2020–10–22 18:02:26.978 CST [33925] FATAL: could not connect to the primary server: server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
2020–10–22 18:02:31.968 CST [33926] FATAL: could not connect to the primary server: could not connect to server: 
 Is the server running on host “192.168.90.220” and accepting
 TCP/IP connections on port 5432?
…

(2) Standby end upgrade
[postgrespankajconnect2 pg12]$ pg_ctl promote
waiting for server to promote…. done
server promoted
2020–10–22 18:03:46.404 CST [33829] LOG: received promote request
2020–10–22 18:03:46.404 CST [33829] LOG: redo done at 0/301A1C8
2020–10–22 18:03:46.404 CST [33829] LOG: last completed transaction was at log time 2020–10–22 17:58:00.474187+08
2020–10–22 18:03:46.406 CST [33829] LOG: selected new timeline ID: 2
2020–10–22 18:03:46.992 CST [33829] LOG: archive recovery complete
2020–10–22 18:03:46.995 CST [33828] LOG: database system is ready to accept connections

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

postgres 33828 1 0 17:55 ? 00:00:00 /home/postgres/pg12/bin/postgres
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 33950 33828 0 18:03 ? 00:00:00 postgres: walwriter 
postgres 33951 33828 0 18:03 ? 00:00:00 postgres: autovacuum launcher 
postgres 33952 33828 0 18:03 ? 00:00:00 postgres: logical replication launcher

New master writes data

[postgrespankajconnect2 data]$ psql
psql (12.0)
Type “help” for help.
postgres=# insert into tb1 values (5,’dam’);
INSERT 0 1

3. The original master is synchronized with the standby

(1) Modify parameters of the new master
[postgrespankajconnect2 data]$ vi pg_hba.conf 
host all all 192.168.90.220/32 trust
host replication all 192.168.90.220/32 trust

[postgrespankajconnect2 data]$ pg_ctl reload
server signaled
2020–10–22 18:11:23.416 CST [33828] LOG: received SIGHUP, reloading configuration files

(2) Modified parameters of the original master

[postgres@pankajconnect1 data]$ vi postgresql.conf
wal_log_hints = on

[postgres@pankajconnect1 data]$ pg_ctl start
waiting for server to start….2020–10–22 18:14:30.947 CST [35680] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–39), 64-bit
2020–10–22 18:14:30.950 CST [35680] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2020–10–22 18:14:30.950 CST [35680] LOG: listening on IPv6 address “::”, port 5432
2020–10–22 18:14:30.951 CST [35680] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2020–10–22 18:14:30.962 CST [35681] LOG: database system was shut down at 2020–10–22 18:02:26 CST
2020–10–22 18:14:30.964 CST [35680] LOG: database system is ready to accept connections
 done
server started
[postgres@pankajconnect1 data]$ pg_ctl stop
waiting for server to shut down….2020–10–22 18:14:33.638 CST [35680] LOG: received fast shutdown request
2020–10–22 18:14:33.640 CST [35680] LOG: aborting any active transactions
2020–10–22 18:14:33.642 CST [35680] LOG: background worker “logical replication launcher” (PID 35687) exited with exit code 1
2020–10–22 18:14:33.643 CST [35682] LOG: shutting down
2020–10–22 18:14:33.663 CST [35680] LOG: database system is shut down
 done
server stopped

(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!

(4) The original master creates the logo file
[postgres@pankajconnect1 data]$ touch standby.signal

(5) Modified parameters of the original master
[postgres@pankajconnect1 data]$ vi postgresql.auto.conf

Modify the host in primary_conninfo to the new primary ip, 192.168.90.222

(6) The original master starts the database service
[postgres@pankajconnect1 data]$ pg_ctl start
waiting for server to start….2020–10–22 18:20:03.702 CST [35804] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–39), 64-bit
2020–10–22 18:20:03.706 CST [35804] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2020–10–22 18:20:03.706 CST [35804] LOG: listening on IPv6 address “::”, port 5432
2020–10–22 18:20:03.710 CST [35804] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2020–10–22 18:20:03.735 CST [35805] LOG: database system was interrupted while in recovery at log time 2020–10–22 18:08:47 CST
2020–10–22 18:20:03.735 CST [35805] HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
2020–10–22 18:20:03.749 CST [35805] LOG: entering standby mode
2020–10–22 18:20:03.751 CST [35805] LOG: redo starts at 0/301A240
2020–10–22 18:20:03.753 CST [35805] LOG: invalid record length at 0/3033BF8: wanted 24, got 0
2020–10–22 18:20:03.769 CST [35808] LOG: started streaming WAL from primary at 0/3000000 on timeline 2
2020–10–22 18:20:03.773 CST [35805] LOG: consistent recovery state reached at 0/3033C30
2020–10–22 18:20:03.773 CST [35804] LOG: database system is ready to accept read only connections
 done
server started

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

postgres 33828 1 0 17:55 ? 00:00:00 /home/postgres/pg12/bin/postgres
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 33950 33828 0 18:03 ? 00:00:00 postgres: walwriter 
postgres 33951 33828 0 18:03 ? 00:00:00 postgres: autovacuum launcher 
postgres 33952 33828 0 18:03 ? 00:00:00 postgres: logical replication launcher 
postgres 34131 33828 0 18:20 ? 00:00:00 postgres: walsender postgres 192.168.90.220(62626) streaming 0/3034990

Query the process of the new standby (original master)

postgres 35804 1 0 18:20 ? 00:00:00 /home/postgres/pg12/bin/postgres
postgres 35805 35804 0 18:20 ? 00:00:00 postgres: startup recovering 000000020000000000000003
postgres 35806 35804 0 18:20 ? 00:00:00 postgres: checkpointer 
postgres 35807 35804 0 18:20 ? 00:00:00 postgres: background writer 
postgres 35808 35804 0 18:20 ? 00:00:00 postgres: walreceiver streaming 0/3034990
postgres 35809 35804 0 18:20 ? 00:00:00 postgres: stats collector

New master writes data

[postgrespankajconnect2 data]$ psql 
psql (12.0)
Type “help” for help.
postgres=# select * from tb1 ;
 i | t 
 — -+ — — — 
 1 | pankaj
 5 | pankajkk
(2 rows)
postgres=# insert into tb1 values (10,’ankit’);
INSERT 0 1

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

[postgres@pankajconnect1 data]$ psql 
psql (12.0)
Type “help” for help.
postgres=# select * from tb1 ;
 i | t 
 — — + — — — 
 1 | pankaj
 5 | pankajkk
 10 | ankit
(3 rows)
postgres=# insert into tb1 values (20,’min’); 
2020–10–22 18:23:50.705 CST [35839] ERROR: cannot execute INSERT in a read-only transaction
2020–10–22 18:23:50.705 CST [35839] STATEMENT: insert into tb1 values (20,’min’);
ERROR: cannot execute INSERT in a read-only transaction

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.