Server with hot standby slave wont start after vacuum

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Server with hot standby slave wont start after vacuum
Date: 2020-04-06 06:39:34
Message-ID: 76CCED1112A14005BCF8486556D43B46@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

Streaming asynchronous binary replication is used with hot standby slave.

To recover disk space

vacuumdb --all --full --skip-locked

is executed in every night is master.

During this vacuumdb stops with error

vacuumdb: error: vacuuming of table "myschema.mytable" in database "mydb" failed: PANIC: could not write to file
"pg_wal/xlogtemp.24729": No space left on device
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

And master wont start anymore:

LOG: server process (PID 24729) was terminated by signal 6: Aborted
DETAIL: Failed process was running: VACUUM (SKIP_LOCKED, FULL) firma39.rid;
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server
process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
...
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2020-04-06 01:14:10 EEST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 2A0/C414BA68
FATAL: could not extend file "global/58294678": wrote only 4096 of 8192 bytes at block 1728
HINT: Check free disk space.
CONTEXT: WAL redo at 2A0/D661D4B0 for XLOG/FPI:
LOG: startup process (PID 24732) exited with exit code 1
LOG: aborting startup due to startup process failure
LOG: database system is shut down

pg_wal contains 2005 files with total size 32 GB and there is no free disk space.

hot standby server is connected over 20 Mbit internet.

Maybe vacuum full causes creation of creates huge number files in pg_wal which cannot transferred fast over 20Mbit internet.

How to fix this so that master continues to work?
Mabe it is possible to disable creation of wal files by vacuum.

Postgres 12 in Debian is used.

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2020-04-06 08:03:20 How to prevent master server crash if hot standby stops
Previous Message arden liu 2020-04-05 18:46:48 Re: psql show me the : and ask user input, when running one sql file