Thread: WAL retention
Hi Guys,
Is there a way to set retention policy for removing WAL files in hours/days instead of number of WAL files (wal_keep_segments)?
I have a problem when backup is running, it complains that some files are missing. The problem is that i have situation when data load is running and a lot of WAL files are generated.
Regards,
Slavcho
Hello,
Which version of PostgreSQL are you using? On Mon, Jun 20, 2016 at 10:38 AM, Slavcho Trnkovski <strnkovski@gmail.com> wrote:
Hi Guys,Is there a way to set retention policy for removing WAL files in hours/days instead of number of WAL files (wal_keep_segments)?I have a problem when backup is running, it complains that some files are missing. The problem is that i have situation when data load is running and a lot of WAL files are generated.Regards,Slavcho
--
Bert Desmet
0477/305361
0477/305361
Hi,
I use version 9.4.
If I create physical replication slot, I'll need standby server to use it. I can use pg_receivexlog but it will not prevent removing WAL files, it will just stream it to some other location.
Do you maybe have an idea how backup can use replication slot (without standby)?
Regards,
Slavcho
On Mon, Jun 20, 2016 at 3:52 PM, Bert <biertie@gmail.com> wrote:
BertBut do not forgot to remove the slot again, else PostgreSQL will never remove the wall files again!If you are using version 9.4 or newer you can use the concept of 'replication slots' to block the removal or archiving of WAL files.Hello,Which version of PostgreSQL are you using?--On Mon, Jun 20, 2016 at 10:38 AM, Slavcho Trnkovski <strnkovski@gmail.com> wrote:Hi Guys,Is there a way to set retention policy for removing WAL files in hours/days instead of number of WAL files (wal_keep_segments)?I have a problem when backup is running, it complains that some files are missing. The problem is that i have situation when data load is running and a lot of WAL files are generated.Regards,SlavchoBert Desmet
0477/305361
On Tue, Jun 21, 2016 at 11:26 AM, Slavcho Trnkovski <strnkovski@gmail.com> wrote:
Hi,I use version 9.4.If I create physical replication slot, I'll need standby server to use it. I can use pg_receivexlog but it will not prevent removing WAL files, it will just stream it to some other location.Do you maybe have an idea how backup can use replication slot (without standby)?Regards,SlavchoOn Mon, Jun 20, 2016 at 3:52 PM, Bert <biertie@gmail.com> wrote:BertBut do not forgot to remove the slot again, else PostgreSQL will never remove the wall files again!If you are using version 9.4 or newer you can use the concept of 'replication slots' to block the removal or archiving of WAL files.Hello,Which version of PostgreSQL are you using?--On Mon, Jun 20, 2016 at 10:38 AM, Slavcho Trnkovski <strnkovski@gmail.com> wrote:Hi Guys,Is there a way to set retention policy for removing WAL files in hours/days instead of number of WAL files (wal_keep_segments)?I have a problem when backup is running, it complains that some files are missing. The problem is that i have situation when data load is running and a lot of WAL files are generated.Regards,SlavchoBert Desmet
0477/305361
If you're using pg_basebackup, be sure and use the --xlog-method=stream (-Xs) option. This ensures that all the WAL files that are needed for a consistent backup are kept as part of the base backup and it does them real-time as the WALs are generated. This avoids the problem you're encountering where, by the end of the backup, the WAL files you need have already been recycled. It opens up a secondary replication connection to the database to do this, so ensure you have max_wal_senders set high enough to allow two connections per backup running.
Keith