Questions tagged [postgresql-14]

Use this tag if your question is about PostgreSQL version 14 specific feature or behaviour. Please also add the generic [tag:postgres] tag.

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 views

Select query with 90+ parameters intermittently 20x slower on RDS PostgreSQL instance when using connection pool

I'm running a select statement on a postgresql table (on an RDS instance) in order to sum up values across 2 mil rows. The select statement has about 100 parameters (mostly different countries we want ...
Arnfred's user avatar
  • 101
0 votes
0 answers
53 views

PostgreSQL 14 is more than 2x slower than PostgreSQL 11

I have a Django web application that is backed by PostgreSQL 11 and I want to upgrade to PostgreSQL 14. But, when running PostgreSQL 14, the test suite runs more than 2x slower. I have compared both ...
Travis's user avatar
  • 113
0 votes
1 answer
36 views

Missing GIN support function (4 or 6) for attribute 1 of index

I have a problem creating GIN indexes in postgres 14.9 and GIN 1.3 The following is the query: CREATE TABLE assert.test_gin(col1 func.doc_sub_type[]); CREATE INDEX ON assert.test_gin USING gin(col1); ...
Ali Azimi's user avatar
3 votes
1 answer
114 views

How to re-TOAST specific data after changing compression?

After changing the default_toast_compression from pglz to lz4 I would like to update existing data to the new, faster, compression. The only way appears to be to dump the entire table and recreate it ...
OrangeDog's user avatar
  • 338
1 vote
0 answers
63 views

like_regex vs jsonb_build_object performance

I'm working with Postges 14.8 and using JSONB to store data. The queries that I'm working with perform filtering and JOIN'ing based on stuff in this JSONB column named data. We need to perform JOIN's ...
MRzeczkowski's user avatar
-1 votes
1 answer
13 views

Getting error converted oracle index to postgresql

we are in the process of migrating oracle to postgresql. during the process we are getting error for the below index. Oracle Index: CREATE INDEX idx1 ON table1 (column1||'-A1' ASC) ; By using schema ...
Ram's user avatar
  • 149
0 votes
1 answer
74 views

Index row size 2976 exceeds in postgresql

We have seen some of the indexes are getting failing with below error. ERROR: index row size 2976 exceeds byte version 4 maximum 2704 for index "idx1" HINT: Values larger than 1/3 of a ...
Ram's user avatar
  • 149
-1 votes
2 answers
73 views

readonly user on PostgreSQL 14 let me create new tables, why?

I try to apply any of these to create read_only_user: https://docs.snaplet.dev/guides/postgresql https://www.commandprompt.com/education/how-to-create-a-read-only-user-in-postgresql/#:~:text=...
Eric Ouellet's user avatar
0 votes
0 answers
22 views

How to restore PostgreSQL WAL from one Primary database to another Primary database nodes?

Suppose I have two separate primary database PrimaryDB1 and another is PrimaryDB2. I want all the transaction of PrimaryDB1 replicate into PrimaryDB2 by WAL replay. How can I do it? I don't want other ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
23 views

How to continuous wal archive and replay from one master to another master in PostgreSQL?

Basically I have 3 node BD cluster with 3 PgPool in total 6 node. I also made another cluster of 6 node to backup of 1st 6 node cluster. 2nd 6 node cluster will be for reporting purpose. How can I ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
1 answer
60 views

Is there any possibility to delete all data directory of PostgreSQL by PgPool?

Recently I found that suddenly all data directories were deleted both primary and standby. I am assuming that it happens due to failover. Example: Suppose there are 4 nodes where 1 is primary and ...
Sheikh Wasiu Al Hasib's user avatar
1 vote
2 answers
179 views

What is acceptable level of BTree index fragmentation in Postgres?

I wonder at which point it makes sense to rebuild BTree index on relatively large tables (couple hundred GB). To me anything above 30% or 40% appears to be a good candidate, but I saw some say even 70%...
a1ex07's user avatar
  • 8,990
2 votes
0 answers
36 views

Strange behaviour when `RAISE LOG ...` is executed with interpolated value

Recently had a script that failed on Postgres v13 ran but successfully ran on v14. I believe the behaviour in v14 to be strange and potentially a bug, but wanted to check here before submitting a bug ...
Jmase's user avatar
  • 21
2 votes
0 answers
120 views

Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume

I'm working with a PostgreSQL table containing terabytes of data, and it grows by millions of rows weekly. Each row is identified by a KSUID, and my primary read patterns are: Retrieve a row by its ...
wheels's user avatar
  • 21
0 votes
1 answer
36 views

PostgreSQL select a record that not pass the condition

