24 questions
-2
votes
0
answers
20
views
PGDump and PGRestore on/from Production PG DB [closed]
I wanted to take backup of production postgres server and restore it for data refresh on lower environments.
So we are thinking to use pg_dump and pg_restore utility for same, but while testing this ...
0
votes
0
answers
20
views
pg_basebackup stuck and does not complete due to EAGAIN (Resource temporarily unavailable) on Primary and Secondary(replica) does not finish
I am trying to create a replica using pg_basebackup and every time it gets stuck at a random percentage (80 to 96) percentage. The primary is looping in the getting "recvfrom(10, 0x7ffe06388c27, ...
- 670
0
votes
0
answers
28
views
Data sets missing after migration
We had to migrate Superset to a new server while upgrading from V1.3 to V3.13. We also moved the database (PostgreSQL 15) containing the datasets for many (150) charts to a new server. We updated the ...
0
votes
0
answers
11
views
Postgres fetch last inserted row to a table using trigger
I have 2 tables, tableA and tableB. How do I capture the row inserted in tableA and insert the same into tableB using triggers in postgresql version 15?
- 45
0
votes
1
answer
36
views
How to have a row always on top even when using OFFSET?
I'm using Postgres.
I want to have a row always on top (example alias = 'abc') in a query regardless of the offset being used.
Right now, I'm doing
ORDER BY
CASE
WHEN alias = 'abc' THEN 0
ELSE ...
0
votes
0
answers
18
views
permission denied when accessing table created by application user
I've added an R/O user:
CREATE USER ro_user WITH ENCRYPTED PASSWORD 'xxx';
GRANT CONNECT ON DATABASE my_db TO ro_user;
GRANT USAGE ON SCHEMA public TO ro_user;
GRANT SELECT ON ALL TABLES IN SCHEMA ...
- 146
0
votes
0
answers
107
views
Does anyone have a default PostgreSQL pg_hba.conf and pg_ident.conf File for postgres version 15?
I've messed my pg_hba.conf file up, and I kinda reinstalled 3 times and i opened the pg_hba.conf for version 15 there was nothing inside. so yeah.
if someone has pg_ident.conf file for postgres ...
0
votes
0
answers
25
views
Grouping records based on similarity
We have requirement to group all the similar records under one groupid for a given parent id.
I am sharing one simple example . I am trying to create sql query for this but no luck .
As you can see in ...
- 448
0
votes
0
answers
53
views
Mass updating Postgres rows in-place to avoid primary key index scans
I've got a large table (25M rows, 30GB) that needs to have a large amount of foreign keys set to a new table that I've just added. I've prepared a temporary table that holds the primary key ID of the ...
- 1,056
0
votes
1
answer
170
views
Postgres Text Search extremely slow
Using PostGres 15.5
My table looks like this:
CREATE TABLE IF NOT EXISTS directory_network_entity (
id varchar(70) NOT NULL,
country_code_2 varchar(2) NOT NULL,
country_name varchar(50) NOT NULL,...
- 1,941
0
votes
0
answers
37
views
Could not find driver on insert for a failed job
I can't seem to find the reason for the below error log:
[2024-03-07 16:28:32] local.ERROR: could not find driver (Connection: postgres, SQL: insert into "failed_jobs" ("connection"...
- 1
0
votes
0
answers
44
views
PEM auto discovery not working for community postgres 15
We’re using postgres community version 15 on suse linux 15.3 . We have enabled system config file and started the database. But while we try to auto discover the server in Postgres Enterprise Manager ...
- 339
1
vote
1
answer
198
views
Execution plan for postgres view combining 2 partitioned tables is accessing all partitions
I have two partitioned tables with similar range partitions (where date_key is a date in integer form -- like 20230901 for Sep 1st, for example:
create table header (
date_key int,
header_key ...
- 58
1
vote
1
answer
419
views
Postgres: is there an exhaustive list of causes that make `CREATE INDEX CONCURRENTLY` end in an `INVALID` state?
Besides these potential, documented causes:
a deadlock
hitting statement_timeout
for a unique index: a uniqueness violation
for expression and partial indexes: expression evaluation errors
Are there ...
- 303
0
votes
1
answer
101
views
How do i prevent updated row from shifting or changing to last row in Postgres using C code?
I'm new to Postgres and i have been creating table's using C program, so now I have run into a issue where a updated row is moving or shifting to the last row of the table and I have to prevent this ...
1
vote
2
answers
261
views
Why is returning 1 required in this Postgres code block?
Asking this question for a colleague who ran into this behaviour with PostgreSQL. Hoping somebody out there can solve this puzzle for us.
The following logs Rows updated: 3 when run:
drop table if ...
- 38.5k
1
vote
2
answers
406
views
How to use jsonb_populate_record() to insert into a table containing an IDENTITY column
I demonstrate the issue with the following simple table. (The actual table and JSON document have a lot more fields.)
CREATE table contact (
id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
...
2
votes
1
answer
149
views
Postgres: is this an equivalent rewrite of OVERLAPS predicate?
For some reason, Postgres (15.3) isn't using a multicolumn index on (an_id, start_ts, end_ts) when OVERLAPS operator is used in a WHERE predicate:
WHERE (start_ts, end_ts) OVERLAPS (input_ts_start, ...
- 303
3
votes
1
answer
789
views
Is it safe to disable log_checkpoints in postgres 15?
I've noticed that Postgres 15 has checkpoint enabled by default, and I'm curious about the rationale behind this decision, especially since it appears to be filling the log file with checkpoint ...
1
vote
0
answers
85
views
Postgres: (how) can I list only querying-relevant settings from `EXPLAIN (SETTINGS true)`?
Non-default configs can be listed with
SELECT name, setting, source, sourcefile FROM pg_settings WHERE source !='default';
But (how) can these configs be further narrowed down, so the list would be ...
- 303
1
vote
2
answers
218
views
How to optimize complex sorting?
I'm building a language learning platform, here are some tables:
create table users
(
id bigserial primary key,
...
)
create table users_languages
(
id ...
- 1,899
1
vote
3
answers
464
views
Google Cloud SQL cannot connect to Apache Superset
While trying to connect Apache Superset to Google Cloud SQL (PostgreSQL) I get this error:
I also unsuccessfully tried to connect by SQLAlchemy URI string
postgresql://username:password@host:port/...
- 41
0
votes
2
answers
830
views
PostgreSQL: Combine two JSON objects into a single JSON object
I am using PostgreSQL 15 and have the following two statements in PL/pgSQL:
-- Statement 1
SELECT to_json(T)
FROM
(SELECT *
FROM category
WHERE category.id = 43) T)
-- Statement 2
SELECT ...
- 10.7k
0
votes
3
answers
788
views
`row_number() over ()` causes drastically slow query only in Production and sort of fixes itself by upgrading from version 14.8 to 15
I have a new database with only about 225 rows.
I was facing a slow query issue only on the postgres running in production on my Ubuntu server. It was reporting 423.460 ms for the execution time. The ...