Thread: Server with hot standby slave wont start after vacuum
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
anotherserver
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.
On Mon, 2020-04-06 at 09:39 +0300, Andrus wrote: > 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. > > 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. Simple: don't run VACUUM (FULL). Since that will rewrite the whole database, you should not be surprised that it generates a lot of WAL. Essentially, you are asking, "I have a cup that fits 20 ounces, now I pour in a gallon, and it overflows. How can I prevent that?" Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com