From: | Craig James <cjames(at)emolecules(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: pgbackrest: backup from standby |
Date: | 2018-08-20 18:15:14 |
Message-ID: | CAFwQ8rd3muqd6hFxi_bK83Tp7ovijjQF2nTvLvxkgL5=dMnoUg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Aug 20, 2018 at 9:12 AM, Craig James <cjames(at)emolecules(dot)com> 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.
---------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2018-08-20 19:36:27 | Re: pgbackrest: backup from standby |
Previous Message | Craig James | 2018-08-20 16:12:48 | pgbackrest: backup from standby |