Postgresql Check Standby Replication

Monitoring postgresql replication is in progress but in not adequate I think. here is some scripts for checking a standby replication. In some cases replication has terminated because of some problems. this scripts give you a glance and you will see the problem about replication. But do not forget to check the log file, you can miss some problems if you do mot check the log file. As I said before postresql views is not adequate.

check overall information about replication on primary

1	select * from pg_stat_replication;

If you are using replication slots then replication slots information view is

1	select * from pg_replication_slots;

check overall information about replication on standby

1	select * from pg_stat_wal_receiver;

check status of replication on standby (version > 10)

1 2 3 4 5

select  pg_is_in_recovery(),         pg_is_wal_replay_paused(),         pg_last_wal_receive_lsn(),         pg_last_wal_replay_lsn(),         pg_last_xact_replay_timestamp();

check status of replication on standby (version < 10)

1 2 3 4

select  pg_is_in_recovery(),         pg_last_xlog_receive_location(),         pg_last_xlog_replay_location(),         pg_last_xact_replay_timestamp();

run this query for a few times and check if lsn/xlocation number ad replay timestamp is changing.
if it has not change then check the primary log file there could be an error about replication.

–check lag on standby (version >10)

1 2 3 4

SELECT  CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()         THEN 0         ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())         END AS log_delay;

check lag on standby (version <10)

1 2 3 4

SELECT  CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()         THEN 0         ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())         END AS log_delay;

check the primary lsn and compare with standby’s last applied lsn

get current lsn from primary

1 2 3 4

SELECT pg_current_wal_lsn();       -[ RECORD 1 ]------+-----------     pg_current_wal_lsn | 0/1ADE2F80

get last applied lsn from standby

1 2 3 4 5 6 7 8 9 10 11 12

select  pg_is_in_recovery(),         pg_is_wal_replay_paused(),         pg_last_wal_receive_lsn(),         pg_last_wal_replay_lsn(),         pg_last_xact_replay_timestamp();               -[ RECORD 1 ]-----------------+------------------------------         pg_is_in_recovery             | t         pg_is_wal_replay_paused       | f         pg_last_wal_receive_lsn       | 0/1005EEEF         pg_last_wal_replay_lsn        | 0/1005EEEF         pg_last_xact_replay_timestamp | 2021-05-05 13:01:14.328936+00

calculate difference between two lsn from above queries

1 2 3 4 5 6

pg_wal_lsn_diff(lsn pg_lsn, lsn pg_lsn) (version > 10 ) pg_xlog_location_diff (location pg_lsn, location pg_lsn) (version < 10)   select pg_wal_lsn_diff('0/1ADE2F80','0/1005EEEF');     -[ RECORD 1 ]---+---------     pg_wal_lsn_diff | 181944465

previous query return result in bytes, we can get gap size in human readable like this

1 2 3 4 5 6 7

select round(181944465/pow(1024,3.0),2) missing_lsn_GiB;     -[ RECORD 1 ]---+-----     missing_lsn_gib | 0.17   select round(181944465/pow(1024,2.0),2) missing_lsn_GiB;     -[ RECORD 1 ]---+-------     missing_lsn_gib | 173.52

CONFIGURATION - Memory Related Parameters

==============CONFIGURATION==========================

-bash-4.1$ cd data/

-bash-4.1$ pwd

/opt/PostgreSQL/10/data

-bash-4.1$ vi postgresql.conf 

shared_buffers=256MB

:wq

-bash-4.1$ pg_ctl -D ../data/ restart

server signaled

-bash-4.1$ psql -p 5432 -c "show shared_buffers;"

Password: 

 shared_buffers 

----------------

 256MB

(1 row)

-bash-4.1$ vi postgresql.conf 

work_mem=10MB

:wq

-bash-4.1$ pg_ctl -D ../data/ reload

server signaled

-bash-4.1$ psql -p 5432 -c "show work_mem;"

Password: 

 work_mem 

----------

 10MB

(1 row)

