Thread: Setting up continuous archiving

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

On 9/26/18 8:20 AM, Yuri Kanivetsky wrote:
> 
> 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.

This is an incredibly complex topic and it is very difficult to 
implement correctly.

My advice is that you use a mature backup solution like pgBackRest or 
barman rather than rolling your own.

-- 
-David
david@pgmasters.net

Greetings,

* Yuri Kanivetsky (yuri.kanivetsky@gmail.com) wrote:
> 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.

9.3 is about to be end-of-life in just another month or so, see:

https://www.postgresql.org/support/versioning/

As mentioned, this is an extremely complicated subject and you should
really use one of the tools that's been written to do exactly this.
Here's a few comments as to why-

> 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'

This rsync command does nothing to verify that the WAL file has been
persisted to disk on the backup server, which is a problem if the backup
server crashes or there's some kind of issue with it after the rsync
finishes (you'll end up with gaps in your WAL stream which could prevent
you from being able to restore a backup or from being able to do PITR).

A good backup tool would also calculate a checksum of the WAL file and
store that independently, verify that the WAL file is for the cluster
configured (and not for some other cluster because someone mistakenly
tried to start archiving two primaries into the same location), verify
that the size of the WAL file is what's expected, and probably do a few
other checks that I'm not remembering right now, but which tools like
pgBackRest do.

>         * 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)

pg_basebackup is pretty good and it'll soon be able to perform
page-level checksum validation of the database while doing a backup,
assuming checksums have been enabled, but sadly it certainly didn't do
that in 9.3.  pg_basebackup should ensure that everything is persisted
to disk, but it doesn't do anything to protect against latent corruption
happening.  To do that, an independent manifest of the backup needs to
be built which tracks the checksum of every file backed up and then that
needs to be checked when performing a restore.

> 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 {} +

This is not something which I'd generally encourage doing..

>     * create recovery.conf in 9.3/main
>
>     restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'

This restore command doesn't perform any validation of the WAL file
which is being pulled back from the archive.

> * 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.

Yes, that's a concern.  pgBackRest has an option to allow you to choose
if you want to let the system run out of disk space or if you want to
throw away WAL (of course, leading to the case where you couldn't
perform PITR and so you'd want to do a new full backup as soon as
possible, but at least you have the choice).

> WAL archiving doesn't track changes to configuration files.

No, that's not likely to ever change.

> pg_basebackup will back up configuration files only if they are inside
> data dir.

Right, a number of the backup tools do that.

> If database doesn't generate much WAL traffic, there could be a long
> delay between completing a transaction and archiving its results
> (archive_timeout).

Yes, that's a reason to consider setting archive_timeout and then using
a tool, like pgBackRest, which will compress the WAL files, avoiding
taking up lots of disk space with mostly-empty WAL files.

> You might want to prevent users from accessing the database until
> you're sure the recovery was successful (pg_hba.conf).

Yes, that's certainly an important thing to consider.

> 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

You really should be looking to upgrade to a more recent version of
PostgreSQL as 9.3 is about to be out of support, and some of those
caveats (eg: hash indexes, at least) are no longer an issue on modern
versions.

> Most importantly, does it makes sense to keep more than one base backup?

Absolutely.  I'd encourage multiple full backups and then also consider
having differential and/or incremental backups as well- one thing to
consider is that WAL replay is a single-threaded and not terribly fast
process.  Having a tool which allows you to do parallel backup/restore
and supports incremental and differential backups, in addition to full
backups, can get you to a system where restores are able to be performed
very quickly, in parallel, with minimal WAL replay time following the
initial restore.

> 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?

PG will soon be able to support either 0600 or 0640 modes for the
database directory, to allow unprivileged processes to perform backups,
so you might want to consider that.

To confirm it's working properly, you might consider having a regular
verification performed where you swap a WAL segment in PG and make sure
that segment reaches the archive properly, which is exactly what the
'pgbackrest check' command does.

Thanks!

