On Wed, Jul 17, 2024 at 9:26=E2=80=AFAM T= homas Simpson <ts@talentstack.to> wrote:

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).=C2=A0 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?=C2=A0 Tuning of an= y type - even if I need to undo it later once the reload is done.

That would, of course, depend on what you're curr= ently doing.=C2=A0 pg_dumpall of a Big Database is certainly suboptimal com= pared to "pg_dump -Fd --jobs=3D24".

This= is what I run (which I got mostly from a databasesoup.com blog post) on the target instance=C2=A0before doing= "pg_restore -Fd --jobs=3D24":

declare -i CheckPoint=3D30
declare -i SharedBuffs=3D32
declare -i Ma= intMem=3D3
declare -i MaxWalSize=3D36
declare -i WalBuffs=3D64

pg_ctl restart -wt$TimeOut -mfast \=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba_file=3D$PGDATA/pg_hba_maint= mode.conf" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c fsync=3Doff&qu= ot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_statement=3Dnone"= \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_temp_files=3D100kB" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_checkpoints=3Don" \=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_min_duration_statement=3D1200= 00" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c shared_buffers=3D${Sh= aredBuffs}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c maintenance= _work_mem=3D${MaintMem}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-= c synchronous_commit=3Doff" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "= -c archive_mode=3Doff" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c fu= ll_page_writes=3Doff" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c che= ckpoint_timeout=3D${CheckPoint}min" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -= o "-c max_wal_size=3D${MaxWalSize}GB" \
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 -o "-c wal_level=3Dminimal" \
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 -o "-c max_wal_senders=3D0" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = -o "-c wal_buffers=3D${WalBuffs}MB" \
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 -o "-c autovacuum=3Doff"=C2=A0

After the pg_restore -Fd --jobs=3D24 = and vacuumdb --analyze-only --jobs=3D24:

pg_ctl stop -wt$TimeOut && pg_ctl = start -wt$TimeOut

Of course, these para= meter values were for my=C2=A0hardware.

My backups were in progress when all the= issues happened, so they're not such a good starting point and I'd actually prefe= r the clean reload since this DB has been through multiple upgrades (without reloads) until now so I know it's not especially clean.= =C2=A0 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

