Page tree
Skip to end of metadata
Go to start of metadata

Introduction

Postgres has a large matrix of replication methods, in order to better protect against data loss in the event of primary database failure,

We decided on streaming replication, and followed the guide at https://wiki.postgresql.org/wiki/Streaming_Replication

Here's a picture of what we want to achieve

Setting up the master - Example Only!!!!!! Modify "archive command" to match correct group!!!!!!

/var/lib/pgsql/9.5/data/postgresql.conf
--- postgresql.conf.orig
+++ postgresql.conf

 # - Connection Settings -
-#listen_addresses = 'localhost'                # what IP address(es) to listen on;
+listen_addresses = '*'                         # what IP address(es) to listen on;

 # - Settings -
-#wal_level = minimal                   # minimal, archive, hot_standby, or logical
+wal_level = hot_standby                        # minimal, archive, hot_standby, or logical

-#archive_mode = off            # enables archiving; off, on, or always
+archive_mode = on              # enables archiving; off, on, or always

-#archive_command = ''          # command to use to archive a logfile segment
+archive_command = 'cp %p /links/groups/itsc/postgres_wal_logs/wiki03/%f'               # command to use to archive a logfile segment

 # Set these on the master and on any standby that will send replication data.
-#max_wal_senders = 0           # max number of walsender processes
+max_wal_senders = 5            # max number of walsender processes

-#wal_keep_segments = 0         # in logfile segments, 16MB each; 0 disables
+wal_keep_segments = 32         # in logfile segments, 16MB each; 0 disables

 # These settings are ignored on a master server.
-#hot_standby = off                     # "on" allows queries during recovery
+hot_standby = on                       # "on" allows queries during recovery
~# psql -U postgres
postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD '********' LOGIN

The connection setting in pg_hba.conf must only include the replica host's IP address

We tried with 10.20.10.192/26 but whenever we tried connect we got this in the logs:

FATAL:  no pg_hba.conf entry for replication connection from host "10.20.10.207", user "replication", SSL off
/var/lib/pgsql/9.5/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    replication        replication     10.20.10.207/32         md5

Copy the database to the replica

  • Start postgres on the primary server.
  •  Make a base backup by copying the primary server's data directory to the standby server.
    - Do it with pg_(start|stop)_backup and rsync on the primary

 

As user root:
# STANDBY=bs-wiki04
# PGDATA=/var/lib/pgsql/9.5/data   ## On Ubuntu it's /var/lib/postgresql/9.5/main
# sudo -u postgres psql -c "SELECT pg_start_backup('label', true)"
# rsync -ac --delete ${PGDATA}/ ${STANDBY}:${PGDATA} --exclude postmaster.pid --exclude pg_hba.conf --exclude recovery.done
# sudo -u postgres psql -c "SELECT pg_stop_backup()"

 

Setting up the replica

/var/lib/pgsql/9.5/data/postgresql.conf is the same as on the master.

 

/var/lib/pgsql/9.5/data/recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf <-- Huh? - John
# 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=10.20.10.197 port=5432 user=replication password=r3pl1cator'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/var/lib/pgsql/failover_db_trigger'
# 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 /links/groups/itsc/postgres_wal_logs/wiki03/%f "%p"'
# For point in time recovery, read Section 24.3.4. "Recovering Using a Continuous Archive Backup"
# at https://www.postgresql.org/docs/9.5/static/continuous-archiving.html
# recovery_target_time = ''        # e.g. '2016-12-12 04:00:00 CET'

We set up pg_hba.conf on the replica, in case we fail over, and the replica becomes the master.

/var/lib/pgsql/9.5/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host replication        replication     10.20.10.197/32         md5

 

  • Start postgres

Watching it at work

From the master
# psql -U postgres -c "SELECT pg_current_xlog_location()"
From the replica
# psql -U postgres -c "select pg_last_xlog_receive_location()"

Troubleshooting

From the master
 # ps -ef | grep postgres
...........
postgres  7121  6393  0 15:14 ?        00:00:00 postgres: wal sender process replication 10.20.10.197(33786) streaming 0/F7000140
...........
From the slave
# ps -ef | grep postgres
...........
postgres 11866 11833  0 15:14 ?        00:00:00 postgres: wal receiver process   streaming 0/F7000140
........... 

Failover

On standby

sudo -u postgres  touch /var/lib/pgsql/failover_db_trigger

On slave

/etc/init.d/postgresql-9.5  stop

sudo -u postgres psql -c "SELECT pg_start_backup('label', true)"

$ rsync -ac --delete ${PGDATA}/ ${STANDBY}:/var/lib/pgsql/9.5/data --exclude postmaster.pid --exclude postgresql.conf --exclude pg_hba.conf --exclude recovery.*

$ sudo -u postgres psql -c "SELECT pg_stop_backup()"

 

Nagios check

command[check_postgresql_slave]=/usr/lib64/nagios/plugins/check_pgactivity -s is_hot_standby -U postgres
command[check_postgresql_replication]=/usr/lib64/nagios/plugins/check_pgactivity -s streaming_delta -U postgres -w3m -c5m
command[check_postgresql_master]=/usr/lib64/nagios/plugins/check_pgactivity -s is_master -U postgres

 

 

  • No labels