Postgresql – Accidentally dropped information_schema postgres
I accidentally ran and committed drop schema information_schema cascade on postgres 15.4 as a DBA. How to recreate the said schema?
PostgreSQL Database PR and DR replication/synchronization
We have a requirement on production environment to run PR and DR as Active/Active. We have four nodes of redhat server. The request will be entertained from both sites means on both sites there should be a server who is…
Postgresql – Query is extremely slow selecting data for last x events for racer
I have a table race_racer that contains time serialized logs for each racer in a race. I am trying to select the lastest record for a given racer in x number of races prior to a date. Basically selecting their…
Postgresql – Skip certain types of nodes in DLL
I'm on Postgres 15.1 and have a node table as follows: CREATE TABLE node ( id TEXT PRIMARY KEY, is_skippable BOOL, prev TEXT REFERENCES node(id), "next" TEXT REFERENCES node(id) ); This table represents a series of doubly linked lists, but…
PostgreSQL: `row_alias is null` and `row_alias is not null` inconsistent return value
I have stumbled upon a peculiar phenomena in an SQL select: select 1 , ui as row_value , ui is null as "ui is null" , ui is not null as "ui is not null" from user_info ui where ui.user_id…
Postgresql – Why current_setting() value is undefined within the exception block
After setting the configuration parameter with set_config() I can read it with current_setting(). But if exception ocured and I try to read value within the exception block it is undefined. do $$declare l_ctx_prm text := 'some_value'; l_dummy numeric; begin raise…
Postgresql – Optimizing simple but slow query with OR condition
Some background: I'm running the following simple select statement on Postgres 15.3 server with 128GB memory, and contrary to my belief it takes ~6 minutes. The statement in involve two relations, big_table_70m with approximately 70M rows and other_table_50m with a…
Docker – Handling Out of Shared Memory Error in PostgreSQL (container) with Sub Partitioned Table of 36B Rows
I have PostgreSql 15.3 running as a docker container. My docker run configuration is -m 512g --memory-swap 512g --shm-size=16g Using this configuration, I loaded 36B rows, taking up about 30T between tables and indexes. I loaded with about 24 parallel…
Postgresql – setting Postgres options during a pg_restore
I am finding it difficult to find the best way to set Postgres options for a pg_restore session only. It would be nice to be able to do this in the environment in which I run pg_restore and not change…
POSTGRESQL 16 – Failed to initialise the database cluster with initdb
Hello i am 2 days stuck trying to install POSTGRE SQL. Today it is on version 16 but already attempted versions 14, 13, 15.... It looks like the bindata folder, which is the db cluster folder, has some kind of…