Обсуждение: WAL storage when archive_command=''
Dear Colleagues, With archive_mode='on' and archive_command='', is there a limit on WAL files accumulation or will they be kept infinitely until the disk is full? Actually I'd prefer them to be kept infinitely, just want to make sure it's really the case. -- Victor Sudakov VAS4-RIPE http://vas.tomsk.ru/ 2:5005/49@fidonet
Am 09.09.22 um 06:56 schrieb Victor Sudakov: > Dear Colleagues, > > With archive_mode='on' and archive_command='', is there a limit on WAL > files accumulation or will they be kept infinitely until the disk is > full? > > Actually I'd prefer them to be kept infinitely, just want to make sure > it's really the case. > There is no limit. The disk will run full. That's why disk space is the #1 priority in monitoring a PostgreSQL instance. -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
Holger Jakobs wrote: > > Am 09.09.22 um 06:56 schrieb Victor Sudakov: > > Dear Colleagues, > > > > With archive_mode='on' and archive_command='', is there a limit on WAL > > files accumulation or will they be kept infinitely until the disk is > > full? > > > > Actually I'd prefer them to be kept infinitely, just want to make sure > > it's really the case. > > > There is no limit. The disk will run full. That's actually great that unarchived WAL files will never get deleted or reused. Thank you for confirming this. I was pretty sure about the situation when the archive_command returns a non-zero status, but not so sure about an empty archive_command. > > That's why disk space is the #1 priority in monitoring a PostgreSQL > instance. -- Victor Sudakov VAS4-RIPE http://vas.tomsk.ru/ 2:5005/49@fidonet
On 9/9/22 02:25, Victor Sudakov wrote: > Holger Jakobs wrote: >> Am 09.09.22 um 06:56 schrieb Victor Sudakov: >>> Dear Colleagues, >>> >>> With archive_mode='on' and archive_command='', is there a limit on WAL >>> files accumulation or will they be kept infinitely until the disk is >>> full? >>> >>> Actually I'd prefer them to be kept infinitely, just want to make sure >>> it's really the case. >>> >> There is no limit. The disk will run full. > That's actually great that unarchived WAL files will never get > deleted or reused. Thank you for confirming this. How are you going to prevent the disk from filling up? -- Angular momentum makes the world go 'round.
Ron wrote: > On 9/9/22 02:25, Victor Sudakov wrote: > > Holger Jakobs wrote: > >> Am 09.09.22 um 06:56 schrieb Victor Sudakov: > >>> Dear Colleagues, > >>> > >>> With archive_mode='on' and archive_command='', is there a limit on WAL > >>> files accumulation or will they be kept infinitely until the disk is > >>> full? > >>> > >>> Actually I'd prefer them to be kept infinitely, just want to make sure > >>> it's really the case. > >>> > >> There is no limit. The disk will run full. > > That's actually great that unarchived WAL files will never get > > deleted or reused. Thank you for confirming this. > > How are you going to prevent the disk from filling up? Someone will eventually set archive_command to something useful before the disk is full. Or Postgress will stop. It does not matter for the current question. I just want to make sure that unarchived WAL files are never ever deleted and there is no limit on the number or size of unarchived WAL files. -- Victor Sudakov VAS4-RIPE http://vas.tomsk.ru/ 2:5005/49@fidonet
Hi guys, I have a PostgreSQL 9.5 server, which started showing the following message in the log: ,ERROR,54000,"multixact ""members"" limit exceeded","This command would create a multixact with 2 members, but the remaining space is only enough for 0 members.","Run a database-wide VACUUM in database with OID 139059 with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings.",,,"SQL statement ""SELECT 1 FROM ONLY ""public"".""alf_server"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x""","insert into I started the vacuum in the database , but the same for the size ,I believe it will take a long time. Is there any way to increase this limit member while the vacuum is running to free up the application? thanks a lot, On Fri, Sep 9, 2022 at 6:30 AM Victor Sudakov <vas@sibptus.ru> wrote: > > Ron wrote: > > On 9/9/22 02:25, Victor Sudakov wrote: > > > Holger Jakobs wrote: > > >> Am 09.09.22 um 06:56 schrieb Victor Sudakov: > > >>> Dear Colleagues, > > >>> > > >>> With archive_mode='on' and archive_command='', is there a limit on WAL > > >>> files accumulation or will they be kept infinitely until the disk is > > >>> full? > > >>> > > >>> Actually I'd prefer them to be kept infinitely, just want to make sure > > >>> it's really the case. > > >>> > > >> There is no limit. The disk will run full. > > > That's actually great that unarchived WAL files will never get > > > deleted or reused. Thank you for confirming this. > > > > How are you going to prevent the disk from filling up? > > Someone will eventually set archive_command to something useful before > the disk is full. Or Postgress will stop. It does not matter for the > current question. > > I just want to make sure that unarchived WAL files are never ever > deleted and there is no limit on the number or size of unarchived WAL files. > > -- > Victor Sudakov VAS4-RIPE > http://vas.tomsk.ru/ > 2:5005/49@fidonet > >
On Saturday, September 10, 2022, Leandro Bugalho <leandrohb@gmail.com> wrote:
Hi guys, I have a PostgreSQL 9.5 server, which started showing the
following message in the log:
Go start a new thread instead of adding unrelated content to existing threads.
David J.
On Friday, September 9, 2022, Victor Sudakov <vas@sibptus.ru> wrote:
I just want to make sure that unarchived WAL files are never ever
deleted and there is no limit on the number or size of unarchived WAL files.
The docs clearly answer this IMO:
If
archive_command
is an empty string (the default) while archive_mode
is enabled, WAL archiving is temporarily disabled, but the server continues to accumulate WAL segment files in the expectation that a command will soon be provided.Though I suppose I would argue to either remove the word temporarily or describe under what conditions, besides putting in a non-empty string, would re-enable it. Additionally, change disabled to suspended since that doesn’t contravene the behavior of an enabled archive mode to never remove WAL without a successful execution of a non-empty archive_command.
David J.
Will do, thanks! On Sun, Sep 11, 2022 at 3:18 AM David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Saturday, September 10, 2022, Leandro Bugalho <leandrohb@gmail.com> wrote: >> >> Hi guys, I have a PostgreSQL 9.5 server, which started showing the >> following message in the log: > > > Go start a new thread instead of adding unrelated content to existing threads. > > David J. >