3

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?

Improve this question
1
  • 1
    My answer is quite general, as you omitted key details from your question like the actual configuration you're using. Post a new question with a detailed, specific information on how you configured things, relevant config files and settings changed, etc, if you want specific help on your configuration. Include log file excerpts too.
    – Craig Ringer
    Jul 30, 2015 at 1:48

1 Answer 1

Reset to default
4

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.

Improve this answer
7
  • Thank you, Craig. Are you saying that if I go the 'wal shipping' route that I do NOT require the standby and the primary to have tcp communications with each other? That it is purely just via WAL shipping and the standby finding and pulling wal files via the recovery command? I can remain in hot standby mode this way? I had originally tried this but was not confident in the results I was seeing. Much appreciated.
    – n8gard
    Jul 30, 2015 at 13:52
  • 1
    @n8gard Correct. You can optionally have the standby connect to the master too, and this will reduce replication lag but is not required.
    – Craig Ringer
    Jul 30, 2015 at 13:58
  • Ok. One last clarification, aside from setting 'standby' 'on' in the recovery.conf, I can set 'hot_standby' in the standby's postgresql.conf without these communication between standby server and primary server?
    – n8gard
    Jul 30, 2015 at 14:11
  • 1
    @n8gard Correct. Hot standby does not depend on streaming replication.
    – Craig Ringer
    Jul 30, 2015 at 14:15
  • 1
    @n8gard Well. The error message explains the problem. The manual link i provided above has more detail
    – Craig Ringer
    Jul 30, 2015 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.