Setting up continuous archiving - Mailing list pgsql-general

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