Thread: 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
Hello Sebastian: On Thu, Jul 10, 2014 at 11:23 AM, Sebastian Fiedler - Nexst4 GmbH <sebastian.fiedler@nexst4.de> wrote: > 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. Aside from the barman stuff. We have a similar setup ( master + hot standby plus dumps ). The time to restore the dumps is only taken when you have a real disaster ( if the master fails you use the standby, not restore the master ) ( Also, if some of the users deletes *, I consider it a real disaster ) so it is not that critical. Also, we've observed text backups are worse than custom format backups, as they force a full restore on you ( we do a coupe of trick which schemas and partitions which allows us to restore the main tables, go back online, and backfill the historics once app is running ). And also, do you have a custom config for restore? For disaster recovery, as the db cannot be used while it is in progress, we stop, change the conf to a tuned one ( things like fsync=off, wal_level=minimal, lots of checkpoint segments, archive mode off, full page writes off ), restore ( which goes much, much faster ), and restart with the production conf. Regards. Francisco Olarte.