pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with WAL files in streaming replication
3+ messages / 3 participants
[nested] [flat]
* Issue with WAL files in streaming replication
@ 2018-01-08 03:10 Leroy Tennison <leroy_tennison@prodigy.net>
0 siblings, 1 reply; 3+ messages in thread
From: Leroy Tennison @ 2018-01-08 03:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Issue with WAL files in streaming replication
@ 2018-01-08 05:56 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Leroy Tennison <leroy_tennison@prodigy.net>
0 siblings, 1 reply; 3+ messages in thread
From: Shreeyansh Dba @ 2018-01-08 05:56 UTC (permalink / raw)
To: Leroy Tennison <leroy_tennison@prodigy.net>; +Cc: pgsql-admin@lists.postgresql.org
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@prodigy.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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Issue with WAL files in streaming replication
@ 2018-01-08 06:40 Ibrahim Edib Kokdemir <kokdemir@gmail.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ibrahim Edib Kokdemir @ 2018-01-08 06:40 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; pgsql-admin@lists.postgresql.org
>
> On Mon, Jan 8, 2018 at 8:40 AM, Leroy Tennison <leroy_tennison@prodigy.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.
>>
>
you should see a recovery.conf in slave server and below line must be
included in that file to delete old archive files.
archive_cleanup_command = '/path/to/pg_archivecleanup /path/to/archive/dir
%r'
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-01-08 06:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-08 03:10 Issue with WAL files in streaming replication Leroy Tennison <leroy_tennison@prodigy.net>
2018-01-08 05:56 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-01-08 06:40 ` Ibrahim Edib Kokdemir <kokdemir@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox