Questions tagged [postgresql-16]
Use this tag if your question is about PostgreSQL version 16 specific feature or behaviour. Please also add the generic [postgres] tag.
28 questions
1
vote
1
answer
36
views
Granting Monitoring privileges to a user in PostgreSQL
I am creating a new user and granting them the pg_read_all_stats privilege so they can monitor all pg_stat_* views. However, when I monitor pg_stat_activity through this user, other users' operations ...
0
votes
1
answer
40
views
Monitoring PostgreSQL Copy Operations
To monitor Copy operations in PostgreSQL in real-time, I use the “pg_stat_progress_copy” view tool:
Here, I specifically want to analyze the number of tuples processed by the command, which is “...
0
votes
1
answer
99
views
How to clean the pg_wal folder when it is not done automatically?
I have two PostgreSQL16 clusters (one cluster per server) each with one and the same database.
They are interconnected via a bi-directional logical replication, with one being the main one and the ...
- 101
0
votes
1
answer
80
views
In PostgresQL 16, how to detect where "Large Objects" are used?
I have a large database that I need to migrate from postgresql 16 to 17. But when programming a logical replication, I receive a message saying that a large object has been modified. But I don't know ...
- 111
0
votes
1
answer
116
views
Win Server 2016 - PostgreSQL 15 and 16 Install Errors - Failed to Load SQL Modules into Database Clusters
Note that PG10 was installed on this server. Prior to installing the new version, I stopped and disabled the PG10 service.
Here's the OS version of the server in question:
I used the EDB installers ...
- 145
0
votes
1
answer
78
views
Write a single query for account balance estimation
Imagine I have a table with balance transactions:
ID
Account ID
Amount
Currency
Date
1
1
100
EUR
2025-03-01
2
1
200
EUR
2025-04-01
3
1
300
EUR
2025-05-01
And a table with known account balances:
ID
...
- 121
1
vote
0
answers
68
views
PostgreSQL 16: WITH/CTE versus Non-Deferrable Constraints
I am using PostgreSQL 16 upwards.
I am currently trying to create an example for a relationship of type G-|o-----|<-H in Crow's foot lingo.
This means that each row in the table g for entity type G ...
- 111
1
vote
1
answer
548
views
No config, data files and cluster after installing PostgreSQL 16 on Ubuntu 22.04
Trying to upgrade a PostgreSQL 13 instance to version 16, I am running into trouble. Before this, I upgraded (in-place) without issue from 11 to 13, but now installing 16 causes issues.
I do:
sudo apt ...
- 113
3
votes
2
answers
546
views
Is Postgres ANY_VALUE(...) arbitrary when using ORDER BY?
Is PostgreSQL's any_value actually arbitrary when using ORDER BY?
The documentation states:
any_value ( anyelement ) → same as input type
Returns an arbitrary value from the non-null input values.
...
- 33
0
votes
0
answers
68
views
Postgres 16 pg_basebackup not working due to system identifier mismatch
I am using the Alpine docker image for Postgres 16 for this experiment.
I have one container which is running and has some tables.
I create a second container which is also running but has no tables.
...
- 1
0
votes
0
answers
2k
views
pg_restore: error: unsupported version (1.16) in file header
baran@heaven:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main....
- 133
0
votes
1
answer
64
views
Postgresql fulltext plainto_tsquery()
I noticed that plainto_tsquery('english', 'the T-bird') produces the TSV 't-bird' & 'bird' instead of just 't-bird' which doubles the runtime for this particular query.
How could I fix that ...
- 222
0
votes
0
answers
93
views
Best practice for modern (pg16+) role/schema management
I'm more of a data engineer than administrator and my role/schema management knowledge is more or less frozen in time since 8.4. I'm aware that pg15 tightened public schema restrictions, and pg14 ...
- 130
0
votes
1
answer
337
views
PostgreSQL throwing "53100: could not extend file "base/xxxxx/xxxxx" with FileFallocate()" despite ample space in the volume
I recently upgraded PostgreSQL 14 to PostgreSQL 16 using --clone method. Both my data directories (old & new cluster) were on same volume. After few days, I started receiving below error.
53100: ...
- 135
0
votes
1
answer
151
views
Postgres 16 Continuous archiving and PITR "invalid checkpoint" error
Here is what I am doing:
I have a Postgres 16 docker container which is continuously archiving WAL files
After 00012 and 00013 were archived, I take a base-backup of the database using pg_basebackup ...
- 1