Thread

1. Experience and feedback on pg_restore --data-only

   Dimitrios Apostolou <jimis@gmx.net> — 2025-03-20T22:48:11Z

   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?
   
   * 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.
   
   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
   
   
   
   
   

2. Re: Experience and feedback on pg_restore --data-only

   Dimitrios Apostolou <jimis@gmx.net> — 2025-03-21T18:36:00Z

   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 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.
   
   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.  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?
   >
   > * 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.
   >
   > 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
   >
   >
   
   
   
   

3. Re: Experience and feedback on pg_restore --data-only

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-03-21T19:00:57Z

   On Fri, Mar 21, 2025 at 2:36 PM Dimitrios Apostolou <jimis@gmx.net> wrote:
   
   > 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.
   >
   
   How often do you have emergencies requiring database restore????  In my
   seven years managing PG systems, I've had TWO.
   
   
   > > AFAICT pg_restore (without --data-only) is optimised for such case.
   >
    [snip]
   
   > > Any feedback for improving my process?
   
   
   Yes: don't use a logical backup tool like pg_dump to backup production
   databases.
   
   PgBackRest (the tool I have experience with; there are others, though) has
   mandatory features like PITR, incremental and differential backups, delta
   restores and encryption.  Use that instead.
   
   To get rid of the cruft in your database, go through the schema and
   manually drop unused tables.
   
   -- 
   Death to <Redacted>, and butter sauce.
   Don't boil me, I'm still alive.
   <Redacted> lobster!
   

4. Re: Experience and feedback on pg_restore --data-only

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-23T15:37:11Z

   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
   adrian.klaver@aklaver.com
   
   
   
   
   

5. Re: Experience and feedback on pg_restore --data-only

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-23T16:00:39Z

   On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
   > 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.
   
   You'd have to create an load the table in the same transaction, that is,
   you'd have to run pg_restore with --single-transaction.
   
   > Any feedback for improving my process? Should I put these ideas somewhere
   > as ideas for improvement on pg_restore?
   
   You put your ideas in the right place.
   
   Yours,
   Laurenz Albe
   
   
   
   

6. Re: Experience and feedback on pg_restore --data-only

   Dimitrios Apostolou <jimis@gmx.net> — 2025-03-24T14:24:17Z

   On Sun, 23 Mar 2025, Laurenz Albe wrote:
   
   > On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
   >> 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.
   >
   > You'd have to create an load the table in the same transaction, that is,
   > you'd have to run pg_restore with --single-transaction.
   
   That would restore the schema from the dump, while I want to create the
   schema from the SQL code in version control.
   
   Something that might work, would be for pg_restore to issue a TRUNCATE
   before the COPY. I believe this would require superuser privelege though,
   that I would prefer to avoid. Currently I issue TRUNCATE for all tables
   manually before running pg_restore, but of course this is in a different
   transaction so it doesn't help.
   
   By the way do you see potential problems with using --single-transaction
   to restore billion-rows tables?
   
   
   Thank you,
   Dimitris
   

