Subject: Re: Experience and feedback on pg_restore --data-only

From: Adrian Klaver <[email-masked]>
Date: Sun, 23 Mar 2025 08:37:11 -0700

On 3/20/25 15:48, 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 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 there
>    are issues I don't see?

If this is --data-only what are the ALTER OWNER and SET SESSION 
AUTHORIZATION for?

> 
> * 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=username option used today for disabling the triggers.

That is what --clean is for, though it needs to have the objects(tables) 
be in the restore e.g. not just --data-only.

> 
> 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=minimal. 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
> 
> 
> 

-- 
Adrian Klaver
[email-masked]

---

From: Dimitrios Apostolou <[email-masked]>
Date: Mon, 24 Mar 2025 17:05:52 +0100 (CET)

On Sun, 23 Mar 2025, Adrian Klaver wrote:

>>     AUTHORIZATION" to switch user. Is this a straightforward change or
>>     there are issues I don't see?
>
> If this is --data-only what are the ALTER OWNER and SET SESSION AUTHORIZATION
> for?

You are probably right, early in my trials I was running pg_restore
without --data-only as a non-superuser so it might be that the error
message comes from there. Haven't noted the exact command for this error
message unfortunately.

The point still stands though. The dbowner user is administrator for this
database, and has CREATEd the users with the right to SET ROLE as any of
them. Those other users own tables in their private schemas. But
pg_restore does SET SESSION AUTHORIZATION which requires superuser priv
instead of SET ROLE. I wonder what the reasons are for that.

Maybe pg_restore could either:

- do SET ROLE instead of SET SESSION AUTHORIZATION
- temporarily use the --superuser powers just for issuing the ALTER ROLE.

Regards,
Dimitris