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
If I recall correctly, PostgreSQL now zeroes the end of the WAL segment after an archive timeout, before archiving the file. (Update): This is true for 9.4; for prior versions you still need to use pg_clearxlogtail
utility to zero the end of any WAL segment as part of your archive script before you gzip
your archive files.
In 9.4 and above you just need to gzip
the file, with no need for pg_clearxlogtail
.
The change was made commit 9a20a9b, but it's only in tag REL9_4_BETA1
, so the change will first appear in 9.4:
commit 9a20a9b
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Mon Jul 8 11:23:56 2013 +0300
Improve scalability of WAL insertions.
This patch replaces WALInsertLock with a number of WAL insertion slots,
allowing multiple backends to insert WAL records to the WAL buffers
concurrently. This is particularly useful for parallel loading large amounts
of data on a system with many CPUs.
This has one user-visible change: switching to a new WAL segment with
pg_switch_xlog() now fills the remaining unused portion of the segment with
zeros. This potentially adds some overhead, but it has been a very common
practice by DBA's to clear the "tail" of the segment with an external
pg_clearxlogtail utility anyway, to make the WAL files compress better.
With this patch, it's no longer necessary to do that.
BTW, you should really be using an archive timeout, rather than manually switching xlogs. Consider having a streaming replica for additional protection.
Update: As Jeff noted, pg_clearxlogtail
hasn't been updated for 9.3. The incompatibility is very simple though; the definition of XLogRecPtr
changed from
typedef struct XLogRecPtr
{
uint32 xlogid; /* log file #, 0 based */
uint32 xrecoff; /* byte offset of location in log file */
} XLogRecPtr;
to
typedef uint64 XLogRecPtr;
when support for platforms with compilers that don't support 64-bit long integers was removed from PostgreSQL in 9.3. So all we need to do is add a macro or function that tests equality in a way that works for both.
The following patch adds support for 9.3, but is completely untested so use entirely at your own risk. If it eats your data then sets fire to your house, well, that's how things are. Patch here, as an attachment to the linked pgfoundry issue.
Best Answer
This is not going to work.
hot_standby_feedback
doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough". No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first.You can use pg_total_relation_size to get the table size. You can take a query like the below, and just plug it into our favorite monitoring solution.
But that still leaves the question of what to do about it.