Stephen

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@yahoo.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
databaseserver you ship it to a backup server via rsync. It is fine but as you said the disadvantage is that the file
isshipped 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
standbyor to the backup server (one can use streaming replication without implementing a standby database). Look at the
docon 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
thedoc to get the concepts and some ideas 
>
> Rgds, Pierre
>
>
> On Wednesday, September 26, 2018, 9:21:29 AM GMT+2, Yuri Kanivetsky <yuri.kanivetsky@gmail.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
>

> I am not sure what you call discrete / 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.
>
>
> Are you talking about PITR ?

Yes. I had the impression, that with pgBackRest you do backups
occasionally, and as a result have a fixed number of states you can
restore to. But it appears they both keep the WAL files. So you can
restore to any point in time.

By the way, do/can they both use streaming to receive WAL records? Or
streaming is only for standby servers. For backups you have only
file-based log shipping?

Then, I suppose they both don't support partial PITR
(http://docs.pgbarman.org/release/2.4/#scope), where there are
standalone backups that extends to points in time for which there are
no WAL files. I'm not sure if this matters, but I assume that it might
be effective in terms of disk space.

Like, base backups + WAL files covering the last month, and a couple
of standalone backups for a couple of months before that. Compared to
base backups + WAL files covering the same period of time.

> But does it make sense to use repmgr ?

By that you mean, why use repmgr, that targets specifically PostgreSQL
in place of Pacemaker + Corosync which are more general pieces of
software?

> I use corosync & pacemaker with PAF for HA so I never had to use repmgr.

I'd like to be able to handle db failure as fast as possible. Ideally,
automatically. Which probably means either repmgr, or corosync +
pacemaker + PAF. Is that what you mean by HA here? Or at least, have a
running instance I can switch to manually. Which means, for example,
pgBackRest's streaming replication.

Regards,
Yuri Kanivetsky

On 10/15/18 5:09 PM, Benoit Lobréau wrote:
> 
>     By the way, do/can they both use streaming to receive WAL records? Or
>     streaming is only for standby servers. For backups you have only
>     file-based log shipping?
> 
> barman supports streaming but it's not as magical as one might think.
> See pgbarman's documentation for how to manager .partial files.
> 
> pgbackrest archives only wal files when postgres uses the 
> archive_command. You also have the option to do paralllel async wal 
> push/get.
> It can be useful if you write wals quicker than you can archive them or 
> if you want to restore more quickly.

We have focused on archive_command because the performance is much 
better because it can be parallelized.

>     Then, I suppose they both don't support partial PITR
>     (http://docs.pgbarman.org/release/2.4/#scope), where there are
>     standalone backups that extends to points in time for which there are
>     no WAL files. I'm not sure if this matters, but I assume that it might
>     be effective in terms of disk space.
> 
> It's a hot backup so you have to have wals files so that your backup is 
> consistent at the end of the backup.
> You can build something like what you describe with pgbackrest, 
> archive-copy and a copy of the backup directory to another place.

The --repo1-retention-archive-type and --repo1-retention-archive options 
allow you do keep WAL for a smaller number of backups in order to save 
space.

https://pgbackrest.org/configuration.html#section-repository/option-repo-retention-archive

So, for example:

[global]
repo1-retention-full=4
# The following option is the default but included for clarity
repo1-retention-archive-type=full
repo1-retention-archive=2

This configuration will retain 4 full backups but only keep PITR WAL for 
2 of them, i.e. the WAL generated between backups.  The WAL required to 
make a backup consistent is always retained so the 2 older backups can 
be played to consistency but no further.

>     I'd like to be able to handle db failure as fast as possible. Ideally,
>     automatically. Which probably means either repmgr, or corosync +
>     pacemaker + PAF. Is that what you mean by HA here? 
> 
> 
> yes. You can also look into patrony for an alternative. It's a matter of 
> preference and requirements.

pgBackRest certainly works with Pacemaker/Corosync and Patroni.

> If your standby is lagging too far behind (and you dont use slots) you 
> can use pgbackrest's archive to fill the gap in wal files and catchup 
> with the master.

Recovering WAL out of the archive is safer than using slots.  Since the 
WAL is transferred over the network compressed it can also save a lot of 
bandwidth.

Regards,
-- 
-David
david@pgmasters.net