1

I'm planning for a tricky (at least for me) migration whereby I have master postgres 10 db from a cloud provider that will not allow for replication user connection. I do have a base backup and WAL getting shipped to AWS S3. I've easily set up a log shipped replica as a hot standby to promote and officially migrate off initial cloud platform.

However, I'd also like to have another warm stand by ready and available immediately for HA after the initial log shipped replica is promoted in the event of a failure I have something immediately available. There are several TB of data so it takes a few hours to generate a replica representing significant risk after database promotion.

1
  • Can't you have a second standby just like your first one, then as soon as you promote one of your standbys, point the other one to the promoted one?
    – jjanes
    Oct 31 '20 at 15:20
1

PostgreSQL supports cascading replication, so you can configure your warm standby as a normal streaming replication standby server to your WAL shipping standby.

If you have recovery_target_timeline = 'latest' on that standby server (default in later PostgreSQL versions), that second standby server will keep replicating when you promote your WAL shipping standby.

6
  • I’ve struggled with this approach without success. For example, first I establish the hot standby log shipped but the cascaded second standby needs a connection (primary_conninfo) to hot standby replica in a recovery.conf but I don’t have a replication user to use because the initial cloud provider doesn’t allow for one to be created and accessed.
    – SciGuyMcQ
    Oct 31 '20 at 12:04
  • 1
    @SciGuyMcQ There must be an existing superuser. They bypass the need for having the replication attribute, so alter pg_hba or pg_ident to let yourself in as that user. You probably won't be able to use password authentication (until after you promote and can reset it)
    – jjanes
    Oct 31 '20 at 15:44
  • Oh duh. Thanks for slapping some sense into me. Yes, that will work.
    – SciGuyMcQ
    Oct 31 '20 at 15:59
  • Do I want to set recovery_target_timeline to latest for the upstream cascading replica or the down stream or both
    – SciGuyMcQ
    Oct 31 '20 at 17:11
  • 1
    You can set it for both, but for the downstream it is important if it should survive promotion of the log shipping standby. Nov 2 '20 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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