PostgreSQL – How to Have Remote Standby Server in Different Location (AWS)

awspostgresqlpostgresql-9.4replication

We have a primary PostgreSQL 9.4 server running multiple instances in our DC. It is using Continuous Archiving via WAL-E out to AWS S3. I would like to have a hot/warm standby server in our VPC within AWS EC2 that would be ready to take over should we need to DR. I can recover the server via WAL-E but can't seem to get it to work in hot_standby mode. What could I be missing? Docs I read seem to suggest that the standby needs to talk to the primary? Is this so? Is this required when log-shipping via WAL-E?

Best Answer

There are two ways to run a replica. You can use either, or both together:

  • Streaming replication, where the replica makes a PostgreSQL protocol connection to the standby as configured with primary_conninfo in recovery.conf; or

  • WAL shipping, where the replica runs a restore_command (set in recovery.conf that fetches WAL archives to replay.

See recovery.conf.

Both require that standby_mode be set in recovery.conf.

WAL-E is designed to be used as an archive_command on the master, to store WAL into S3, then as a recovery_command on the replica to fetch WAL. There is no need for a streaming replication connection. You can add one anyway, and it will be used where available, and the system will fall back to WAL archive recovery if it can't connect over streaming replication. Or you can just use WAL-based archiving.

The main advantage of using streaming replication as well is that it's more timely; you don't have to wait until an entire WAL archive is filled and shipped before the replica sees the changes.

For more information see the manual.

Related Question