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.
102 questions
2
votes
1
answer
84
views
UNNEST vs ANY()
When selecting a number of records from a Postgres table based on a list of ids passed as a parameter, what are the benefits and drawbacks of
SELECT id, <more...>
FROM tbl
INNER JOIN UNNEST($1) ...
- 123
0
votes
0
answers
65
views
How to Separate Data, Indexes, WAL, and Logs Across Different Disks in PostgreSQL for Improved I/O Performance?
I'm working on optimizing disk I/O for a PostgreSQL database by distributing data, indexes, WAL files, and logs across separate SSDs. Here's my current disk setup:
/dev/sda for data
/dev/sdb for WAL ...
0
votes
0
answers
25
views
Issues with High Write Transactions in PostgreSQL Cluster with Pgpool Load Balancer
I am currently managing a 3-node PostgreSQL asynchronous streaming replication cluster setup, which includes one master node and two standby nodes, all sitting behind a pgpool load balancer. During ...
0
votes
0
answers
39
views
Understanding reason for error message while attempting to install postgresql on a system that already has postgresql installed
Received the following error while attempting to install postgres 14 on Ubuntu 22.04 (expanded output at end of post):
update-alternatives: error: alternative postmaster.1.gz can’t be master: it is a ...
- 315
0
votes
1
answer
51
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 ...
- 3,499
0
votes
0
answers
19
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 ...
- 45
0
votes
0
answers
28
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.
...
1
vote
1
answer
46
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 ...
- 11
0
votes
1
answer
115
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&...
- 35
1
vote
1
answer
158
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' ;
...
- 11
0
votes
0
answers
64
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 ...
- 179
0
votes
1
answer
488
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
...
- 45
0
votes
1
answer
128
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 ...
- 101
0
votes
0
answers
279
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 ...
- 113
0
votes
1
answer
52
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);
...
3
votes
1
answer
261
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 ...
- 338
1
vote
0
answers
227
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 ...
- 11
-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 ...
- 149
0
votes
1
answer
371
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 ...
- 149
-1
votes
2
answers
209
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=...
- 101
0
votes
0
answers
35
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 ...
0
votes
0
answers
51
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 ...
0
votes
1
answer
131
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 ...
1
vote
2
answers
681
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%...
- 9,030
2
votes
0
answers
38
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 ...
- 21
2
votes
0
answers
213
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 ...
- 21
0
votes
1
answer
68
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-...
- 35
0
votes
0
answers
367
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&...
- 11
-1
votes
2
answers
343
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? ...
0
votes
0
answers
645
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 (...
1
vote
0
answers
145
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.
...
- 11
1
vote
1
answer
1k
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 ...
- 623
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 ...
1
vote
1
answer
166
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::...
0
votes
2
answers
634
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),
...
- 125
1
vote
0
answers
61
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 ...
- 1,217
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 ...
- 113
1
vote
0
answers
99
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 ...
- 1,217
1
vote
1
answer
169
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 = ...
- 125
2
votes
1
answer
2k
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....
- 161
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 ...
- 139
0
votes
1
answer
408
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
...
- 43
0
votes
1
answer
213
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 ...
- 115
1
vote
2
answers
824
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
...
- 1,217
-1
votes
1
answer
495
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 > ...
- 163
1
vote
1
answer
722
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 "...
- 11
0
votes
2
answers
182
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 & ...
- 125
1
vote
1
answer
843
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 ...
- 135
2
votes
1
answer
544
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 ...
- 123
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 ...
- 623