Re: Setting up continuous archiving

Hi,

Thanks for your replies. By the way, I'm now running PostgreSQL 10 :)
My idea was to start with continuous archiving, then start a
log-shipping standby, then make it use streaming replication. Since
I'm kind of overwhelmed with information, options to be considered.

Anyways, I'm now trying to decide which one to use: pgBackRest or
Barman :) Barman's documentation is easier to follow, at least for me.
But it doesn't allow partial PITR. That is, you can't have full weekly
backups of the last couple of months (discrete) in addition to base
backups + WAL logs of the last few weeks (continuous).

pgBackRest doesn't seem to allow the latter: recovery to any point in
time, only to some discrete moments. Correct me if I'm wrong.

Then, with pgBackRest you can run standby that uses streaming
replication. Barman delegates that to repmgr. Which looks like a more
mature (?) solution. Probably easier to switch the site to the slave,
and back.

So, ideally I'd like to have standby that uses streaming replication,
plus full weekly backups of the last couple of months, plus ability to
restore to any point in time within the last few weeks period.

Is that doable with both of them (pgBackRest, Barman)? Does it make
sense to use repmgr with pgBackRest?

Regards,
Yuri Kanivetsky
On Wed, Sep 26, 2018 at 8:19 PM Pierre Timmermans <ptim007(at)yahoo(dot)com> wrote:
>
> Hello
>
> What you are doing is called "log shipping", which means that when a wal (write-ahead log) is filled in on the database server you ship it to a backup server via rsync. It is fine but as you said the disadvantage is that the file is shipped only when it is full, so you could have data loss (the last wal not shipped)
>
> A more modern and easy way is to use streaming replication: in this case the logs are streamed continuously to the standby or to the backup server (one can use streaming replication without implementing a standby database). Look at the doc on the next page than the one you referred to (https://www.postgresql.org/docs/9.3/static/high-availability.html)
>
> There is a nice tool that does one you plan to do (rsync of archived file) but also the more modern way (streaming replication): it is called barman: https://www.pgbarman.org/. You should probably use their tool but you can also read the doc to get the concepts and some ideas
>
> Rgds, Pierre
>
>
> On Wednesday, September 26, 2018, 9:21:29 AM GMT+2, Yuri Kanivetsky <yuri(dot)kanivetsky(at)gmail(dot)com> wrote:
>
>
> Hi,
>
> I'm trying to compile a basic set of instruction needed to set up
> continuous archiving and to recover from a backup. I'm running
> PostgreSQL 9.3 on Debian Stretch system.
>
> I've got a database and a backup servers. The idea is to set up WAL
> archiving, and occasionally do full (base) backups. A base backup plus
> WAL segment files from that point onward must provide enough
> information to restore database to the latest state. Except for a
> window for data loss that is caused by the fact that WAL segment files
> aren't transferred momentarily, and more importantly that WAL segment
> files are only transferred upon being filled.
>
> ---
>
> Setting up continuous archiving
>
> * Set up WAL archiving
>
> * on backup server under postgres user
>
> * create /var/lib/postgresql/wal_archive dir
>
> $ mkdir /var/lib/postgresql/wal_archive
>
> * on database server under postgres user
>
> * generate ssh key
>
> $ ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa # providing
> path to key file makes it
> # to not ask questions
>
> * add corresponding record to known_hosts file
>
> $ ssh-keyscan -t rsa BACKUP_SRV >> ~/.ssh/known_hosts
>
> * locally
>
> * authorize login from database to backup server
>
> $ ssh DATABASE_SRV cat ~/.ssh/id_rsa.pub | ssh BACKUP_SRV
> 'mkdir --mode 0700 .ssh; cat >> ~/.ssh/authorized_keys; chmod 0600
> .ssh/authorized_keys'
>
> * on database server under root
>
> * change postgresql.conf
>
> wal_level = archive
> archive_mode = on
> archive_command = 'rsync -a %p
> BACKUP_SRV:/var/lib/postgresql/wal_archive/%f'
>
> * restart PostgreSQL
>
> # systemctl resart postgresql
>
> * Make a base backup
>
> * on database server under root
>
> * add a line to postgresql.conf
>
> max_wal_senders = 1
>
> * add a line to pg_hba.conf
>
> host replication replication BACKUP_SRV_IP/BACKUP_SRV_NETMASK trust
>
> * restart PostgreSQL
>
> # systemctl restart postgresql
>
> * on database server under postgres user
>
> * create replication user
>
> CREATE USER replication WITH REPLICATION;
>
> or
>
> $ createuser --replication replication
>
> * on backup server under postgres user
>
> * make base backup
>
> $ pg_basebackup -h DATABASE_SRV -U replication -D
> /var/lib/postgresql/base_backups/$(date +%Y%m%d-%H%M%S)
>
> Restoring from a backup
>
> * under root
>
> * stop PostgreSQL if running
>
> # systemctl stop postgresql
>
> * under postgres user
>
> * move data dir
>
> $ mv 9.3{,-$(date +%Y%m%d-%H%M%S)}
>
> * copy backup
>
> $ mkdir 9.3
> $ cp -r base_backups/TIMESTAMP 9.3/main
>
> * copy unarchived segment files
>
> $ find 9.3-TIMESTAMP/main/pg_xlog -maxdepth 1 -type f -exec cp -t
> 9.3/main/pg_xlog {} +
>
> * create recovery.conf in 9.3/main
>
> restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
>
> * under root
>
> * start PostgreSQL
>
> # systemctl start postgresql
>
> A few notes.
>
> Running out of space on backup server can lead in its turn to database
> server running out of space, since WAL segment files stop being
> archived and keep piling up. The same might happen when archiving
> falls behind. Which also widens the window for data loss.
>
> WAL archiving doesn't track changes to configuration files.
> pg_basebackup will back up configuration files only if they are inside
> data dir.
>
> If database doesn't generate much WAL traffic, there could be a long
> delay between completing a transaction and archiving its results
> (archive_timeout).
>
> You might want to prevent users from accessing the database until
> you're sure the recovery was successful (pg_hba.conf).
>
> I'm not considering here possible issues with tablespaces and other caveats:
>
> https://www.postgresql.org/docs/9.3/static/continuous-archiving.html#CONTINUOUS-ARCHIVING-CAVEATS
>
> ---
>
> Most importantly, does it makes sense to keep more than one base backup?
>
> Also, does it look any good? Does it make sense to make ~/wal_archive
> and ~/base_backups dirs not readable by group and the world? From what
> I can see files in ~/wal_archive are 0600, ~/base_backups/TIMESTAMP is
> 0700. How can I confirm that it's working properly? Is WAL segments
> files appearing in ~/wal_archive enough?
>
> Thanks in advance.
>
> Regards,
> Yuri Kanivetsky
>