Questions tagged [postgresql-13]

Specifically for PostgreSQL version 13

Filter by
Sorted by
Tagged with
0 votes
0 answers
11 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
12 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
  • 578
0 votes
0 answers
16 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
20 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
30 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
-1 votes
0 answers
27 views

invalid memory alloc request size at postgresql pg_dump

Unable to backup database in PostgreSQL because of data corruption. I found it is only one table, rest of the tables found ok. Most case I found this error happen is at the large table. pg_dump: error:...
Sheikh Wasiu Al Hasib's user avatar
2 votes
0 answers
34 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
8 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
45 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
30 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
93 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
133 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
42 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
0 votes
0 answers
23 views

Postgres - how to manage losing a tablespace

I have a postgres 13 database installed on an SSD drive. 90% of the tables and indexes are in a tablespace on the SSD drive. I also have a 2nd hard disk (magnetic HDD) which contains a 2nd tablespace, ...
ConanTheGerbil's user avatar
3 votes
1 answer
84 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
58 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
30 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
61 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
0 votes
0 answers
46 views

Cent OS update to postgresql13-contrib-13.9 causes error postgres_fdw.so undefined symbol: get_rel_all_updated_cols

I am running CentOS 7.9 and the latest OS updates caused and issue when updating the library postgresql13-contrib-13.9-1. After that update when attempting to create postgres_fdw or use an existing ...
Troy Meyerink's user avatar
1 vote
0 answers
50 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
70 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
0 votes
1 answer
99 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
219 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
0 answers
559 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
  • 606
0 votes
1 answer
53 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,437
3 votes
1 answer
156 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
2k 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
0 votes
0 answers
41 views

Postgres DB slowness issue and idle sessions higher than active sessions

Facing postgres db slowness issue setted max db connection limit 400 but connection not exceeding more than 230 also could see idle sessions higher than active sessions.Any suggestions for tuning db ...
Gopi's user avatar
  • 1
5 votes
1 answer
432 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
216 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
69 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
  • 121
2 votes
1 answer
42 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
498 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
0 votes
0 answers
23 views

How can I trace currently running process and activity in PostgreSQL?

I am using 3 node async PostgreSQL cluster. Where sometimes I found that load average of primary node is crossed 100 where threshold is 16, where standby node load average is lower. I am too confused ...
Sheikh Wasiu Al Hasib's user avatar
1 vote
1 answer
823 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
675 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
614 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
313 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
218 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
0 votes
1 answer
601 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
221 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
230 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
605 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
465 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 run at different time. Instead of 1 AM and 3 AM pg_corn runs another time. Why it is ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
0 answers
95 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
23 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
615 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
  • 11
1 vote
1 answer
940 views

How to recover Postgresq data from $PGDATA/base files after system crash

I was working on my Ubuntu machine and left it on to grab food. Upon returning, I found it had shut itself down. It was not power failture because I have a home backup system and lights never go off ...
TheRealChx101's user avatar
0 votes
0 answers
448 views

Postgres - Debugging slow query

we are using postgres 13.7 My company runs a fairly complicated analytical query, unfortunately, I'm not allowed to disclose the query here. The explain plan has around 30 nodes and joins about 10 ...
sha's user avatar
  • 137