I have attempted to set up a Postgres 9.5 server as a streaming
replication backup (guessing my way through the process after reading
the Postgresql documentation and a few tutorials) but am running into an
issue. The WAL archive files are not being removed on the backup. I'm
wondering if everything is OK and I should have discontinued the WAL
archive process or if something else is wrong. Thanks for any help.
The following should be the relevant configuration.
Master:
postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'ssh (IP address of slave) "test ! -f
/var/lib/postgresql/archive-dest/%f" && scp %p (IP address of
slave):/var/lib/postgresql/archive-dest/%f'
archive_timeout = 0
max_wal_senders = 2
max_replication_slots = 3
pb_hba.conf
host replication postgres 127.0.0.1/32 md5
host replication (user) (IP address of
master)/32 md5
host replication (user) (IP address of
slave)/32 md5
Slave:
postgresql.conf
listen_addresses = '*'
max_connections = 500
wal_level = hot_standby
max_wal_senders = 2
wal_keep_segments = 10
hot_standby = on
pb_hba.conf
host replication postgres 127.0.0.1/32 md5
host replication (user) (IP address of
master)/32 md5
host replication (user) (IP address of
slave)/32 md5
What I'm seeing is
Master:
ps -ef | grep post
.
.
.
postgres 1659 1294 0 2017 ? 00:01:57 postgres: wal
writer process
.
postgres 1661 1294 0 2017 ? 00:00:02 postgres: archiver
process last was 0000000100000017000000DC
.
.
postgres 12186 1294 0 2017 ? 00:03:40 postgres: wal
sender process (user) (IP address of slave)(33872) streaming 17/DDA464A8
ls -al /var/lib/postgresql/9.5/main/pg_xlog
(earliest is) -rw------- 1 postgres postgres 16777216 Jan 5 06:44
0000000100000017000000DE
.
.
(latest is) -rw------- 1 postgres postgres 16777216 Jan 5 16:15
0000000100000017000000DD
psql -c 'table pg_stat_replication;'
12186 | 17760 | (user) | walreceiver | (IP address of slave) | |
33872 | 2017-12-29 13:01:36.740747-06 | | streaming | 17/DDA464A8 |
17/DDA464A8 | 17/DDA464A8 | 17/DDA464A8 | 0 | async
Slave:
ps -ef | grep post
.
.
postgres 23909 23908 0 2017 ? 00:01:05 postgres: startup
process recovering 0000000100000017000000DD
.
postgres 23913 23908 0 2017 ? 00:07:56 postgres: wal
receiver process streaming 17/DDA464A8
ls -al /var/lib/postgresql/(archive target directory)
(earliest is almost 5 days ago, 31 total: earliest to latest)
.
(latest is) -rw------- 1 postgres postgres 16777216 Jan 5 14:35
0000000100000017000000DC
psql -c 'pg_is_in_recovery();'
pg_is_in_recovery
-------------------
t
(1 row)
psql -c 'select now() - pg_last_xact_replay_timestamp() AS
replication_delay;'
replication_delay
-------------------
00:00:05.842482
(1 row)
