What is best strategy for backup on streaming replication(hot standby) environment

Question

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).

1. 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).

2. 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,

Answer 1

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:

[standby]
description = "Replica of main PostgreSQL DB"
ssh_command = ssh postgres@db02
conninfo = host=db02 user=postgres
backup_options = concurrent_backup
streaming_conninfo = host=db02 user=postgres
streaming_archiver = on

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:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -a %p barman@backup:/var/lib/barman/standby/incoming/%f'

the incoming directory should match

barman:~$ barman diagnose | grep incoming_wals_directory

Also on standby server update pg_hba.conf (where 10.0.0.3 is ipaddress of barman server):

host   all             postgres            10.0.0.3/32        trust

And enable WAL files streaming:

barman~$ barman receive-wal standby

You can check your configuration using:

barman:~$ barman check standby

Server standby:
        PostgreSQL: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 1 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        pgespresso extension: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

Then you should be ready to run full backup:

   barman:~$ barman backup standby

Answer 2

Please go through below links

http://blog.thecodepath.com/2012/02/13/adventures-in-scaling-part-3-postgresql-streaming-replication/

http://bartek.im/blog/2012/12/04/postgresql-92-streaming-primer.html

and if it is helpful please give feedback.and raise this solution.