Truly Incremental backup for Postgres

ptrack_backup_todo.md

Description

First, I will talk about the current state of affairs. We have a several approaches:

1. Scan all db files and compare page LSN (barman, pg_rman). If you have big db it is not your choice.

2. Scan archive WAL files (current pg_arman). If you have high load it is not oyur choice too. (Big databases rarely living without high load.)

3. Pages are tracked using bitmap during runtime, so the needed ones can be easily dumped. (Oracle and my ptrack (patches for postgres and pg_arman)) It's really your choice.

A few words about ptrack: After enabling ptrack appears little overhead, no more than 3%.

HOWTO:

You need build and install postgres from here: https://github.com/postgrespro/postgrespro/tree/PGPRO9_5_ptrack (PGPRO9_5_ptrack branch)
Also you can apply patch for vanilla postgres 9.5: https://gist.github.com/stalkerg/7ce2394c4f3b36f995895190a633b4fa
In config file you need have next options:

ptrack_enable = on
wal_level = archive 
archive_command = 'test ! -f /home/postgres/backup/arman/wal/%f && cp %p /home/postgres/backup/arman/wal/%f'
wal_log_hints = on

After that you need build and install pg_arman from our github: https://github.com/postgrespro/pg_arman about it you can read github page.

Example backup (If you have a running Postgres):

# Init pg_aramn backup folder
pg_arman init -B /home/postgres/backup/pgarman
cat << __EOF__ >> /home/postgres/backup/pgarman/pg_arman.ini
ARCLOG_PATH = '/home/postgres/backup/arman/wal'
__EOF__
# Make full backup with 2 thread and verbose mode. 
pg_arman backup -B /home/postgres/backup/pgarman -D /home/postgres/pgdata/arman -b full -v -j 2
# Validate backup
pg_arman validate -B /home/postgres/backup/pgarman -D /home/postgres/pgdata/arman
# Show backups information
pg_arman show -B /home/postgres/backup/pgarman

# Now you can insert or update some data in your database

# Then start the incremental backup.
pg_arman backup -B /home/postgres/backup/pgarman -D /home/postgres/pgdata/arman -b ptrack -v -j 2
pg_arman validate -B /home/postgres/backup/pgarman -D /home/postgres/pgdata/arman
# And seeing really small increment :)
pg_arman show -B /home/postgres/backup/pgarman

For restore after remove your pgdata you can use:

pg_arman restore -B /home/postgres/backup/pgarman -D /home/postgres/pgdata/arman -j 4 --verbose

Stream

You can use stream replication for save WAL in backup folder during backup process. Some modifications for previous HOWTO:

1. You need set options for replications and access rights. Detail: https://wiki.postgresql.org/wiki/Streaming_Replication

2. You need add --stream for backup command and for restore command.

PS -B you can set into BACKUP_PATH env var and -D to PGDATA