I have partitioned a table named Order into two partitions, Order_open and Order_close. I run following query on Order table to retrieve the related record ("orderID"='e641d634-5c6c-4c75-...
Mofarah's user avatar
  • 11
0 votes
0 answers
101 views

barman check pg ,error in pg_receivexlog compatible: FAILED

Configuration PostgreSQL version: 14.2, pg_receivexlog version: None I am having an issue with a barman - PostgreSQL configuration that manifests itself with the following error(s): Server pg: ...
khaled ghazy's user avatar
0 votes
0 answers
184 views

PDT [95116] FATAL: could not access file "pg_stat_monitor": No such file or directory

Recently I accidentally added pg_stat_monitor as a shared preload library but now I can't start my postgres database server because of PDT [95116] FATAL: could not access file "pg_stat_monitor&...
champa's user avatar
  • 11
-1 votes
2 answers
184 views

Unable to drop database because of special character?

Unable to drop database whose name is shown below in the screenshot. In front of the database name, there is a plus sign I don't understand how to drop this database. Is there any format to drop this? ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
17 views

i want to have a streaming replication with delay 10 minutes behind the primary

I have set up a postgreSQL14 streaming replication. I set recovery_min_apply_delay=5min . every thing is ok. I have tested scenarious in which i delete some records from primary and i can recover them ...
Mostafa's user avatar
0 votes
0 answers
232 views

Postgres - Insert into table is stuck

I have a dynamic query that creates a table and then inserts data into this table (in Postgres 14.2). It works OK in 99% of cases, but sometimes insert just gets stuck. SELECT part is not a problem (...
Martin Blažek's user avatar
1 vote
0 answers
101 views

PostgreSQL 14 blocked

I have a fairly serious problem with PostgreSQL, it happens at intervals of a few days that some tables on my PostgreSQL instance crash, or rather this is my feeling. Linked queries are very slow. ...
Leone Miceli's user avatar
0 votes
0 answers
33 views

Postgres 14 upsert column where nulls are unique

I have a table with a column where only 1 null is allowed (nulls should be considered unique). I can a create partial index on this column to enforce this rule. However I can't find a way to use that ...
Jake's user avatar
  • 101
0 votes
0 answers
646 views

Why does postgresql have so many replication slots open?

I use logical replication to move changes from my primary to a replica. Today, to update the replica, I: disabled the subscription on the replica (ALTER SUBSCRIPTION foo DISABLE;) upgraded it to ...
mlissner's user avatar
  • 533
0 votes
0 answers
689 views

Postgresql logical replication suddenly stop (wal status is extended, An existing connection was forcibly closed by the remote host)

I checked the replication status of our production server when i noticed that one of the replicated DB is missing (using pg_stat_replication command). I also check the replication slots and found out ...
chris_dev's user avatar
1 vote
1 answer
90 views

NpgsqlParameter Array of smallints is receiving 0 instead of null value from postgres

I got strange result with NpgsqlParameter in C++/CLI project. NpgsqlParameter^ status = wCMD->Parameters->Add(gcnew NpgsqlParameter("param1", NpgsqlDbType::Array | NpgsqlDbType::...
Thanh Dong's user avatar
0 votes
2 answers
238 views

How to display name and type of table columns in creation order?

I have created this tbl_cities using the code below: CREATE TABLE IF NOT EXISTS public.tbl_Cities (City_ID SERIAL NOT NULL, City_Name VARCHAR(100) NOT NULL, City_State_Region VARCHAR(100), ...
michal roesler's user avatar
1 vote
0 answers
49 views

Postgres - now to optimise a table for high frequency INSERTs?

I need to load data into a table as fast as possible. So far I've done the following. Create the table UNLOGGED Removed all indexes Removed all triggers Created the table in a tablespace on a SSD ...
ConanTheGerbil's user avatar
1 vote
2 answers
873 views

Issue configuring Postgresql for remote connection: 'Job for postgresql.service failed'

I'm trying to configure Postgresql 14.7 for remote connections in a SLES15 environment. I've been following the instructions from Postgres for Zypper installation supplemented with these instructions ...
W. Kessler's user avatar
1 vote
0 answers
55 views

What circumstances will cause a postgres 'unlogged' table to lose data?

For performance reasons I have created a Postgres UNLOGGED table. Everything works fine, performance is blisteringly fast. But very occasionally I lose all the data in it. According to the ...
ConanTheGerbil's user avatar
1 vote
1 answer
132 views

Query with smaller subset to scan taking exponentially longer?

I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains. I have a query which takes almost 17 minutes to run: select * from public.extrinsics where chain_id = ...
Deekor's user avatar
  • 125
2 votes
1 answer
797 views

What permissions are needed to prevent ERROR: permission denied for pg_tablespace_size('pg_global') in RDS?

I'm looking for some help identifying a permission I need to grant to prevent an error in a proprietary app. The App is loading data into an AWS RDS postgresql database. Loading data is working fine....
Philip Couling's user avatar
1 vote
0 answers
22 views

Why group by using additional column is required when using a view [duplicate]

Here I have simplified tables and query, I don't get it why using select from view v_ticket requires grouping by column group_id also, but when selecting it directly from ticket table not? create ...
sh4rkyy's user avatar
  • 129
0 votes
0 answers
18 views

For storing logs that always are queried in batches could make sense to store the full batch as json?

I store each change to the tables in a log table: CREATE TABLE log ( log_id BIGSERIAL, source TEXT NOT NULL CHECK (not_empty(source)), -- The table source_key TEXT ...
mamcx's user avatar
  • 153
0 votes
1 answer
172 views

Convert byte to certain unit

What's the function to convert byte value to certain unit in PostgreSQL-14? for example: Byte Convert Converted Value Unit Value ----- ---- ----- 1 MB 0.000001 ...
DevDon's user avatar
  • 33
0 votes
1 answer
140 views

pg_rewind failing to detect different system in docker container

I have two docker containers running on two different docker hosts. docker host1: 192.168.10.3 docker host2: 192.168.10.4 Container 1 is running on host1 and container 2 is running on host2. Port 5432 ...
Niraj Nandane's user avatar
1 vote
1 answer
115 views

Moving records from one table to another - a few records at a time

I have been using this query (successfully) to move records from one table to another in a Postgres database. WITH moved_rows AS ( DELETE FROM tableB RETURNING * ) INSERT INTO tableA ...
ConanTheGerbil's user avatar
-1 votes
1 answer
242 views

Use pgdump sql file to upgrade version?

Can I use a pgdump .sql file generated by one version as a way to upgrade to another version? For example: If I created a backup on PostgreSQL 14 with: pg_dump --no-privileges --no-owner $DB_NAME > ...
Michael's user avatar
  • 153
1 vote
1 answer
488 views

Create user mapping without password how to configure authentication

I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says. local_db=> select * from employee; ERROR: could not connect to server "...
Aymen Rahal's user avatar
0 votes
2 answers
106 views

Conditionally execute DDL, short-circuiting syntax error

I'm trying to execute a different create or replace function signature according to version() - since anyarray & anyelement are removed in postgres 14 and replaced by anycompatiblearray & ...
OJFord's user avatar
  • 115
1 vote
1 answer
552 views

Unnest array of rows with a tagged column

I'm trying to create a query that will return data in this format: id time value 1 1 2.5 1 2 3.5 1 3 6.4 2 1 8.3 2 2 8.5 I'm using Timescaledb and wish to use one of their downsampling ...
dotconnor's user avatar
  • 135
2 votes
1 answer
415 views

Why changing limit triggers different query plans in postgresql?

Changing only limit from 40 to 50 in following query triggers different execution plans. And unfortunatly one I needed is much slower. So the question is: why this happening and how can I force ...
Gor Rustamyan's user avatar
4 votes
1 answer
1k views

Set PostgreSQL config from command line with ALTER SYSTEM

I need to write a shell/bash script that automatically changes some PostgreSQL configurations. Here's the command that I wrote: sudo -u postgres psql -U postgres -d postgres -c " ALTER SYSTEM SET ...
collimarco's user avatar
1 vote
0 answers
142 views

Resuming PostgreSQL logical replication (missing pg_stat_replication entry)

I implemented a logical replication on PostgreSQL 14 (from AWS to on-premise) via windows. A few days we experience internet lags. When i checked the pg_stat_replication in publication source, it was ...
starting dba jr's user avatar
-5 votes
1 answer
243 views

Why does PostgreSQL's SQL require this much unnecessary code for INTERVALs with placeholders?

If I try this from PHP: database_query('SELECT * FROM whatever WHERE timestamp < now() - INTERVAL $1', $_GET['number of years back'] . ' year'); ... I just get a total question mark back from ...
Homer's user avatar
  • 99
0 votes
1 answer
85 views

Preserve WALs on promoted node in PostgreSQL 14

I have PostgreSQL streaming replication setup using replication slot. Node1 is primary and Node2 is slave. Now, node1 is down and promoted node2. Node2 is running and in read-write mode. There is ...
Niraj Nandane's user avatar
0 votes
1 answer
80 views

How to append 2 default values to a JSONB column in Postgres SQL 14?

Previously I had 4 default values for a jsonb column in my table cities. create_table "cities", force: :cascade do |t| t.jsonb "config", default: {"A"=&...
Priyanshu0007's user avatar
2 votes
1 answer
120 views

Full-text search with hyphenated integers

Compare the following search queries: SELECT to_tsvector('yellow-green') @@ to_tsquery('yellow & green') as word_word, to_tsvector('Apollo-11') @@ to_tsquery('apollo & 11') as ...
ennui's user avatar
  • 81
3 votes
1 answer
3k views

"duplicate key value violates unique constraint" in upsert in Postgres 14

I have a query that I've been successfully running since Postgres added upsert support, I believe that was 9.6: INSERT INTO gitrefresh(projecttag, state, analysis_started, counter_requested, ...
GDR's user avatar
  • 141
0 votes
0 answers
313 views

pgbench : too many client connections for select()

I was working on benchmark testing for pg10 and pg14. I did the testing for 100 clients with 12 threads in pg14 I was getting following error, "pgbench:too many client connections for select()&...
mathin0496's user avatar
2 votes
1 answer
18k views

PostgreSQL FATAL: no pg_hba.conf entry for host "[ipv6]", user "[user]", database "postgres", no encryption

Unable to connect to a postgreSQL database on a LAN. (No problems connecting to the db on the server locally) Error shown in pgAdmin.app: Unable to connect to server: connection to server at "xxx....
WhatsYourFunction's user avatar