--000000000000a90b35061d71bb54-- From ts@talentstack.to Thu Jul 18 13:54:07 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sURb6-0034zl-T0 for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 13:55:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sURb4-00Giad-8s for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 13:55:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sURb3-00GiaU-Ot for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 13:55:02 +0000 Received: from mail.network-systems-solutions.net ([162.250.175.178]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sURaz-000CTD-BX for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 13:54:59 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail.network-systems-solutions.net (Postfix) with ESMTP id AC722807C3 for ; Thu, 18 Jul 2024 14:32:43 +0000 (UTC) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 38D488E0736 for ; Thu, 18 Jul 2024 09:54:49 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1721310888-3614-3610/6/41; Thu, 18 Jul 2024 13:54:48 +0000 Content-Type: multipart/alternative; boundary=------------RXa98QSq0nkBXFgZsVcQXUew Message-Id: Date: Thu, 18 Jul 2024 09:54:07 -0400 Mime-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: filesystem full during vacuum - space recovery issues To: pgsql-admin@lists.postgresql.org References: <38b20a9f-ef8a-486a-bb7d-f7a7be20f98d@talentstack.to> Content-Language: en-CA From: Thomas Simpson In-Reply-To: X-NSSLTD-Archiving: Added to store 1 as 91334 X-Scanned-By: MIMEDefang 2.84 on 127.0.1.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------RXa98QSq0nkBXFgZsVcQXUew Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Thanks Ron for the suggestions - I applied some of the settings which=20 helped throughput a little bit but were not an ideal solution for me -=20 let me explain. Due to the size, I do not have the option to use the directory mode (or=20 anything that uses disk space) for dump as that creates multiple=20 directories (hence why it can do multiple jobs).=C2=A0 I do not have the=20 several hundred TB of space to hold the output and there is no practical=20 way to get it, especially for a transient reload. I have my original server plus my replica; as the replica also applied=20 the WALs, it too filled up and went down.=C2=A0 I've basically recreated = this=20 as a primary server and am using a pipeline to dump from the original=20 into this as I know that has enough space for the final loaded database=20 and should have space left over from the clean rebuild (whereas the=20 original server still has space exhausted due to the leftover files). Incidentally, this state is also why going to a backup is not helpful=20 either as the restore and then re-apply the WALs would just end up=20 filling the disk and recreating the original problem. Even with the improved throughput, current calculations are pointing to=20 almost 30 days to recreate the database through dump and reload which is=20 a pretty horrible state to be in. I think this is perhaps an area of improvement - especially as larger=20 PostgreSQL databases become more common, I'm not the only person who=20 could face this issue. Perhaps an additional dumpall mode that generates multiple output pipes=20 (I'm piping via netcat to the other server) - it would need to combine=20 with a multiple listening streams too and some degree of=20 ordering/feedback to get to the essentially serialized output from the=20 current dumpall.=C2=A0 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=E2=80=AFAM Thomas Simpson wrote: ---8<--snip,snip---8<--- > That would, of course, depend on what you're currently doing.=C2=A0=20 > pg_dumpall of a Big Database is certainly suboptimal compared to=20 > "pg_dump -Fd --jobs=3D24". > > This is what I run (which I got mostly from a databasesoup.com=20 > blog post) on the target instance=C2=A0before= =20 > doing "pg_restore -Fd --jobs=3D24": > declare -i CheckPoint=3D30 > declare -i SharedBuffs=3D32 > declare -i MaintMem=3D3 > declare -i MaxWalSize=3D36 > declare -i WalBuffs=3D64 > pg_ctl restart -wt$TimeOut -mfast \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba_file=3D$PGDATA/pg_hba_maintmode.= conf" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c fsync=3Doff" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_statement=3Dnone" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_temp_files=3D100kB" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_checkpoints=3Don" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_min_duration_statement=3D120000"= \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c shared_buffers=3D${SharedBuffs}GB" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c maintenance_work_mem=3D${MaintMem}GB= " \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c synchronous_commit=3Doff" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c archive_mode=3Doff" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c full_page_writes=3Doff" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c checkpoint_timeout=3D${CheckPoint}mi= n" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D${MaxWalSize}GB" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_level=3Dminimal" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_senders=3D0" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_buffers=3D${WalBuffs}MB" \ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c autovacuum=3Doff" > > After the pg_restore -Fd --jobs=3D24 and vacuumdb --analyze-only = --jobs=3D24: > pg_ctl stop -wt$TimeOut && pg_ctl start -wt$TimeOut > > Of course, these parameter values were for *my*=C2=A0hardware. > > 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.=C2= =A0 > 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 > --------------RXa98QSq0nkBXFgZsVcQXUew Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

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).=C2=A0 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.=C2=A0 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.=C2=A0 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=E2=80=AFAM Thomas = Simpson <ts@talentstack.to> wrote:

---8<--snip,snip---8<---

That would, of course, depend on what you're currently doing.=C2=A0 pg_dumpall of a Big Database is certainly suboptimal compared to "pg_dump -Fd --jobs=3D24".

This is what I run (which I got mostly from a d= atabasesoup.com blog post) on the target instance=C2=A0before doing "pg_resto= re -Fd --jobs=3D24":

declare -i CheckPoint=3D30
declare -i SharedBuffs=3D32
declare -i MaintMem=3D3
declare -i MaxWalSize=3D36
declare -i WalBuffs=3D64

pg_ctl restart -wt$TimeOut -mfast \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba_file=3D$PGDATA/pg_hb= a_maintmode.conf" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c fsync=3Doff" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_statement=3Dnone" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_temp_files=3D100kB" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_checkpoints=3Don" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_min_duration_stateme= nt=3D120000" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c shared_buffers=3D${Share= dBuffs}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c maintenance_work_mem=3D$= {MaintMem}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c synchronous_commit=3Doff= " \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c archive_mode=3Doff" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c full_page_writes=3Doff" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c checkpoint_timeout=3D${C= heckPoint}min" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D${MaxWalS= ize}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_level=3Dminimal" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_senders=3D0" = \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_buffers=3D${WalBuffs= }MB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c autovacuum=3Doff"=C2=A0<= br>

After the pg_restore -Fd --jobs=3D24 and vacuumdb --analyze-only --jobs=3D24:

pg_ctl stop -wt$TimeOut &&= ; pg_ctl start -wt$TimeOut

Of course, these parameter values were for my=C2=A0= 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.=C2=A0 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

--------------RXa98QSq0nkBXFgZsVcQXUew-- From ronljohnsonjr@gmail.com Thu Jul 18 15:16:05 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUSrn-003DXB-QR for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 15:16:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUSrl-000EXG-V4 for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 15:16:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUSrl-000EX8-FU for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 15:16:21 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sUSri-000DiP-Rj for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 15:16:21 +0000 Received: by mail-ot1-x334.google.com with SMTP id 46e09a7af769-7039e4a4a03so496915a34.3 for ; Thu, 18 Jul 2024 08:16:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721315776; x=1721920576; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=urh2Mel08HMi6CTKyi9zAvhCgC0QJyHku4vSvSoCA8k=; b=HQlmi+ngjypRspGtwWBEUu1kJckJmaShLtUYpGi/4swKZhaWS11nGxjFRNVVnwRX76 /fW7TFuk914G8HS3aJfHpS2M9RJQaejT6EQkXiIc0IbCSjl9EDqG3kG9nQXhkStJTGfN AE9xtqcJfDOmJjxAFNFZm0mA2gtj4mzhMxZA0RE4Lg6Fk2J5jA2rEQbtaEQrpj+CFypd i3oTmDkxQNpGP+dr/GHQym9Wk3yl5l1swThO9LgyJlM8hPvdrdLbec7/cysIdnfwvYTD THb5kgE6UPiSdPHjtXYM0BANPpnwOwG5lyKUBnLTi9z02j7FLtsHQQVtvyC7nDnBcUtP NMiw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721315776; x=1721920576; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=urh2Mel08HMi6CTKyi9zAvhCgC0QJyHku4vSvSoCA8k=; b=ZlsDefjroyCU91oMM60meSNADXVY3lW65r/ynsit4KjBugNu9nGuCphd9CH1xG4dQN UOLgawABD2yhmZw9/+AtbAvzJX0KWOUSY3cDFYqzh3W0tU5SXaekC8Ya7IhBsl0h4Sf4 gtOakxpy0l5KqImbnhPXZcWiNjwxw40kRo77IabLYxVlUYEJfj+fyIKZ3GlD5iJv3txx ZEmNkBigy3hZF4uIzMbpdrY6MikxWymEvuerjXNfpswJeEznJN1Rp3lFdPg2DWPtTfO3 TBrokw8i99ODAgp1u3vMWJH/Ptq7xD4pQZhaK9cqWyGJazJoeUaCXFqRCRrLMkcwhmJd 0eLw== X-Gm-Message-State: AOJu0YwaPJ6MkeTMKTBrtLrnxvOQRAv0P6WJHlxN0SZ/Uqy0oSxX+0Ym WmrvPU3TazSx8qixefz7qNk4fryi2n5Bx8CLr4JoBLGVJco8t5AS8lYEnd8Ygn2PTdP5HddEmf+ Jy/KezZMgXYklRXKWFMsIEHnWSewkHA== X-Google-Smtp-Source: AGHT+IFxbN1UVHat0gZ6eDsPvjDDEE3SnW7RUgm1PJtgq46WcDVvMwG5Uvn1+/DdSgBh7oHMy6rAoajXku+O32427ms= X-Received: by 2002:a05:6871:611:b0:260:3c60:4e2e with SMTP id 586e51a60fabf-260d9157675mr4257024fac.4.1721315776467; Thu, 18 Jul 2024 08:16:16 -0700 (PDT) MIME-Version: 1.0 References: <38b20a9f-ef8a-486a-bb7d-f7a7be20f98d@talentstack.to>

In-Reply-To: From: Ron Johnson Date: Thu, 18 Jul 2024 11:16:05 -0400 Message-ID: Subject: Re: filesystem full during vacuum - space recovery issues To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000671c53061d870f15" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000671c53061d870f15 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Thomas Simpson = 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 - le= t > 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 sever= al > hundred TB of space to hold the output and there is no practical way to g= et > it, especially for a transient reload. > > I have my original server plus my replica; as the replica also applied th= e > 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 thi= s > 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 fillin= g > 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 coul= d > 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 wi= th > 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=E2=80=AFAM Thomas Simpson 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=3D24". > > 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=3D24": > declare -i CheckPoint=3D30 > declare -i SharedBuffs=3D32 > declare -i MaintMem=3D3 > declare -i MaxWalSize=3D36 > declare -i WalBuffs=3D64 > pg_ctl restart -wt$TimeOut -mfast \ > -o "-c hba_file=3D$PGDATA/pg_hba_maintmode.conf" \ > -o "-c fsync=3Doff" \ > -o "-c log_statement=3Dnone" \ > -o "-c log_temp_files=3D100kB" \ > -o "-c log_checkpoints=3Don" \ > -o "-c log_min_duration_statement=3D120000" \ > -o "-c shared_buffers=3D${SharedBuffs}GB" \ > -o "-c maintenance_work_mem=3D${MaintMem}GB" \ > -o "-c synchronous_commit=3Doff" \ > -o "-c archive_mode=3Doff" \ > -o "-c full_page_writes=3Doff" \ > -o "-c checkpoint_timeout=3D${CheckPoint}min" \ > -o "-c max_wal_size=3D${MaxWalSize}GB" \ > -o "-c wal_level=3Dminimal" \ > -o "-c max_wal_senders=3D0" \ > -o "-c wal_buffers=3D${WalBuffs}MB" \ > -o "-c autovacuum=3Doff" > > After the pg_restore -Fd --jobs=3D24 and vacuumdb --analyze-only --jobs= =3D24: > 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 sinc= e >> this DB has been through multiple upgrades (without reloads) until now s= o 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 >> >> --000000000000671c53061d870f15 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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

Single-threading will ALWA= YS=C2=A0be 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=E2=80=AFAM Thomas Simpson <ts@talentstack.to> wrote:

=20 =20 =20
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).=C2=A0 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.=C2=A0 I've basi= cally 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.=C2=A0 But this feels like PostgreSQL expert developer territory.

