Обсуждение: Standby Server and Barman Backup on production system
Hello, I had followed this discuss (http://www.postgresql.org/message-id/CABRT9RAXzUa=_zT_M4Z1vyDuFkpgNCZLUnRTUO5gvK2kKkNu=A@mail.gmail.com). I have a similar problem now: I use one Postgres Server as Master an an other one as Standby (WAL archives). I do also a daily backup of the Master Server using pg_dump. Now there is a situation where a possible restore via "cat dumpfile | psql ...." takes to long and the server load is too high. So my idea is to use barman for backup. Is it possible to use wal replication and barman backupin one config file? Is there someone how has experience with this? The relevant barman (test)config looks like: wal_level = archive archive_mode = on archive_command = 'rsync -a %p /var/lib/barman/btest/incoming/%f' The relevant wal replication config on production system (master) looks like: wal_level = hot_standby archive_mode = on archive_command = 'rsync -a %p -e "ssh -i /var/lib/postgresql/.ssh/id_rsa" postgres@standby.srv:/var/lib/postgresql/9.1/wals/master_main/%f </dev/null' Can I use a 2'nd rsync command here? How should I do? What are differences between "wal_level = archive" and "wal_level = archive" or doesn't matter here?-- Nexst4 GmbH Riesaer Straße 7 01129 Dresden Tel.: +49 (351) 655 76 64 Fax: +49 (351) 655 76 66 Mail: sebastian.fiedler@nexst4.de Geschäftsführer: Matthias Schmidt, Alf Thiele Sitz der Gesellschaft: Dresden HRB 27274
On Thu, Jul 10, 2014 at 2:24 AM, basti <mailinglist@unix-solution.de> wrote:
Hello, I had followed this discuss (http://www.postgresql.org/message-id/CABRT9RAXzUa=_zT_M4Z1vyDuFkpgNCZLUnRTUO5gvK2kKkNu=A@mail.gmail.com). I have a similar problem now: I use one Postgres Server as Master an an other one as Standby (WAL archives). I do also a daily backup of the Master Server using pg_dump. Now there is a situation where a possible restore via "cat dumpfile | psql ...." takes to long and the server load is too high. So my idea is to use barman for backup. Is it possible to use wal replication and barman backupin one config file? Is there someone how has experience with this? The relevant barman (test)config looks like: wal_level = archive archive_mode = on archive_command = 'rsync -a %p /var/lib/barman/btest/incoming/%f' The relevant wal replication config on production system (master) looks like: wal_level = hot_standby archive_mode = on archive_command = 'rsync -a %p -e "ssh -i /var/lib/postgresql/.ssh/id_rsa" postgres@standby.srv:/var/lib/postgresql/9.1/wals/master_main/%f </dev/null'
I believe that since this is just a bash command you could feasibly either:
* Chain together two rsync commands with &&
* Write a script that takes the from (%p) and to (%f) locations as arguments to rsync them to the appropriate places.
Can I use a 2'nd rsync command here? How should I do? What are differences between "wal_level = archive" and "wal_level = archive" or doesn't matter here?
hot_standby has more information. From the documentation for 9.1:
In hot_standby level, the same information is logged as with archive, plus information needed to reconstruct the status of running transactions from the WAL. To enable read-only queries on a standby server, wal_level must be set to hot_standby on the primary, and hot_standby must be enabled in the standby.
So you'll get more information and bigger files being transferred if you set your wal_level to hot_standby.
I've personally never tried rsyncing to two locations at once, so I'm not going to comment on that. Good luck.
QP