Subject: Re: pgbackrest: backup from standby
To: Craig James <cjames@emolecules.com>, pgsql-admin@postgresql.org
References: 
 <CAFwQ8rcyq5ymqsqEV0VSyqeVZvhEh-qmU2+fK3EXA0C8uHNWzg@mail.gmail.com>
From: David Steele <david@pgmasters.net>
Openpgp: preference=signencrypt
Autocrypt: addr=david@pgmasters.net; prefer-encrypt=mutual; keydata=
 xsFNBFSheh0BEADGNb3c1kOaX5OGUDhqHTwJ3JW7XAllfWLdknpRg+/fihgLPPRnQHZhjq28
 nmMLqM+C8TjezT2BgVACeu8qqASnuzN/E5+bAl9hVcgCP4eV/lEIWwVxmhcHqSdeWqWZSBRB
 tf07fLBnz22+3LwVsESN+fanWxnoqqvE0+hyGnCEDhOBkihgbYHuq4vw1+xgoGN1uDk4NShV
 El0ZWO90P4e5mOWA2Zsex+8YhGJlfRwMpU69okFCvjpWJKE+ufeJdlrxEpSSKW7SQ0kG1VF7
 CvDKpF70TCWkb4Bagb6c7lwZrMjxuBnlIvOlV3RRY67PAaVIFRRvQJeJABOPHp8HnH8cwici
 7gwwfWBrZZeAdPqs6VNjANqI9uaDasRnD3yHP6CcHIAeCl5hYnzNTkIqBRlnmD5y8s0VRDLr
 6JR3FxscOsvwXPJ+He1RpRlxQDlBboaRFUz8endeQHhGUeHyekQcSYUaz4PX3OJBG0Xi7nr8
 EOP6zOeP61z3dL8NuO3QjgPiygvS0qwMEPvOYo52nEGYn08nvFVajI8yxOXFnIwnmpgr5gdq
 nL9ga72tvSyJQAUx+YB7r1bKjRn/Mlt/fPIS5PXONofiO1K8uX+pzvIX6dnm7ihpFf1XC+uG
 5+DKBnU7yS7jCRL52GxWHbuC3mT4tvzqH2b13TLng1Y7Izi+hwARAQABzSJEYXZpZCBTdGVl
 bGUgPGRhdmlkQHBnbWFzdGVycy5uZXQ+wsF5BBMBCAAjAhsDAh4BAheABQsJCAcDBRUKCQgL
 BBYCAwEFAlShuEECGQEACgkQn830JKJznPPsLhAAtKkIu0mFfxcVqApg4PNjZKYY4TWeN+I+
 go/VKX8QWMadr33t8Sxcgyn48bjBYzgWQXhF4rMcFxDxI3ly5bdE8QKZkhFJJT7N2fBoo9Zm
 ZOsOvPd+5hz07VZpgNSiTXMAphj8+GSTLbYufm/OUnfpS1eovPtPdYPQpNC3RVHA0gtisz26
 NgMLg6QN0iQuITs9N+wiaGtyVIornl4XAY2dM/Hvfr1aDFkukNNr52sc/mGqocG/dKC7dONA
 3860y2MLszQcbAE45iEkmwpGkvxyc3i3tvdD3VNlVI1MKJUBnxFPUEI0lVq5WpApFSIU31+j
 aNunKIcfVxRnM8FTJBsw3RnFRpSsEtiLaZRm5sOZHa9+dy/DWDb+8O1I+momCo88+Jksd6pZ
 2Tz9msOiJiTnFsCZgfbbmayH6w8ZyfHuO0DuxScM1UK+6CLC2VVjQFuH6WHqJpaENg5INswS
 qpUc05iC4i+6dZOe44cviX6LpHuP8vWM0E63CMun0l42yHWy+k6nLvx/fj7t++tF2JIJ92OG
 n/2MkM5lIKknRg27UjYAspD6f3NSZUMIe6tGyWPTtLOu8zOL5CeRyCHMWwUVDKJSBzlK2mV8
 e0EbmtUJFsoNWXWKPKUIxX7JXPN3v523HmOhtyz5SnkGtwEqCZiv/JccYmh+S2IokAuscLNP
 EJrOwU0EVKF6HQEQALPpUsP67xfcj6NUMyySXSytUzMIVi5XNTyazuNcVfFZzT9QL8wFQkgy
 i/HOD3tmvdGxz6H8V+v+cszp88gCd8TwClss7u+iPsLlAQQWC4q0WiX4OhwFv2H004iuft8i
 odjiWDkYEgIFxlPNxQfHFmchkyODIJXQmnzbgsXwGZtJzAxEVh6AgYJqE/Ev/+kTJhJ7703n
 oIb+PXvafoB5axnU4nf7DuFa9X+q2/ZfzyAXUCKvzDvxb24iUgWFzlWBx0CnI21Kh6aS6hR6
 o00D2OBiU+yCpW9eRTi3SuDuslmtCUaZz5V4SHo37ochEYfZ2LYOPsBO3PUMx8T7MDYrmv/x
 4W5rdjugz4NXWROStNNLpS74LW20rdrEKMDltph7U2zfjjS663K+CUxoXn/ha2WnzBocoY5o
 KwdViLln4s0ivOTa4clHJJUsYiAxLShAe2AP+5kJim24tAabbEOsrRTn9RpT7J8jVT/G0YDE
 glujEt0U04Akkj5NO4G7vJP/AyX5zKZetkp/HFiFBZMkUuXD/KgDc5EM2fpQtaxGGY2fcQqY
 VHUbbxstxyl5d349M9rok7O90hw9BkP0Q8UEpgmlZ6ftwx+ZfZmBEdQ7HcMHnSPSNJkXt/Vi
 ozlcrSnkVXzCXvH9DHrIpgenp8btsJ2XyjSdrEu4jAqYeirW+3qRABEBAAHCwV8EGAEIAAkF
 AlSheh0CGwwACgkQn830JKJznPNmERAApYaQK6i9F6hM2RUD/PqQC45WmzOXK9bDSS9hdk4p
 Nc5jSW2+FkcMFe1XFG2NyGf1v5KBPxuDTQp35PIwRsL59xNmPICu0ae6zmCPUpxbMIEzb/VU
 wXqeuNp3cRVll06goDt5w+2LlAtBrxuDjiWxby9LH+vsIUMLWYu+M50pdtOcXOsO1+zyfs3w
 U6xRZ3PaFDKO1cB68okDFG/cFHnYfY5aETV7LgVBHEOQLwL01U01QC/iqY/aNO4iUH/+PBc0
 R6mYlP7IqDQOQVTJ0dXP365thl0Q7c9e2JQSS9wuSv2EEiR//nnZREtLyrQNLwsXE8UFBDe7
 uA8Lm2ldmQYpHHKAcorGVTHYJaaus2LjH8tZ1i4RGg4hKMJSHil/5C+Ghl+PmcACVeGaxB0c
 mJ8+0jyWDkLCDMAXJJyvXRWIONTAxVt5kKg1/iMAq1IQYNd66agnmgxdSvKLPUo0JJ5fhQJp
 2bqDDTrINTZg+Rub2HeoOMxYJsMypK+6aY4iVlm2Z7A9SgyQDjGAXzD9+XaezNFeXxfPbjo6
 46qA9SycdYhiwaTcTQakiLkZWRIkSjGQwNCK9NktE1fYnXfR0+WaWFduQ4BfM5uta1bt8esB
 /mLEjVYS9RRAWZ/untJrR9JYGTC4Mu6tcLGZD7385yX3TNM1iaIUGxlKkHHCZCnILyo=
Message-ID: <d67a6a2a-5b8b-ee93-ea3f-464b8d8aa6ce@pgmasters.net>
Date: Mon, 20 Aug 2018 15:36:27 -0400
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: 
 <CAFwQ8rcyq5ymqsqEV0VSyqeVZvhEh-qmU2+fK3EXA0C8uHNWzg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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