Hello
What you are doing is called "log shipping", which means that when a wal (write-ahead log) is filled in on the database server you ship it to a backup server via rsync. It is fine but as you said the disadvantage is that the file is shipped only when it is full, so you could have data loss (the last wal not shipped)
A more modern and easy way is to use streaming replication: in this case the logs are streamed continuously to the standby or to the backup server (one can use streaming replication without implementing a standby database). Look at the doc on the next page than the one you referred to (https://www.postgresql.org/docs/9.3/static/high-availability.html)
There is a nice tool that does one you plan to do (rsync of archived file) but also the more modern way (streaming replication): it is called barman: https://www.pgbarman.org/. You should probably use their tool but you can also read the doc to get the concepts and some ideas
Rgds, Pierre
On Wednesday, September 26, 2018, 9:21:29 AM GMT+2, Yuri Kanivetsky <yuri.kanivetsky@xxxxxxxxx> wrote:
Hi,
I'm trying to compile a basic set of instruction needed to set up
continuous archiving and to recover from a backup. I'm running
PostgreSQL 9.3 on Debian Stretch system.
I've got a database and a backup servers. The idea is to set up WAL
archiving, and occasionally do full (base) backups. A base backup plus
WAL segment files from that point onward must provide enough
information to restore database to the latest state. Except for a
window for data loss that is caused by the fact that WAL segment files
aren't transferred momentarily, and more importantly that WAL segment
files are only transferred upon being filled.
---
Setting up continuous archiving
* Set up WAL archiving
* on backup server under postgres user
* create /var/lib/postgresql/wal_archive dir
$ mkdir /var/lib/postgresql/wal_archive
* on database server under postgres user
* generate ssh key
$ ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa # providing
path to key file makes it
# to not ask questions
* add corresponding record to known_hosts file
$ ssh-keyscan -t rsa BACKUP_SRV >> ~/.ssh/known_hosts
* locally
* authorize login from database to backup server
$ ssh DATABASE_SRV cat ~/.ssh/id_rsa.pub | ssh BACKUP_SRV
'mkdir --mode 0700 .ssh; cat >> ~/.ssh/authorized_keys; chmod 0600
.ssh/authorized_keys'
* on database server under root
* change postgresql.conf
wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p
BACKUP_SRV:/var/lib/postgresql/wal_archive/%f'
* restart PostgreSQL
# systemctl resart postgresql
* Make a base backup
* on database server under root
* add a line to postgresql.conf
max_wal_senders = 1
* add a line to pg_hba.conf
host replication replication BACKUP_SRV_IP/BACKUP_SRV_NETMASK trust
* restart PostgreSQL
# systemctl restart postgresql
* on database server under postgres user
* create replication user
CREATE USER replication WITH REPLICATION;
or
$ createuser --replication replication
* on backup server under postgres user
* make base backup
$ pg_basebackup -h DATABASE_SRV -U replication -D
/var/lib/postgresql/base_backups/$(date +%Y%m%d-%H%M%S)
Restoring from a backup
* under root
* stop PostgreSQL if running
# systemctl stop postgresql
* under postgres user
* move data dir
$ mv 9.3{,-$(date +%Y%m%d-%H%M%S)}
* copy backup
$ mkdir 9.3
$ cp -r base_backups/TIMESTAMP 9.3/main
* copy unarchived segment files
$ find 9.3-TIMESTAMP/main/pg_xlog -maxdepth 1 -type f -exec cp -t
9.3/main/pg_xlog {} +
* create recovery.conf in 9.3/main
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
* under root
* start PostgreSQL
# systemctl start postgresql
A few notes.
Running out of space on backup server can lead in its turn to database
server running out of space, since WAL segment files stop being
archived and keep piling up. The same might happen when archiving
falls behind. Which also widens the window for data loss.
WAL archiving doesn't track changes to configuration files.
pg_basebackup will back up configuration files only if they are inside
data dir.
If database doesn't generate much WAL traffic, there could be a long
delay between completing a transaction and archiving its results
(archive_timeout).
You might want to prevent users from accessing the database until
you're sure the recovery was successful (pg_hba.conf).
I'm not considering here possible issues with tablespaces and other caveats:
---
Most importantly, does it makes sense to keep more than one base backup?
Also, does it look any good? Does it make sense to make ~/wal_archive
and ~/base_backups dirs not readable by group and the world? From what
I can see files in ~/wal_archive are 0600, ~/base_backups/TIMESTAMP is
0700. How can I confirm that it's working properly? Is WAL segments
files appearing in ~/wal_archive enough?
Thanks in advance.
Regards,
Yuri Kanivetsky
