How to do it…

Follow these steps for initial configuration of file-based log shipping:

  1. Identify your archive location and ensure that it has sufficient space. This recipe assumes that the archive is a directory on the standby node, identified by the $PGARCHIVE environment variable. This is set on both the master and standby nodes, as the master must write to the archive and the standby must read from it. The standby node is identified on the master using $STANDBYNODE.
  2. Configure replication security. Perform a key exchange to allow the master and the standby to run the rsync command in either direction.
  3. Adjust the master's parameters in postgresql.conf, as follows:
 wal_level = 'archive' 
 archive_mode = on 
 archive_command = 'scp %p $STANDBYNODE:$PGARCHIVE/%f' 
 archive_timeout = 30
  1. Adjust Hot Standby parameters if required (see the Hot Standby and read scalability recipe).
  2. Take a base backup, very similar to the process for taking a physical backup described in Chapter 11, Backup and Recovery.
  3. Start the backup by running the following command:
psql -c "select pg_start_backup('base backup for log shipping')"
  1. Copy the data files (excluding the pg_wal directory). Note that this requires some security configuration to ensure that rsync can be executed without needing to provide a password when it executes. If you skipped step 2, do this now, as follows:
rsync -cva --inplace --exclude=*pg_wal* \  
${PGDATA}/ $STANDBYNODE:$PGDATA
  1. Stop the backup by running the following command:
psql -c "select pg_stop_backup(), current_timestamp"
  1. Set the recovery.conf parameters in the data directory on the standby server, as follows:
standby_mode  =  'on'
restore_command  =  'cp  $PGARCHIVE/%f  %p' archive_cleanup_command  =  'pg_archivecleanup  $PGARCHIVE  %r' trigger_file  =  '/tmp/postgresql.trigger.5432'
  1. Start the standby server.
  2. Carefully monitor the replication delay until the catch-up period is over.
    During the initial catch-up period, the replication delay will be much higher than we would normally expect it to be. You are advised to set hot_standby to off for the initial period only.

Use a script; don't do this by hand, even when testing or just exploring the capabilities. If you make a mistake, you'd want to rerun things from the start again, and doing things manually is both laborious and an extra source of error.