7. Re: Experience and feedback on pg_restore --data-only

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-24T15:31:49Z

   On 3/24/25 07:24, Dimitrios Apostolou wrote:
   > On Sun, 23 Mar 2025, Laurenz Albe wrote:
   > 
   >> On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
   >>> 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.
   >>
   >> You'd have to create an load the table in the same transaction, that is,
   >> you'd have to run pg_restore with --single-transaction.
   > 
   > That would restore the schema from the dump, while I want to create the
   > schema from the SQL code in version control.
   
   
   I am not following, from your original post:
   
   "
     ... 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.
   "
   
   You are running the process in two steps, where the first does not 
   involve pg_restore. Not sure why doing the pg_restore --data-only 
   portion in single transaction is not possible?
   
   > 
   > Something that might work, would be for pg_restore to issue a TRUNCATE
   > before the COPY. I believe this would require superuser privelege though,
   > that I would prefer to avoid. Currently I issue TRUNCATE for all tables
   > manually before running pg_restore, but of course this is in a different
   > transaction so it doesn't help.
   > 
   > By the way do you see potential problems with using --single-transaction
   > to restore billion-rows tables?
   
   COPY is all or none(version 17+ caveat(see 
   https://www.postgresql.org/docs/current/sql-copy.html  ON_ERROR)), so if 
   the data dump fails in --single-transaction everything rolls back.
   
   > 
   > 
   > Thank you,
   > Dimitris
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

8. Re: Experience and feedback on pg_restore --data-only

   shammat@gmx.net — 2025-03-24T15:51:28Z

   Dimitrios Apostolou schrieb am 20.03.2025 um 23:48:
   > 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?
   
   I think that can be avoided by using --no-owner and --no-privileges
   
   
   
   
   
   
   

9. Re: Experience and feedback on pg_restore --data-only

   Dimitrios Apostolou <jimis@gmx.net> — 2025-03-24T15:51:30Z

   On Mon, 24 Mar 2025, Adrian Klaver wrote:
   
   > On 3/24/25 07:24, Dimitrios Apostolou wrote:
   >>  On Sun, 23 Mar 2025, Laurenz Albe wrote:
   >>
   >>>  On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
   >>>>  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.
   >>>
   >>>  You'd have to create an load the table in the same transaction, that is,
   >>>  you'd have to run pg_restore with --single-transaction.
   >>
   >>  That would restore the schema from the dump, while I want to create the
   >>  schema from the SQL code in version control.
   >
   >
   > I am not following, from your original post:
   >
   > "
   > ... 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.
   > "
   >
   > You are running the process in two steps, where the first does not involve
   > pg_restore. Not sure why doing the pg_restore --data-only portion in single
   > transaction is not possible?
   
   Laurenz informed me that I could avoid writing to the WAL if I "create and
   load the table in a single transaction".
   I haven't tried, but here is what I would do to try --single-transaction:
   
   Transaction 1: manually issuing all of CREATE TABLE etc.
   
   Transaction 2: pg_restore --single-transaction --data-only
   
   The COPY command in transaction 2 would still need to write to WAL, since
   it's separate from the CREATE TABLE.
   
   Am I wrong somewhere?
   
   >>  Something that might work, would be for pg_restore to issue a TRUNCATE
   >>  before the COPY. I believe this would require superuser privelege though,
   >>  that I would prefer to avoid. Currently I issue TRUNCATE for all tables
   >>  manually before running pg_restore, but of course this is in a different
   >>  transaction so it doesn't help.
   >>
   >>  By the way do you see potential problems with using --single-transaction
   >>  to restore billion-rows tables?
   >
   > COPY is all or none(version 17+ caveat(see
   > https://www.postgresql.org/docs/current/sql-copy.html  ON_ERROR)), so if the
   > data dump fails in --single-transaction everything rolls back.
   
   So if I restore all tables, then an error about a "table not found" would
   not roll back already copied tables, since it's not part of a COPY?
   
   
   Thank you for the feedback,
   Dimitris
   
   

10. Re: Experience and feedback on pg_restore --data-only

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-03-24T16:00:15Z

    Why are you regularly having emergencies requiring the restoration of
    multi-TB tables to databases with lots of cruft?
    
    Fixing that would go a long way towards eliminating your problems with
    pg_restore.
    
    On Mon, Mar 24, 2025 at 11:51 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
    
    > On Mon, 24 Mar 2025, Adrian Klaver wrote:
    >
    > > On 3/24/25 07:24, Dimitrios Apostolou wrote:
    > >>  On Sun, 23 Mar 2025, Laurenz Albe wrote:
    > >>
    > >>>  On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
    > >>>>  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.
    > >>>
    > >>>  You'd have to create an load the table in the same transaction, that
    > is,
    > >>>  you'd have to run pg_restore with --single-transaction.
    > >>
    > >>  That would restore the schema from the dump, while I want to create the
    > >>  schema from the SQL code in version control.
    > >
    > >
    > > I am not following, from your original post:
    > >
    > > "
    > > ... 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.
    > > "
    > >
    > > You are running the process in two steps, where the first does not
    > involve
    > > pg_restore. Not sure why doing the pg_restore --data-only portion in
    > single
    > > transaction is not possible?
    >
    > Laurenz informed me that I could avoid writing to the WAL if I "create and
    > load the table in a single transaction".
    > I haven't tried, but here is what I would do to try --single-transaction:
    >
    > Transaction 1: manually issuing all of CREATE TABLE etc.
    >
    > Transaction 2: pg_restore --single-transaction --data-only
    >
    > The COPY command in transaction 2 would still need to write to WAL, since
    > it's separate from the CREATE TABLE.
    >
    > Am I wrong somewhere?
    >
    > >>  Something that might work, would be for pg_restore to issue a TRUNCATE
    > >>  before the COPY. I believe this would require superuser privelege
    > though,
    > >>  that I would prefer to avoid. Currently I issue TRUNCATE for all tables
    > >>  manually before running pg_restore, but of course this is in a
    > different
    > >>  transaction so it doesn't help.
    > >>
    > >>  By the way do you see potential problems with using
    > --single-transaction
    > >>  to restore billion-rows tables?
    > >
    > > COPY is all or none(version 17+ caveat(see
    > > https://www.postgresql.org/docs/current/sql-copy.html  ON_ERROR)), so
    > if the
    > > data dump fails in --single-transaction everything rolls back.
    >
    > So if I restore all tables, then an error about a "table not found" would
    > not roll back already copied tables, since it's not part of a COPY?
    >
    >
    > Thank you for the feedback,
    > Dimitris
    >
    >
    
    -- 
    Death to <Redacted>, and butter sauce.
    Don't boil me, I'm still alive.
    <Redacted> lobster!
    

11. Re: Experience and feedback on pg_restore --data-only

    Dimitrios Apostolou <jimis@gmx.net> — 2025-03-24T16:05:52Z

    On Sun, 23 Mar 2025, Adrian Klaver wrote:
    
    > On 3/20/25 15:48, Dimitrios Apostolou wrote:
    >>
    >>  * 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?
    
    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
    

12. Re: Experience and feedback on pg_restore --data-only

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-24T16:09:19Z

    On 3/24/25 08:51, Dimitrios Apostolou wrote:
    > On Mon, 24 Mar 2025, Adrian Klaver wrote:
    > 
    >> On 3/24/25 07:24, Dimitrios Apostolou wrote:
    >>>  On Sun, 23 Mar 2025, Laurenz Albe wrote:
    >>>
    >>>>  On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
    >>>>>  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.
    >>>>
    >>>>  You'd have to create an load the table in the same transaction, 
    >>>> that is,
    >>>>  you'd have to run pg_restore with --single-transaction.
    >>>
    >>>  That would restore the schema from the dump, while I want to create the
    >>>  schema from the SQL code in version control.
    >>
    >>
    >> I am not following, from your original post:
    >>
    >> "
    >> ... 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.
    >> "
    >>
    >> You are running the process in two steps, where the first does not 
    >> involve
    >> pg_restore. Not sure why doing the pg_restore --data-only portion in 
    >> single
    >> transaction is not possible?
    > 
    > Laurenz informed me that I could avoid writing to the WAL if I "create and
    > load the table in a single transaction".
    
     From here:
    
    https://www.postgresql.org/message-id/455d28421ae33c73b73a6f527d2f72816ca5dd29.camel%40cybertec.at
    
    What he said was:
    
    "You'd have to create an load the table in the same transaction, that 
    is, you'd have to run pg_restore with --single-transaction."
    
    Where I assume he meant '... create and load ...'. That is not the same 
    as what you are doing below.
    
    > I haven't tried, but here is what I would do to try --single-transaction:
    > 
    > Transaction 1: manually issuing all of CREATE TABLE etc.
    > 
    > Transaction 2: pg_restore --single-transaction --data-only
    > 
    > The COPY command in transaction 2 would still need to write to WAL, since
    > it's separate from the CREATE TABLE.
    > 
    > Am I wrong somewhere?
    > 
    
    >> COPY is all or none(version 17+ caveat(see
    >> https://www.postgresql.org/docs/current/sql-copy.html  ON_ERROR)), so 
    >> if the
    >> data dump fails in --single-transaction everything rolls back.
    > 
    > So if I restore all tables, then an error about a "table not found" would
    > not roll back already copied tables, since it's not part of a COPY?
    
    If you are following what you show above then the tables and other 
    objects would be created manually from the version control outside of 
    pg_restore and on successful completion and commit of that transaction 
    they would persist until such time as you change them. The second step 
    pg_restore --single-transaction --data-only is where you could 99% of 
    the way through and have a failure that rolls back all the data entered 
    in the tables.
    
    > 
    > 
    > Thank you for the feedback,
    > Dimitris
    > 
    
    -- 
    Adrian Klaver
    adrian.klaver@aklaver.com
    
    
    
    
    

13. Re: Experience and feedback on pg_restore --data-only

    Dimitrios Apostolou <jimis@gmx.net> — 2025-03-24T16:15:01Z

    Hi Ron,
    
    I read your reply in the mailing list archives as I'm not subscribed to
    the list, and I'm copy-pasting a response here. Please include me as a
    recipient in further replies.
    
    > Why are you regularly having emergencies requiring the restoration of
    > multi-TB tables to databases with lots of cruft?
    >
    > Fixing that would go a long way towards eliminating your problems with
    > pg_restore.
    
    I don't have emergencies yet. I'm testing the process of restoring the
    database dump, and it takes more than 24 hours currently. A successful
    test is vital to approve the process.
    
    But the primary usage of pg_restore that I have is not to save me from
    emergencies but to populate the dev database with recent data.
    
    
    Regards,
    Dimitris
    
    
    
    
    

14. Re: Experience and feedback on pg_restore --data-only

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-24T18:39:17Z

    On Mon, 2025-03-24 at 15:24 +0100, Dimitrios Apostolou wrote:
    > By the way do you see potential problems with using --single-transaction
    > to restore billion-rows tables?
    
    No.
    
    Yours,
    Laurenz Albe
    
    
    
    

15. Re: Experience and feedback on pg_restore --data-only

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-24T18:41:35Z

    On Mon, 2025-03-24 at 16:51 +0100, Dimitrios Apostolou wrote:
    > Laurenz informed me that I could avoid writing to the WAL if I "create and
    > load the table in a single transaction".
    > I haven't tried, but here is what I would do to try --single-transaction:
    > 
    > Transaction 1: manually issuing all of CREATE TABLE etc.
    > 
    > Transaction 2: pg_restore --single-transaction --data-only
    > 
    > The COPY command in transaction 2 would still need to write to WAL, since
    > it's separate from the CREATE TABLE.
    > 
    > Am I wrong somewhere?
    
    No, that is correct.
    
    Yours,
    Laurenz Albe
    
    
    
    

16. Re: Experience and feedback on pg_restore --data-only

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-25T15:27:13Z

    On 3/24/25 09:15, Dimitrios Apostolou wrote:
    > Hi Ron,
    > 
    > I read your reply in the mailing list archives as I'm not subscribed to
    > the list, and I'm copy-pasting a response here. Please include me as a
    > recipient in further replies.
    > 
    >> Why are you regularly having emergencies requiring the restoration of
    >> multi-TB tables to databases with lots of cruft?
    >>
    >> Fixing that would go a long way towards eliminating your problems with
    >> pg_restore.
    > 
    > I don't have emergencies yet. I'm testing the process of restoring the
    > database dump, and it takes more than 24 hours currently. A successful
    > test is vital to approve the process.
    
    It is doubtful that pg_dump/pg_restore will meet the requirements. You 
    are probably looking at some process that does incremental updates and 
    then restores from that. Something like pgbackrest:
    
    https://pgbackrest.org/
    
    comes to mind.
    
    > 
    > But the primary usage of pg_restore that I have is not to save me from
    > emergencies but to populate the dev database with recent data.
    > 
    > 
    > Regards,
    > Dimitris
    > 
    
    -- 
    Adrian Klaver
    adrian.klaver@aklaver.com
    
    
    
    
    

17. Re: Experience and feedback on pg_restore --data-only

    Dimitrios Apostolou <jimis@gmx.net> — 2025-04-02T17:42:01Z

    Hi Laurenz,
    
    On Sun, 23 Mar 2025, Laurenz Albe wrote:
    
    > On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
    >> 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.
    >
    > You'd have to create an load the table in the same transaction, that is,
    > you'd have to run pg_restore with --single-transaction.
    
    Do you know why --single-transaction can't be combined with -j ?
    I'm thinking it's because every process would need to have its own
    transaction, which apparently results to more than one transaction.
    
    Do you think this could be alleviated somehow with subtransactions?
    
    I'm trying to think of a way to enjoy the performance benefits of
    --single-transaction, while also parallelizing the pg_restore. For this
    use case I don't care about the content appearing all in one transaction.
    Would it be possible then to have dependency resolution in such a way that
    we split the tasks into one transaction per worker process?
    
    Just thinking out loud here, I'd be interested in feedback.
    
    Thanks,
    Dimitris