Re: PostgresSQL Setup error

[DINESH NAIR]

Hi,

There could be many reasons , sharing most common ones:


  1.
Typo  errors ie case sensitive
  2.
Password change errors
  3.
Incorrect username ,database name , port number
  4.
Configuration issues in hba file
  5.
Privilege issue
  6.
Server Not Restarted/Reloaded or full restart has not taken place for the server

Sharing the psql command for ready reference

psql -h  -p  -U  -d 



On 7/9/25 07:06, Jordan Adams wrote:
> Hi Team,
>
> Hope this email finds you well,
>
> Apologies as I am unaware if this is the correct mailing address.
>
> I would however like to inquire about an issue currently being
> experienced with Postgres with one of our external clients.
>
> When doing the setup and following the steps accordingly with regards to
> adding the details for PostgreSQL Unicode ODBC Driver and testing, I run
> into an error where it states the below.
>
> *note - I was provided with the username and password .
>
> "psql: FATAL: password authentication failed"

Was that the complete message?

Not something like:

psql: error: connection to server at "localhost" (127.0.0.1), port 5482
failed: FATAL:  password authentication failed for user "aklaver"


Also provide:

1) Postgres version.

2) Client version.

>
> Please advise if assistance can be provided?
>
>
> Kind Regards
>
> Jordan Adams
>
> IT Support
>
> iSquared Technologies (Pty) Ltd
>
> 021 671 5778





Thanks

Dinesh Nair



From: Jordan Adams 
Sent: Wednesday, July 9, 2025 7:36 PM
To: pgsql-gene...@postgresql.org 
Subject: PostgresSQL Setup error

You don't often get email from jor...@isquared.co.za. Learn why this is 
important
Caution: This email was sent from an external source. Please verify the 
sender’s identity before clicking links or opening attachments.
Hi Team,

Hope this email finds you well,

Apologies as I am unaware if this is the correct mailing address.

I would however like to inquire about an issue currently being experienced with 
Postgres with one of our external clients.

When doing the setup and following the steps accordingly with regards to adding 
the details for PostgreSQL Unicode ODBC Driver and testing, I run into an error 
where it states the below.

*note - I was provided with the username and password .

"psql: FATAL: password authentication failed"

Please advise if assistance can be provided?



Kind Regards

Jordan Adams



IT Support

iSquared Technologies (Pty) Ltd

021 671 5778

http://www.isquared.co.za

206 Main Road, Claremont, Cape Town 7708





Please send all support emails to 
supp...@isquared.co.za and not to an engineer 
directly as they cannot respond to these emails. Please send all procurement 
requests to procurem...@isquared.co.za 
Thanks.

Re: analyze-in-stages post upgrade questions

[Mircea Cadariu]

Hi Laurenz,


Got it. I have only one suggestion for the patch. Consider adding a 
corresponding test in src/bin/scripts/t/100_vacuumdb.pl.


Proposal (I used this to check the patch):

$node->safe_psql('postgres',
    "CREATE TABLE parent_table (a INT) PARTITION BY LIST (a);\n"
      . "CREATE TABLE child_table PARTITION OF parent_table FOR VALUES 
IN (1);\n"

      . "INSERT INTO parent_table VALUES (1);\n");
$node->issues_sql_like(
    [
        'vacuumdb', '--analyze-only', 'postgres'
    ],
    qr/statement:\s+ANALYZE\s+public\.parent_table/s,
    '--analyze_only updates statistics for partitioned tables');


Kind regards,

Mircea Cadariu

Re: Aggregate versions of hashing functions (md5, sha1, etc...)

[Adrian Klaver]

On 7/10/25 04:48, Dominique Devienne wrote:


Seems so logical to me, that these hashing functions were available
are aggregates, I can't be the first one to think of that, can it?



I've been on this list since late 2002 and I don't recall this ever
being brought up. Now it is entirely possible that age has dimmed my 
recall abilities:) Though a quick search seems to confirm my memory.



Thanks, --DD




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

Re: Aggregate versions of hashing functions (md5, sha1, etc...)

[Ron Johnson]

On Thu, Jul 10, 2025 at 12:26 PM Adrian Klaver 
wrote:

> On 7/10/25 04:48, Dominique Devienne wrote:
>
> > Seems so logical to me, that these hashing functions were available
> > are aggregates, I can't be the first one to think of that, can it?
> >
>
> I've been on this list since late 2002 and I don't recall this ever
> being brought up. Now it is entirely possible that age has dimmed my
> recall abilities:) Though a quick search seems to confirm my memory.
>

