From: David Steele <david@pgmasters.net>
To: Craig James <cjames@emolecules.com>
To: pgsql-admin@postgresql.org
Subject: Re: pgbackrest: backup from standby
Date: Mon, 20 Aug 2018 15:36:27 -0400
Message-ID: <d67a6a2a-5b8b-ee93-ea3f-464b8d8aa6ce@pgmasters.net> (raw)
In-Reply-To: <CAFwQ8rcyq5ymqsqEV0VSyqeVZvhEh-qmU2+fK3EXA0C8uHNWzg@mail.gmail.com>
References: <CAFwQ8rcyq5ymqsqEV0VSyqeVZvhEh-qmU2+fK3EXA0C8uHNWzg@mail.gmail.com>
On 8/20/18 12:12 PM, Craig James 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 <http://radon.emolecules.com;
> backup-user=postgres
It's much easier just to mount the nfs volume to the standby and then
you can use the same configuration on the primary and standby, though I
can see below you have reasons not to do that.
> 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).
This is not supported. If you enable "archive_mode=always" pgBackRest
will complain because there's no way to ensure that multiple server are
not archiving, especially on S3.
> Note that the primary is also archived to a local NFS disk. I want to
> keep that going, too, but create a second archive on S3. (The reason for
> this is that we're migrating from our own hardware to AWS, and during
> the transition need both systems fully functional.)
It's possible to backup/archive to multiple repos but the configuration
is complex. I recommend that you migrate your pgBackRest repo to S3
first. That goes like:
1) Sync the pgbackrest repo to S3 (using s3fs)
2) Stop archiving by setting archive_command=false
3) Sync the pgbackrest repo again to catch any files that were missed
the first time.
4) Reconfigure for S3 and enable archiving.
> There are sections in the user guide on /Backup from a Standby
> <https://pgbackrest.org/user-guide.html#standby-backup>/ and on /S3
> Support <https://pgbackrest.org/user-guide.html#s3-support>/, but I
> can't sort out how to combine them. Apparently I need to define
> pg1-host/pg1-path, and pg2-host/pg2-path, then a stanza for S3. But does
> all this go in the pgbackrest.conf of the primary ("radon" in my case),
> or on the standby, or somehow split across both?
You have an asymmetrical configuration here since there is not backup
server and shown in the pgBackRest docs. That makes configuration quite
a bit harder and make failover harder.
> Another question: if I modify these files, will the current standby
> replica keep streaming properly? That is, can I add the S3 repository to
> the current primary/standby pair without having to start over?
Yes, but multiple repositories are not natively support at this time so
it requires multiple configurations and some care.
Regards,
--
-David
david@pgmasters.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: david@pgmasters.net, cjames@emolecules.com
Subject: Re: pgbackrest: backup from standby
In-Reply-To: <d67a6a2a-5b8b-ee93-ea3f-464b8d8aa6ce@pgmasters.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox