How incremental backups work in PostgreSQL, and how to implement them in 10 minutes

-c, --checksum
This changes the way rsync checks if the files have been changed
and are in need of a transfer. Without this option, rsync uses
a "quick check" that (by default) checks if each file’s size and
time of last modification match between the sender and receiver.
This option changes this to compare a 128-bit checksum for each
file that has a matching size. Generating the checksums means
that both sides will expend a lot of disk I/O reading all the
data in the files in the transfer (and this is prior to any
reading that will be done to transfer changed files), so this
can slow things down significantly.
sudo apt-get install barman
or
sudo yum install barman
[barman]
barman_home = /data/barman
barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
reuse_backup = link
backup_method = rsync
archiver = on
[pghost]
description = “Postgres server”
ssh_command = ssh postgres@pghost
conninfo = host=pghost user=postgres port=5432
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
retention_policy = REDUNDANCY 4
host barman all pghosts_ip_address:/32 trust
listen_adressess = '*'
archive_mode = on
archive_command = ‘rsync -a %p barman@bmhost:/PATH/TO/WALS/%f`
barman show-server pghost | grep streaming_wals_directory
pg_ctl reload
or
systemctl reload postgresql
barman list-server
barman check pghost
barman switch-wal pghost
barman backup pghost
barman list-backup pghost
barman show-backup pghost BACKUPID
* * * * * /usr/bin/barman cron
0 20 * * * /usr/bin/barman backup pghost
barman recover <server_name> <backup_id> /path/to/recover/dir
barman recover <server_name> <backup_id> --remote-ssh-command <COMMAND> /path/to/recover/dir 
--target-time TARGET_TIME

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Kobi Rosenstein

Kobi Rosenstein

Devops DBA. This blog chronicles my “gotcha” moments — Each post contains an answer I would have like to have found when searching for those pesky errors I get.