Search Postgresql Archives

Re: PostgreSQL logical replication depends on WAL segments?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume I had to set wal_keep_segments to some really high number and stop our security cronjob cleaning old WAL segments (because we already had some problems with almost full disk due to old WAL segments) until the whole transfer of snapshot is done. Because only after the whole snapshot is transferred logical replication workers start to transfer WAL logs reflecting changes done from the moment snapshot was taken...

jm

Understand there are other downsides to just keeping around a huge amount of WAL segments apart from only taking up disk space.  None of the data held in those WAL segments can be vacuumed away while they are left around, which can lead to significant bloat and performance issues over time.

I'm not exactly clear on your use case, but if you need to just resychronize data for a single table, there is a built-in way to do that (actually would be nice if the docs spelled this out).

On publisher:

ALTER PUBLICATION mypub DROP TABLE old_data_table;

On subscriber:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);

On publisher:

ALTER PUBLICATION mypub ADD TABLE old_data_table;

On subscriber:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);


Many thanks Jeremy, I changed my implementation based on your recommendation and now it works like a charm for all available tables.

 
The last command will resync the table from the current table data, regardless of the WAL file situation.  This is the "normal" way you would go about resynchronizing data between clusters when a long time has passed, rather than trying to keep all that WAL around!

So far as I can tell from testing, above pattern is the easiest way to do this, and it will not resynchronize any of the other tables in your subscription.

P.S. do heed the advice of the others and get more familiar with the docs around WAL archiving.

Thanks,
Jeremy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux