pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG 10 streaming replication pg_wal question
5+ messages / 3 participants
[nested] [flat]
* PG 10 streaming replication pg_wal question
@ 2017-11-30 17:20 ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: ghiureai @ 2017-11-30 17:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi LIst,
I am testing PG10 streaming replication , with archiving off, I have
pg_wal on separate directory to monitor the growth , I had the slave
offline yesterday for more than 20h while I was restoring one of db
(60GB) with pg_restore from a backup taken with pg_dump, today I brought
the slave PG host online and replication catch up nicely , master and
slave are in sync now, but on master host the pg_wal is still same
large size ( as yesterday) even after all the wal files had been
applied to salve, would replication process not suppose to removed the
wal files on master after being applied to salve ?
( do I need to manually implement a cleanup job of this wal files, as
mentioned archiving is off on both servers)
see bellow:
master: pg_current_wal_lsn
--------------------
C5/D82DDD68
and slave: pg_last_wal_receive_lsn
-------------------------
C5/D82DDD68
and master :
4.0K ./wal/archive_status
166G ./wal
last wal file :
w------- 1 postgres postgres 16777216 Nov 29 16:05 00000007000000C5000000D8
ans slave :
12K ./wal/archive_status
1.1G ./wal
last wal file:
rw------- 1 postgres postgres 16777216 Nov 30 08:56 00000007000000C5000000D8
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 10 streaming replication pg_wal question
@ 2017-11-30 17:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2017-11-30 17:35 UTC (permalink / raw)
To: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>; pgsql-admin@lists.postgresql.org
ghiureai wrote:
> I am testing PG10 streaming replication , with archiving off, I have
> pg_wal on separate directory to monitor the growth , I had the slave
> offline yesterday for more than 20h while I was restoring one of db
> (60GB) with pg_restore from a backup taken with pg_dump, today I brought
> the slave PG host online and replication catch up nicely , master and
> slave are in sync now, but on master host the pg_wal is still same
> large size ( as yesterday) even after all the wal files had been
> applied to salve, would replication process not suppose to removed the
> wal files on master after being applied to salve ?
> ( do I need to manually implement a cleanup job of this wal files, as
> mentioned archiving is off on both servers)
Never remove WAL files yourself.
pg_wal will shrink eventually.
At the next checkpoint, PostgreSQL will remove all WAL files
that were completed and archived successfully since the
previous checkpoint, thus reducing WAL size a little.
If there is activity on the databases, pg_wal will eventually
shrink back to max_wal_size.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 10 streaming replication pg_wal question
@ 2017-11-30 17:41 ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: ghiureai @ 2017-11-30 17:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Thank you Laurenz,
can I run a checkpoint manually in master host than ?
On 11/30/2017 09:35 AM, Laurenz Albe wrote:
> ghiureai wrote:
>> I am testing PG10 streaming replication , with archiving off, I have
>> pg_wal on separate directory to monitor the growth , I had the slave
>> offline yesterday for more than 20h while I was restoring one of db
>> (60GB) with pg_restore from a backup taken with pg_dump, today I brought
>> the slave PG host online and replication catch up nicely , master and
>> slave are in sync now, but on master host the pg_wal is still same
>> large size ( as yesterday) even after all the wal files had been
>> applied to salve, would replication process not suppose to removed the
>> wal files on master after being applied to salve ?
>> ( do I need to manually implement a cleanup job of this wal files, as
>> mentioned archiving is off on both servers)
> Never remove WAL files yourself.
>
> pg_wal will shrink eventually.
> At the next checkpoint, PostgreSQL will remove all WAL files
> that were completed and archived successfully since the
> previous checkpoint, thus reducing WAL size a little.
>
> If there is activity on the databases, pg_wal will eventually
> shrink back to max_wal_size.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 10 streaming replication pg_wal question
@ 2017-11-30 18:19 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2017-11-30 18:19 UTC (permalink / raw)
To: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>; pgsql-admin@lists.postgresql.org
ghiureai wrote:
> can I run a checkpoint manually in master host than ?
Yes, but that will only remove WAL files if there has
been enough activity to cause one or more WAL switches.
You just have to wait.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 10 streaming replication pg_wal question
@ 2017-11-30 18:46 bricklen <bricklen@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: bricklen @ 2017-11-30 18:46 UTC (permalink / raw)
To: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Nov 30, 2017 at 10:19 AM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> ghiureai wrote:
> > can I run a checkpoint manually in master host than ?
>
> Yes, but that will only remove WAL files if there has
> been enough activity to cause one or more WAL switches.
>
> You just have to wait.
>
Note that you can shorten that waiting time by executing, as a superuser:
checkpoint;
select pg_switch_wal();
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-11-30 18:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-30 17:20 PG 10 streaming replication pg_wal question ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
2017-11-30 17:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-11-30 17:41 ` ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
2017-11-30 18:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-11-30 18:46 ` bricklen <bricklen@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