602 questions
1
vote
1
answer
117
views
Can postgres auto-vacuum block another process trying to run ALTER or CREATE queries on the same table?
A process is trying to ALTER a table (to set default column value) in a workflow. It is getting blocked due to postgres auto-vacuum process not releasing a lock (It might be holding a ...
0
votes
1
answer
62
views
Unable to connect to table in PostgreSQL DB with read permissions via Spring Boot
The task is as follows - it was necessary to connect to an existing DB that has read access to a certain schema with multiple tables. I am trying to connect to one of the tables (I repeat, only read ...
- 103
0
votes
1
answer
209
views
pg_repack - CREATE INDEX stucj in IDLE state for than 24 hrs
I am in the process of pg_repacking a huge table (13TB). Following is the info for my setup:
pg_total_relation_size (Including indexes) - 13TB
pg_table_size - 4TB
pg_repack version - 1.4.7
Postgres ...
- 449
2
votes
0
answers
49
views
postgresql reindex results in index row size error
I have a huge table with a primary key and a btree_gist index. After some years of operation, the performance of selections went constantly down. I assume the gist index is fragmented.
When reindexing,...
- 478
-1
votes
1
answer
246
views
My postgresql indexes are read slowly from disk
I have a database of sufficient size that it does not fit entirely in RAM, including indexes that also exceed RAM capacity. When performing queries, I observe significant differences in processing ...
- 302
0
votes
1
answer
421
views
Applying custom queries to multiple databases for PostgreSQL in Datadog
We're using RDS with PostgreSQL engine v12.17 and integration with Datadog. We have multiple databases and have multiple custom queries for these databases.
Currently, there is a lot of duplication, ...
1
vote
1
answer
160
views
Compatiility of Postgres 14.9 with Centos 6.9
I have postgres 12.18 installed on my Centos 6.9 OS (I know it's too old). I want to upgrade to postgres 14.9, is it possible ?
I am trying to install postgres 14 but getting lot of issues.
sudo yum ...
- 81
0
votes
0
answers
140
views
Routing query to different Postgres DB instances
I have a requirement to route the incoming query to respective DB instances. I'm new to managing Databases.
eg: I have 4 Postgres RDS. say rds_master, rds1, rds2 and rds3
rds_master contains schema ...
1
vote
0
answers
56
views
How to get milliseconds converted into date time format with deciaml value
I have a table as
create table my_table(created timestamp);
The value which I want to insert is - 1706812200000
But it is in milliseconds. I want to convert it into 2024-04-30 16:48:02.079519 format.
...
- 11
0
votes
2
answers
196
views
What roles does a postgresql database make use of?
I've created a backup of a database on one computer
postgres@machine1$ pg_dump mydb > mydb.dump.sql
and then, from a second computer, having created a mydb database, I restore it from the dump
...
- 812
0
votes
1
answer
159
views
Union types integer and text cannot be matched
After migrating database data from Postgresql 9 to GCP cloud sql PostgreSql 12, the query shown here is failing with this error:
“UNION types integer and text cannot be matched”
Query:
Select c1, c2,...
- 11
0
votes
1
answer
1k
views
Create odoo user with admin privileges from psql
I lost the admin user's password and reinisilazing with an email did not work (i did not receive an email). so i would like to use PgAdmin to create a new user with admin privileges to access Odoo ...
- 61
1
vote
1
answer
141
views
POSTGRES: Getting different set of records when added a new column [duplicate]
$: select id from vehicles limit 5 offset 45
-- Returns
-- 735
-- 736
-- 737
-- 738
-- 739
$: select id, state from vehicles limit 5 offset 45;
-- Returns
-- 1381 | new
-- 1327 |...
- 3,264
0
votes
0
answers
63
views
PostgreSQL Error: Connection to the database failed: Server Closed the connection unexpectedly
when I connect to the PostgreSQL Database from localhost then sometimes it gives Error: Server Closed the connection unexpectedly although when execute the command again immediately then connection ...
0
votes
0
answers
58
views
Outliers produce a bad query plan
I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns:
CREATE TABLE document
(
id serial PRIMARY KEY,
organizationid INTEGER,
status_1 ...
- 33
-1
votes
1
answer
70
views
Filter query by an element from inner json array
Simple table in PostgreSQL 12.3
create table aaa(id integer, data json);
In data column stores JSON with the following form:
{
"foo": 1,
"persons": [
{
...
- 53
0
votes
0
answers
457
views
How is interpreted the difference between postgres buffers_alloc and sum of buffers_backend, buffers_clean, buffers_checkpoint?
When checking pg_stat_bgwriter in postgres, I am noticing that there is a difference between buffers_alloc and the sum of buffers_backend, buffers_clean and buffers_checkpoint.
What could be assumed ...
- 301
0
votes
0
answers
87
views
How can I write a recursive query on a hierarchical table such that the output is provided as nested JSON?
I have tables that are basically as follows:
CREATE TABLE persons (
person_id serial primary key,
parent_person_id int REFERENCES persons (person_id),
person_name text
);
INSERT INTO ...
- 117
0
votes
0
answers
274
views
Logical replication fails at failover because of sequence
I have two servers A and B, server A is the publisher for table with primary key sequence. Before i switch to server B, I update the sequence in server B to be same as server A. Then I do the failover ...
0
votes
1
answer
1k
views
Postgresql - Idle query keep open connections to all databases
I have a strange situation in my PostgreSQL server. I have the below idle query that keeps running in all databases in the server.
datid | 1029662
datname | ganar
pid |...
- 11
-1
votes
3
answers
127
views
UPDATE performance when JOIN produces duplicates rows for columns in SET
Consider the following example:
There are 3 tables: clients (1) -> (n) orders (1) -> (n) order_updates.
I want to update clients based on the corresponding order_updates with JOIN (which ...
- 44.7k
-1
votes
1
answer
2k
views
Postgresql find a string inside the columns of all tables
I want to search for a string that is inside of a column of some table. The system has about 200 tables. I need to search all columns of all tables to achieve what I want.
Is it possible in Postgresql ...
- 2,697
0
votes
1
answer
90
views
Running the definition of a view returns different results to running the view itself
I am currently using PostgreSQL 12.11 and have a view called previous_customers with the following definition (extracted from DDL):
CREATE VIEW previous_customers(customer_id, alumni) as
SELECT ...
1
vote
1
answer
703
views
In "pg_repack" I faced an Error : cannot update table "table_123" because it does not have a replica identity and publishes updates
ERROR: query failed: ERROR: cannot update table "table_123" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ...
1
vote
0
answers
361
views
Postgres Copy command multiple delimiter
I am trying to use the copy command in postgres to upload the CSV file into the database using the below command
psql -h 127.0.0.1 -d target -U postgres -c "\copy test FROM 'E:\pg_test\test.csv' ...
- 623