-bash-4.1$ psql -p 5432

Password: 

psql.bin (10.9)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 edbstore  |                                                            | {}

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# set work_mem to '100MB';

SET

postgres=# show work_mem;

 work_mem 

----------

 100MB

(1 row)

postgres=# \c

You are now connected to database "postgres" as user "postgres".

postgres=# show work_mem;

 work_mem 

----------

 10MB

(1 row)

postgres=# alter user edbstore set work_mem to '100MB';

ALTER ROLE

postgres=# \c postgres edbstore

Password for user edbstore: 

You are now connected to database "postgres" as user "edbstore".

postgres=> show work_mem;

 work_mem 

----------

 100MB

(1 row)

postgres=# \c  edbstore 

You are now connected to database "edbstore" as user "postgres".

edbstore=# \dn

   List of schemas

   Name   |  Owner   

----------+----------

 accounts | u02

 hr       | u01

 public   | postgres

(3 rows)

edbstore=# \c edbstore u01

You are now connected to database "edbstore" as user "u01".

edbstore=> show search_path;

   search_path   

-----------------

 "$user", public

(1 row)

edbstore=> \c edbstore postgres

You are now connected to database "edbstore" as user "postgres".

edbstore=# alter user u01 set search_path to hr;

ALTER ROLE

edbstore=# \c edbstore u01

You are now connected to database "edbstore" as user "u01".

edbstore=> show search_path;

 search_path 

-------------

 hr

(1 row)

edbstore=> 

edbstore=# select * from pg_shadow;

 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil |    useconfig     

----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+------------------

 postgres |       10 | t           | t        | t       | t            |                                     |          | 

 u01      |    16411 | f           | f        | f       | f            | md5b8f200a2ca29c977c85af06ed81fde21 |          | {search_path=hr}

 u02      |    16412 | f           | f        | f       | f            | md5c764c50c8397ef7afa316dd36a71cb44 |          | 

(3 rows)

edbstore=# 

edbstore=# alter system set shared_buffers to '512MB';

ALTER SYSTEM

edbstore=# show shared_buffers;

 shared_buffers 

----------------

 128MB

(1 row)

edbstore=# \q

-bash-4.1$ psql -p 5433

psql.bin (10.9)

Type "help" for help.

postgres=# show data_directory;

  data_directory  

------------------

 /home/prod

(1 row)

postgres=# \q

-bash-4.1$ pg_ctl -D /home/prod/ restart

-bash-4.1$ psql -p 5433

postgres=# show shared_buffers;

 shared_buffers 

----------------

 512MB

(1 row)

postgres=# 

postgres=# select name,setting,sourcefile from pg_settings where name like 'shared_buffers%';

      name      | setting |              sourcefile               

----------------+---------+---------------------------------------

 shared_buffers | 65536   | /home/prod/postgresql.auto.conf

(1 row)

postgres=# 

============CASE STUDY============================

1. Open psql and write a statement to change work_mem to 10MB. This change must persist across server restarts

-bash-4.1$ vi postgresql.conf 

work_mem=10MB

:wq

-bash-4.1$ pg_ctl -D ../data/ reload

server signaled

-bash-4.1$ psql -p 5432 -c "show work_mem;"

Password: 

 work_mem 

----------

10MB

2. Open psql and write a statement to change work_mem to 20MB for the current session

set work_mem to '20MB';

3. Open psql and write a statement to change work_mem to 1 MB for the postgres user

alter user edbstore set work_mem to '1MB';

4. Write a query to list all parameters requiring a server restart

select name,applie from pg_file_settings;

5. Open the configuration file for your Postgres database cluster and make the following changes

    

    Maximum allowed connections to 50

    max_connections = 50

    Authentication time to 10 mins

    authentication_timeout=10m default is 1m 

    Shared buffers to 256 MB

    shared_buffers=256MB

    work_mem to 10 MB

    work_mem=10MB

    wal_buffers to 8MB

    wal_buffers=8MB