What even is an aggregate hash function?  (I can imagine a few
possibilities, but don't want to assume.)

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: error “server process was terminated by signal 11: Segmentation fault” running pg_create_logical_replication_slot using pgoutput plugin

[abrahim abrahao]

 Thanks  Hayato and Shlok, The Citus extension package is installed, but it is 
not preload on shared_preload_libraries and citus extesion is not created.I 
will create a new container without Citus extension package and adding stack 
trace ( I think this is the one you're talking about) as soon as possible and I 
will update here as soon I complete the test.
See information below.
show shared_preload_libraries;   
shared_preload_libraries--- pg_stat_statements, 
pg_repack(1 row)
\dx                                            List of installed extensions     
   Name        | Version |   Schema   |                              
Description+-++
 btree_gist         | 1.7     | public     | support for indexing common 
datatypes in GiST ltree              | 1.2     | public     | data type for 
hierarchical tree-like structures pg_stat_statements | 1.10    | public     | 
track planning and execution statistics of all SQL statements executed pg_trgm  
          | 1.5     | public     | text similarity measurement and index 
searching based on trigrams pgcrypto           | 1.3     | public     | 
cryptographic functions plpgsql            | 1.0     | pg_catalog | PL/pgSQL 
procedural language postgis            | 3.5.1   | public     | PostGIS 
geometry and geography spatial types and functions uuid-ossp          | 1.1     
| public     | generate universally unique identifiers (UUIDs)(8 rows)

Steps done until pg_create_logical_replication_slot command (just the steps, 
does not include the full command)
set wal_level, max_replication_slots, max_wal_senders and listen_addresses      
   name          | setting---+- listen_addresses    
  | * max_replication_slots | 10 max_wal_senders       | 20 wal_level           
  | logical
Changed pg_hba filerestart databasepg_ctl restart -D $POSTGRESQL_DATA
create a user "CREATE USER user_rep WITH REPLICATION ENCRYPTED PASSWORD"ALTER 
DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myg GRANT SELECT ON TABLES TO 
user_rep;CREATE PUBLICATION myg_pub FOR TABLES IN SCHEMA myg;ALTER PUBLICATION 
myg_pub ADD TABLE myg
SELECT snapshot_name FROM pg_create_logical_replication_slot

On Wednesday, July 9, 2025 at 10:19:07 p.m. EDT, Hayato Kuroda (Fujitsu) 
 wrote:  
 
 Dear Shlok, Abrahim,

> Also, I was going to the logs on found:
> > > < 2025-07-08 14:57:08.653 UTC psql postgres postgres 172.18.0.94(53414)
> SELECT 0 2025-07-08 14:57:07 UTC 1096 686d31c3.448 2025-07-08
> 14:57:08.653 UTC > LOG:  Initializing CDC decoder
> 
> This log is not present in Postgres source code. Why is this log appearing 
> here?

I found the output in Citus source code [1]. So, I'm afraid that you may load 
the
shared library provided by Citus when you created the replication slot.

If so, Citus community may be the better place to discuss the bug.
We can help if you can reproduce the bug by the PostgreSQL core codes.

[1]: 
https://github.com/citusdata/citus/blob/5deaf9a61673e10c183b6d4f13593f168e1c2c10/src/backend/distributed/cdc/cdc_decoder.c#L85

Best regards,
Hayato Kuroda
FUJITSU LIMITED

  

Aggregate versions of hashing functions (md5, sha1, etc...)

[Dominique Devienne]

We store scientific information in PostgreSQL, and some of that is
bytea and large, thus we must "chunk it" both for performance, and not
be limited to 1GB (we do exceed that, in rare occasions).

Recently I added md5/sha1 hashing support for such values (for various
reasons, to track corruptions in our ETLs, now fixed, but also in the
future for custom smart sync and such), and was surprised to discover
there are no aggregate versions of those functions, neither the
built-in md5 one (now with bytea overload), nor for pgcrypto. Did I
miss something?

Any chance this might be added in the future?

Seems so logical to me, that these hashing functions were available
are aggregates, I can't be the first one to think of that, can it?

Thanks, --DD

PS: The holly-grail IMHO, would be built-in support for hashing, with
intelligent lazy-compute and persistence correctly reset on changes.
Probably opt-in.

PPS: Built-in Support for xxh64, or at least in official pgcrypto,
would also be nice. With aggregate!

PPPS: I remember Oracle LOBs supporting a built-in implicit mime-type
attribute on them, which was used as the content-type with served over
the built-in WebDAV extension for the DB. I wish PostgreSQL had
something like that too.

S: the lo extension is not viable for us, FWIW. All the above
should be opt-in on bytea columns IMHO.

Re: Lock timeout in commit

[Tom Lane]

Steve Baldwin  writes:
> I'm occasionally seeing a lock timeout in a commit statement. For example:

> 2025-07-10 08:56:07.225 UTC,"b2bc_api","b2bcreditonline",23592,"
> 10.124.230.241:60648",686f8022.5c28,55,"COMMIT",2025-07-10 08:56:02
> UTC,3984/10729,676737574,ERROR,55P03,"canceling statement due to lock
> timeout",,"COMMIT",,,"098c62f7-bb76-4607-88da-120c6edee423","client
> backend",,-2835399305386018931

> Am I right in thinking this is likely due to a deferred foreign key
> constraint?

Plausible guess perhaps, although FKs don't normally need strong
locks.

> I couldn't see any obvious clue in the log. Is there somewhere
> else I can look?

I think all you could do is monitor the pg_locks view and hope to
catch the process in "waiting" state before it fails.

It occurs to me to wonder though if we couldn't provide more
context in the error about what lock is being waited for.

regards, tom lane

Lock timeout in commit

[Steve Baldwin]

Hi all,

I'm occasionally seeing a lock timeout in a commit statement. For example:

2025-07-10 08:56:07.225 UTC,"b2bc_api","b2bcreditonline",23592,"
10.124.230.241:60648",686f8022.5c28,55,"COMMIT",2025-07-10 08:56:02
UTC,3984/10729,676737574,ERROR,55P03,"canceling statement due to lock
timeout",,"COMMIT",,,"098c62f7-bb76-4607-88da-120c6edee423","client
backend",,-2835399305386018931

Am I right in thinking this is likely due to a deferred foreign key
constraint? I couldn't see any obvious clue in the log. Is there somewhere
else I can look?

In case it matters, the server is running version 17.4.

Thanks,

Steve

Re: having temp_tablespaces on less reliable storage

[David G. Johnston]

On Thu, Jul 10, 2025 at 10:58 AM Dimitrios Apostolou  wrote:

> Can't find any related documentation, but I expect loss of "temp" space is
> of minor importance.
>

You might want to try finding some old discussions about why putting temp
tablespace on a RAM-drive is not a supported configuration.

David J.

Re: Aggregate versions of hashing functions (md5, sha1, etc...)

[Merlin Moncure]

On Thu, Jul 10, 2025 at 5:48 AM Dominique Devienne 
wrote:

> We store scientific information in PostgreSQL, and some of that is
> bytea and large, thus we must "chunk it" both for performance, and not
> be limited to 1GB (we do exceed that, in rare occasions).
>


> Recently I added md5/sha1 hashing support for such values (for various
> reasons, to track corruptions in our ETLs, now fixed, but also in the
> future for custom smart sync and such), and was surprised to discover
> there are no aggregate versions of those functions, neither the
> built-in md5 one (now with bytea overload), nor for pgcrypto. Did I
> miss something?
>
> Any chance this might be added in the future?
>
> Seems so logical to me, that these hashing functions were available
> are aggregates, I can't be the first one to think of that, can it?
>
> Thanks, --DD
>
> PS: The holly-grail IMHO, would be built-in support for hashing, with
> intelligent lazy-compute and persistence correctly reset on changes.
> Probably opt-in.
>
> PPS: Built-in Support for xxh64, or at least in official pgcrypto,
> would also be nice. With aggregate!
>
> PPPS: I remember Oracle LOBs supporting a built-in implicit mime-type
> attribute on them, which was used as the content-type with served over
> the built-in WebDAV extension for the DB. I wish PostgreSQL had
> something like that too.
>
> S: the lo extension is not viable for us, FWIW. All the above
> should be opt-in on bytea columns IMHO.
>

Does it have to match the md5 of the 'unchunked' variant exactly? If not,
maybe you can rig a custom aggregate that would just "hash amend" the
chunks using the n-1 chunk has as salt, this would be fast and easy, at the
cost of not matching the unchunked variant.

I had to do something very similar with compression, I had a need to be
able to compress bytea values with lz4 at SQL level due to limited support
for extensions in the cloud.  it works great...if a bit slow, and requires
the database to handle the decompression.

merlin

optimizing number of workers

[Greg Hennessy]

Having just received a shiny new dual CPU machine to use as a postgresql
server, I'm trying to do some reasonable efforts to configure it correctly.
The hard
ware has 128 cores, and I am running a VM with Redhat 9 and Postgresql
16.9.

In postgresql.conf I have:
max_worker_processes = 90   # (change requires restart)
max_parallel_workers_per_gather = 72# gsh 26 oct 2022
max_parallel_maintenance_workers = 72   # gsh 12 jun 2025
max_parallel_workers =  72  # gsh 12 jun 2025
max_logical_replication_workers = 72# gsh 12 jun 2025
max_sync_workers_per_subscription = 72   # gsh 12 jun 2025
autovacuum_max_workers = 12 # max number of autovacuum
subprocesses

When I do a simple count of a large (large being 1.8 Billion entries), I get
about 10 workers used.

prod_v1_0_0_rc1=# explain (analyze, buffers) select count(*) from
gaiadr3.gaia_source;

 QUERY PLAN

 Finalize Aggregate  (cost=14379796.81..14379796.82 rows=1 width=8) (actual
time=16702.806..16705.479 rows=1 loops=1)
   Buffers: shared hit=2507481
   ->  Gather  (cost=14379795.78..14379796.79 rows=10 width=8) (actual
time=16702.513..16705.470 rows=11 loops=1)
 Workers Planned: 10
 Workers Launched: 10
 Buffers: shared hit=2507481
 ->  Partial Aggregate  (cost=14379785.78..14379785.79 rows=1
width=8) (actual time=16691.820..16691.821 rows=1 loops=11)
   Buffers: shared hit=2507481
   ->  Parallel Index Only Scan using gaia_source_nest128 on
gaia_source  (cost=0.58..13926632.85 rows=181261171 width=0) (actual
time=0.025..9559.644 rows=164700888 loops=11)
 Heap Fetches: 0
 Buffers: shared hit=2507481
 Planning:
   Buffers: shared hit=163
 Planning Time: 14.898 ms
 Execution Time: 16705.592 ms

Postgres has chosen to use only a small fraction of the CPU's I have on
my machine. Given the query returns an answer in about 8 seconds, it may be
that Postgresql has allocated the proper number of works. But if I wanted
to try to tweak some config parameters to see if using more workers
would give me an answer faster, I don't seem to see any obvious knobs
to turn. Are there parameters that I can adjust to see if I can increase
throughput? Would adjusting parallel_setup_cost or parallel_tuple_cost
likely to be of help?

Re: Lock timeout in commit

[Steve Baldwin]

On Fri, 11 Jul 2025 at 01:28, Tom Lane  wrote:

>
> I think all you could do is monitor the pg_locks view and hope to
> catch the process in "waiting" state before it fails.
>
> It occurs to me to wonder though if we couldn't provide more
> context in the error about what lock is being waited for.
>
> Thanks Tom !!

The application is an API server so we intentionally set the lock timeout
to a very short interval (5 ms). Having locking context would be great.

Other than deferred FK constraints, what other locking actions are deferred
to commit time?

Cheers,

Steve

having temp_tablespaces on less reliable storage

[Dimitrios Apostolou]

Hello list,

I have a database split across many tablespaces, with temp_tablespaces 
pointing to a separate, less reliable device (single local NVMe drive). 
How dangerous is it for the cluster to be unrecoverable after a crash?


If the drive goes down and the database can't read/write to 
temp_tablespaces, what will happen?


If I then configure temp_tablespaces to point to a working location, would 
that be enough to start the cluster? Or other bad things can happen?


Can't find any related documentation, but I expect loss of "temp" space is 
of minor importance.


Thanks in advance,
Dimitris

RE: error “server process was terminated by signal 11: Segmentation fault” running pg_create_logical_replication_slot using pgoutput plugin

[Hayato Kuroda (Fujitsu)]

Dear Abrahim

> The Citus extension package is installed, but it is not preload on 
> shared_preload_libraries
> and citus extesion is not created.

It is possible that a shared library is loaded even if shared_preload is not set
and CREATE EXTENSION is not executed. Per my understanding the specified plugin
name would be searched by the same rule as other libraries. See [1].

Another example is 'test_decoding'. It is a sample plugin which postgres-core
includes. Anyone can use the plugin via SQL function. CREATE EXTENSION is not 
needed.

```
postgres=# SELECT pg_create_logical_replication_slot('slot', 'test_decoding');
 pg_create_logical_replication_slot 

 (slot,0/1829CE0)
(1 row)
```

> I will create a new container without Citus extension package ...

Yeah, it is quite helpful to understand the issue correctly. Thanks for working 
on it.

[1]: https://www.postgresql.org/docs/devel/xfunc-c.html#XFUNC-C-DYNLOAD

Best regards,
Hayato Kuroda
FUJITSU LIMITED