Thread: Question about pg_dump + pg_restore + pg_toast
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.
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
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 v16to 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.
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.
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!
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!
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
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!
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!
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
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]
-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!