agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedQuestion about pg_dump + pg_restore + pg_toast
11+ messages / 5 participants
[nested] [flat]
* Question about pg_dump + pg_restore + pg_toast
@ 2025-02-24 09:19 Edwin UY <edwin.uy@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Edwin UY @ 2025-02-24 09:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
Checking one of the databases, we have the schema below with size as below.
I just rename it as abc, this is the one we want to dump and restore.
schema_name | schema_size
--------------------+-------------
public | 656 kB
pg_catalog | 6608 kB
pg_toast | 412 GB
information_schema | 104 kB
abc | 187 GB
We are upgrading this database from v12 to v16 and we want to do a dump of
it in case we decided to do a restore from v16 to v12.
SO, after the upgrade, in case we want to rollback to v12, we will do a
pg_dump using v16 and then using v12 pg_restore
When we do the dump, does it take into consideration the size of pg_toast
as well? So it will be 412+187-GB :( ?
BTW, was pg_toast created automatically by PostgreSQL? Reading on
https://www.postgresql.org/docs/current/storage-toast.html, there is no
mention of it.
Any feedback is much appreciated. Thanks in advance.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-02-24 09:29 Guillaume Lelarge <guillaume.lelarge@dalibo.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2025-02-24 09:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
On 24/02/2025 10:19, Edwin UY wrote:
> Hi,
>
> Checking one of the databases, we have the schema below with size as
> below. I just rename it as abc, this is the one we want to dump and restore.
>
> schema_name | schema_size
> --------------------+-------------
> public | 656 kB
> pg_catalog | 6608 kB
> pg_toast | 412 GB
> information_schema | 104 kB
> abc | 187 GB
>
> We are upgrading this database from v12 to v16 and we want to do a dump
> of it in case we decided to do a restore from v16 to v12.
> SO, after the upgrade, in case we want to rollback to v12, we will do a
> pg_dump using v16 and then using v12 pg_restore
> When we do the dump, does it take into consideration the size of
> pg_toast as well? So it will be 412+187-GB :( ?
>
Yes. They are part of the tables, so, sure, they will be dumped (at
least for those in the "abc" schema).
> BTW, was pg_toast created automatically by PostgreSQL? Reading on
> https://www.postgresql.org/docs/current/storage-toast.html <https://
> www.postgresql.org/docs/current/storage-toast.html>, there is no mention
> of it.
>
Yes, it is automatically added when a user creates a database.
Regards.
--
Guillaume Lelarge
Consultant
https://dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-02-24 11:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2025-02-24 11:35 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; pgsql-admin@lists.postgresql.org
On Mon, 2025-02-24 at 22:19 +1300, Edwin UY wrote:
> We are upgrading this database from v12 to v16 and we want to do a dump of it in case we decided to do a restore from v16 to v12.
> SO, after the upgrade, in case we want to rollback to v12, we will do a pg_dump using v16 and then using v12 pg_restore
Be warned that downgrade is not supported and restoring the dump to v12 might well fail.
You might have to manually edit the dump file until you can restore it without errors.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-02-24 21:30 Edwin UY <edwin.uy@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Edwin UY @ 2025-02-24 21:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Yikes, I thought it is alright so long as I use the latest version of
pg_dump and restore.
Thanks for the warning.
On Tue, Feb 25, 2025 at 12:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Mon, 2025-02-24 at 22:19 +1300, Edwin UY wrote:
> > We are upgrading this database from v12 to v16 and we want to do a dump
> of it in case we decided to do a restore from v16 to v12.
> > SO, after the upgrade, in case we want to rollback to v12, we will do a
> pg_dump using v16 and then using v12 pg_restore
>
> Be warned that downgrade is not supported and restoring the dump to v12
> might well fail.
> You might have to manually edit the dump file until you can restore it
> without errors.
>
> Yours,
> Laurenz Albe
>
> --
>
> *E-Mail Disclaimer*
> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
> oder
> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>
> *CONFIDENTIALITY NOTICE & DISCLAIMER
> *This message and any attachment are
> confidential and may be privileged or otherwise protected from disclosure
> and solely for the use of the person(s) or entity to whom it is intended.
> If you have received this message in error and are not the intended
> recipient, please notify the sender immediately and delete this message
> and
> any attachment from your system. If you are not the intended recipient, be
> advised that any use of this message is prohibited and may be unlawful,
> and
> you must not copy this message or attachment or disclose the contents to
> any other person.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-02-24 23:05 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2025-02-24 23:05 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Run the PG12 pg_dump just before the pg_upgrade.
Forward compatibility in maintenance utilities is (usually) guaranteed.
Backwards compatibility (which is what you want)? Not so much.
On Mon, Feb 24, 2025 at 4:31 PM Edwin UY <edwin.uy@gmail.com> wrote:
> Yikes, I thought it is alright so long as I use the latest version of
> pg_dump and restore.
> Thanks for the warning.
>
> On Tue, Feb 25, 2025 at 12:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
>> On Mon, 2025-02-24 at 22:19 +1300, Edwin UY wrote:
>> > We are upgrading this database from v12 to v16 and we want to do a dump
>> of it in case we decided to do a restore from v16 to v12.
>> > SO, after the upgrade, in case we want to rollback to v12, we will do a
>> pg_dump using v16 and then using v12 pg_restore
>>
>> Be warned that downgrade is not supported and restoring the dump to v12
>> might well fail.
>> You might have to manually edit the dump file until you can restore it
>> without errors.
>>
>> Yours,
>> Laurenz Albe
>>
>> --
>>
>> *E-Mail Disclaimer*
>> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
>> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
>> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
>> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
>> oder
>> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie,
>> sich
>> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>>
>> *CONFIDENTIALITY NOTICE & DISCLAIMER
>> *This message and any attachment are
>> confidential and may be privileged or otherwise protected from disclosure
>> and solely for the use of the person(s) or entity to whom it is intended.
>> If you have received this message in error and are not the intended
>> recipient, please notify the sender immediately and delete this message
>> and
>> any attachment from your system. If you are not the intended recipient,
>> be
>> advised that any use of this message is prohibited and may be unlawful,
>> and
>> you must not copy this message or attachment or disclose the contents to
>> any other person.
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-03-01 20:53 Edwin UY <edwin.uy@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Edwin UY @ 2025-03-01 20:53 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
If need be, do I manually edit the dumpfile using something like vi?
On Tue, Feb 25, 2025 at 12:06 PM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:
> Run the PG12 pg_dump just before the pg_upgrade.
>
> Forward compatibility in maintenance utilities is (usually) guaranteed.
> Backwards compatibility (which is what you want)? Not so much.
>
> On Mon, Feb 24, 2025 at 4:31 PM Edwin UY <edwin.uy@gmail.com> wrote:
>
>> Yikes, I thought it is alright so long as I use the latest version of
>> pg_dump and restore.
>> Thanks for the warning.
>>
>> On Tue, Feb 25, 2025 at 12:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
>> wrote:
>>
>>> On Mon, 2025-02-24 at 22:19 +1300, Edwin UY wrote:
>>> > We are upgrading this database from v12 to v16 and we want to do a
>>> dump of it in case we decided to do a restore from v16 to v12.
>>> > SO, after the upgrade, in case we want to rollback to v12, we will do
>>> a pg_dump using v16 and then using v12 pg_restore
>>>
>>> Be warned that downgrade is not supported and restoring the dump to v12
>>> might well fail.
>>> You might have to manually edit the dump file until you can restore it
>>> without errors.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>> --
>>>
>>> *E-Mail Disclaimer*
>>> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
>>> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene
>>> Adressat
>>> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
>>> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
>>> oder
>>> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie,
>>> sich
>>> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>>>
>>> *CONFIDENTIALITY NOTICE & DISCLAIMER
>>> *This message and any attachment are
>>> confidential and may be privileged or otherwise protected from
>>> disclosure
>>> and solely for the use of the person(s) or entity to whom it is
>>> intended.
>>> If you have received this message in error and are not the intended
>>> recipient, please notify the sender immediately and delete this message
>>> and
>>> any attachment from your system. If you are not the intended recipient,
>>> be
>>> advised that any use of this message is prohibited and may be unlawful,
>>> and
>>> you must not copy this message or attachment or disclose the contents to
>>> any other person.
>>>
>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-03-01 21:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2025-03-01 21:27 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Edwin UY <edwin.uy@gmail.com> writes:
> If need be, do I manually edit the dumpfile using something like vi?
That's usually the last-ditch answer. Often you can just feed the
script to psql and ignore any errors, though.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-03-02 00:40 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2025-03-02 00:40 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sat, Mar 1, 2025 at 4:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Edwin UY <edwin.uy@gmail.com> writes:
> > If need be, do I manually edit the dumpfile using something like vi?
>
> That's usually the last-ditch answer. Often you can just feed the
> script to psql and ignore any errors, though.
Single-threaded text is pretty slow for a 500GB database.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-03-02 00:44 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2025-03-02 00:44 UTC (permalink / raw)
To: pgsql-admin
1. Why would you need to edit the dump file?
2. It's not 2013 anymore: Use multiple threads when backing up!
On Sat, Mar 1, 2025 at 3:54 PM Edwin UY <edwin.uy@gmail.com> wrote:
> If need be, do I manually edit the dumpfile using something like vi?
>
> On Tue, Feb 25, 2025 at 12:06 PM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> Run the PG12 pg_dump just before the pg_upgrade.
>>
>> Forward compatibility in maintenance utilities is (usually) guaranteed.
>> Backwards compatibility (which is what you want)? Not so much.
>>
>> On Mon, Feb 24, 2025 at 4:31 PM Edwin UY <edwin.uy@gmail.com> wrote:
>>
>>> Yikes, I thought it is alright so long as I use the latest version of
>>> pg_dump and restore.
>>> Thanks for the warning.
>>>
>>> On Tue, Feb 25, 2025 at 12:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
>>> wrote:
>>>
>>>> On Mon, 2025-02-24 at 22:19 +1300, Edwin UY wrote:
>>>> > We are upgrading this database from v12 to v16 and we want to do a
>>>> dump of it in case we decided to do a restore from v16 to v12.
>>>> > SO, after the upgrade, in case we want to rollback to v12, we will do
>>>> a pg_dump using v16 and then using v12 pg_restore
>>>>
>>>> Be warned that downgrade is not supported and restoring the dump to v12
>>>> might well fail.
>>>> You might have to manually edit the dump file until you can restore it
>>>> without errors.
>>>>
>>>> Yours,
>>>> Laurenz Albe
>>>>
>>>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-03-02 06:39 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Laurenz Albe @ 2025-03-02 06:39 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin
On Sat, 2025-03-01 at 19:44 -0500, Ron Johnson wrote:
> [warning and instructions for downgrading PostgreSQL]
> 1. Why would you need to edit the dump file?
Because it might not load into a server with a lower version.
> 2. It's not 2013 anymore: Use multiple threads when backing up!
You cannot do that with the "plain" format, since it writes to a
single file. And you cannot use the "directory" format that would
allow parallelization, because you cannot edit the DDL statements
with a binary format.
Just don't downgrade PostgreSQL. There is no good reason to do that.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about pg_dump + pg_restore + pg_toast
@ 2025-03-02 09:45 Edwin UY <edwin.uy@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Edwin UY @ 2025-03-02 09:45 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yeah tried to do the pg_dump, 8 hours down the line, not even halfway done.
As expected, it is stuck on the way with the largest toast.
pg_dump -v -d [database] -U [user] \
-h [hostname] \
--if-exists --clean -Fc \
-f [dumpfile] \
--quote-all-identifiers -n [schemaname]
Will try the -Fd and -j option.
On Sun, Mar 2, 2025 at 1:40 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Sat, Mar 1, 2025 at 4:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Edwin UY <edwin.uy@gmail.com> writes:
>> > If need be, do I manually edit the dumpfile using something like vi?
>>
>> That's usually the last-ditch answer. Often you can just feed the
>> script to psql and ignore any errors, though.
>
>
> Single-threaded text is pretty slow for a 500GB database.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-03-02 09:45 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-24 09:19 Question about pg_dump + pg_restore + pg_toast Edwin UY <edwin.uy@gmail.com>
2025-02-24 09:29 ` Guillaume Lelarge <guillaume.lelarge@dalibo.com>
2025-02-24 11:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-24 21:30 ` Edwin UY <edwin.uy@gmail.com>
2025-02-24 23:05 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-01 20:53 ` Edwin UY <edwin.uy@gmail.com>
2025-03-01 21:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-02 00:40 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-02 09:45 ` Edwin UY <edwin.uy@gmail.com>
2025-03-02 00:44 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-02 06:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox