From: Guillaume Lelarge <guillaume.lelarge@dalibo.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Question about pg_dump + pg_restore + pg_toast
Date: Mon, 24 Feb 2025 10:29:44 +0100
Message-ID: <559a5bad-3ba9-406d-af78-c28429037245@dalibo.com> (raw)
In-Reply-To: <CA+wokJ8s940oNGb7ACo4z5mffsuVfEHB2pMNMQdbWtdUrRRYdw@mail.gmail.com>
References: <CA+wokJ8s940oNGb7ACo4z5mffsuVfEHB2pMNMQdbWtdUrRRYdw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: guillaume.lelarge@dalibo.com, pgsql-admin@lists.postgresql.org
Subject: Re: Question about pg_dump + pg_restore + pg_toast
In-Reply-To: <559a5bad-3ba9-406d-af78-c28429037245@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox