Date: Thu, 20 Mar 2025 23:48:11 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Experience and feedback on  pg_restore --data-only
Message-ID: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:oT5J4/+B2Bo=;WR58yGYWc+qW3FsaMo/Y5IpjXOv
 V96+WBi1o+sBAAZwAHPqcMNtSRyW4f67ci2Eru0uwixlwFfaFuT3JcRNT/6YBc2nvwBG1JxwU
 nXDrGcXvnZ5uYkPPvI8Ddh5bj0qgAlV8E+G2rjQ8V7plIX1AlPDegruconCMeNhg/YxP9S8Fw
 5YfGiRhL65DJyBqj7pvXRbT2w0izJ+0oC6OINWjFl9P9eX2grMYSGAhPxlg06N18qQne6GWmu
 c9hHsuF80LHhwUHtTJ5BHZVxcNHhfIgBokj+HAVPdh0jL5q3SkaNuxPuxu7fIlV+eFVkj7JDZ
 JW8z2OCw8rhr82687cqdItjyNpkGnY803hUlV0AYbAGwfC0e/ya9JbsuMPnUnWjfd39A5PRDi
 vBX9QNFFkYqjh5oIMReihWqObaDriWSRdbIGO6Wp0NutTKQhgB2Z6Ww01SO7yDRIe2wTjdA4c
 eamWsuYjhioarPW0zIxnRZbaRtV2/M4cUDaZLGyYdlcVJSS+o7pCNNkimetjhHGFRA+dGzS+r
 ubBfc63bK8hVU9toB9wpmqpXVRnh/NnlkrOn0kAyN6Sm8HSy5ML1p6Rz3WtJv87yKwsKFPdWQ
 CzaYg7WIuWxuteNWG4P3O8b25/lYaTdiw7nKNE5YE35jR+Slq2HzVRW8oMnWDELF1phPExhgi
 ZaaIOncYMaB9oXlEunhmIwMg/fXC55NihUTy9MZ5aovB/cUyyqTUjcsaVhbS3CHW1dwnzKHct
 uTxsH/1EgjAjKzHVQYYRUV0YcFP7XQBMvbgVojQ5USJCdUdvGdybSFK2iNEPuQALX759vWGCr
 hFMGpc5AXrOsgNzI90loJasqBIW1p9eK5rHiy462zPsTOLd4WbpebgpWG3Re0ICb+XMWHTRIz
 tdUvGAGlHky7uWlekLxYZmlui/mFwQbj5z0zKaThWQ8PSU6ASwR3+fRFkyh2fQq6AsYFCKA53
 HDpWVLs/lUeEpl6UMb9TIMWfi0AyPmfh4OoeaKkjxuV8xsVs20ZUcKTe1R5R5TGGLmAfYHeZA
 Dn5d4W6JkJ2TJ3skspFT9tpqL5UL4XCV3v7IgdjlQ9zNAaFFHmSnAXYFxMkWDq7pe+AL0XDy8
 xu1Rz4RpCdyFTdMTSPh0nYpz+D/F8i3P5ml0uDJV/OgtW4G0aoHv9NFfzVNP3ITSisT3L9gIi
 VBiZPin/4nAdrHbwV9B+B3sqdD+0eUoz89VACjgGmuyyWcSU6v0fw2Qg6tTF1wgdgTXQCt2Hx
 3mGKqLZ8AQ17PSctdj+gJu6RDh5nM5J9LKFOVFqVUQnX2pS2nc7t6TX+Z9RTiLBFiXl1c8Kht
 /wlCHoaKtpKwrhQEmnCA179L84xX3dHqhgshMZgVHUfX8M4l6X4fr7L0v6VH0Qvccyb/xPe7r
 EuvD9+YgYomTVmoQvZByee8HOz+z8DIjIWwvbRsiZ4hSKXIyAUvPlg0YgSds5Zo3ytfzfsmx6
 P51r+qQ==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e54%40gmx.net>
Precedence: bulk

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 in
those cases it makes often sense to get rid of the churn and create a
clean 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.

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.  Things
   would work if pg_restore did "SET ROLE" instead of "SET SESSION
   AUTHORIZATION" to switch user. Is this a straightforward change or ther=
e
   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 if
   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 having
   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 this
   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 speed
   up the process quite a bit.


Any feedback for improving my process? Should I put these ideas somewhere
as ideas for improvement on pg_restore?

Thank you in advance,
Dimitris