+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Single Host Replication Setup++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Archive based Streaming Replication with hot_Standby, Master and Standby databases servers +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Database Name :
1) Master_Server : (10.*.*.201)2) Standby_Server : (10.*.*.201)
Type of Replication: Archive based Streaming replication with hot_standby
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Primary Setup :-
-bash-4.2$ initdb -D master_server
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".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory master_server ... initdb: could not create directory "master_server": Permission denied
-bash-4.2$
+++= chown -R postgres:postgres /usr/pgsql-11/bin
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Create Cluster using initdb command from postgres binary location /bin/.. path
-- Master Database name : master_server
-- change the ownership of bin ( /use/pgsql-11/bin) to postgres from root user .
-bash-4.2$ /usr/pgsql-11/bin/./intidb -D master_server/
-bash: /usr/pgsql-11/bin/./intidb: No such file or directory
-bash-4.2$ /usr/pgsql-11/bin/./initdb -D master_server/
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".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory master_server ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Dubai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
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:
/usr/pgsql-11/bin/./pg_ctl -D master_server/ -l logfile start
-bash-4.2$
-- The master_server directories are created under /var/lib/pgsql/11 path
-- let us verify the directory
/var/lib/pgsql/11/master_server
-bash-4.2$ ls
base pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf
global pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION pg_xact postgresql.conf
-bash-4.2$
-- Since Master and standby databases are running on the same host ,so we need to change the port number for master and slave
-- and the below paremeters for replications
-- the master portnumber is 5490, listen_addresses parameter values in postgresql.conf file
the below parameters values are updated in postgresql.conf for replication (master side):-
port = 5490
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/11/archive/masarc/%f'
max_wal_senders = 2
wal_keep_segments = 20
bash-4.2$ cat postgresql.conf | grep port
listen_addresses = '*' # what IP address(es) to listen on;
port = 5490 # (change requires restart)
#ssl_passphrase_command_supports_reload = off
# supported by the operating system:
# supported by the operating system:
# %r = remote host and port
-bash-4.2$
-- restarted the database after chanding the portnumber(5490) using pg_ctl command
/usr/pgsql-11/bin/pg_ctl -D master_server restart
pwd :=
/var/lib/pgsql/11
/usr/pgsql-11/bin/pg_ctl -D master_server restart
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D master_server restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-10-15 14:24:08.870 +04 [7372] LOG: listening on IPv4 address "0.0.0.0", port 5490
2019-10-15 14:24:08.870 +04 [7372] LOG: listening on IPv6 address "::", port 5490
2019-10-15 14:24:08.871 +04 [7372] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5490"
2019-10-15 14:24:08.873 +04 [7372] LOG: listening on Unix socket "/tmp/.s.PGSQL.5490"
2019-10-15 14:24:08.897 +04 [7372] LOG: redirecting log output to logging collector process
2019-10-15 14:24:08.897 +04 [7372] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$ pwd
/var/lib/pgsql/11
-bash-4.2$
----------------------------------------------------------------------------------------------
-- now try the command stop and start for the cluster (database: master_server)
-bash-4.2$ /usr/pgsql-11/bin/./pg_ctl -D master_server/ -l logfile start
waiting for server to start.... done
server started
-bash-4.2$
-- new the cluster has been started with new port number 5490 for master_server database, just verify this port number
-- using netstat command
-bash-4.2$ netstat -nlutlp | grep 5490
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:5490 0.0.0.0:* LISTEN 6336/postgres
tcp6 0 0 ::1:5490 :::* LISTEN 6336/postgres
-bash-4.2$
-- We have found that process id -> 6366(pid) is running for the database:master_server --> postgres(5490).
-bash-4.2$ ps -ef | grep postgres | grep 6336
postgres 6336 1 0 11:11 pts/0 00:00:00 /usr/pgsql-11/bin/postgres -D master_server
postgres 6337 6336 0 11:11 ? 00:00:00 postgres: logger
postgres 6339 6336 0 11:11 ? 00:00:00 postgres: checkpointer
postgres 6340 6336 0 11:11 ? 00:00:00 postgres: background writer
postgres 6341 6336 0 11:11 ? 00:00:00 postgres: walwriter
postgres 6342 6336 0 11:11 ? 00:00:00 postgres: autovacuum launcher
postgres 6343 6336 0 11:11 ? 00:00:00 postgres: stats collector
postgres 6344 6336 0 11:11 ? 00:00:00 postgres: logical replication launcher
postgres 6355 5812 0 11:13 pts/0 00:00:00 grep --color=auto 6336
-bash-4.2$
Connect the master_server using 5490 :-
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=#
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Create recovery file (recovery.conf) on the standby side for recovery for the steaming replications(standby side)
wal_level=replica
archive_mode=on
archive_command='cp %p /var/lib/pgsql/11/archive/masarc/%f'
max_wal_senders=
wal_keep_segments=
++++ since standby db is on same host , we will make this in to 127.0.0.1+++
-bash-4.2$ cat recovery.conf
standby_mode=on
primary_conninfo='host=127.0.0.1 port=5490'
restore_command='cp /var/lib/pgsql/11/archive/masarc/%f %p'
trigger_file='/tmp/failover_5490'
-bash-4.2$
--restart the master once again
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D master_server restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-10-15 12:24:23.808 +04 [6705] LOG: listening on IPv6 address "::1", port 5490
2019-10-15 12:24:23.809 +04 [6705] LOG: listening on IPv4 address "127.0.0.1", port 5490
2019-10-15 12:24:23.810 +04 [6705] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5490"
2019-10-15 12:24:23.812 +04 [6705] LOG: listening on Unix socket "/tmp/.s.PGSQL.5490"
2019-10-15 12:24:23.837 +04 [6705] LOG: redirecting log output to logging collector process
2019-10-15 12:24:23.837 +04 [6705] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
-- create tables
postgres=# create table rep1 as select * from pg_class,pg_description;
SELECT 1565334
postgres=# create table rep2 as select * from pg_class,pg_description;
SELECT 1579065
postgres=#
--reload the configuration files
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D master_server reload
server signaled
-bash-4.2$
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Standby Server setup :-
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--start the basebackup of master_server(primary side) for standby server creation, this backup command will create all directories structures of master_server for standby server
-bash-4.2$ ./pg_basebackup -P -p 5490 --format=t -D /var/lib/pgsql/11/standby_server
779667/779667 kB (100%), 1/1 tablespace
-bash-4.2$
-- The backup command will create *.tar file which we need to untar it
-bash-4.2$ cd /var/lib/pgsql/11/standby_server
-bash-4.2$ ls -tlr
total 796060
-rw-------. 1 postgres postgres 798380032 Oct 15 14:37 base.tar
-rw-------. 1 postgres postgres 16779264 Oct 15 14:37 pg_wal.tar
-bash-4.2$
-bash-4.2$ du -sh *.tar
762M base.tar
17M pg_wal.tar
-bash-4.2$
--untar the base.tar file which contains all the configurations including "backup_lavel" file which is for backup log informations
-bash-4.2$ tar -xf base.tar
-bash-4.2$ ls
backup_label current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION pg_xact tablespace_map
base global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.auto.conf
base.tar log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_wal.tar postgresql.conf
-bash-4.2$
-- Let us check the backup_label file, here you can find the backup start time, checkpoint location,backup method, the backup
-- command started from master db server
-bash-4.2$ cat backup_label
START WAL LOCATION: 0/36000028 (file 000000010000000000000036)
CHECKPOINT LOCATION: 0/36000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-10-15 14:37:15 +04
LABEL: pg_basebackup base backup
START TIMELINE: 1
-bash-4.2$
--We are verifying the archive location below where we can find the archive files for backup
-bash-4.2$ pwd
/var/lib/pgsql/11/archive/masarc
-bash-4.2$ ls -tlr *backup*
-rw-------. 1 postgres postgres 340 Oct 15 14:37 000000010000000000000036.00000028.backup
-bash-4.2$
-- set the eviornment variable
PATH=$PATH:$HOME/bin
export PGPATH=/usr/pgsql-11/bin
export PGDATA=/var/lib/pgsql/11/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/usr/pgsql-11/share/locale
export MANPATH=$MANPATH:/usr/pgsql-11/share/man
export ARCPATH1=/var/lib/pgsql/11/archive/masarc
export PGDATAM1=/var/lib/pgsql/11/master_server
export PGDATAS1=/var/lib/pgsql/11/standby_server
-bash-4.2$
-- update the port=5491 for standby_server in postgresql.conf file in standby_server directory
-bash-4.2$ pwd
/var/lib/pgsql/11/standby_server
base.tar pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postgresql.conf tablespace_map.old
-bash-4.2$
--port & hot_standby parameters values are updated in the postgresql.conf file
port=5491
hot_standby=on
-- recovery file creation
-bash-4.2$ cat >> recovery.conf
standby_mode=on
primary_conninfo='host=127.0.0.1 port=5490'
restore_command='cp /var/lib/pgsql/11/archive/masarc/%f %p'
trigger_file='/tmp/failover_5433'
-bash-4.2$ vi recovery.conf
-bash-4.2$
-- Now start the standby server from ../bin/...directory
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server start
waiting for server to start....2019-10-15 16:05:18.162 +04 [7992] FATAL: data directory "/var/lib/pgsql/11/standby_server" has invalid permissions
2019-10-15 16:05:18.162 +04 [7992] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
stopped waiting
pg_ctl: could not start server
Examine the log output.
-- change the permission to 700 for the directory standby server
-bash-4.2$ chmod -R 700 standby_server/
-bash-4.2$
--> Let us start the standby_server again ,
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server start
waiting for server to start....2019-10-15 16:06:08.154 +04 [8000] LOG: listening on IPv4 address "0.0.0.0", port 5491
2019-10-15 16:06:08.155 +04 [8000] LOG: listening on IPv6 address "::", port 5491
2019-10-15 16:06:08.156 +04 [8000] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5491"
2019-10-15 16:06:08.159 +04 [8000] LOG: listening on Unix socket "/tmp/.s.PGSQL.5491"
2019-10-15 16:06:08.196 +04 [8000] LOG: redirecting log output to logging collector process
2019-10-15 16:06:08.196 +04 [8000] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
-- Standby server is started with the port number : 5491 and now check the process id and port :-
ps -ef | grep postgres ......
postgres 8000 1 0 16:06 pts/1 00:00:00 /usr/pgsql-11/bin/postgres -D standby_server
postgres 8001 8000 0 16:06 ? 00:00:00 postgres: logger
postgres 8002 8000 0 16:06 ? 00:00:00 postgres: startup recovering 000000010000000000000037
postgres 8004 8000 0 16:06 ? 00:00:00 postgres: checkpointer
postgres 8005 8000 0 16:06 ? 00:00:00 postgres: background writer
postgres 8007 8000 0 16:06 ? 00:00:00 postgres: stats collector
postgres 8008 8000 0 16:06 ? 00:00:00 postgres: walreceiver streaming 0/37000140
postgres 8009 7444 0 16:06 ? 00:00:00 postgres: walsender postgres 127.0.0.1(35580) streaming 0/37000140
postgres 8012 7749 0 16:06 pts/1 00:00:00 ps -ef
postgres 8013 7749 0 16:06 pts/1 00:00:00 grep --color=auto postgres
-bash-4.2$ ps -ef | grep postgres | grep standby_server
postgres 8000 1 0 16:06 pts/1 00:00:00 /usr/pgsql-11/bin/postgres -D standby_server
postgres 8017 7749 0 16:06 pts/1 00:00:00 grep --color=auto standby_server
-bash-4.2$
The standby server is started with process id : 8000 and the portnumber is 5491
--> verify it using netstat -nltulp
netstat -nlulpt
tcp6 0 0 :::5491 :::* LISTEN 8000/postgres
tcp6 0 0 :::22 :::* LISTEN -
udp 0 0 0.0.0.0:111 0.0.0.0:* -
udp 0 0 0.0.0.0:783 0.0.0.0:* -
udp6 0 0 :::111 :::* -
udp6 0 0 :::783 :::* -
-bash-4.2$
--> now the replication setup is completed for archive based streaming replications
--> let us verify the streaming now
postgres 7444 1 0 14:33 pts/0 00:00:00 /usr/pgsql-11/bin/postgres -D master_server
postgres 7445 7444 0 14:33 ? 00:00:00 postgres: logger
postgres 7447 7444 0 14:33 ? 00:00:00 postgres: checkpointer
postgres 7448 7444 0 14:33 ? 00:00:00 postgres: background writer
postgres 7449 7444 0 14:33 ? 00:00:00 postgres: walwriter
postgres 7450 7444 0 14:33 ? 00:00:00 postgres: autovacuum launcher
postgres 7451 7444 0 14:33 ? 00:00:00 postgres: archiver last was 000000010000000000000036.00000028.backup
postgres 7452 7444 0 14:33 ? 00:00:00 postgres: stats collector
postgres 7453 7444 0 14:33 ? 00:00:00 postgres: logical replication launcher
root 7746 7699 0 15:49 pts/1 00:00:00 sudo su - postgres
root 7748 7746 0 15:49 pts/1 00:00:00 su - postgres
postgres 7749 7748 0 15:49 pts/1 00:00:00 -bash
postgres 8000 1 0 16:06 pts/1 00:00:00 /usr/pgsql-11/bin/postgres -D standby_server
postgres 8001 8000 0 16:06 ? 00:00:00 postgres: logger
postgres 8002 8000 0 16:06 ? 00:00:00 postgres: startup recovering 000000010000000000000037
postgres 8004 8000 0 16:06 ? 00:00:00 postgres: checkpointer
postgres 8005 8000 0 16:06 ? 00:00:00 postgres: background writer
postgres 8007 8000 0 16:06 ? 00:00:00 postgres: stats collector
postgres 8008 8000 0 16:06 ? 00:00:00 postgres: walreceiver streaming 0/37000140
postgres 8009 7444 0 16:06 ? 00:00:00 postgres: walsender postgres 127.0.0.1(35580) streaming 0/37000140
postgres 8031 7749 0 16:10 pts/1 00:00:00 ps -ef
postgres 8032 7749 0 16:10 pts/1 00:00:00 grep --color=auto postgres
-bash-4.2$
-- Verify the status of the standby_server database, if it is says 't' then it means true using pg_is_in_recovery()
select pg_is_in_recovery();
standby
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#
check in primary#
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=#
-bash-4.2$ date
Tue Oct 15 16:27:58 +04 2019
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
(2 rows)
Create the table rep3 from primary side -- >
postgres=# create table rep3 as select * from pg_class,pg_description;
SELECT 1592796
postgres=#
The table rep3 is created which can be verified in archive location , now we can see the archivelog files are created
from primary side the archivelog files from (000000010000000000000037) 16:28 to (00000001000000000000004F) 16:28
-bash-4.2$ pwd
/var/lib/pgsql/11/archive/masarc
-bash-4.2$
-rw-------. 1 postgres postgres 340 Oct 15 14:37 000000010000000000000036.00000028.backup
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000037
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000038
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000039
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003A
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003B
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003C
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003D
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003E
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003F
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000040
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000041
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000042
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000043
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000044
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000045
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000046
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000047
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000048
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000049
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004A
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004B
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004C
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004D
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004E
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004F
-bash-4.2$
check the table lists from standby_server side :-
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
(2 rows)
-- check the timestamp
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
public | rep3 | table | postgres
(3 rows)
postgres=# exit
-bash-4.2$ date
Tue Oct 15 16:29:34 +04 2019
-bash-4.2$
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
Test 2#
postgres=# \q
-bash-4.2$
-bash-4.2$
-- check the timestamp
-bash-4.2$ date
Wed Oct 16 12:05:49 +04 2019
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
public | rep3 | table | postgres
(3 rows)
postgres=# create table rep4 as select * from pg_class,pg_description;
SELECT 1606527
postgres=#
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000050
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000051
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000052
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000053
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000054
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000055
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000056
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000057
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000058
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000059
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005A
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005B
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005C
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005D
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005E
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005F
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000060
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000061
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000062
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000063
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000064
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000065
time stamp
-bash-4.2$ date
Wed Oct 16 12:07:45 +04 2019
-bash-4.2$ psql -p 5491
psql (11.5)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
public | rep3 | table | postgres
public | rep4 | table | postgres --> table rep4 has been replicated to standby_server db side.
(4 rows)
postgres=#
Test 3 :-
postgres=# create database replication_db_test;
CREATE DATABASE
postgres=#
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \c replication_db_test
You are now connected to database "replication_db_test" as user "postgres".
replication_db_test=#
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
postgres=# create user replication_user with password 'ashok1';
CREATE ROLE
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
------------------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
replication_user | | {} |
postgres=#
-- check the user is in standby_server side
-bash-4.2$ date
Tue Oct 15 16:37:35 +04 2019
-bash-4.2$ psql -p 5491
psql (11.5)
Type "help" for help.
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
------------------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
replication_user | | {} |
postgres=#
# primary side check replication status using pg_stat_replication #
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
------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+------------+-------
-----+------------+------------+-----------+-----------+------------+---------------+------------
8009 | 10 | postgres | walreceiver | 127.0.0.1 | | 35580 | 2019-10-15 16:06:08.47769+04 | | streaming | 0/5091F820 | 0/5091
F820 | 0/5091F820 | 0/5091F820 | | | | 0 | async
(1 row)
-- check the current wal number --
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/5091F820
(1 row)
postgres=#
## recovery side check ##
postgres=# select pg_is_in_recovery(); -> recovery mode is true
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# select pg_last_wal_receive_lsn();
pg_last_wal_receive_lsn
-------------------------
0/5091F820
(1 row)
postgres=# select pg_last_wal_replay_lsn();
pg_last_wal_replay_lsn
------------------------
0/5091F820
(1 row)
postgres=# select pg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
-------------------------------
2019-10-15 16:37:12.490004+04
(1 row)
postgres=#
Version 11 :-
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_por\x86_64/pgdg-centos11-11-2t | backend_start | backend_xmin | state | sent_lsn | write_
lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+------------+-------
-----+------------+------------+-----------+-----------+------------+---------------+------------
8009 | 10 | postgres | walreceiver | 127.0.0.1 | | 35580 | 2019-10-15 16:06:08.47769+04 | | streaming | 0/5091F820 | 0/5091
F820 | 0/5091F820 | 0/5091F820 | | | | 0 | async
(1 row)
postgres=#
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
pid | 8009
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 127.0.0.1
client_hostname |
client_port | 35580
backend_start | 2019-10-15 16:06:08.47769+04
backend_xmin |
state | streaming
sent_lsn | 0/5091F820
write_lsn | 0/5091F820
flush_lsn | 0/5091F820
replay_lsn | 0/5091F820
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
postgres=#
--TEST 4 , let us stop the standbY_server and check the status is in primary --
stop sedondary
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server stop
waiting for server to shut down.... done
server stopped
-bash-4.2$ date
Tue Oct 15 17:08:33 +04 2019
-bash-4.2$
-- the pg_stat_replication query doesn't shows the status of the replication, Since the standby_server database is down
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
(0 rows)
postgres=#
bash-4.2$ date
Tue Oct 15 17:09:21 +04 2019
-bash-4.2$
-- start secondary now
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server start
waiting for server to start....2019-10-15 17:09:52.811 +04 [8355] LOG: listening on IPv4 address "0.0.0.0", port 5491
2019-10-15 17:09:52.811 +04 [8355] LOG: listening on IPv6 address "::", port 5491
2019-10-15 17:09:52.812 +04 [8355] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5491"
2019-10-15 17:09:52.814 +04 [8355] LOG: listening on Unix socket "/tmp/.s.PGSQL.5491"
2019-10-15 17:09:52.833 +04 [8355] LOG: redirecting log output to logging collector process
2019-10-15 17:09:52.833 +04 [8355] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
-- check it in primary
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
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
------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------
------+------------+------------+-----------+-----------+------------+---------------+------------
8363 | 10 | postgres | walreceiver | 127.0.0.1 | | 35582 | 2019-10-15 17:09:52.866973+04 | | streaming | 0/5091F820 | 0/509
1F820 | 0/5091F820 | 0/5091F820 | | | | 0 | async
(1 row)
postgres=#
--check it in recovery
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#
--start the basebackup of master_server(primary side) for standby server creation, this backup command will create all directories structures of master_server for standby server
-bash-4.2$ ./pg_basebackup -P -p 5490 --format=t -D /var/lib/pgsql/11/standby_server
779667/779667 kB (100%), 1/1 tablespace
-bash-4.2$
-- The backup command will create *.tar file which we need to untar it
-bash-4.2$ cd /var/lib/pgsql/11/standby_server
-bash-4.2$ ls -tlr
total 796060
-rw-------. 1 postgres postgres 798380032 Oct 15 14:37 base.tar
-rw-------. 1 postgres postgres 16779264 Oct 15 14:37 pg_wal.tar
-bash-4.2$
-bash-4.2$ du -sh *.tar
762M base.tar
17M pg_wal.tar
-bash-4.2$
--untar the base.tar file which contains all the configurations including "backup_lavel" file which is for backup log informations
-bash-4.2$ tar -xf base.tar
-bash-4.2$ ls
backup_label current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION pg_xact tablespace_map
base global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.auto.conf
base.tar log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_wal.tar postgresql.conf
-bash-4.2$
-- Let us check the backup_label file, here you can find the backup start time, checkpoint location,backup method, the backup
-- command started from master db server
-bash-4.2$ cat backup_label
START WAL LOCATION: 0/36000028 (file 000000010000000000000036)
CHECKPOINT LOCATION: 0/36000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-10-15 14:37:15 +04
LABEL: pg_basebackup base backup
START TIMELINE: 1
-bash-4.2$
--We are verifying the archive location below where we can find the archive files for backup
-bash-4.2$ pwd
/var/lib/pgsql/11/archive/masarc
-bash-4.2$ ls -tlr *backup*
-rw-------. 1 postgres postgres 340 Oct 15 14:37 000000010000000000000036.00000028.backup
-bash-4.2$
-- set the eviornment variable
PATH=$PATH:$HOME/bin
export PGPATH=/usr/pgsql-11/bin
export PGDATA=/var/lib/pgsql/11/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/usr/pgsql-11/share/locale
export MANPATH=$MANPATH:/usr/pgsql-11/share/man
export ARCPATH1=/var/lib/pgsql/11/archive/masarc
export PGDATAM1=/var/lib/pgsql/11/master_server
export PGDATAS1=/var/lib/pgsql/11/standby_server
-bash-4.2$
-- update the port=5491 for standby_server in postgresql.conf file in standby_server directory
-bash-4.2$ pwd
/var/lib/pgsql/11/standby_server
base.tar pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postgresql.conf tablespace_map.old
-bash-4.2$
--port & hot_standby parameters values are updated in the postgresql.conf file
port=5491
hot_standby=on
-- recovery file creation
-bash-4.2$ cat >> recovery.conf
standby_mode=on
primary_conninfo='host=127.0.0.1 port=5490'
restore_command='cp /var/lib/pgsql/11/archive/masarc/%f %p'
trigger_file='/tmp/failover_5433'
-bash-4.2$ vi recovery.conf
-bash-4.2$
-- Now start the standby server from ../bin/...directory
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server start
waiting for server to start....2019-10-15 16:05:18.162 +04 [7992] FATAL: data directory "/var/lib/pgsql/11/standby_server" has invalid permissions
2019-10-15 16:05:18.162 +04 [7992] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
stopped waiting
pg_ctl: could not start server
Examine the log output.
-- change the permission to 700 for the directory standby server
-bash-4.2$ chmod -R 700 standby_server/
-bash-4.2$
--> Let us start the standby_server again ,
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server start
waiting for server to start....2019-10-15 16:06:08.154 +04 [8000] LOG: listening on IPv4 address "0.0.0.0", port 5491
2019-10-15 16:06:08.155 +04 [8000] LOG: listening on IPv6 address "::", port 5491
2019-10-15 16:06:08.156 +04 [8000] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5491"
2019-10-15 16:06:08.159 +04 [8000] LOG: listening on Unix socket "/tmp/.s.PGSQL.5491"
2019-10-15 16:06:08.196 +04 [8000] LOG: redirecting log output to logging collector process
2019-10-15 16:06:08.196 +04 [8000] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
-- Standby server is started with the port number : 5491 and now check the process id and port :-
ps -ef | grep postgres ......
postgres 8000 1 0 16:06 pts/1 00:00:00 /usr/pgsql-11/bin/postgres -D standby_server
postgres 8001 8000 0 16:06 ? 00:00:00 postgres: logger
postgres 8002 8000 0 16:06 ? 00:00:00 postgres: startup recovering 000000010000000000000037
postgres 8004 8000 0 16:06 ? 00:00:00 postgres: checkpointer
postgres 8005 8000 0 16:06 ? 00:00:00 postgres: background writer
postgres 8007 8000 0 16:06 ? 00:00:00 postgres: stats collector
postgres 8008 8000 0 16:06 ? 00:00:00 postgres: walreceiver streaming 0/37000140
postgres 8009 7444 0 16:06 ? 00:00:00 postgres: walsender postgres 127.0.0.1(35580) streaming 0/37000140
postgres 8012 7749 0 16:06 pts/1 00:00:00 ps -ef
postgres 8013 7749 0 16:06 pts/1 00:00:00 grep --color=auto postgres
-bash-4.2$ ps -ef | grep postgres | grep standby_server
postgres 8000 1 0 16:06 pts/1 00:00:00 /usr/pgsql-11/bin/postgres -D standby_server
postgres 8017 7749 0 16:06 pts/1 00:00:00 grep --color=auto standby_server
-bash-4.2$
The standby server is started with process id : 8000 and the portnumber is 5491
--> verify it using netstat -nltulp
netstat -nlulpt
tcp6 0 0 :::5491 :::* LISTEN 8000/postgres
tcp6 0 0 :::22 :::* LISTEN -
udp 0 0 0.0.0.0:111 0.0.0.0:* -
udp 0 0 0.0.0.0:783 0.0.0.0:* -
udp6 0 0 :::111 :::* -
udp6 0 0 :::783 :::* -
-bash-4.2$
--> now the replication setup is completed for archive based streaming replications
--> let us verify the streaming now
postgres 7444 1 0 14:33 pts/0 00:00:00 /usr/pgsql-11/bin/postgres -D master_server
postgres 7445 7444 0 14:33 ? 00:00:00 postgres: logger
postgres 7447 7444 0 14:33 ? 00:00:00 postgres: checkpointer
postgres 7448 7444 0 14:33 ? 00:00:00 postgres: background writer
postgres 7449 7444 0 14:33 ? 00:00:00 postgres: walwriter
postgres 7450 7444 0 14:33 ? 00:00:00 postgres: autovacuum launcher
postgres 7451 7444 0 14:33 ? 00:00:00 postgres: archiver last was 000000010000000000000036.00000028.backup
postgres 7452 7444 0 14:33 ? 00:00:00 postgres: stats collector
postgres 7453 7444 0 14:33 ? 00:00:00 postgres: logical replication launcher
root 7746 7699 0 15:49 pts/1 00:00:00 sudo su - postgres
root 7748 7746 0 15:49 pts/1 00:00:00 su - postgres
postgres 7749 7748 0 15:49 pts/1 00:00:00 -bash
postgres 8000 1 0 16:06 pts/1 00:00:00 /usr/pgsql-11/bin/postgres -D standby_server
postgres 8001 8000 0 16:06 ? 00:00:00 postgres: logger
postgres 8002 8000 0 16:06 ? 00:00:00 postgres: startup recovering 000000010000000000000037
postgres 8004 8000 0 16:06 ? 00:00:00 postgres: checkpointer
postgres 8005 8000 0 16:06 ? 00:00:00 postgres: background writer
postgres 8007 8000 0 16:06 ? 00:00:00 postgres: stats collector
postgres 8008 8000 0 16:06 ? 00:00:00 postgres: walreceiver streaming 0/37000140
postgres 8009 7444 0 16:06 ? 00:00:00 postgres: walsender postgres 127.0.0.1(35580) streaming 0/37000140
postgres 8031 7749 0 16:10 pts/1 00:00:00 ps -ef
postgres 8032 7749 0 16:10 pts/1 00:00:00 grep --color=auto postgres
-bash-4.2$
-- Verify the status of the standby_server database, if it is says 't' then it means true using pg_is_in_recovery()
select pg_is_in_recovery();
standby
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#
check in primary#
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=#
-bash-4.2$ date
Tue Oct 15 16:27:58 +04 2019
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
(2 rows)
Create the table rep3 from primary side -- >
postgres=# create table rep3 as select * from pg_class,pg_description;
SELECT 1592796
postgres=#
The table rep3 is created which can be verified in archive location , now we can see the archivelog files are created
from primary side the archivelog files from (000000010000000000000037) 16:28 to (00000001000000000000004F) 16:28
-bash-4.2$ pwd
/var/lib/pgsql/11/archive/masarc
-bash-4.2$
-rw-------. 1 postgres postgres 340 Oct 15 14:37 000000010000000000000036.00000028.backup
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000037
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000038
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000039
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003A
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003B
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003C
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003D
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003E
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000003F
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000040
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000041
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000042
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000043
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000044
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000045
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000046
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000047
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000048
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 000000010000000000000049
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004A
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004B
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004C
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004D
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004E
-rw-------. 1 postgres postgres 16777216 Oct 15 16:28 00000001000000000000004F
-bash-4.2$
check the table lists from standby_server side :-
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
(2 rows)
-- check the timestamp
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
public | rep3 | table | postgres
(3 rows)
postgres=# exit
-bash-4.2$ date
Tue Oct 15 16:29:34 +04 2019
-bash-4.2$
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
Test 2#
postgres=# \q
-bash-4.2$
-bash-4.2$
-- check the timestamp
-bash-4.2$ date
Wed Oct 16 12:05:49 +04 2019
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
public | rep3 | table | postgres
(3 rows)
postgres=# create table rep4 as select * from pg_class,pg_description;
SELECT 1606527
postgres=#
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000050
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000051
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000052
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000053
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000054
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000055
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000056
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000057
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000058
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000059
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005A
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005B
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005C
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005D
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005E
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 00000001000000000000005F
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000060
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000061
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000062
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000063
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000064
-rw-------. 1 postgres postgres 16777216 Oct 16 12:06 000000010000000000000065
time stamp
-bash-4.2$ date
Wed Oct 16 12:07:45 +04 2019
-bash-4.2$ psql -p 5491
psql (11.5)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | rep1 | table | postgres
public | rep2 | table | postgres
public | rep3 | table | postgres
public | rep4 | table | postgres --> table rep4 has been replicated to standby_server db side.
(4 rows)
postgres=#
Test 3 :-
postgres=# create database replication_db_test;
CREATE DATABASE
postgres=#
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \c replication_db_test
You are now connected to database "replication_db_test" as user "postgres".
replication_db_test=#
-bash-4.2$ psql -p 5490
psql (11.5)
Type "help" for help.
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
postgres=# create user replication_user with password 'ashok1';
CREATE ROLE
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
------------------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
replication_user | | {} |
postgres=#
-- check the user is in standby_server side
-bash-4.2$ date
Tue Oct 15 16:37:35 +04 2019
-bash-4.2$ psql -p 5491
psql (11.5)
Type "help" for help.
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
------------------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
replication_user | | {} |
postgres=#
# primary side check replication status using pg_stat_replication #
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
------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+------------+-------
-----+------------+------------+-----------+-----------+------------+---------------+------------
8009 | 10 | postgres | walreceiver | 127.0.0.1 | | 35580 | 2019-10-15 16:06:08.47769+04 | | streaming | 0/5091F820 | 0/5091
F820 | 0/5091F820 | 0/5091F820 | | | | 0 | async
(1 row)
-- check the current wal number --
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/5091F820
(1 row)
postgres=#
## recovery side check ##
postgres=# select pg_is_in_recovery(); -> recovery mode is true
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# select pg_last_wal_receive_lsn();
pg_last_wal_receive_lsn
-------------------------
0/5091F820
(1 row)
postgres=# select pg_last_wal_replay_lsn();
pg_last_wal_replay_lsn
------------------------
0/5091F820
(1 row)
postgres=# select pg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
-------------------------------
2019-10-15 16:37:12.490004+04
(1 row)
postgres=#
Version 11 :-
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_por\x86_64/pgdg-centos11-11-2t | backend_start | backend_xmin | state | sent_lsn | write_
lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+------------+-------
-----+------------+------------+-----------+-----------+------------+---------------+------------
8009 | 10 | postgres | walreceiver | 127.0.0.1 | | 35580 | 2019-10-15 16:06:08.47769+04 | | streaming | 0/5091F820 | 0/5091
F820 | 0/5091F820 | 0/5091F820 | | | | 0 | async
(1 row)
postgres=#
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
pid | 8009
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 127.0.0.1
client_hostname |
client_port | 35580
backend_start | 2019-10-15 16:06:08.47769+04
backend_xmin |
state | streaming
sent_lsn | 0/5091F820
write_lsn | 0/5091F820
flush_lsn | 0/5091F820
replay_lsn | 0/5091F820
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
postgres=#
--TEST 4 , let us stop the standbY_server and check the status is in primary --
stop sedondary
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server stop
waiting for server to shut down.... done
server stopped
-bash-4.2$ date
Tue Oct 15 17:08:33 +04 2019
-bash-4.2$
-- the pg_stat_replication query doesn't shows the status of the replication, Since the standby_server database is down
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
(0 rows)
postgres=#
bash-4.2$ date
Tue Oct 15 17:09:21 +04 2019
-bash-4.2$
-- start secondary now
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D standby_server start
waiting for server to start....2019-10-15 17:09:52.811 +04 [8355] LOG: listening on IPv4 address "0.0.0.0", port 5491
2019-10-15 17:09:52.811 +04 [8355] LOG: listening on IPv6 address "::", port 5491
2019-10-15 17:09:52.812 +04 [8355] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5491"
2019-10-15 17:09:52.814 +04 [8355] LOG: listening on Unix socket "/tmp/.s.PGSQL.5491"
2019-10-15 17:09:52.833 +04 [8355] LOG: redirecting log output to logging collector process
2019-10-15 17:09:52.833 +04 [8355] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
-- check it in primary
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
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
------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------
------+------------+------------+-----------+-----------+------------+---------------+------------
8363 | 10 | postgres | walreceiver | 127.0.0.1 | | 35582 | 2019-10-15 17:09:52.866973+04 | | streaming | 0/5091F820 | 0/509
1F820 | 0/5091F820 | 0/5091F820 | | | | 0 | async
(1 row)
postgres=#
--check it in recovery
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#
