Re: Standby Server and Barman Backup on production system

On Thu, Jul 10, 2014 at 2:24 AM, basti <mailinglist(at)unix-solution(dot)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(at)standby(dot)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