postgresql.mdwn

[[!toc levels=3]]

[PostgreSQL](https://www.postgresql.org/) is an advanced database server that is robust and
fast, although possibly less well-known and popular than it's eternal
rival in the free software world, MySQL.

Tutorial
========

Those are quick reminders on easy things to do in a cluster.

Connecting
----------

Our PostgreSQL setup is fairly standard so connecting to the database
is like any other Debian machine:

    sudo -u postres psql

This drops you in a [psql shell](https://www.postgresql.org/docs/9.6/app-psql.html) where you can issue SQL queries
and so on.

Howto
=====

Deployment
----------

A PostgreSQL server should be deployed with puppet, using the
`postgresql` module. Make sure you also include the
`postgres::backup_source` class so that it's registered with the
backup server, see the `dip::database` class for an example on how to
deploy a server.

See the [[puppet]] documentation for more information on how to deploy
Puppet things.

TODO: turn this in a Tutorial by making it more straightforward to
deploy a cluster, for example just by including a single class.

Find what is taking up space
----------------------------

This will report size information for all tables, in both raw bytes and "pretty" form:

    SELECT *, pg_size_pretty(total_bytes) AS total
        , pg_size_pretty(index_bytes) AS INDEX
        , pg_size_pretty(toast_bytes) AS toast
        , pg_size_pretty(table_bytes) AS TABLE
      FROM (
      SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
          SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
                  , c.reltuples AS row_estimate
                  , pg_total_relation_size(c.oid) AS total_bytes
                  , pg_indexes_size(c.oid) AS index_bytes
                  , pg_total_relation_size(reltoastrelid) AS toast_bytes
              FROM pg_class c
              LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE relkind = 'r'
      ) a
    ) a ORDER BY total_bytes;

Same with databases:

    SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
        END AS SIZE
    FROM pg_catalog.pg_database d
        ORDER BY
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_database_size(d.datname)
            ELSE NULL
        END DESC -- nulls first
        LIMIT 20;

Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also [the upstream manual](https://www.postgresql.org/docs/9.1/disk-usage.html).

Running a full backup
---------------------

Backups are normally automatically ran on the backup server (currently
`bungei`) but you can also run a backup by hand. This will run a
backup of `meronense`, for example:

    sudo -u torbackup postgres-make-one-base-backup $(grep ^meronense.torproject.org $(which postgres-make-base-backups ))

See the [reference](#Reference) section for details.

Picking a restore procedures
----------------------------

We have two ways of restoring PostgreSQL backups, **indirect** and
**direct**.

The **indirect** procedures first transfers the backup files to the
server and then runs the restore, so it require more space than the
direct procedure. It might also be slower than the direct restore
procedure because files are copied around twice: once from the backup
server, and again loaded in the database.

The **direct** procedure *streams* the backup files directly from the
backup server. The `BASE` file is streamed into the `tar` command for
restore of the base snapshot, then the PostgreSQL restore command
copies each log directly from the backup server as well. It requires
minimal amount of space, as files are not copied to a temporary
location on the new server. The downside is it might be more difficult
to deploy and diagnose, as it has more moving parts.

Both procedures are adaptations of the [official recovery
procedure](https://www.postgresql.org/docs/9.3/continuous-archiving.html#BACKUP-PITR-RECOVERY), which can be refered to for more information.

Indirect restore procedures
---------------------------

Reminder: this procedure **copies** the backup files from the backup
server to the database server and restores from those.

 1. First, you will need to give the backup server access to the new
    postgresql server, which we'll call the "client" for now. First,
    login to the client and allow the backup server to connect, and
    show the public hostkey:

        iptables -I INPUT -s $BACKUP_SERVER -j ACCEPT
        cat /etc/ssh/ssh_host_rsa_key.pub

 2. Then load the server's private key in an agent and show it to
    allow on the client. On the server, assuming `$IP` is the IP of
    the client and `$HOSTKEY` is its hostkey:

        ssh-agent bash
        ssh-add /etc/ssh/ssh_host_rsa_key
        mkdir -p ~/.ssh
        echo "$IP $HOSTKEY" >> ~/.ssh/known_hosts
        cat /etc/ssh/ssh_host_rsa_key.pub

 3. And on the client, allow the server `$HOSTKEY` (the above `cat
    /etc/ssh/ssh_host_rsa_key.pub` on the backup server):

        echo "$HOSTKEY" >> /etc/ssh/userkeys/root.more

 4. Then, we can transfer files over from the backup server to the
    client:

        cd /srv/backups/pg
        rsync -aP $CLIENT $CLIENT:/var/lib/postgresql/restore

 5. Disable Puppet so you have control on when PostgreSQL is running:

        puppet agent --disable 'keeping control of postgresql startup -- anarcat 2019-10-09'

 6. Then, on the client, install the software, stop the server and
    move the template cluster out of the way:

        apt install postgres rsync
        service postgresql stop
        mv /var/lib/postgresql/*/main{,.orig}
        su -c 'mkdir -m 0700 /var/lib/postgresql/9.6/main' postgres

    We'll be restoring files in that `main` directory.

    Make sure you run the SAME MAJOR VERSION of PostgreSQL than the
    backup! You cannot restore across versions. This might mean
    installing from backports or an older version of Debian.

 7. Then you need to find the right `BASE` file to restore from. Each
    `BASE` file has a timestamp in its filename, so just sorting them
    by name should be enough to find the latest one. Uncompress the
    `BASE` file in place:

        cat ~postgres/restore/$CLIENT/main.BASE.bungei.torproject.org-20190805-145239-$CLIENT.torproject.org-main-9.6-backup.tar.gz | su postgres -c 'tar -C /var/lib/postgresql/11/main -x -z -f -'

    (Use `pv` instead of `cat` for a progress bar with large backups.)

 8. Make sure the `pg_xlog` directory doesn't contain any files.

 9. Then you need to create a `recovery.conf` file in
    `/var/lib/postgresql/9.6/main` that will tell postgres where to
    find the WAL files. At least the `restore_command` need to be
    specified. Something like this should work:

        restore_command = 'cp /var/lib/postgresql/restore/subnotablie/main.WAL.%f %p'

    You can specify a specific recovery point in the `recovery.conf`,
    see the [upstream documentation](https://www.postgresql.org/docs/9.3/recovery-target-settings.html) for more information.

 10. Then start the server and look at the logs to follow the recovery
     process:

        service postgresql start
        tail -f /var/log/postgresql/*

 11. re-enable Puppet, which should clean up the extra SSH key and
     firewall rules:

        puppet agent -t
    
     make sure it's okay in `/etc/ssh/userkeys/root` and `iptables -L`.

When the restore succeeds, the `recovery.conf` file will be renamed to
`recovery.done` and you will see something like:

    2019-08-12 21:36:53.453 UTC [16901] LOG:  selected new timeline ID: 2
    2019-08-12 21:36:53.470 UTC [16901] LOG:  archive recovery complete
    cp: cannot stat '/var/lib/postgresql/restore/subnotablie/main.WAL.00000001.history': No such file or directory
    2019-08-12 21:36:53.577 UTC [16901] LOG:  MultiXact member wraparound protections are now enabled
    2019-08-12 21:36:53.584 UTC [16900] LOG:  database system is ready to accept connections

Ignore the error from `cp` complaining about the `.history` file, it's
harmless.

### Troubleshooting

If you find the following error in the logs:

    FATAL:  could not locate required checkpoint record

It's because postgres cannot find the WAL logs to restore from. There
could be many causes for this, but the ones I stumbled upon were:

 * wrong location for `recovery.conf` (it's in `/var`, not `/etc`),
   probably the case if you don't see an error from `cp`
 * wrong permissions on the archive (put the WAL files in `~postgres`,
   not `~root`)
 * wrong path or pattern for `restore_command` (double-check the path
   and make sure to include the right prefix, e.g. `main.WAL`)

Direct restore procedure
------------------------

Reminder: this procedure **streams** the files from the backup server
to the database server and restore directly. See above for more
information on how to pick a restore procedure.

 1. First, disable Puppet so you have control on when PostgreSQL is
    running:
 
        puppet agent --disable 'keeping control of postgresql startup -- anarcat 2019-10-09'

 2. Then install the right PostgreSQL version and stop the server:

        apt install postgres-9.6
        service postgresql stop

    Make sure you run the SAME MAJOR VERSION of PostgreSQL than the
    backup! You cannot restore across versions. This might mean
    installing from backports or an older version of Debian.

 3. On that new PostgreSQL server, show the `postgres` server public
    key, creating it if missing:

        [ -f ~postgres/.ssh/id_rsa.pub ] || sudo -u postgres ssh-keygen
        cat ~postgres/.ssh/*.pub

 4. Then on the backup server, allow the user to access backups of the
    old server:

        echo "command="/usr/local/bin/debbackup-ssh-wrap --read-allow=/srv/backups/pg/$OLDSERVER $CLIENT",restrict $HOSTKEY" > /etc/ssh/userkeys/torbackup.more

    This assumes we connect to a *previous* server's backups, named
    `$OLDSERVER` (e.g. `dictyotum`). The `$HOSTKEY` is the public key
    found on the postgres server above.

    Warning: the above will fail if the key is already present in
    `/etc/ssh/userkeys/torbackup`, edit the key in there instead in
    that case.

 5. Then you need to find the right `BASE` file to restore from. Each
    `BASE` file has a timestamp in its filename, so just sorting them
    by name should be enough to find the latest one. Uncompress the
    `BASE` file in place, as the `postgres` user:

        sudo -u postgres -i
        mkdir -m 0700 /var/lib/postgresql/9.6/main
        ssh torbackup@$BACKUPSERVER $(hostname) retrieve-file pg $OLDSERVER $BASEFILE \
          | sed '0,/^$/d' \
          | tar -C /var/lib/postgresql/9.6/main -x -z -f -

    Add a `pv` before the `tar` call in the pipeline for a progress bar
    with large backups, and replace:

     * `$BACKUPSERVER` with the backupserver name and username
        (currently `bungei.torproject.org`)
     * `$OLDSERVER` with the old server's (short) hostname
       (e.g. `dictyotum`)
     * `$CLUSTERNAME` with the name of the cluster to restore
       (e.g. usually `main`)
     * `$BASEFILE` with something like
       `$CLUSTERNAME.BASE.$BACKUPSERVER-20191004-062226-$OLDSERVER.torproject.org-$CLUSTERNAME-9.6-backup.tar.gz`
       or, fully expanded:
       `bacula.BASE.bungei.torproject.org-20191010-184205-dictyotum.torproject.org-bacula-9.6-backup.tar.gz`
       where the `$CLUSTERNAME` is the non-default `bacula`

    The above might hang for a while (yes, maybe even a minute) in the
    beginning, but it should eventually get started and complete. The
    "hang" is because `retrieve-file` sends a header which includes a
    `sha512sum` and it takes a while to compute. The `sed` command in
    that pipeline is there to skip that header. Example of a
    successful restore operated in [#31786][]:
   
        ssh torbackup@bungei.torproject.org bacula-director-01 retrieve-file pg dictyotum bacula.BASE.bungei.torproject.org-20191010-184205-dictyotum.torproject.org-bacula-9.6-backup.tar.gz | pv -s 33G | sed '0,/^$/d' | tar -C /var/lib/postgresql/9.6/main -x -z -f -

    If the above doesn't work, use the indirect procedure to restore
    the `BASE` file.

[#31786]: https://bugs.torproject.org/31786

 6. Make sure the `pg_xlog` directory doesn't contain any files.
 
        rm -rf -- /var/lib/postgresql/9.6/main/pg_xlog/*

 7. Then you need to create a `recovery.conf` file in
    `/var/lib/postgresql/9.6/main` that will tell postgres where to
    find the WAL files. At least the `restore_command` need to be
    specified. Something like this should work:

        restore_command = '/usr/local/bin/pg-receive-file-from-backup $OLDSERVER $CLUSTERNAME.WAL.%f %p'

    ... where:

     * `$OLDSERVER` should be replaced by the previous postgresql
       server name (e.g. `dictyotum`)
     * `$CLUSTERNAME` should be replaced by the previous cluster name
       (e.g. `main`, generally)

    You can specify a specific recovery point in the `recovery.conf`,
    see the [upstream documentation](https://www.postgresql.org/docs/9.3/recovery-target-settings.html) for more information. Also
    make sure the file is owned by postgres:

        $EDITOR /var/lib/postgresql/9.6/main/recovery.conf
        chown postgres /var/lib/postgresql/9.6/main/recovery.conf

 8. Then start the server and look at the logs to follow the recovery
    process:

        service postgresql start
        tail -f /var/log/postgresql/*

    You should see something like this:

        2019-10-09 21:17:47.335 UTC [9632] LOG:  database system was interrupted; last known up at 2019-10-04 08:12:28 UTC
        2019-10-09 21:17:47.517 UTC [9632] LOG:  starting archive recovery
        2019-10-09 21:17:47.524 UTC [9633] [unknown]@[unknown] LOG:  incomplete startup packet
        2019-10-09 21:17:48.032 UTC [9639] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:48.538 UTC [9642] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:49.046 UTC [9645] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:49.354 UTC [9632] LOG:  restored log file "00000001000005B200000074" from archive
        2019-10-09 21:17:49.552 UTC [9648] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:50.058 UTC [9651] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:50.565 UTC [9654] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:50.836 UTC [9632] LOG:  redo starts at 5B2/74000028
        2019-10-09 21:17:51.071 UTC [9659] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:17:51.577 UTC [9665] postgres@postgres FATAL:  the database system is starting up
        2019-10-09 21:20:35.790 UTC [9632] LOG:  restored log file "00000001000005B20000009F" from archive
        2019-10-09 21:20:37.745 UTC [9632] LOG:  restored log file "00000001000005B2000000A0" from archive
        2019-10-09 21:20:39.648 UTC [9632] LOG:  restored log file "00000001000005B2000000A1" from archive
        2019-10-09 21:20:41.738 UTC [9632] LOG:  restored log file "00000001000005B2000000A2" from archive
        2019-10-09 21:20:43.773 UTC [9632] LOG:  restored log file "00000001000005B2000000A3" from archive

    ... and so on.

    TODO: this warning comes up from time to time, problem?

        2019-10-09 23:47:13.446 UTC [16973] LOG:  could not link file "pg_xlog/00000001000005B3000000C3" to "pg_xlog/00000001000005B3000000F9": File exists

    Then the recovery will complete with something like this:

        2019-10-10 01:30:55.460 UTC [16953] LOG:  redo done at 5B8/9C5BE738
        2019-10-10 01:30:55.460 UTC [16953] LOG:  last completed transaction was at log time 2019-10-10 01:04:23.238233+00
        2019-10-10 01:31:03.536 UTC [16953] LOG:  restored log file "00000001000005B80000009C" from archive
        2019-10-10 01:31:06.458 UTC [16953] LOG:  selected new timeline ID: 2
        2019-10-10 01:31:17.485 UTC [16953] LOG:  archive recovery complete
        2019-10-10 01:32:11.975 UTC [16953] LOG:  MultiXact member wraparound protections are now enabled
        2019-10-10 01:32:12.438 UTC [16950] LOG:  database system is ready to accept connections
        2019-10-10 01:32:12.439 UTC [26501] LOG:  autovacuum launcher started

 9. Then remove the temporary SSH access on the backup server, either
    by removing the `.more` key file or restoring the previous key
    configuration:

        rm /etc/ssh/userkeys/torbackup.more

 10. re-enable Puppet:

        puppet agent -t

### Troubleshooting

If you get this error in the PostgreSQL restore logs:

    No directories from which read is allowed given on cmdline.
    Unkown format in line 1 () when getting dictyotum:main.WAL.00000005.history from backup host

It's because the SSH keys deployed on the server does not have the
`--read-allowed` argument.

If the restore doesn't work, try to connect by hand:

    sudo -u postgres /usr/local/bin/pg-receive-file-from-backup dictyotum bacula.WAL.00000001000005AB0000003A /tmp/foo

If you get this kind of errors, it's because you forgot to restore the
`BASE` file first:

    2019-10-08 21:25:43.711 UTC [14320] [unknown]@[unknown] LOG:  incomplete startup packet
    2019-10-08 21:25:44.215 UTC [14326] postgres@postgres FATAL:  the database system is starting up
    2019-10-08 21:25:44.451 UTC [14316] WARNING:  WAL was generated with wal_level=minimal, data may be missing
    2019-10-08 21:25:44.451 UTC [14316] HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
    2019-10-08 21:25:44.451 UTC [14316] LOG:  consistent recovery state reached at 0/153A3F0
    2019-10-08 21:25:44.451 UTC [14316] LOG:  invalid record length at 0/153A3F0: wanted 24, got 0
    2019-10-08 21:25:44.451 UTC [14316] LOG:  redo is not required
    2019-10-08 21:25:44.721 UTC [14334] postgres@postgres FATAL:  the database system is starting up
    2019-10-08 21:25:45.226 UTC [14340] postgres@postgres FATAL:  the database system is starting up
    2019-10-08 21:25:45.549 UTC [14316] LOG:  selected new timeline ID: 6
    2019-10-08 21:25:45.732 UTC [14343] postgres@postgres FATAL:  the database system is starting up
    2019-10-08 21:25:45.765 UTC [14316] LOG:  archive recovery complete
    2019-10-08 21:25:46.238 UTC [14349] postgres@postgres FATAL:  the database system is starting up
    2019-10-08 21:25:46.466 UTC [14316] LOG:  MultiXact member wraparound protections are now enabled
    2019-10-08 21:25:46.467 UTC [14315] LOG:  database system is ready to accept connections
    2019-10-08 21:25:46.467 UTC [14351] LOG:  autovacuum launcher started

See also the "Direct restore procedure" troubleshooting section, which
also applies here.

Dealing with Nagios warnings
----------------------------

TODO: there's some information about backup handling in the [Debian
DSA documentation](https://dsa.debian.org/howto/postgres-backup/).

Reference
=========

Backup design
-------------

We use upstream's [Continuous Archiving and Point-in-Time Recovery
(PITR)](https://www.postgresql.org/docs/9.3/continuous-archiving.html)
which relies on postgres's "write-ahead log" (WAL) to write regular
"transaction logs" of the cluster to the backup host. (Think of
transaction logs as incremental backups.) This is configured in
`postgresql.conf`, using a line like this:

    archive_command = '/usr/local/bin/pg-backup-file main WAL %p'

That is a site-specific script which reads a config file in
`/etc/dsa/pg-backup-file.conf` where the backup host is specified
(currently `torbackup@bungei.torproject.org`). That command passes the
WAL logs onto the backup server, over SSH. A WAL file is shipped
immediately when it is full (16MB of data by default) but no later
than 6 hours (varies, see `archive_timeout` on each host) after it was
first written to. On the backup server, the `command` is set to
`debbackup-ssh-wrap` in the `authorized_keys` file and takes the
`store-file pg` argument to write the file to the right location.

WAL files are written to `/srv/backups/pg/$HOSTNAME` where `$HOSTNAME`
(without `.torproject.org`). WAL files are prefixed with `main.WAL.`
(where `main` is the cluster name) with a long unique string after,
e.g. `main.WAL.00000001000000A40000007F`.

For that system to work, we also need *full* backups to happen on a
regular basis. This happens straight from the backup server (still
`bungei`) which connects to the various postgres servers and runs a
[pg_basebackup](https://manpages.debian.org/pg_basebackup) to get a complete snapshot of the cluster. This
happens *weekly* (every 7 to 10 days) in the wrapper
`postgres-make-base-backups`, which is a wrapper (based on a Puppet
`concat::fragment` template) that calls
`postgres-make-one-base-backup` for each postgres server.

The base files are written to the same directory as WAL file and are
named using the template:

    $CLUSTER.BASE.$SERVER_FQDN-$DATE-$ID-$CLIENT_FQDN-$CLUSTER-$VERSION-backup.tar.gz

... for example:

    main.BASE.bungei.torproject.org-20190804-214510-troodi.torproject.org-main-9.6-backup.tar.gz

All of this works because SSH public keys and postgres credentials are
passed around between servers. That is handled in the Puppet
`postgresql` module for the most part, but some bits might still be
configured manually on some servers.

Backups are checked for freshness in Nagios using the
`dsa-check-backuppg` plugin with its configuration stored in
`/etc/dsa/postgresql-backup/dsa-check-backuppg.conf.d/`, per
cluster. The Nagios plugin also takes care of expiring backups when
they are healthy.

Other documentation
-------------------

See also:

 * [PostgreSQL official documentation](https://www.postgresql.org/docs/manuals/)
 * [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Main_Page)
 * [Debian DSA documentation](https://dsa.debian.org/howto/postgres-backup/)