Skip to main content

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
0 answers
20 views

How to patch a Postgresql located on a Windows server? [duplicate]

I'm new to Postgresql administration and looking for some guidance on process to apply patch/upgrade to a PostgresSQL server installed on a Windows VM. Is there a patch executable or do I need to ...
variable's user avatar
  • 3,476
0 votes
1 answer
16 views

How to connect/take over a Postgresql instance without any login?

I have inherited a Postgresql server but have no records of the admin credentials or any user account. I do have local admin access to the Windows VM that hosts this Postgresql server. How can I gain ...
variable's user avatar
  • 3,476
0 votes
0 answers
15 views

Change cipher PBEWithMD5AndDES

I am using PostgreSQL 14. I created a database, set up a user, and performed a migration. Now, the database is connected to the application, and a security test was conducted. I have been tasked with ...
Jan Kůst's user avatar
0 votes
0 answers
14 views

EDB Replication behavior

I'm using EDB Replication with 3 postgres databases in MMR Replication. Let's call them Node1, Node2 and Node3. I have a table "todo" on each one of them and I'm getting some weird behavior. ...
Christos Arapidis's user avatar
1 vote
1 answer
29 views

Trouble with only read user in Postgresql 14

I have a cluster where running around 20 postgres databases. I need to create read only user for every db. On this server i have service user e.g. service_user that do automaticly all operations in ...
Slam's user avatar
  • 11
0 votes
1 answer
37 views

Using ENUM as partition key when RANGE Partitioning PostgreSQL

I am working with PostgreSQL 14. I have a table called Order with a status column: CREATE TABLE "Order" ( "orderId" BIG SERIAL PRIMARY KEY NOT NULL, "orderDescription&...
Mofarah's user avatar
  • 25
0 votes
1 answer
56 views

Postgres planning time is very high

I have a table named, and it has enough CPU, memory, and shared buffers. But when I run a simple query: Explain analyse SELECT * FROM assets WHERE leased_to = 'org_name' or owned_by = 'org_name' ; ...
sam's user avatar
  • 1
0 votes
0 answers
14 views

pgp_sym_decrypt_bytea key error

I am trying to use a symmetric key to encrypt and decrypt a field in Postgres 14.10. I am encrypting per documentation like so: select pgp_sym_encrypt('data', 'password'); I am feeding the return of ...
kravb's user avatar
  • 179
0 votes
1 answer
203 views

Missing collation cs_CZ.utf8

I've set up a database on PostgreSQL 14, and now when I try to insert data, I'm getting the following error: ERROR: collation "cs_CZ.utf8" for encoding "UTF8" does not exist ...
Jan Kůst's user avatar
0 votes
1 answer
84 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
190 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
44 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
173 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
155 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
203 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
127 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
33 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
35 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
107 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
413 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
  • 9,010
2 votes
0 answers
37 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
182 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
49 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
  • 25
0 votes
0 answers
204 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
307 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
268 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
460 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
126 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
1 vote
1 answer
995 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
  • 593
0 votes
0 answers
1k 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
132 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
462 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
59 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
1k 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
75 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
153 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
1k 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
  • 139
0 votes
1 answer
286 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
186 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
2 answers
433 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
425 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
623 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
131 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
  • 125
1 vote
1 answer
721 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
482 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
2k 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
167 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
331 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