Re: Starting Postgres when there is no disk space

Best option....Copy/move the entire pgdata to a larger space. It may also
be enough to just move the WAL (leaving a symlink) freeing up the 623M but
I doubt it since VACUUM FULL occurs in the same table space and can need an
equal amount of space (130G) depending on how much it can actually free up.

You may also get away with just moving (and leaving a symlink) for the base
but I don't recall if that works or not.

On Wed, May 1, 2019 at 18:07 Igal Sapir <igal(at)lucee(dot)org> wrote:

> I have Postgres running in a Docker container with PGDATA mounted from the
> host. Postgres consume all of the disk space, 130GB [1], and can not be
> started [2]. The database has a lot of bloat due to much many deletions.
> The problem is that now I can not start Postgres at all.
>
> I mounted an additional partition with 100GB, hoping to fix the bloat with
> a TABLESPACE in the new mount, but how can I do anything if Postgres will
> not start in the first place?
>
> I expected there to be a tool that can defrag the database files, e.g. a
> "vacuumdb" utility that can run without Postgres. Or maybe run Postgres
> and disable the WAL so that no new disk space will be required.
>
> Surely, I'm not the first one to experience this issue. How can I fix
> this?
>
> Thank you,
>
> Igal
>
> [1]
> root(at)ff818ff7550a:/# du -h --max-depth=1 /pgdata
> 625M /pgdata/pg_wal
> 608K /pgdata/global
> 0 /pgdata/pg_commit_ts
> 0 /pgdata/pg_dynshmem
> 8.0K /pgdata/pg_notify
> 0 /pgdata/pg_serial
> 0 /pgdata/pg_snapshots
> 16K /pgdata/pg_subtrans
> 0 /pgdata/pg_twophase
> 16K /pgdata/pg_multixact
> 130G /pgdata/base
> 0 /pgdata/pg_replslot
> 0 /pgdata/pg_tblspc
> 0 /pgdata/pg_stat
> 0 /pgdata/pg_stat_tmp
> 7.9M /pgdata/pg_xact
> 4.0K /pgdata/pg_logical
> 0 /pgdata/tmp
> 130G /pgdata
>
> [2]
> postgres(at)1efd26b999ca:/$ /usr/lib/postgresql/11/bin/pg_ctl start
> waiting for server to start....2019-05-01 20:43:59.301 UTC [34] LOG:
> listening on IPv4 address "0.0.0.0", port 5432
> 2019-05-01 20:43:59.301 UTC [34] LOG: listening on IPv6 address "::",
> port 5432
> 2019-05-01 20:43:59.303 UTC [34] LOG: listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5432"
> 2019-05-01 20:43:59.322 UTC [35] LOG: database system shutdown was
> interrupted; last known up at 2019-05-01 19:37:32 UTC
> 2019-05-01 20:43:59.863 UTC [35] LOG: database system was not properly
> shut down; automatic recovery in progress
> 2019-05-01 20:43:59.865 UTC [35] LOG: redo starts at 144/4EFFFC18
> ...2019-05-01 20:44:02.389 UTC [35] LOG: redo done at 144/74FFE060
> 2019-05-01 20:44:02.389 UTC [35] LOG: last completed transaction was at
> log time 2019-04-28 05:05:24.687581+00
> .2019-05-01 20:44:03.474 UTC [35] PANIC: could not write to file
> "pg_logical/replorigin_checkpoint.tmp": No space left on device
> 2019-05-01 20:44:03.480 UTC [34] LOG: startup process (PID 35) was
> terminated by signal 6: Aborted
> 2019-05-01 20:44:03.480 UTC [34] LOG: aborting startup due to startup
> process failure
> 2019-05-01 20:44:03.493 UTC [34] LOG: database system is shut down
> stopped waiting
> pg_ctl: could not start server
> Examine the log output.
>
>
>
>
>
>
>
>
>
>
>
> --

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler