I have questions.
What is best strategy for backup on streaming replication(hot standby) environment.
I’d like to do backup on slave server and restore(PITR).
First, I set up streaming replication(hot standby).
-
I tried backup using barman tool on slave.
But barman tool couldn’t execute on slave because pg_start_backup() can’t be executed on slave sever(read only). -
I tried backup using pg_basebackup on slave.
But pg_basebackup is not included wal and wal archive.
Because I need a PITR(point-in-time-recovery), I tried to copy wal and wal archive from master to slave.
It was successful but was complicate and also inefficient.
Anyone have good strategy for backup on streaming replication(hot standby) environment?
Also, If backup is executed on master(about 100GB DB size), How much it has effect on master?
Thanks,
Best Answer
As of
>= 1.3.1
Barman supports backup from a standby replica (concurrent_backup
). Barman config, e.g./etc/barman.d/standby.conf
looks like this:If your master is running on PostgreSQL <= 9.5 you'd have to install pgespresso extension (there are binary packages e.g. for Debian from PGDG APT repos). PostgreSQL 9.6 introduced native streaming API, there's no need for special extension.
On standby server make sure to configure
archive_command
:the incoming directory should match
Also on standby server update
pg_hba.conf
(where10.0.0.3
is ipaddress of barman server):And enable WAL files streaming:
You can check your configuration using:
Then you should be ready to run full backup: