Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,496 questions
1
vote
1
answer
140
views
How to avoid an "invalid locale" error when restoring a dump from Windows to Debian Linux?
Postgres 17 cluster is in Windows server in Estonian locale. Databases are defined like
CREATE DATABASE mydb
WITH
OWNER = mydb_owner
ENCODING = 'UTF8'
LC_COLLATE = 'et-EE'
LC_CTYPE ...
- 143
0
votes
1
answer
45
views
What is the name/abbreviation of the PostgreSQL dialect and how to highlight it in Markdown?
Question
I know TSQL is the SQL dialect of Microsoft dialect. However, I failed to find an equivalent for PostgresSQL. Thus I ask, what is the name/abbreviation of the PostgreSQL dialect?
My initial ...
- 103
3
votes
1
answer
150
views
Does SQL Server have a mechanism to recover from torn page writes like PostgreSQL's full_page_writes?
I am comparing how PostgreSQL and SQL Server protect data pages from torn writes.
PostgreSQL has a feature called full_page_writes.
On the first modification to a page after a checkpoint, PostgreSQL ...
- 1,573
1
vote
1
answer
39
views
Is it possible to use scientific notation to display large numbers in psql?
I have tables with very large numbers, which are entered using scientific notation (e.g. 1e100). But when in the PostgreSQL's psql client (as of 18.x), they get expanded into the full length:
# SELECT ...
- 3,850
0
votes
0
answers
19
views
Configure Postgres to listen to VRF interface
I'm looking into Linux VRFs i.e. support for virtual routing and forwarding.
The documentation mentions:
Applications that are to work within a VRF need to bind their socket to the VRF device:
...
- 101
3
votes
1
answer
133
views
MERGE causes unique constraint violation when source contains key multiple times
Given two tables counter and cnt_source defined like this:
create temporary table counter (key bigint, count bigint);
create unique index counter_key_uniq on counter (key);
create temporary table ...
- 31
0
votes
2
answers
45
views
Monitoring long-running queries in Aurora PostgreSQL
I'm supporting a new system that's going live next month. I'm trying to have PostgreSQL log any long-running (over 1 second) queries to the error log. I've set the following parameters:
log_duration: ...
- 183
0
votes
1
answer
59
views
Why does READ COMMITTED allow Phantom Reads in PostgreSQL?
When writing SQL queries against a large PostgreSQL database, I am seeing phantom reads despite setting the transaction isolation level to READ COMMITTED. Can you provide a concrete example of a multi-...
- 13
0
votes
1
answer
25
views
change ownership of output file when run as postgres user or redirect?
I am making a query as user postgres via my current user, xyz. I am returning the results of this query as JSON. I want to save this (huge) JSON into a file owned by my current user xyz. And I do not ...
- 103
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
53
views
Unable to restart PostgreSQL after automatic shutdown
My PostgreSQL server has unexpectedly shut down.
In the log file (postgresql-Wed.log), I can see the following:
2025-11-12 18:18:54 LOG: received fast shutdown request
2025-11-12 18:18:54 LOG: ...
- 1,843
0
votes
0
answers
35
views
PostgreSQL logical replication failing with "permission denied for schema" despite operating as the superuser
On a PostgreSQL 14 database, I've got a logical replication publication of 108 tables across 10 schemas. For some unknown reason, the subscriber fails to replicate tables in one of the schemas (call ...
- 175
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
30
views
PostgreSQL:Why promoting standby while primary is online causing WAL fork?
I have been testing PostgreSQL 17 AYSNC replication and failover on Ubuntu VMs. Suppose I have two VMs: A as the primary and B as the standby. If I promote B while A is offline, and later configure A ...
- 700
0
votes
0
answers
27
views
Transaction applied partially after failover in postgres
We noticed a data inconsistency in a 3-node asynchronous replication Postgres setup.
A single JDBC transaction inserted/updated rows in two tables but we are noticing that one table did not have a few ...
- 1