Date: Fri, 21 Mar 2025 19:36:00 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: Experience and feedback on pg_restore --data-only
In-Reply-To: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net>
Message-ID: <756f3204-5cb9-5c7e-642c-bc5ec78db59d@gmx.net>
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:Iqx+PaOR8kg=;MCnW3iFKm3EMD2Kdfdmg/Txb1AH
 Vvf/CVRAjTRph3GoGFsDGoxKasFIeb9PTA6oL/gtL3yZIX/tv1dhflK0eEHgX/toBOS0LXtXj
 F3CdKfkzrbTQXkQx1ADv9fVlJY3BOKLD4pP2z1uM4rdwqCceTALlIKojIxLrY/GzFSp0ctseJ
 TbiebPA478T62X1nPOW+zAYiAxUt/HkyX/97jOfG14O8u2SO+6XlVXxeHlj0zsZsA9kE9ceQa
 FTZQ6sCDonrY81e7W1tvLBtOvW4Gp2suQF9er92tJASnIcI6b+be9udIMpIh3ncoQDD+juMS7
 gkouwywlKCTxLQfWQ5IO9FqMWR7vTe2TfxxISWPCmGZ/7F0tbzuTUMnMqqvKgYcWvU+eyGwiM
 3xecCa5h7pziMoIH0EHfpDz4E52xObU7Yq6ZfznJ7621LGbH67xdG5Yp0Yc2UB6U53EGvMM3P
 hV1KvOLLDyHQkNffoi9cb+s7cu1z5dCVRUpKmvNPOEU8HJvSRI7enonn2QXhcP1jHgoGsVk5l
 MUuDiYZ0T2q9WBz6GAglkCViW5cAuT7N+leNkq1ADHO9E/riO0YmP5ZUqmnWiYmf3BzScD5jx
 cfehEpSQsxr+YRDOrAqT+YM6HVolv5mlIjWv+ptxSJZKjQ87mQsFH/X88qSNeqDzRrlQXPONK
 tpBIZDWnqGWaKWrwE4vzeaGO62+ZLX85hRSsFDOGBe0UrelvEKTPeBxRFv5CNsYIk+muR9Hh0
 SeL5XgpONT2LQ7oN3rNDlUXwNXTBoZkSfmM2uPAvTMmFhwLrYK52N2ychw3VAbsKGXjxrIfFU
 Sh58kDVKKsmUMRwj6wnMarcKoyumfOypi8TR5m6Ja/fo/+1NB6bQpheaaLZ7PzJcWIsvBpbGQ
 d5Cn5XVqxuzH+hXw5FGNepZibxsiz+uMNwUOAenqB0nCSmNGraE1IV9gCQ3qKlHyQNOIS+aPR
 WKOXG45Xd9dbn7k5sCfx8XOG34KT3/hpyAjjz1BKpyydw8jRcHxOKSYMtTgTGLypL+Nu4BXLA
 CpIUD6ScKIe6GgUBHVsjXPrzOhbMpB5M645ABJ+obYYto9VW7dAItWMKzvJxYroC0XE6YONjN
 sbp2/gBza9t4PRsmMVscdYesjGaSDIyZ384Siq1puCkOO9EvhbYSYpDT6NCFlkM/3D9mwh6up
 TEQyCS3BBYX75LTp1XmOqyBVi0prNak5Mm25+zFGYap8L2ADjExMb7Lt43/b4YA0JZqKQCo4A
 iWgXejxZ3B8eKBkJKk5iHDu2MEaSyybKMD7FV/YkPDtYBgTo5Od1q8DHBX39Rcsr1K3JRHfbO
 w6QGVOH7Q/J4Dmwn61wVYkTYjmpRJAT0vbdj2C87H+3MymdGWFPqfRSysGDaUwWUjMHOVUFmw
 1GxgYBKu3hkWfCSbZrWRqtbACwKUhg1y0RKISuTGiStV7V3pHgeE2bg5OeITOZwO86B2eT0xX
 Ss6kjjQ==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/756f3204-5cb9-5c7e-642c-bc5ec78db59d%40gmx.net>
Precedence: bulk

On Thu, 20 Mar 2025, Dimitrios Apostolou wrote:

> Rationale:
>
> When restoring a backup in an emergency situation, it's fine to run
> pg_restore as superuser and get an exact replica of the dumped db.
> AFAICT pg_restore (without --data-only) is optimised for such case.
>
> But pg_dump/restore can be used as a generic data-copying utility, and i=
n
> those cases it makes often sense to get rid of the churn and create a cl=
ean
> database by running the SQL schema definition from version control, and =
then
> copy the data for only the tables created.
>
> For this case, I choose to run pg_restore --data-only, and run it as the=
 user
> who owns the database (dbowner), not as a superuser, in order to avoid
> changes being introduced under the radar.

Another important reason I use --data-only: tablespaces. The new host has
different storage configuration and tablespaces and the schema has been
updated to reflect those. Therefore I must create the database using the
updated schema definitions on the new host.

>
> Things that made my life hard:
>
> * plenty of permission denials for both ALTER OWNER or SET SESSION
>   AUTHORIZATION (depending on command line switches).  Both of these
>   require superuser privilege, but in my case this is not really needed.
>   Dbowner has CREATEROLE and is the one who creates all the roles (WITH
>   SET TRUE), and their private schemata in the specific database.  Thing=
s
>   would work if pg_restore did "SET ROLE" instead of "SET SESSION
>   AUTHORIZATION" to switch user. Is this a straightforward change or the=
re
>   are issues I don't see?
>
> * After each failed attempt, I need to issue a TRUNCATE table1,table2,..=
.
>   before I try again.  I wrote my own function for that. It would help i=
f
>   pg_restore would optionally truncate before COPY.  I believe it would
>   require superuser privilege for it, that could achieve using the
>   --superuser=3Dusername option used today for disabling the triggers.
>
> Performance issues: (important as my db size is >5TB)
>
> * WAL writes: I didn't manage to avoid writing to the WAL, despite havin=
g
>   setting wal_level=3Dminimal. I even wrote my own function to ALTER all
>   tables to UNLOGGED, but failed with "could not change table T to
>   unlogged because it references logged table".  I'm out of ideas on thi=
s
>   one.
>
> * Indices: Could pg_restore have a switch to DROP indices before each
>   COPY, and re-CREATE them after, exactly as they were?  This would spee=
d
>   up the process quite a bit.
>
>
> Any feedback for improving my process? Should I put these ideas somewher=
e as
> ideas for improvement on pg_restore?
>
> Thank you in advance,
> Dimitris
>
>