Show replication status in PostgreSQL

This is a small tip to view the replication status of slave postgresql servers. The simplest way to see the replication state on a master server is executing this query:

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

This query can output can be like this:

postgres=# select client_addr, state, sent_location
postgres-# write_location, flush_location, replay_location, sync_priority from pg_stat_replication;
 client_addr |   state   | write_location | flush_location | replay_location | sync_priority
-------------+-----------+----------------+----------------+-----------------+---------------
 10.0.2.184  | streaming | AB/416D178     | AB/416D178     | AB/416D178      |             0
(1 row)

If the slave is up in hot standby mode, you can tell the time in seconds the delay of transactions applied on the slave with this query:

select now() - pg_last_xact_replay_timestamp() AS replication_delay;

This is a possible output:

postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
-------------------
 00:00:08.057668
(1 row)

In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing (this is generally the same limitation as MySQL's SHOW SLAVE STATUS output).

Backup & Restore Database In PostgreSQL (Pg_dump,Pg_restore)

1)Backup data with pg_dump

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/test.backup" test01

To list all of the available options of pg_dump , please issue following command.

pg_dump -?
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name
2) Restore data with pg_restore
pg_restore -i -h localhost -p 5432 -U postgres -d test01 -v 
"/usr/local/backup/test.backup"
To list all of the available options of pg_restore , please issue following command.
pg_restore -?
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode

Postgres Replication with Archiving on Master.

Both postgres machine are on 9.0.12

 

When I set wal_level = 'hot_standby' on master and hot_standby = 'on' in slave. 

It does do streaming repl but no archiving on master?

 

# master

wal_level = 'hot_standby'

archive_mode = on

archive_command = '../pgscripts/archive.sh %p %f'

 

# - Streaming Replication -

max_wal_senders = 3

wal_keep_segments = 8

 

# slave

wal_level = 'hot_standby'

archive_mode = on

archive_command = '../pgscripts/archive.sh %p %f'

checkpoint_segments = 8

hot_standby = on

 

master log:

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

LOG:  connection received: host=[local]

LOG:  connection authorized: user=pgsql database=postgres

LOG:  connection received: host=192.168.4.223 port=56788

LOG:  replication connection authorized: user=repuser host=192.168.4.223 port=56788

 

slave log:

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

LOG:  entering standby mode

LOG:  consistent recovery state reached at 6/B2000078

LOG:  database system is ready to accept read only connections

LOG:  streaming replication successfully connected to primary

LOG:  connection received: host=[local]

LOG:  connection authorized: user=pgsql database=postgres



Create recovery.conf




# Note that recovery.conf must be in $PGDATA directory.

 

# Specifies whether to start the server as a standby. In streaming replication,

# this parameter must to be set to on.

standby_mode          = 'on'

 

# Specifies a connection string which is used for the standby server to connect

# with the primary.

primary_conninfo      = 'host=192.168.4.233 port=5432 user=repuser password=changeme'

 

# Specifies a trigger file whose presence should cause streaming replication to

# end (i.e., failover).

trigger_file = '/var/db/pgsql_backup/postgresql.trigger.standby'

 

# Specifies a command to load archive segments from the WAL archive. If

# wal_keep_segments is a high enough number to retain the WAL segments

# required for the standby server, this may not be necessary. But

# a large workload can cause segments to be recycled before the standby

# is fully synchronized, requiring you to start again from a new base backup.

# restore_command = 'cp /var/db/pgsql_backup/archive/%f "%p"'

 

# archive_cleanup_command = 'pg_archivecleanup /var/db/pgsql_backup/archive %r'