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!