Re: Starting Postgres when there is no disk space

Thank you both. The symlink sounds like a very good idea. My other disk
is 100GB and the database is already 130GB so moving the whole thing will
require provisioning that will take more time. I will try the symlinks
first. Possibly moving some tables to a tablespace on the other partition
to make more room.

I have a scheduled process that runs daily to delete old data and do full
vacuum. Not sure why this happened (again).

Thanks,

Igal

On Wed, May 1, 2019 at 6:02 PM Michael Loftis <mloftis(at)wgops(dot)com> wrote:

> 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
>