On my PostgreSQL (8.3) I use this trick:
- I get table's disk size using
pg_total_relation_size()
- this includes indexes and TOAST size, which is what VACUUM
processes. This gives me the idea of how many bytes the VACUUM
has to read.
- I run
VACUUM
on the table.
- I find the
pid
of the VACUUM
process (in pg_catalog.pg_stat_activity
).
- In Linux shell I run
while true; do cat /proc/123/io | grep read_bytes; sleep 60; done
(where 123
is the pid) - this shows me bytes read by the process from the disk so far.
This gives me rough idea on how many bytes are processed (read) every minute by the VACUUM
. I presume that the VACUUM
must read through the whole table (including indexes and TOAST), whose disk size I know from the step 1.
I presume that the table is large enough so that the majority of it's pages must be read from disk (they are not present in Postgres shared memory), so the read_bytes
field is good enough to be used as a progress counter.
Everytime I did this, the total bytes read by the process was no more than 5% from the total relation size, so I guess this approach may be good enough for You.
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
Best Answer
You are plausibly in the range where network bandwidth is limiting. I get 9 hours versus your reported 23, but that could be accounted for by overhead, inefficiencies or competing network usage.
You can try turning on network compression, which requires changing the backup method, which in turn will require you to configure "ssh_command".
Of course this will impose some CPU overhead on the server for it to do the compression.