Questions tagged [postgresql-13]
Specifically for PostgreSQL version 13
148 questions
0
votes
0
answers
9
views
postgreSQL server 13.13 version , setting the log_statement (none,ddl,mod) parameter not effect the log file outputs
We are using postgreSQL server 13.13 version , setting the log_statement (none,ddl,mod,all) parameter does not effect the log file output.
Setting log_statement = none and restart server
Upgrading ...
- 1
0
votes
0
answers
32
views
Temporary relation files not removed after sessions ends - Single PostgreSQL instance PostgreSQL 13
We are facing default temporary tablespace disk space full issue due to lots of below temporary relations files created but not removed after ending session
1.1G t756_1536213694.409
1.1G ...
- 143
0
votes
0
answers
37
views
PostgreSQL: pg_settings view's "pending_restart" not showing correct value
I have seeing a strange behaviour with PostgreSQL13 pg_settings system view. I tried updating max_connections using SET command and left without a restart. But if I query from the same session or any ...
- 393
1
vote
0
answers
51
views
Why is the replication delay in pgpool showing a very large number?
To calculate replication delay in pgpool, the value is obtained from the view pg_stat_replication.replay_lag.
However, sometimes when executing show pool_nodes, it displays a significantly larger ...
0
votes
1
answer
194
views
Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?
In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
0
votes
0
answers
32
views
Correcting over permissive postgresql, is this the right appproach?
I have inherited a PostgreSQL (13.x) legacy DB I am working with. Original creation was with single admin role w/ownership and all rights that services connected to. We are looking to make this less ...
0
votes
1
answer
185
views
Why is restored database faster than original?
I ran this explain analyse in my PostgreSQL 13.7 database:
explain(analyze, verbose, buffers, settings) select * from main_table;
Seq Scan on public.main_table (cost=0.00..10246.15 rows=220715 width=...
0
votes
0
answers
139
views
Repmgr installation and primary register
When I run /usr/pgsql-13/bin/repmgr -f /var/lib/pgsql/repmgr.conf primary register I get an error:
NOTICE: attempting to install extension "repmgr"
ERROR: unable to create "repmgr" ...
- 1
0
votes
1
answer
107
views
How to pg_restore some tables with automatic index creation?
How to restore some table from pg_dump custom format, and automatically create it's index ?
I just restore some tables from database dump (custom format).
pg_restore -d database --table=table1 --table=...
- 11
1
vote
1
answer
270
views
How large can you set track_activity_query_size without incurring performance issues?
We have some pretty large queries in our production postgresql (13.8) database that exceed the default 1024 for track_activity_query_size (some queries are over 100k). I've bumped this value up to ...
- 113
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
50
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 ...
0
votes
1
answer
96
views
Why pg_wal directory holding 16MB WAL file of last 24hr in PostgreSQL13?
In my PostgreSQL pg_wal directory total wal segments sum about 200GB where max_wal_size 8GB.
We archived WAL file at desired location using archive_command but wal file not removed from pg_wal ...
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
0
votes
0
answers
33
views
On select specific single row showing different result in PostgreSQL
Selecting a specific single row showing different result in PostgreSQL which is very strange. There is a table named Employee. When I search like " SELECT UserId FROM Employee WHERE UserId='3123'&...
0
votes
1
answer
917
views
How to conditionally recreate indexes created by `CREATE INDEX ON <table_name> (<column_name>)`?
I am squashing migrations and rewriting all constructs like:
CREATE TABLE entities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
description text
);
CREATE INDEX ON ...
- 286
1
vote
0
answers
33
views
How to optimize structure of the tables?
Database
PostgreSQL 13.10
Structure
Constraints
name is "filter_test_group_id_test_group_id_test_name_id_filter_key_uniq"
UNIQUE(id_test_group, id_test_name, id_filter, key)
Indexes
...
- 11
-1
votes
2
answers
342
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
1
answer
2k
views
Install pgbench on non PostgreSql server
I use pgbench tool on a CentOS 8 server without installed PostgreSQL.
In case Postgresql 10 install command is:
dnf install postgresql-contrib
pgbench --version
pgbench (PostgreSQL) 10.23
With ...
- 103
1
vote
0
answers
198
views
Cannot change logging settings per-user in Postgresql
I have a special database role that only handles passwords, and I want to exclude it from database logs, but include logs from all other users.
In my postgresql.conf file I have these custom settings:
...
4
votes
1
answer
653
views
Is it safe to wipe out pgsql_tmp while the database is not running?
I've had a couple of instances recently of being forced to shut down my PostgreSQL 13.10 database uncleanly, and it taking a few hours to come back up afterwards.
In both cases, strace revealed that ...
- 143
0
votes
1
answer
464
views
Modifying and RETURNING data in the same postgres command
I have a simple bit of SQL to reset some values in a table
UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)
This works fine, but I'd like to modify it to return the value of all field1 ...
- 1,217
1
vote
0
answers
77
views
Is there a way to set up custom global output formatting for timestamptz values?
In order to reduce friction between frontend/backend/database in regards of dates, I've decided to use a subset of RFC3339 which is also compatible with ISO 8601 and HTML. As per this graph that means ...
- 286
1
vote
1
answer
182
views
Need to drop a function by oid value (I think)
I'm on Postgres 13 (RDS) and need to drop a function by its oid value. Here's what I'm looking at:
postgres> select oid,proowner::regrole,proname||'('||pg_catalog.pg_get_function_identity_arguments(...
- 13
1
vote
0
answers
79
views
Query reads way more data on Postgres 13 than 11
I have two servers, one running Postgres 11, the other Postgres 13. Both servers have the same amount of RAM and CPU cores/speeds, and both are configured to these parameters. I have a query which on ...
0
votes
2
answers
181
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
0
votes
1
answer
177
views
Could a slow update query be faster if broken down into multiple update statements in Postgres?
Context
I have an UPDATE statement in Postgres which essentially restructures a jsonb column.Here's what it's doing if you're curious:
metadata = (
jsonb_build_object(
'...
- 101
1
vote
0
answers
1k
views
FATAL: the database system is in recovery mode
I am getting the below errors every 2 or 3 days(when high usage of database) and also postmaster.pid file is changed automatically.
my Database version is postgresql 13
Could not get JDBC Connection; ...
- 11
0
votes
1
answer
1k
views
PgPool-II can't find the servers where the databases live (even though the PgPool is on a db server)
PgPool-II 4.3.3 Postgresql 13.9 RHEL 8.6
Postgresql is installed on FOOBARa and FOOBARb, and data is successfully being stream replicated from a to b.
Then I installed PgPool-II on a, and configured ...
- 658
0
votes
1
answer
129
views
PostgreSQL: Generate rows for missing years within range (system year + 9)
I have an PostgreSQL 13.6 table that has rows for certain years:
with data (year_, amount) as (
select 2024, 100 union all
select 2025, 200 union all
select 2025, 300 union all
select 2026, 400 union ...
- 1,497
3
votes
1
answer
255
views
How to "merge" rows along with their foreign many-to-many relations without violating unique constraints?
Fiddle: https://dbfiddle.uk/-JLFuIrN
Table
CREATE TABLE files (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
CREATE TABLE folders (
id bigint GENERATED ALWAYS AS IDENTITY ...
- 286
2
votes
1
answer
5k
views
How to get rid of BufferMapping?
I work with a partition table (200 partitions), in each partition of 5.000.000 records (table with id sequence, index on every partition and total 10 column, 7 int and 3 text).
I start 200 sessions ...
- 121
5
votes
1
answer
816
views
postgresql matching or converting utf-8 variant strings
Postgres 13
I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql.
I am running into ...
- 153
1
vote
1
answer
499
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
0
votes
0
answers
218
views
How Update psql?
For some reason I have PostgreSQL version 13 installed, but my psql is version 10. How can I fix this?
[Tue Dec 27 22:09:13 user3@server_f ~] /usr/bin/psql --version
psql (PostgreSQL) 10.21
- 131
2
votes
1
answer
43
views
Does a multicolumn unique index support my query?
Using PostgreSQL-13, I need advice about key index. I have a table named "employee" and I manage my DB using DBeaver Community Edition (latest).
Fieldname Type
---------- ----
pid ...
- 21
0
votes
2
answers
972
views
Understanding how Nested Loop expected rows works
Postgres 13.5
I have a query where the plan it comes up with creates (via nested loops) huge numbers of rows, spends hours processing them, and then promptly discards them (Rows Removed by Filter: big ...
- 103
1
vote
1
answer
2k
views
How to perform updates on newly created rows within the same transaction?
Fiddle link: https://dbfiddle.uk/EOE627Oa
Tables
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
login text ...
- 286
0
votes
1
answer
995
views
How do you expand a table where one column is a key and the other one is a json array of values into a record set of key/value columns?
Fiddle link: https://dbfiddle.uk/HlySSJ58
Tables and Types
CREATE TYPE profile_init AS (
name text,
description text
);
CREATE TYPE account_init AS (
login text,
password text,
email text,
...
- 286
0
votes
1
answer
1k
views
How do you pass the array of composite type to the argument of an SQL function?
Database example: https://dbfiddle.uk/sERgZPiB
Tables and types
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL,
password text NOT NULL,
email ...
- 286
2
votes
1
answer
643
views
How do you run "SELECT" on the composite type argument of an SQL function?
Example: https://dbfiddle.uk/bCSwVpd9
Database:
CREATE TABLE entities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TYPE entity_id AS (
id bigint
);
Function:
CREATE FUNCTION ...
- 286
1
vote
0
answers
355
views
Oracle Reference Partition migration to Postgresql
We are in process of migrating from Oracle 19c to Postgresql v13. We have a table in Oracle which has numerical primary key and is range partitioned based on a date column. This table has 3 child ...
- 111
1
vote
1
answer
2k
views
Is there any impact on PostgreSQL database to run AUTO VACUUM frequently?
I have 10-15 large table, 5GB to 30GB. And rest of them less than 1GB. Some tables data is huge and where daily 500 000 dead tuples generated. And autovacuum start almost 7 days later when dead tuples ...
0
votes
0
answers
439
views
When and how to run VACUUM FULL for large table at PostgreSQL13?
I have a table "transaction_history" which is near about 35GB. Continuously data is inserting and updating on this table. Since table become larger day by day that's why I archived daily ...
1
vote
1
answer
395
views
How to perform dummy multi-inserts with generate_series()?
Given the tables like this: https://dbfiddle.uk/Z8hOhnYG
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);
CREATE TABLE profiles (
id bigint GENERATED ALWAYS AS ...
- 286
0
votes
0
answers
1k
views
Too many client connection at PostgreSQL
I have 500 connection in my 3 node PostgreSQL cluster. But sometimes I found error like "too many clients already"
Why it show too many client connection? How does it calculate too many ...
1
vote
1
answer
1k
views
pg_cron timezone issue, unable to run at specified time
Unable to run at proper timezone.
I want to run two things
bash scripts
Stored procedure
For both case I found it runs at the wrong time. Instead of 1 AM and 3 AM pg_cron runs at a different time. ...
0
votes
0
answers
118
views
Unable to delete data from dynamically assigned table at PostgreSQL13
Unable to delete from dynamically assigned table data at PostgreSQL13.
Basically I want to archive 10 days older PostgreSQL data to another table. I want to pass days, source table and target table as ...
0
votes
1
answer
49
views
Postgresql showing different behavior on two environment having exactly same configuration
We have two environment staging and production. For Database service we are using AWS RDS(postgresql).
SELECT *
FROM addressfabric_v2_2022_7
WHERE ST_Intersects(ST_GeomFromText('POLYGON((-75.7891846 ...
