Postgres dump

SQL Dump

pg_dump dbname | xz -9 > dump.sql.xz
unxz -c dump.sql.xz | psql dbname -1
pg_dump dbname -c -Z9 > dump.sql.gz
gunzip -c dump.sql.gz | psql dbname -1

Continuous Archiving and Point-in-Time Recovery (PITR)

Set postgresql.conf parameters:

# The WAL level must be `replica` or `logical`
wal_level = replica

Enable replication in pg_hba.conf:

local   replication     postgres                                peer

Restart the db.


To avoid losing of last incomplete WAL segment using archive_command, we'll pg_receivewal to stream even unfinished WAL segments. When we start it first time with a particular slot it will start fetching from the current WAL segment.

The purpose of replication slot is to track how much WAL files is necessary to keep for a particular standby synchronization. Let's create one: sudo -u postgres pg_receivewal -S slot1 --create-slot

Now let's try to receive logs: sudo -u postgres pg_receivewal -S slot1 -Z9 -v -D /backup/wal

If everything works let's make it permanent by e.g. using supervisor:

sudo tee /etc/supervisor/conf.d/receivewal.conf > /dev/null << EOF
command=pg_receivewal -S slot1 -Z9 -v -D /backup/wal

Receivewal monitoring

We can look at active streaming by select * from pg_stat_replication; It shows only connected

Base backup

After activating Continuous Archiving you should do a base backup as a starting point for recovery. It makes backup of the entire database cluster.

sudo -u postgres pg_basebackup -D - -Ft -X fetch | xz -9 | \
sudo -u postgres tee "/backup/base/$(date +"%Y-%m-%dT%H-%M-%S").tar.xz" > /dev/null

You can do it on a daily basis with crontab job:
`0 5 * * * sudo -u postgres ...`

Official docs: [chapter 25.3.4](

1. Make sure database server is stopped
2. Backup the entire db cluster folder if it's possible,
   or at least `pg_wal/` (`pg_xlog`) subdirectory, as it might contain not archived
3. Remove all existing files and subdirectories under the cluster data directory
4. Restore files from base backup, make sure they're owned by `postgres` user
5. Remove any files present in `pg_wal/` (`pg_xlog`) folder (which came with base
6. Copy WAL segments saved in step 2 to `$PGDATA/pg_wal`
7. If you have a `.partial` WAL segment in `pg_receivewal` backup and you don't
   have this segment completed (saved in step 2), remove `.partial` extension:

        PGDATA=/data/dbs/postgresql/11/main find ./ -name "*.partial" -exec sh -c ' \
            NAME=`basename "$0" .gz.partial`; \
            DEST=$PGDATA/pg_wal/$NAME; \
            test ! -f $DEST && \
            zcat $0 > $NAME && \
            truncate -s16M $NAME && \
            mv $NAME $DEST \
        ' '{}' \;

8. Create a recovery file `$PGDATA/recovery.conf`

        # a required parameter
        # %f - is a WAL segment file name
        # %p - path where the file would be copied to
        restore_command = 'gzip -d < /backup/wal/%f.gz > %p'

        # for PITR or if normal recovery breaks at some point
        # recovery_target_time = '2004-07-14 22:39:00 UTC'

9. Start the server. To see any possible errors, you can start it without
   demonization by something like:
   `/usr/lib/postgresql/11/bin/pg_ctl start -D /etc/postgresql/11/main`
10. After a successful recovery the `recovery.conf` should be renamed to `recovery.done`