Thanks

Tom

On 17-Jul-2024 09:49, Ron Johnson wrote:

=20

On Wed, Jul 17, 2024 at 9:26=E2=80=AFAM Thomas Sim= pson <ts@tal= entstack.to> wrote:

---8<--snip,snip---8<---
That would, of course, depend on what you're currently doing.=C2=A0 pg_dumpall of a Big Database is certainly suboptimal compared to "pg_dump -Fd --jobs=3D24&qu= ot;.

This is what I run (which I got mostly from a databasesoup.com blog post) on the target instance=C2=A0before doing "pg_re= store -Fd --jobs=3D24":

declare -i CheckPoint=3D30
declare -i SharedBuffs=3D32
declare -i MaintMem=3D3
declare -i MaxWalSize=3D36
declare -i WalBuffs=3D64

pg_ctl restart -wt$TimeOut -mfast \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba_file=3D$PGDATA/pg= _hba_maintmode.conf" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c fsync=3Doff" \ =C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_statement=3Dnone&= quot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_temp_files=3D100k= B" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_checkpoints=3Don&= quot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log_min_duration_stat= ement=3D120000" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c shared_buffers=3D${Sh= aredBuffs}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c maintenance_work_mem= =3D${MaintMem}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c synchronous_commit=3D= off" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c archive_mode=3Doff&qu= ot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c full_page_writes=3Dof= f" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c checkpoint_timeout=3D= ${CheckPoint}min" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D${MaxW= alSize}GB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_level=3Dminimal&q= uot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_senders=3D0&q= uot; \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c wal_buffers=3D${WalBu= ffs}MB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c autovacuum=3Doff"= ;=C2=A0

