Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,494 questions
0
votes
0
answers
30
views
Optimise "Virtual Keys" in PostgreSQL Views
I am integrating data display software that expects tables for a dynamic use case, and have opted to produce views for the display software to consume. Essentially, the data display issues queries of ...
- 143
0
votes
1
answer
24
views
Enabled always statement level trigger on logical replication target does not seem to fire
Overview
I have the following setup using Azure PostgreSQL flexible server v17:
a main database containing a certain table included in the table list for a logical replication publication
a replica ...
- 1,191
0
votes
0
answers
35
views
Automatically transfer all data from postgres server to another remote server
I need to synchronize a test environment with production data (n8n worflows, user info, etc), both have postgres databases and reside on the same intranet
(e.g: server A: 10.0.0.10, server B: 10.0.0....
0
votes
0
answers
38
views
PostgreSQL pg_dump fails with "server closed the connection unexpectedly" during COPY operation
Problem Summary
My automated PostgreSQL backup script using pg_dump was working fine until a few days ago. Now it consistently fails with a connection error during the COPY operation on the alternate ...
- 101
0
votes
1
answer
57
views
Postgres tool to help with vacuum full when low on space
I'm often in the situation where I would like to "vacuum full" a table but there's not enough space left to duplicate data to remove bloat. That's because, to vacuum, a duplicate table is ...
- 103
0
votes
1
answer
21
views
Postgres database migration. pg_restore seems to hang
I'm attempting to migrate (users, structures, triggers, data - everything) a postgres 17 database on a windows 10 machine to a new windows 11 server.
The new machine has a default installation of ...
- 1,313
-1
votes
0
answers
26
views
Postgres Logical replication
I have set up logical replication from a PostgreSQL 14 instance running on an Azure VM to a PostgreSQL 16 Flexible Server in Azure.
Here’s the setup and process I followed:
Source (VM): PostgreSQL 14, ...
0
votes
1
answer
43
views
Logical replication betweet different schemas
I just discovered that logical replication doesn't work between different schemas, like you cannot publish schema1.table1 in server1 to schema2.table1 in server2.
In my setup, I have multiple servers ...
2
votes
1
answer
112
views
Valid `ccnew` and `ccold` indexes left over after `REINDEX CONCURRENTLY`
I'm using REINDEX CONCURRENTLY in a cron job. Sometimes the reindexing fails (which is not the topic of this question), and PostgreSQL leaves behind an index whose name contains ccnew (as expected). ...
- 155
0
votes
1
answer
79
views
Efficiently sorting by values in a separate table
I have a PostgreSQL 17 database that unfortunately requires queries that are very dynamic and hard to optimize. The part that I want to ask about are queries that need to order the results by some ...
- 117
1
vote
0
answers
19
views
Pgadmin4 Query Tool history recovery, find history of failed query in order to recover dropped function
I was trying to edit a function in Pgadmin4 (in Windows) using the query tool. Database is on a remote server.
I used the "Create Script" function and enabled the DROP IF EXISTS line.
There ...
- 11
2
votes
1
answer
72
views
Why does Postgres not allow SERIALIZABLE transactions on read-only replicas?
I get the following error when trying to send queries to a Postgres server in hot standby:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
COMMIT;
And the error:
SQL Error [...
Carl Mastrangelo
0
votes
2
answers
59
views
what is the role of SSL in postgresql connectivity?
While installing postgresql
downloaded from here:
I get a connection problem that says:
server does not support SSL, but SSL was required
How can I make my server (in this case my laptop) support SSL?...
- 17.3k
0
votes
2
answers
60
views
postgresql 17, xml xpath queries not work
We need to process XML data, and struggle with this. I followed some XML with PostgreSQL tutorials, and all was fine. Then I wanted to import XML files, around 30-40kB, exported from a diagram Editor. ...
0
votes
1
answer
35
views
postgres - locate user/role on filesystem level
postgres database objects are represented as files, or directories on the filesystem level (inside $PGDATA/base/).
I can see that i.e. for database, table.
What is the presentation of a postgres role/...
- 293