Re: filesystem full during vacuum - space recovery issues

There's no free lunch, and you can't squeeze blood from a turnip.

Single-threading will *ALWAYS* be slow: if you want speed, temporarily
throw more hardware at it: specifically another disk (and possibly more RAM
and CPU).

On Thu, Jul 18, 2024 at 9:55 AM Thomas Simpson <ts(at)talentstack(dot)to> wrote:

> Thanks Ron for the suggestions - I applied some of the settings which
> helped throughput a little bit but were not an ideal solution for me - let
> me explain.
>
> Due to the size, I do not have the option to use the directory mode (or
> anything that uses disk space) for dump as that creates multiple
> directories (hence why it can do multiple jobs). I do not have the several
> hundred TB of space to hold the output and there is no practical way to get
> it, especially for a transient reload.
>
> I have my original server plus my replica; as the replica also applied the
> WALs, it too filled up and went down. I've basically recreated this as a
> primary server and am using a pipeline to dump from the original into this
> as I know that has enough space for the final loaded database and should
> have space left over from the clean rebuild (whereas the original server
> still has space exhausted due to the leftover files).
>
> Incidentally, this state is also why going to a backup is not helpful
> either as the restore and then re-apply the WALs would just end up filling
> the disk and recreating the original problem.
>
> Even with the improved throughput, current calculations are pointing to
> almost 30 days to recreate the database through dump and reload which is a
> pretty horrible state to be in.
>
> I think this is perhaps an area of improvement - especially as larger
> PostgreSQL databases become more common, I'm not the only person who could
> face this issue.
>
> Perhaps an additional dumpall mode that generates multiple output pipes
> (I'm piping via netcat to the other server) - it would need to combine with
> a multiple listening streams too and some degree of ordering/feedback to
> get to the essentially serialized output from the current dumpall. But
> this feels like PostgreSQL expert developer territory.
>
> Thanks
>
> Tom
>
>
> On 17-Jul-2024 09:49, Ron Johnson wrote:
>
> On Wed, Jul 17, 2024 at 9:26 AM Thomas Simpson <ts(at)talentstack(dot)to> wrote:
>
> ---8<--snip,snip---8<---
>
> That would, of course, depend on what you're currently doing. pg_dumpall
> of a Big Database is certainly suboptimal compared to "pg_dump -Fd
> --jobs=24".
>
> This is what I run (which I got mostly from a databasesoup.com blog post)
> on the target instance before doing "pg_restore -Fd --jobs=24":
> declare -i CheckPoint=30
> declare -i SharedBuffs=32
> declare -i MaintMem=3
> declare -i MaxWalSize=36
> declare -i WalBuffs=64
> pg_ctl restart -wt$TimeOut -mfast \
> -o "-c hba_file=$PGDATA/pg_hba_maintmode.conf" \
> -o "-c fsync=off" \
> -o "-c log_statement=none" \
> -o "-c log_temp_files=100kB" \
> -o "-c log_checkpoints=on" \
> -o "-c log_min_duration_statement=120000" \
> -o "-c shared_buffers=${SharedBuffs}GB" \
> -o "-c maintenance_work_mem=${MaintMem}GB" \
> -o "-c synchronous_commit=off" \
> -o "-c archive_mode=off" \
> -o "-c full_page_writes=off" \
> -o "-c checkpoint_timeout=${CheckPoint}min" \
> -o "-c max_wal_size=${MaxWalSize}GB" \
> -o "-c wal_level=minimal" \
> -o "-c max_wal_senders=0" \
> -o "-c wal_buffers=${WalBuffs}MB" \
> -o "-c autovacuum=off"
>
> After the pg_restore -Fd --jobs=24 and vacuumdb --analyze-only --jobs=24:
> pg_ctl stop -wt$TimeOut && pg_ctl start -wt$TimeOut
>
> Of course, these parameter values were for *my* hardware.
>
>> My backups were in progress when all the issues happened, so they're not
>> such a good starting point and I'd actually prefer the clean reload since
>> this DB has been through multiple upgrades (without reloads) until now so I
>> know it's not especially clean. The size has always prevented the full
>> reload before but the database is relatively low traffic now so I can
>> afford some time to reload, but ideally not 10 days.
>>
>> Yours,
>> Laurenz Albe
>>
>>