Skip to main content

Questions tagged [postgresql-13]

Specifically for PostgreSQL version 13

Filter by
Sorted by
Tagged with
1 vote
0 answers
29 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 ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
1 answer
67 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 ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
26 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 ...
rahlcarwash's user avatar
0 votes
1 answer
167 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=...
Mapashito's user avatar
0 votes
0 answers
66 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" ...
Minja 's user avatar
0 votes
1 answer
65 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=...
Sapta's user avatar
  • 11
1 vote
1 answer
147 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 ...
blindsnowmobile'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

Dump postgres table into a directory structure?

I have a table "templates" with about 150 records. Each record has various attributes, but the one of greatest interest is the "template" column, which is a text field that ...
Wildcard's user avatar
  • 588
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
103 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
0 votes
1 answer
73 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 ...
Sheikh Wasiu Al Hasib's user avatar
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
0 votes
0 answers
32 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'&...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
82 views

Postgres 13: Update column when MV refresh is complete

I have what feels like a very simple operation I wish to perform, but cannot find any guidance. I have an application that allows users to upload a series of files with records, which get processed ...
Kevin H's user avatar
0 votes
1 answer
640 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 ...
Biller Builder's user avatar
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 ...
Oleg Karoza's user avatar
-1 votes
2 answers
265 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
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 ...
Vitalii Pro's user avatar
0 votes
0 answers
135 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: ...
Primordial_Hamster's user avatar
4 votes
1 answer
399 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 ...
Ben Millwood's user avatar
0 votes
1 answer
280 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 ...
ConanTheGerbil's user avatar
1 vote
0 answers
51 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 ...
Biller Builder's user avatar
1 vote
1 answer
116 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(...
Jeff Clough's user avatar
1 vote
0 answers
71 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 ...
Primordial_Hamster's user avatar
0 votes
2 answers
129 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
0 votes
1 answer
157 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( '...
andy's user avatar
  • 101
1 vote
0 answers
641 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; ...
Manoj's user avatar
  • 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 ...
RonJohn's user avatar
  • 647
0 votes
1 answer
99 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 ...
User1974's user avatar
  • 1,477
3 votes
1 answer
221 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 ...
Biller Builder's user avatar
2 votes
1 answer
4k 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 ...
lampa's user avatar
  • 121
5 votes
1 answer
688 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 ...
unomi's user avatar
  • 153
1 vote
1 answer
374 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. ...
Cowabunga's user avatar
  • 135
0 votes
0 answers
183 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
Rich_F's user avatar
  • 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 ...
Dev 's user avatar
  • 21
0 votes
2 answers
874 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 ...
Ryley's user avatar
  • 103
1 vote
1 answer
1k 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 ...
Biller Builder's user avatar
0 votes
1 answer
929 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, ...
Biller Builder's user avatar
0 votes
1 answer
888 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 ...
Biller Builder's user avatar
2 votes
1 answer
539 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 ...
Biller Builder's user avatar
1 vote
0 answers
330 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 ...
Jacob's user avatar
  • 111
1 vote
1 answer
1k 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 ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
398 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 ...
Sheikh Wasiu Al Hasib's user avatar
1 vote
1 answer
363 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 ...
Biller Builder's user avatar
0 votes
0 answers
976 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 ...
Sheikh Wasiu Al Hasib's user avatar
1 vote
1 answer
927 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. ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
113 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 ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
1 answer
43 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 ...
Mohit Sethi's user avatar
1 vote
1 answer
1k views

Got message: FATAL: expected SASL response, got message type 88

I have a strange situation. A small program working fine till these days (about half a year). The program (NodeJS) is creating a local connection to DB: 1st: creating new connection to local db. 2nd: ...
noszone's user avatar
  • 111