agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedfilesystem full during vacuum - space recovery issues
13+ messages / 6 participants
[nested] [flat]
* filesystem full during vacuum - space recovery issues
@ 2024-07-15 18:47 Thomas Simpson <ts@talentstack.to>
0 siblings, 2 replies; 13+ messages in thread
From: Thomas Simpson @ 2024-07-15 18:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi
I have a large database (multi TB) which had a vacuum full running but
the database ran out of space during the rebuild of one of the large
data tables.
Cleaning down the WAL files got the database restarted (an archiving
problem led to the initial disk full).
However, the disk space is still at 99% as it appears the large table
rebuild files are still hanging around using space and have not been
deleted.
My problem now is how do I get this space back to return my free space
back to where it should be?
I tried some scripts to map the data files to relations but this didn't
work as removing some files led to startup failure despite them
appearing to be unrelated to anything in the database - I had to put
them back and then startup worked.
Any suggestions here?
Thanks
Tom
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-16 00:58 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Thomas Simpson <ts@talentstack.to>
1 sibling, 2 replies; 13+ messages in thread
From: Laurenz Albe @ 2024-07-16 00:58 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; pgsql-admin@lists.postgresql.org
On Mon, 2024-07-15 at 14:47 -0400, Thomas Simpson wrote:
> I have a large database (multi TB) which had a vacuum full running but the database
> ran out of space during the rebuild of one of the large data tables.
>
> Cleaning down the WAL files got the database restarted (an archiving problem led to
> the initial disk full).
>
> However, the disk space is still at 99% as it appears the large table rebuild files
> are still hanging around using space and have not been deleted.
>
> My problem now is how do I get this space back to return my free space back to where
> it should be?
>
> I tried some scripts to map the data files to relations but this didn't work as
> removing some files led to startup failure despite them appearing to be unrelated
> to anything in the database - I had to put them back and then startup worked.
>
> Any suggestions here?
That reads like the sad old story: "cleaning down" WAL files - you mean deleting the
very files that would have enabled PostgreSQL to recover from the crash that was
caused by the full file system.
Did you run "pg_resetwal"? If yes, that probably led to data corruption.
The above are just guesses. Anyway, there is no good way to get rid of the files
that were left behind after the crash. The reliable way of doing so is also the way
to get rid of potential data corruption caused by "cleaning down" the database:
pg_dump the whole thing and restore the dump to a new, clean cluster.
Yes, that will be a painfully long down time. An alternative is to restore a backup
taken before the crash.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-16 01:14 Imran Khan <imran.k.23@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 13+ messages in thread
From: Imran Khan @ 2024-07-16 01:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Thomas Simpson <ts@talentstack.to>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Also, you can use multi process dump and restore using pg_dump plus pigz
utility for zipping.
Thanks
On Tue, Jul 16, 2024, 4:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Mon, 2024-07-15 at 14:47 -0400, Thomas Simpson wrote:
> > I have a large database (multi TB) which had a vacuum full running but
> the database
> > ran out of space during the rebuild of one of the large data tables.
> >
> > Cleaning down the WAL files got the database restarted (an archiving
> problem led to
> > the initial disk full).
> >
> > However, the disk space is still at 99% as it appears the large table
> rebuild files
> > are still hanging around using space and have not been deleted.
> >
> > My problem now is how do I get this space back to return my free space
> back to where
> > it should be?
> >
> > I tried some scripts to map the data files to relations but this didn't
> work as
> > removing some files led to startup failure despite them appearing to be
> unrelated
> > to anything in the database - I had to put them back and then startup
> worked.
> >
> > Any suggestions here?
>
> That reads like the sad old story: "cleaning down" WAL files - you mean
> deleting the
> very files that would have enabled PostgreSQL to recover from the crash
> that was
> caused by the full file system.
>
> Did you run "pg_resetwal"? If yes, that probably led to data corruption.
>
> The above are just guesses. Anyway, there is no good way to get rid of
> the files
> that were left behind after the crash. The reliable way of doing so is
> also the way
> to get rid of potential data corruption caused by "cleaning down" the
> database:
> pg_dump the whole thing and restore the dump to a new, clean cluster.
>
> Yes, that will be a painfully long down time. An alternative is to
> restore a backup
> taken before the crash.
>
> Yours,
> Laurenz Albe
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-17 13:24 Thomas Simpson <ts@talentstack.to>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 13+ messages in thread
From: Thomas Simpson @ 2024-07-17 13:24 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Thanks Laurenz & Imran for your comments.
My responses inline below.
Thanks
Tom
On 15-Jul-2024 20:58, Laurenz Albe wrote:
> On Mon, 2024-07-15 at 14:47 -0400, Thomas Simpson wrote:
>> I have a large database (multi TB) which had a vacuum full running but the database
>> ran out of space during the rebuild of one of the large data tables.
>>
>> Cleaning down the WAL files got the database restarted (an archiving problem led to
>> the initial disk full).
>>
>> However, the disk space is still at 99% as it appears the large table rebuild files
>> are still hanging around using space and have not been deleted.
>>
>> My problem now is how do I get this space back to return my free space back to where
>> it should be?
>>
>> I tried some scripts to map the data files to relations but this didn't work as
>> removing some files led to startup failure despite them appearing to be unrelated
>> to anything in the database - I had to put them back and then startup worked.
>>
>> Any suggestions here?
> That reads like the sad old story: "cleaning down" WAL files - you mean deleting the
> very files that would have enabled PostgreSQL to recover from the crash that was
> caused by the full file system.
>
> Did you run "pg_resetwal"? If yes, that probably led to data corruption.
No, I just removed the excess already archived WALs to get space and
restarted. The vacuum full that was running had created files for the
large table it was processing and these are still hanging around eating
space without doing anything useful. The shutdown prevented the
rollback cleanly removing them which seems to be the core problem.
> The above are just guesses. Anyway, there is no good way to get rid of the files
> that were left behind after the crash. The reliable way of doing so is also the way
> to get rid of potential data corruption caused by "cleaning down" the database:
> pg_dump the whole thing and restore the dump to a new, clean cluster.
>
> Yes, that will be a painfully long down time. An alternative is to restore a backup
> taken before the crash.
My issue now is the dump & reload is taking a huge time; I know the
hardware is capable of multi-GB/s throughput but the reload is taking a
long time - projected to be about 10 days to reload at the current rate
(about 30Mb/sec). The old server and new server have a 10G link between
them and storage is SSD backed, so the hardware is capable of much much
more than it is doing now.
Is there a way to improve the reload performance? Tuning of any type -
even if I need to undo it later once the reload is done.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-17 13:49 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Simpson <ts@talentstack.to>
0 siblings, 1 reply; 13+ messages in thread
From: Ron Johnson @ 2024-07-17 13:49 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Jul 17, 2024 at 9:26 AM Thomas Simpson <ts@talentstack.to> wrote:
[snip]
> uge time; I know the hardware is capable of multi-GB/s throughput but the
> reload is taking a long time - projected to be about 10 days to reload at
> the current rate (about 30Mb/sec). The old server and new server have a
> 10G link between them and storage is SSD backed, so the hardware is capable
> of much much more than it is doing now.
>
> Is there a way to improve the reload performance? Tuning of any type -
> even if I need to undo it later once the reload is done.
>
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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 13:54 Thomas Simpson <ts@talentstack.to>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas Simpson @ 2024-07-18 13:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@talentstack.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
> <http://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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 15:16 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Simpson <ts@talentstack.to>
0 siblings, 0 replies; 13+ messages in thread
From: Ron Johnson @ 2024-07-18 15:16 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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@talentstack.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@talentstack.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
>>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 15:19 Paul Smith* <paul@pscs.co.uk>
parent: Thomas Simpson <ts@talentstack.to>
1 sibling, 1 reply; 13+ messages in thread
From: Paul Smith* @ 2024-07-18 15:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 15/07/2024 19:47, Thomas Simpson wrote:
>
> My problem now is how do I get this space back to return my free space
> back to where it should be?
>
> I tried some scripts to map the data files to relations but this
> didn't work as removing some files led to startup failure despite them
> appearing to be unrelated to anything in the database - I had to put
> them back and then startup worked.
>
I don't know what you tried to do
What would normally happen on a failed VACUUM FULL that fills up the
disk so the server crashes is that there are loads of data files
containing the partially rebuilt table. Nothing 'internal' to PostgreSQL
will point to those files as the internal pointers all change to the new
table in an ACID way, so you should be able to delete them.
You can usually find these relatively easily by looking in the relevant
tablespace directory for the base filename for a new huge table (lots
and lots of files with the same base name - eg looking for files called
*.1000 will find you base filenames for relations over about 1TB) and
checking to see if pg_filenode_relation() can't turn the filenode into a
relation. If that's the case that they're not currently in use for a
relation, then you should be able to just delete all those files
Is this what you tried, or did your 'script to map data files to
relations' do something else? You were a bit ambiguous about that part
of things.
Paul
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 18:59 Thomas Simpson <ts@talentstack.to>
parent: Paul Smith* <paul@pscs.co.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas Simpson @ 2024-07-18 18:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 18-Jul-2024 11:19, Paul Smith* wrote:
> On 15/07/2024 19:47, Thomas Simpson wrote:
>>
>> My problem now is how do I get this space back to return my free
>> space back to where it should be?
>>
>> I tried some scripts to map the data files to relations but this
>> didn't work as removing some files led to startup failure despite
>> them appearing to be unrelated to anything in the database - I had to
>> put them back and then startup worked.
>>
> I don't know what you tried to do
>
> What would normally happen on a failed VACUUM FULL that fills up the
> disk so the server crashes is that there are loads of data files
> containing the partially rebuilt table. Nothing 'internal' to
> PostgreSQL will point to those files as the internal pointers all
> change to the new table in an ACID way, so you should be able to
> delete them.
>
> You can usually find these relatively easily by looking in the
> relevant tablespace directory for the base filename for a new huge
> table (lots and lots of files with the same base name - eg looking for
> files called *.1000 will find you base filenames for relations over
> about 1TB) and checking to see if pg_filenode_relation() can't turn
> the filenode into a relation. If that's the case that they're not
> currently in use for a relation, then you should be able to just
> delete all those files
>
> Is this what you tried, or did your 'script to map data files to
> relations' do something else? You were a bit ambiguous about that part
> of things.
>
[BTW, v9.6 which I know is old but this server is stuck there]
Yes, I was querying relfilenode from pg_class to get the filename
(integer) and then comparing a directory listing for files which did not
match the relfilenode as candidates to remove.
I moved these elsewhere (i.e. not delete, just move out the way so I
could move them back in case of trouble).
Without these apparently unrelated files, the database did not start and
complained about them being missing, so I had to put them back. This
was despite not finding any reference to the filename/number in pg_class.
At that point I gave up since I cannot afford to make the problem worse!
I know I'm stuck with the slow rebuild at this point. However, I doubt
I am the only person in the world that needs to dump and reload a large
database. My thought is this is a weak point for PostgreSQL so it makes
sense to consider ways to improve the dump reload process, especially as
it's the last-resort upgrade path recommended in the upgrade guide and
the general fail-safe route to get out of trouble.
Thanks
Tom
> Paul
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 20:32 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Simpson <ts@talentstack.to>
0 siblings, 1 reply; 13+ messages in thread
From: Ron Johnson @ 2024-07-18 20:32 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <ts@talentstack.to> wrote:
[snip]
> [BTW, v9.6 which I know is old but this server is stuck there]
>
> [snip]
> I know I'm stuck with the slow rebuild at this point. However, I doubt I
> am the only person in the world that needs to dump and reload a large
> database. My thought is this is a weak point for PostgreSQL so it makes
> sense to consider ways to improve the dump reload process, especially as
> it's the last-resort upgrade path recommended in the upgrade guide and the
> general fail-safe route to get out of trouble.
>
No database does fast single-threaded backups.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 20:53 Thomas Simpson <ts@talentstack.to>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas Simpson @ 2024-07-18 20:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 18-Jul-2024 16:32, Ron Johnson wrote:
> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <ts@talentstack.to> wrote:
> [snip]
>
> [BTW, v9.6 which I know is old but this server is stuck there]
>
> [snip]
>
> I know I'm stuck with the slow rebuild at this point. However, I
> doubt I am the only person in the world that needs to dump and
> reload a large database. My thought is this is a weak point for
> PostgreSQL so it makes sense to consider ways to improve the dump
> reload process, especially as it's the last-resort upgrade path
> recommended in the upgrade guide and the general fail-safe route
> to get out of trouble.
>
> No database does fast single-threaded backups.
Agreed. My thought is that is should be possible for a 'new dumpall' to
be multi-threaded.
Something like :
* Set number of threads on 'source' (perhaps by querying a listening
destination for how many threads it is prepared to accept via a control
port)
* Select each database in turn
* Organize the tables which do not have references themselves
* Send each table separately in each thread (or queue them until a
thread is available) ('Stage 1')
* Rendezvous stage 1 completion (pause sending, wait until feedback from
destination confirming all completed) so we have a known consistent
state that is safe to proceed to subsequent tables
* Work through tables that do refer to the previously sent in the same
way (since the tables they reference exist and have their data) ('Stage 2')
* Repeat progressively until all tables are done ('Stage 3', 4 etc. as
necessary)
The current dumpall is essentially doing this table organization
currently [minus stage checkpoints/multi-thread] otherwise the dump/load
would not work. It may even be doing a lot of this for 'directory'
mode? The change here is organizing n threads to process them
concurrently where possible and coordinating the pipes so they only send
data which can be accepted.
The destination would need to have a multi-thread listen and co-ordinate
with the sender on some control channel so feed back completion of each
stage.
Something like a destination host and control channel port to establish
the pipes and create additional netcat pipes on incremental ports above
the control port for each thread used.
Dumpall seems like it could be a reasonable start point since it is
already doing the complicated bits of serializing the dump data so it
can be consistently loaded.
Probably not really an admin question at this point, more a feature
enhancement.
Is there anything fundamentally wrong that someone with more intimate
knowledge of dumpall could point out?
Thanks
Tom
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 22:41 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Simpson <ts@talentstack.to>
0 siblings, 1 reply; 13+ messages in thread
From: Ron Johnson @ 2024-07-18 22:41 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Multi-threaded writing to the same giant text file won't work too well,
when all the data for one table needs to be together.
Just temporarily add another disk for backups.
On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <ts@talentstack.to> wrote:
>
> On 18-Jul-2024 16:32, Ron Johnson wrote:
>
> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <ts@talentstack.to> wrote:
> [snip]
>
>> [BTW, v9.6 which I know is old but this server is stuck there]
>>
> [snip]
>
>> I know I'm stuck with the slow rebuild at this point. However, I doubt I
>> am the only person in the world that needs to dump and reload a large
>> database. My thought is this is a weak point for PostgreSQL so it makes
>> sense to consider ways to improve the dump reload process, especially as
>> it's the last-resort upgrade path recommended in the upgrade guide and the
>> general fail-safe route to get out of trouble.
>>
> No database does fast single-threaded backups.
>
> Agreed. My thought is that is should be possible for a 'new dumpall' to
> be multi-threaded.
>
> Something like :
>
> * Set number of threads on 'source' (perhaps by querying a listening
> destination for how many threads it is prepared to accept via a control
> port)
>
> * Select each database in turn
>
> * Organize the tables which do not have references themselves
>
> * Send each table separately in each thread (or queue them until a thread
> is available) ('Stage 1')
>
> * Rendezvous stage 1 completion (pause sending, wait until feedback from
> destination confirming all completed) so we have a known consistent state
> that is safe to proceed to subsequent tables
>
> * Work through tables that do refer to the previously sent in the same way
> (since the tables they reference exist and have their data) ('Stage 2')
>
> * Repeat progressively until all tables are done ('Stage 3', 4 etc. as
> necessary)
>
> The current dumpall is essentially doing this table organization currently
> [minus stage checkpoints/multi-thread] otherwise the dump/load would not
> work. It may even be doing a lot of this for 'directory' mode? The change
> here is organizing n threads to process them concurrently where possible
> and coordinating the pipes so they only send data which can be accepted.
>
> The destination would need to have a multi-thread listen and co-ordinate
> with the sender on some control channel so feed back completion of each
> stage.
>
> Something like a destination host and control channel port to establish
> the pipes and create additional netcat pipes on incremental ports above the
> control port for each thread used.
>
> Dumpall seems like it could be a reasonable start point since it is
> already doing the complicated bits of serializing the dump data so it can
> be consistently loaded.
>
> Probably not really an admin question at this point, more a feature
> enhancement.
>
> Is there anything fundamentally wrong that someone with more intimate
> knowledge of dumpall could point out?
>
> Thanks
>
> Tom
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-19 02:59 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Scott Ribe @ 2024-07-19 02:59 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
1) Add new disk, use a new tablespace to move some big tables to it, to get back up and running
2) Replica server provisioned sufficiently for the db, pg_basebackup to it
3) Get streaming replication working
4) Switch over to new server
In other words, if you don't want terrible downtime, you need yet another server fully provisioned to be able to run your db.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
> On Jul 18, 2024, at 4:41 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> Multi-threaded writing to the same giant text file won't work too well, when all the data for one table needs to be together.
>
> Just temporarily add another disk for backups.
>
> On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <ts@talentstack.to> wrote:
>
> On 18-Jul-2024 16:32, Ron Johnson wrote:
>> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <ts@talentstack.to> wrote:
>> [snip]
>> [BTW, v9.6 which I know is old but this server is stuck there]
>> [snip]
>> I know I'm stuck with the slow rebuild at this point. However, I doubt I am the only person in the world that needs to dump and reload a large database. My thought is this is a weak point for PostgreSQL so it makes sense to consider ways to improve the dump reload process, especially as it's the last-resort upgrade path recommended in the upgrade guide and the general fail-safe route to get out of trouble.
>> No database does fast single-threaded backups.
> Agreed. My thought is that is should be possible for a 'new dumpall' to be multi-threaded.
> Something like :
> * Set number of threads on 'source' (perhaps by querying a listening destination for how many threads it is prepared to accept via a control port)
> * Select each database in turn
> * Organize the tables which do not have references themselves
> * Send each table separately in each thread (or queue them until a thread is available) ('Stage 1')
> * Rendezvous stage 1 completion (pause sending, wait until feedback from destination confirming all completed) so we have a known consistent state that is safe to proceed to subsequent tables
> * Work through tables that do refer to the previously sent in the same way (since the tables they reference exist and have their data) ('Stage 2')
> * Repeat progressively until all tables are done ('Stage 3', 4 etc. as necessary)
> The current dumpall is essentially doing this table organization currently [minus stage checkpoints/multi-thread] otherwise the dump/load would not work. It may even be doing a lot of this for 'directory' mode? The change here is organizing n threads to process them concurrently where possible and coordinating the pipes so they only send data which can be accepted.
> The destination would need to have a multi-thread listen and co-ordinate with the sender on some control channel so feed back completion of each stage.
> Something like a destination host and control channel port to establish the pipes and create additional netcat pipes on incremental ports above the control port for each thread used.
> Dumpall seems like it could be a reasonable start point since it is already doing the complicated bits of serializing the dump data so it can be consistently loaded.
> Probably not really an admin question at this point, more a feature enhancement.
> Is there anything fundamentally wrong that someone with more intimate knowledge of dumpall could point out?
> Thanks
> Tom
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-07-19 02:59 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-15 18:47 filesystem full during vacuum - space recovery issues Thomas Simpson <ts@talentstack.to>
2024-07-16 00:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-16 01:14 ` Imran Khan <imran.k.23@gmail.com>
2024-07-17 13:24 ` Thomas Simpson <ts@talentstack.to>
2024-07-17 13:49 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-18 13:54 ` Thomas Simpson <ts@talentstack.to>
2024-07-18 15:16 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-18 15:19 ` Paul Smith* <paul@pscs.co.uk>
2024-07-18 18:59 ` Thomas Simpson <ts@talentstack.to>
2024-07-18 20:32 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-18 20:53 ` Thomas Simpson <ts@talentstack.to>
2024-07-18 22:41 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-19 02:59 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox