Back ups for postgres are done using base backups in combination with write ahead logs (WAL) files.
| Type | Frequency | Storage |
| base backup | weekly or monthly |
3 cycles on local storage indefinitely on AWS S3
|
| write ahead logs | every 16 megabytes |
3 cycles indefinitely on AWS S3 |
| file | setting | |
| /etc/postgresql/xx/main/postgresql.conf | archive_mode = on | |
|
archive_command = '/home/backup/scripts/wal_archive_command.sh %f %p >> \ /home/backup/logs/wal_archive.log 2>&1'
|
bash command to copy wal files | |
| wal_level = logical |
logical - ability to record row level operations highest setting
|
The backup routines are contained in a git project pg-backup. To implement:
Add postgres to pg-backup group so it can access files in /home/pg-backup
usermod -a -G pg-backup postgresEnsure that files created in /home/pg-backup is owned by pg-backup group
chmod -R g+s /home/pg-backupSet access control so that files created in /home/pg-backup are group read and write access
setfacl -R -d -m g::rwX /home/pg-backupCreate the postgres user pg-backup
CREATE USER pg-backup WITH REPLICATION PASSWORD 'change-this-password';4. update credentials.aws with the proper credentials
5. update /etc/crontab to run the basebackup.sh, pgdump.sh, backup_archiver.sh periodically.
6. test the crontab entries by running them on the command line and checking the logs