Re: Issue with WAL files in streaming replication

Hi Leroy Tennison,

I suspect that you are expecting archives to be included as a part of
basebackup,But this is to be taken care manually as there is no options in
Postgres to remove archives automatically from archive directory.

You need to accomplished completely using the scripts to include this
archives into your backup then removing it manually.

Hope this helps..

Best Regards,
Shreeyansh

On Mon, Jan 8, 2018 at 8:40 AM, Leroy Tennison <leroy_tennison(at)prodigy(dot)net>
wrote:

> 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)
>
>
>

--
[image: http://www.shreeyansh.com] <http://www.shreeyansh.com>