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

[Laurenz Albe]

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

Re: Create replication slot options

[Christophe Pettus]

> On Mar 24, 2025, at 17:31, Phillip Diffley  wrote:
> 
> I am testing out some streaming logical replication commands and am having 
> trouble specifying options when calling CREATE_REPLICATION_SLOT.
> 
> I connect to the database with
> psql "dbname=replication_test_db replication=database"
> 
> Then I am able to successfully run
> CREATE_REPLICATION_SLOT test_slot LOGICAL pgoutput;
> 
> But if I try to specify the snapshot option with
> CREATE_REPLICATION_SLOT test_slot_2 LOGICAL pgoutput SNAPSHOT 'nothing';
> the command fails with "ERROR:  syntax error"
> 
> I have tried several combinations of brackets commas and equals signs but 
> have not been able to get the command with additional options to work. What 
> is the right syntax to use here?

The options after LOGICAL  need to be in parentheses:

CREATE_REPLICATION_SLOT test_slot_2 LOGICAL pgoutput ( SNAPSHOT 
'nothing' );

In the PostgreSQL documentation, parentheses mean literal parentheses rather 
than being part of the grammar.

Today Page is not accessible - postgresql-15.spec

[Cars Jeeva]

Hi Team,

I am trying to access the postgresql-15.spec to build the RPM for my
project but it is not accessible from morning.

*https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=7a8b87ec6db538ca115de4fb962709c7a7cebe11;hb=HEAD
*

Could you please check on this?

Thanks,
Mano Chandar

Re: Today Page is not accessible - postgresql-15.spec

[Adrian Klaver]

On 3/24/25 07:18, Cars Jeeva wrote:

Hi Team,

I am trying to access the postgresql-15.spec to build the RPM for my 
project but it is not accessible from morning.


/_*https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=7a8b87ec6db538ca115de4fb962709c7a7cebe11;hb=HEAD
 
*_/
/_
_/
Could you please check on this?


That seems to a general problem, I cannot access any file from:

projects/postgresql.git/tree

It would help to also bring this up on:

https://www.postgresql.org/list/pgsql-www/



Thanks,
Mano Chandar
_/
/_
_/
/_
_/
/_


--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Dimitrios Apostolou]

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

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

[Ron Johnson]

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  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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

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

[Dimitrios Apostolou]

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

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

[Dimitrios Apostolou]

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

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

[Dimitrios Apostolou]

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

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

[Laurenz Albe]

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

Re: size of attributes table is too big

[Siraj G]

Thank you!

I noticed over 99% free space. Now the challenge is running FULL VACUUM on
a table with size over 500GB. It is going to take a couple of hours I
presume.

Also, I hope aggressive vacuuming will prevent us from this situation.

Regards
Siraj




On Wed, Mar 19, 2025 at 11:27 PM Ron Johnson 
wrote:

> On Wed, Mar 19, 2025 at 1:06 PM Siraj G  wrote:
>
>> Hello!
>>
>> I have a PG (v16) instance which is occupying around 1TB of storage. Out
>> of this, around 350GB is occupied by the table pg_catalog.pg_attribute.
>> Why is the catalog table's size so big?
>>
>> Here are the sizes:
>>
>> pg_attribute
>> 338 GB
>> pg_attribute_relid_attnam_index
>> 117 GB
>> pg_attribute_relid_attnum_index
>> 69 GB
>>
>> I think this table must have tons of dead tuples. Please suggest to me if
>> we can purge any data/shrink the size of this table.
>>
>>
> Run pgstattuple and pgstatindex on them.  They'll tell you how much bloat
> you have.
>
> And tune your autovacuum parameters to be more aggressive.  These, for
> example, are my settings:
> autovacuum_analyze_scale_factor = 0.015
> autovacuum_vacuum_scale_factor = 0.015
> autovacuum_vacuum_insert_scale_factor = 0.015
> autovacuum_vacuum_insert_threshold = 250
>
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
>  lobster!
>

Re: Today Page is not accessible - postgresql-15.spec

[Álvaro Herrera]

On 2025-Mar-24, Adrian Klaver wrote:

> On 3/24/25 07:18, Cars Jeeva wrote:
> > Hi Team,
> > 
> > I am trying to access the postgresql-15.spec to build the RPM for my
> > project but it is not accessible from morning.
> > 
> > /_*https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=7a8b87ec6db538ca115de4fb962709c7a7cebe11;hb=HEAD
> >  
> > *_/
> > /_
> > _/
> > Could you please check on this?
> 
> That seems to a general problem, I cannot access any file from:
> 
> projects/postgresql.git/tree

Should be fixed as of a few hours ago, per
https://postgr.es/m/9ada1715-8208-4212-b352-d9ad2a1de...@thebuild.com

> It would help to also bring this up on:
> 
> https://www.postgresql.org/list/pgsql-www/

Yeah.

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/
"I'm always right, but sometimes I'm more right than other times."
  (Linus Torvalds)
https://lore.kernel.org/git/pine.lnx.4.58.0504150753440.7...@ppc970.osdl.org/

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

[Laurenz Albe]

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

Re: Bloated toast table with empty associated table

[Adrian Klaver]

On 3/20/25 04:39, Paul Allen wrote:

Hello.





Problem.

My backend application attempts unsuccessfully repeatedly to insert the
same ~100 rows with images, and despite table's row count remains 0,
toast table's size is growing up permanently, reaching 100, 200, 300 GB
until it takes all available space.


You are going to need to show the complete process you are using as well 
as the error messages you get in the Postgres log.





Thanks. Daniil Rozanov




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Determine server version from psql script

[David G. Johnston]

On Sunday, March 23, 2025, Igor Korot  wrote:

>
> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
> version?
>

No.  You have to drop the trigger if it does exist and then create the new
one.

David J.