Thread: PG 10 streaming replication pg_wal question
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
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
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 >
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
On Thu, Nov 30, 2017 at 10:19 AM, Laurenz Albe
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();