PG 10 streaming replication pg_wal question
PG 10 streaming replication pg_wal question
От:
ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
Дата:
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
Re: PG 10 streaming replication pg_wal question
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: PG 10 streaming replication pg_wal question
От:
ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
Дата:
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 >
Re: PG 10 streaming replication pg_wal question
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: PG 10 streaming replication pg_wal question
От:
bricklen <bricklen@gmail.com>
Дата:
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();