aAPT
dDebian
fFFmpeg
jJava
mMercurial: Usage
oOCaml
pPostgreSQL

Home Applications PostgreSQL

PostgreSQL: Steaming Replication

Primary server

  1. Create user for replication
sudo -u postgres createuser -U postgres replicant -P -c 5 --replication

  1. Add this user to pg_hba.conf

  2. Adjust postgresql.conf

wal_level = hot_standby
max_wal_senders = 5
archive_mode = on
archive_command = 'test ! -f /srv/nfs/archive/%f && cp %p /srv/nfs/archive/%f'
  1. Restart the master server

Standby server

  1. Prepare for backup, clean the original cluster data
rm -fv /var/lib/postgresql/9.x/main
  1. Create initial backup
 sudo -u postgres pg_basebackup -h [master address] -D /var/lib/postgresql/9.x/main -U replicant -v -P --xlog-method=stream
  1. Adjust postgresql.conf
hot_standby = on
  1. Create recovery.conf1
cp -avr /usr/share/postgresql/9.x/recovery.conf.sample /etc/postgresql/9.x/main/recovery.conf
  1. Adjust recovery.conf
standby_mode = on
primary_conninfo = 'host=[master address] port=5432 user=replicant password=[password]'
restore_command = 'cp /mnt/archive/wal/%f %p'
archive_cleanup_command = 'pg_archivecleanup /mnt/archive/wal/ %r'
trigger_file = '/tmp/postgresql.trigger.5432'
  1. Link recovery.conf to cluster data directory (recovery.conf must be in the $PGDATA directory)
cd /var/lib/postgresql/9.x/main
ln -s /etc/postgresql/9.x/main/recovery.conf
  1. Start the standby server
Notes

1 On PostgreSQL 12 and above, archive recovery, streaming replication, and PITR are configured using normal server configuration parameters (i.e. postgresql.conf configuration file)ref.

See also

PostgreSQL wiki