Re: pgbackrest: backup from standby

[Craig James <cjames@xxxxxxxxxxxxxx>]

On Mon, Aug 20, 2018 at 9:12 AM, Craig James <cjames@xxxxxxxxxxxxxx> wrote:

The pgbackrest documentation has a section on backing up from a standby, but I'm having trouble figuring it out. I think it's just a terminology problem: I can't figure out which configuration directives go on which server.

To begin with, I already have streaming replication running:

Primary server (live database): "radon"

[emolecules]

  db-path=/var/lib/postgresql/9.6/main

[global]

  repo-path=/pg_archive/pgbackrest

  retention-full=10

  backup-user=postgres

  log-level-file=detail

Hot-standby server: "standby"

[emolecules]

  db-path=/var/lib/postgresql/9.6/main

  recovery-option=standby_mode=on

 [global]

  backup-host=radon.emolecules.com

  backup-user=postgres

The standby server is on Amazon AWS, and I want to set up a backup Amazon S3 archive that gets its snapshot and WAL files from the standby in order to save bandwidth costs from the primary (don't want to copy the data twice).

Partially answering my own question, but I'm still confused. It looks to me like I have to create *two* stanzas for the same database on the standby server, because there are two repositories (the original from which it was created, and the S3 one). I need the first stanza because that's how the standby is created from the primary ("pgbackrest --stanza=emolecules1 restore"), and I need the second stanza to make a backup to s3 ("pgbackup --stanza=emolecules2 backup").

Pgbackrest doesn't seem to have an option to specify which repository to use, even though the repo-xxx options can be indexed, i.e. repo1-xxx, repo2-xxx, etc. Or maybe I overlooked something?

So here's my best guess. The master server configuration stays as shown above, but the slave server requires two stanzas:

[global]

  backup-user=postgres

[emolecules1]

  repo1-host=radon.emolecules.com

  pg1-path=/var/lib/postgresql/9.6/main

  recovery-option=standby_mode=on

[emolecules2]

  process-max=4

  repo1-type=s3

  repo1-cipher-type=none

  repo1-path=/production

  repo1-retention-diff=5

  repo1-retention-full=5

  repo1-s3-bucket=some-bucket-name-us-west-2

  repo1-s3-endpoint=s3.amazonaws.com

  repo1-s3-key=XXXXXXX

  repo1-s3-key-secret=xxxxxxxxxxxxxxxxxxxxx

  repo1-s3-region=us-west-2

  start-fast=y

  stop-auto=y

[global:archive-push]

  compress-level=3

And then I do the S3 backups using "--stanza=emolecules2".

Another point I'm not 100% sure of. On the standby server, do I just add

archive_command = 'pgbackrest --stanza=emolecules archive-push %p'

to the postgresql.conf file, just as I would on the primary? I.e. will this work, even though it's using streaming replication from the primary?

Thanks!

Craig

--

---------------------------------
Craig A. James

Chief Technology Officer

eMolecules, Inc.

---------------------------------