After the pg_restore -Fd --jobs=3D24 and vacuumdb --analyze-only --jobs=3D24:

pg_ctl stop -wt$TimeOut && pg_ctl start -wt$TimeOut

Of course, these parameter values were for my=C2=A0ha= rdware.
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.=C2=A0 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

--000000000000671c53061d870f15-- From paul@pscs.co.uk Thu Jul 18 15:19:31 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUT0J-003EtM-VB for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 15:25:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUT0H-000M1x-3L for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 15:25:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUT0G-000M1p-HE for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 15:25:09 +0000 Received: from mail2.pscs.co.uk ([178.159.10.131] helo=mail.pscs.co.uk) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUT0D-000DAF-Du for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 15:25:07 +0000 Authentication-Results: mail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=pscs Received: from lmail.pscs.co.uk ([192.168.120.1]) by mail.pscs.co.uk ([192.168.120.185] running VPOP3) with ESMTPSA (TLSv1.3 TLS_AES_256_GCM_SHA384) for ; Thu, 18 Jul 2024 16:25:01 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pscs.co.uk; q=dns/txt; s=lmail; h=Message-ID:Date:MIME-Version:Subject:To:References:From:In-Reply-To :Content-Type:Content-Transfer-Encoding:Cc:Reply-to:Sender; t=1721315973; x=1721920773; bh=4CdlVBiJFC03H/TqLyHUKHTSnO8FDOMSXshiaBzW0lA=; b=pOO95PVCgBVEJPtiH3Jzyl/nvbsUPfu8KPylLG/TJCmthhz9wMSaMdJuwRwLiPDPw9885NT2 JO7NIB0YJti/kHTgI/kg1PB19kWdBBkkebqfS8EnMpkjqCXKbXb3kZ7V+phmA3xChn98z3GYyr XH9NVVEUsVW8gjNPEDryyaogo= Authentication-Results: lmail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=paul Received: from [192.168.57.71] ([217.155.111.120] (217-155-111-120.dsl.in-addr.zen.co.uk)) by lmail.pscs.co.uk ([192.168.120.70] running VPOP3) with ESMTPSA (TLSv1.3 TLS_AES_256_GCM_SHA384) for ; Thu, 18 Jul 2024 16:19:32 +0100 Message-ID: Date: Thu, 18 Jul 2024 16:19:31 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: filesystem full during vacuum - space recovery issues Content-Language: en-GB To: pgsql-admin@lists.postgresql.org References: From: Paul Smith* In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Authenticated-Sender: paul X-Server: VPOP3 Enterprise V8.5 - Registered X-Organisation: Paul Smith Computer Services X-VPOP3Tester: 12 345 X-Authenticated-Sender: pscs List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 From ts@talentstack.to Thu Jul 18 18:59:35 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUWNY-003eUB-5z for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 19:01:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUWNW-0032Jc-AI for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 19:01:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUWNV-0032IP-I9 for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 19:01:22 +0000 Received: from mail.network-systems-solutions.net ([162.250.175.178]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUWNR-000EwZ-Eb for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 19:01:19 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail.network-systems-solutions.net (Postfix) with ESMTP id A569D8143D for ; Thu, 18 Jul 2024 19:39:03 +0000 (UTC) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 2F4EC8E05A0 for ; Thu, 18 Jul 2024 15:01:10 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1721329269-3614-3610/6/43; Thu, 18 Jul 2024 19:01:09 +0000 Content-Type: multipart/alternative; boundary=------------TJOGvoUyt3WXIwHhKt1yhzD7 Message-Id: Date: Thu, 18 Jul 2024 14:59:35 -0400 Mime-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: filesystem full during vacuum - space recovery issues To: pgsql-admin@lists.postgresql.org References:

Content-Language: en-CA From: Thomas Simpson In-Reply-To: X-NSSLTD-Archiving: Added to store 1 as 91335 X-Scanned-By: MIMEDefang 2.84 on 127.0.1.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------TJOGvoUyt3WXIwHhKt1yhzD7 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable 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=20 >> space back to where it should be? >> >> I tried some scripts to map the data files to relations but this=20 >> didn't work as removing some files led to startup failure despite=20 >> them appearing to be unrelated to anything in the database - I had to=20 >> 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=20 > disk so the server crashes is that there are loads of data files=20 > containing the partially rebuilt table. Nothing 'internal' to=20 > PostgreSQL will point to those files as the internal pointers all=20 > change to the new table in an ACID way, so you should be able to=20 > delete them. > > You can usually find these relatively easily by looking in the=20 > relevant tablespace directory for the base filename for a new huge=20 > table (lots and lots of files with the same base name - eg looking for=20 > files called *.1000 will find you base filenames for relations over=20 > about 1TB) and checking to see if pg_filenode_relation() can't turn=20 > the filenode into a relation. If that's the case that they're not=20 > currently in use for a relation, then you should be able to just=20 > delete all those files > > Is this what you tried, or did your 'script to map data files to=20 > relations' do something else? You were a bit ambiguous about that part=20 > 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=20 (integer) and then comparing a directory listing for files which did not=20 match the relfilenode as candidates to remove. I moved these elsewhere (i.e. not delete, just move out the way so I=20 could move them back in case of trouble). Without these apparently unrelated files, the database did not start and=20 complained about them being missing, so I had to put them back.=C2=A0 = This=20 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.=C2=A0 However, I = doubt=20 I am the only person in the world that needs to dump and reload a large=20 database.=C2=A0 My thought is this is a weak point for PostgreSQL so it = makes=20 sense to consider ways to improve the dump reload process, especially as=20 it's the last-resort upgrade path recommended in the upgrade guide and=20 the general fail-safe route to get out of trouble. Thanks Tom > Paul > > > --------------TJOGvoUyt3WXIwHhKt1yhzD7 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

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.=C2=A0 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.=C2=A0 = However, I doubt I am the only person in the world that needs to dump and reload a large database.=C2=A0 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

--------------TJOGvoUyt3WXIwHhKt1yhzD7-- From ronljohnsonjr@gmail.com Thu Jul 18 20:32:49 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUXoN-003sEF-Mk for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 20:33:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUXoL-003v1m-3A for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 20:33:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUXoK-003v1e-Oh for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 20:33:09 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sUXoE-000GJK-Kj for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 20:33:08 +0000 Received: by mail-oi1-x230.google.com with SMTP id 5614622812f47-3d96365dc34so711200b6e.2 for ; Thu, 18 Jul 2024 13:33:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721334780; x=1721939580; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=7lKHw1bGPs1fT7q4AaAN23APA+SbNjntdNfBakEzxkQ=; b=ckHjSfCAaeRHR7YtmB7IZp1IDk7LAnAiN5QB/DSnJ5t+pH0zKIBu+5JEp8lwQSPmIK M2cS73PnnUTNuufRF/jSwvh/D/qGS+qSyMgbnMLDvJJ8wY53SoZSJNjBR2FR8hFT10KP vcvRgvisTUMEfmMLJgVc2LhmxDOaV1SvCklN1uRbDX1kOEJMjXuvTgC4MSoi41jbXXOO p7UnCL0Mh6pycPTMUJgPUZ1qCVE/7+N1MYy0E5nMzzai7o3UyUo9PaqVg+hP/DDXxlQc DLu0slgvYS1k44H7qAD2m2sGd2CCuQpqoC96r2EzHSqabwRuPGcR2QN0ad28AvsPh7Iv JBdw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721334780; x=1721939580; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=7lKHw1bGPs1fT7q4AaAN23APA+SbNjntdNfBakEzxkQ=; b=pOhAwzT/lNOCcxJRS/ZEaKC9ecbLrEHO8MZBesggBn+ElqZs0qyl5zmgYNOi5MISer Ge8B/l45uVZ3EbOAnj47hPzzacWRp6ip8mJQpmtye5Q8TvCLqhiFRsdfKSUyBgTHur87 aj3jOojlVINrsar4HuGjF6VPwaid1fozEpRoQRMDF0xpno/KL/xfLvH3CmhzvKk+fgCI juscZ8rLmn9jopqmLtkQl3XGGqOrT0oFEiW23/Q0YU3ZGjKs6rMP1z5IhcXqhIsa/cgU IxLnbCcVXGlR3jjEOnSyuS71ie4cx9xCYNAeuce0r5yjUTx1xjJCl56hHCyoiKkPD3KV lTdQ== X-Gm-Message-State: AOJu0YzxxqCVQzx0q+gM54yG8kAC96HsAOyZLUihWRI5cysGl75PCi/k VMB/wNx0iNBBeYJKf08a13PDRfrPQSH1/LwM4QjrGVAQpT+RuTJYSIz/MyzYipDHfWwN9JlP9YR VuS6pHe2q+GhnyFw4eNgnz8w0PrpzJA== X-Google-Smtp-Source: AGHT+IHxD6+NgkrE9uWnJed9A/MMEvS0DzVNOOqlQE+fBU9BNuPXRWI9lMxqpA3AnrhDFnxDF8oLF9nvseOKJq5Hjzg= X-Received: by 2002:a05:6871:14e:b0:261:360:746c with SMTP id 586e51a60fabf-2610360778cmr1191179fac.19.1721334780238; Thu, 18 Jul 2024 13:33:00 -0700 (PDT) MIME-Version: 1.0 References:

In-Reply-To: From: Ron Johnson Date: Thu, 18 Jul 2024 16:32:49 -0400 Message-ID: Subject: Re: filesystem full during vacuum - space recovery issues To: Thomas Simpson Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001da352061d8b7c5a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001da352061d8b7c5a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Simpson = 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 th= e > general fail-safe route to get out of trouble. > No database does fast single-threaded backups. --0000000000001da352061d8b7c5a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM T= homas Simpson <ts@talentstack.to> wrote:

[snip]

[BTW, v9.6 which I know is old= but this server is stuck there]

[snip]=C2=A0

I know I'm stuck with the slow rebu= ild at this point.=C2=A0 However, I doubt I am the only person in the world that needs to dump and reload a large database.=C2=A0 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.

=C2=A0No database d= oes fast single-threaded backups.

--0000000000001da352061d8b7c5a-- From ts@talentstack.to Thu Jul 18 20:53:23 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUY9h-003uaH-Bf for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 20:55:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUY9f-004EJb-HW for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 20:55:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUY9f-004EG4-4h for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 20:55:11 +0000 Received: from mail.network-systems-solutions.net ([162.250.175.178]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUY9X-000GTf-7X for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 20:55:10 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail.network-systems-solutions.net (Postfix) with ESMTP id 340EF8143D for ; Thu, 18 Jul 2024 21:32:50 +0000 (UTC) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 89D228E05A0 for ; Thu, 18 Jul 2024 16:54:56 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1721336095-3615-3610/6/39; Thu, 18 Jul 2024 20:54:55 +0000 Content-Type: multipart/alternative; boundary=------------RVbB3CWM0f6FwWwPrSLKqHBd Message-Id: <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to> Date: Thu, 18 Jul 2024 16:53:23 -0400 Mime-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: filesystem full during vacuum - space recovery issues To: pgsql-admin@lists.postgresql.org References:

Content-Language: en-CA From: Thomas Simpson In-Reply-To: X-NSSLTD-Archiving: Added to store 1 as 91336 X-Scanned-By: MIMEDefang 2.84 on 127.0.1.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------RVbB3CWM0f6FwWwPrSLKqHBd Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable On 18-Jul-2024 16:32, Ron Johnson wrote: > On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Simpson 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.=C2=A0 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. > > =C2=A0No database does fast single-threaded backups. Agreed.=C2=A0 My thought is that is should be possible for a 'new = dumpall' to=20 be multi-threaded. Something like : * Set number of threads on 'source' (perhaps by querying a listening=20 destination for how many threads it is prepared to accept via a control=20 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=20 thread is available)=C2=A0 ('Stage 1') * Rendezvous stage 1 completion (pause sending, wait until feedback from=20 destination confirming all completed) so we have a known consistent=20 state that is safe to proceed to subsequent tables * Work through tables that do refer to the previously sent in the same=20 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=20 necessary) The current dumpall is essentially doing this table organization=20 currently [minus stage checkpoints/multi-thread] otherwise the dump/load=20 would not work.=C2=A0 It may even be doing a lot of this for 'directory'=20 mode?=C2=A0 The change here is organizing n threads to process them=20 concurrently where possible and coordinating the pipes so they only send=20 data which can be accepted. The destination would need to have a multi-thread listen and co-ordinate=20 with the sender on some control channel so feed back completion of each=20 stage. Something like a destination host and control channel port to establish=20 the pipes and create additional netcat pipes on incremental ports above=20 the control port for each thread used. Dumpall seems like it could be a reasonable start point since it is=20 already doing the complicated bits of serializing the dump data so it=20 can be consistently loaded. Probably not really an admin question at this point, more a feature=20 enhancement. Is there anything fundamentally wrong that someone with more intimate=20 knowledge of dumpall could point out? Thanks Tom --------------RVbB3CWM0f6FwWwPrSLKqHBd Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

On 18-Jul-2024 16:32, Ron Johnson wrote:

On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas = Simpson <ts@talentstack.to> wrote:

[snip]

[BTW, v9.6 which I know is old but this server is stuck there]

[snip]=C2=A0

I know I'm stuck with the slow rebuild at this point.=C2= =A0 However, I doubt I am the only person in the world that needs to dump and reload a large database.=C2=A0 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.

=C2=A0No database does fast single-threaded backups.

Agreed.=C2=A0 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)=C2=A0 ('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.=C2=A0 It may even be doing a lot of this = for 'directory' mode?=C2=A0 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

--------------RVbB3CWM0f6FwWwPrSLKqHBd-- From ronljohnsonjr@gmail.com Thu Jul 18 22:41:14 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUZpd-004BjF-RW for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 22:42:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUZob-005KTf-Va for pgsql-admin@arkaria.postgresql.org; Thu, 18 Jul 2024 22:41:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUZob-005KTX-Bn for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 22:41:33 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sUZoU-000GdL-9O for pgsql-admin@lists.postgresql.org; Thu, 18 Jul 2024 22:41:32 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-250ca14422aso704042fac.0 for ; Thu, 18 Jul 2024 15:41:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721342485; x=1721947285; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=vY6DJR5qFA7c7LZInx+OBLvp6F+sUjznQMfKK1f4c3c=; b=e/HtRj/U/MapKEZKaUuYaMvoe+TRBVBrOdHdUQsCAV075mhyaH5p6vvPz4vW5GB7sy 05TZBg9b1I+i+OJxY2PrQcCUVeD2usX4a85ou2emrWykt8g80w7pdNl/3gpFrqC0Nqh8 vfNnG0j6ouquIAF5xfcVP0P48tq2BLQGhRjTvBSw6tr7n7IKLja3k2zfpVDJ+GumDMVn GMCEWLIoxlL5ebRJjjGlr5YHbYCcDNaHbOEZ9lApZ/SVUFP7S+GMTqB1rXcJr+IHZJqC WkUfhul21I7eWDny4yAXyU5FciXeP72boXwKAaM5UeVEwdklf8GB3cBI2veRnFMu05v2 zYsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721342485; x=1721947285; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=vY6DJR5qFA7c7LZInx+OBLvp6F+sUjznQMfKK1f4c3c=; b=Gq+eyOo4zxcbpFIGHQwlDMPZCKD1UuZ3iU0FaujQKP6+6pOCaos1BhfPg3q88mgoza cfi6PtuMAZ5uwdEENktZHnvIsjuUALM3rF9R8uRV6KExu7FRYr5u6Sq/PWM1sbjQGvBo q1OuO7+VyYoPfZvbDiyvLbiCWz/aSgnr7Ih5DglimttSnDIYUt6rjeZpia0A2NzunFP9 HwkQKhQxpyp/rN6F0UaAh5FI0xy5AKwHwJiLqdLWGE27e8uKDpsj//Tjjjs9ylfthLvo V1LIhy0p3mnLqjQE/0csbF0GGae7RiEGTq4WDbpHBvp6hxOwqh3L7RcP+b4lOamrMqYx BT0A== X-Gm-Message-State: AOJu0YwkENbprAwfGbuj2XfnnV2+kklSjfGMVlke5QhYFJ/Iq2vIfDu/ /mrW+xm1yj7egvKF5+DHIZ7G85CEgcx6k/F+a7m/jNRbiYOUQfLX2jzSO9wvbvQ7o8lK7zebClV zaJnm8VjLGoqD5vGH4xqMOrY1oBzk/g== X-Google-Smtp-Source: AGHT+IGAM+ltGC82G5rZClRbAL8W/xXG6MOlhhzXVgI6OBSJgiWTE0z6JgCPOx5Td0f1E82+CssaJdGW4MEfcD7dSDA= X-Received: by 2002:a05:6870:2191:b0:261:9fc:16b9 with SMTP id 586e51a60fabf-26109fc18e7mr553076fac.33.1721342485310; Thu, 18 Jul 2024 15:41:25 -0700 (PDT) MIME-Version: 1.0 References:

<7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to> In-Reply-To: <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to> From: Ron Johnson Date: Thu, 18 Jul 2024 18:41:14 -0400 Message-ID: Subject: Re: filesystem full during vacuum - space recovery issues To: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000005fb395061d8d471a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005fb395061d8d471a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Thomas Simpson = wrote: > > On 18-Jul-2024 16:32, Ron Johnson wrote: > > On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Simpson 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 t= he >> 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 wa= y > (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 currentl= y > [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 chan= ge > 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 t= he > 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 > > > --0000000000005fb395061d8d471a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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 ano= ther disk for backups.

On Thu, Jul 18, 2024 at 4:55=E2=80=AFPM Thomas Simpso= n <ts@talentstack.to> wrote:=

=20 =20 =20

On 18-Jul-2024 16:32, Ron Johnson wrote:

=20

On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Sim= pson <ts@tal= entstack.to> wrote:

[snip]

[BTW, v9.6 which I know is old but this server is stuck there]

[snip]=C2=A0

I know I'm stuck with the slow rebuild at this point.= =C2=A0 However, I doubt I am the only person in the world that needs to dump and reload a large database.=C2=A0 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.

=C2=A0No database does fast single-threaded backups.

Agreed.=C2=A0 My thought is that is should be possible for a 'ne= w 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)=C2=A0 ('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.=C2=A0 It may even be doing a lot of this fo= r 'directory' mode?=C2=A0 The change here is organizing n threa= ds 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

--0000000000005fb395061d8d471a-- From scott_ribe@elevated-dev.com Fri Jul 19 02:59:23 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUdqS-004bCD-1s for pgsql-admin@arkaria.postgresql.org; Fri, 19 Jul 2024 02:59:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUdqQ-007sph-3T for pgsql-admin@arkaria.postgresql.org; Fri, 19 Jul 2024 02:59:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUdqP-007spV-Lv for pgsql-admin@lists.postgresql.org; Fri, 19 Jul 2024 02:59:42 +0000 Received: from mailout.easymail.ca ([64.68.200.34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUdqM-000JDU-RB for pgsql-admin@lists.postgresql.org; Fri, 19 Jul 2024 02:59:41 +0000 Received: from localhost (localhost [127.0.0.1]) by mailout.easymail.ca (Postfix) with ESMTP id 04BAC6179E; Fri, 19 Jul 2024 02:59:35 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=elevated-dev.com; s=easymail; t=1721357975; bh=98o9dniBi4NFJ4CN38BwW0PQ57EOJ/+Ms0tLIahG5yo=; h=Subject:From:In-Reply-To:Date:Cc:References:To:From; b=rz/i9bMkycr47g7kpN9AvSyyQtu/gaOMK6Hz02STE4vawVDvKOvWjm7LZIyT0eVw2 UCVFD4FNayO+PQZButi0W9gbqzRJKSHcrynLvhCFvYyCjgcgUO4v7q2hopg0rCVTOH R7xWVI7JQf0cWRlDb8aKJeeM1W+6cwOQFbSVQanPXMN59Dqi/4YENn9II0ADqJTOgb GoxlniJlNCU+UcvaoWixoyi5EqXKWxxHa60x/Mc4O5iwwmW+GaHadXQq3hxj6aT0Yn eUGQvPJGrDwaTfIhNLhyO8+I4ty1TgFcVGO1UAmOIi0r2LFwVVG8WpmaXts1fK/E3K doeXlP5BhemXA== X-Virus-Scanned: Debian amavisd-new at emo07-pco.easydns.vpn Received: from mailout.easymail.ca ([127.0.0.1]) by localhost (emo07-pco.easydns.vpn [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id henVmQ2nW9Kl; Fri, 19 Jul 2024 02:59:34 +0000 (UTC) Received: from smtpclient.apple (unknown [165.140.184.195]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mailout.easymail.ca (Postfix) with ESMTPSA id 6B37561945; Fri, 19 Jul 2024 02:59:34 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=elevated-dev.com; s=easymail; t=1721357974; bh=98o9dniBi4NFJ4CN38BwW0PQ57EOJ/+Ms0tLIahG5yo=; h=Subject:From:In-Reply-To:Date:Cc:References:To:From; b=Zzo92k7NL5KpATO1TVnYOFlwCUQZKZ9UKCINfB1ug2YuF10Qv0KCyLeC8+JaeshvJ TWZJYwgxtpcrHeMiitfimCK19SLJb+stFvq48m+/Ta8mXN90d3/9urSyYQa2W9+gPU iWsL1ABsvjWxyGbExLnBWnLvQNrv25VTJPC1/HZVoWvD4RJQTGEh6phyPaBISEpLZq Ki0ODk5qUXEArhyxOPohP22vGZeLDfwcyOKxudRPxGVUATMc50BoOUc9do+Ju/9n5I izb1R1x2lZaZkzJLSiVBxiIveuix4Tp6Z6nyXe1x9g0njdZ2e8OABdY5fi840bUdm1 Z/ghEcZj8HMCg== Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 $3774.600.62$) Subject: Re: filesystem full during vacuum - space recovery issues From: Scott Ribe In-Reply-To: Date: Thu, 18 Jul 2024 20:59:23 -0600 Cc: Pgsql-admin Content-Transfer-Encoding: quoted-printable Message-Id: References:

<7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to> To: Thomas Simpson X-Mailer: Apple Mail (2.3774.600.62) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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=E2=80=AFPM, Ron Johnson = wrote: >=20 > 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. >=20 > Just temporarily add another disk for backups. >=20 > On Thu, Jul 18, 2024 at 4:55=E2=80=AFPM Thomas Simpson = wrote: >=20 > On 18-Jul-2024 16:32, Ron Johnson wrote: >> On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Simpson = wrote: >> [snip] >> [BTW, v9.6 which I know is old but this server is stuck there] >> [snip]=20 >> 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 >=20