2022 Developer Survey is open! Take survey.

Questions tagged [postgresql-12]

Specifically for PostgreSQL version 12

Filter by
Sorted by
Tagged with
0 votes
0 answers
4 views

Is wal_keep_segments required with streaming replication when archive and restore commands are set?

I have a 2 node postresql(12.9) cluster with streaming replication enabled. I have also set the archive and restore commands which I verified is working as expected. No replication slots or ...
user avatar
1 vote
0 answers
26 views

Put "SET ROLE" or "SET SESSION AUTHORIZATION" behind a password

I want an role to be able to use SET ROLE or SET SESSION AUTHORIZATION to switch to a more privileged role. However, this action should be behind an password. This password is not necessarily the ...
user avatar
  • 197
3 votes
1 answer
67 views

Change the definition of an Index with Expression without dropping/recreating the index

I'm in a peculiar situation: I have multiple indexes with expressions that use a function. I want to switch them to a different function, where I can guarantee the function behaves the same (so the ...
user avatar
  • 197
0 votes
1 answer
29 views

Postgres pg_wals full whats best practice for removal of old WALs that were not removed automatically

We had an outage on our postgresql-12 server at the weekend, we have pg_wals that reside on a partition that had become full. This then crashed the server due to the space running out. We then moved ...
user avatar
  • 143
0 votes
0 answers
26 views

Why pgbouncer authenticate with any plain text password?

I have set user information in userlist.txt file for client authentication. But it is working with any password whether it is available in database or not. Suppose username is admin and password is &...
user avatar
0 votes
1 answer
10 views

What can be used to convert a long date into just days so format YYYY-MM-DD HH:MM:SS to just YYYY-MM-DD in Postgresql 12

I have a bunch of dates which I am trying to use for archiving. In postgresql 12 I am trying to get a whole bunch of row data into a more simplistic format for processing. Currently it looks as ...
user avatar
  • 143
1 vote
2 answers
49 views

Get postgres partitioned table structure by a select

I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres. Something like \d <table>. All I find so far is by doing pgdump. db=> \d ...
user avatar
  • 383
0 votes
0 answers
29 views

Postgres 12 PG_Squeeze dropping Kafka slots whilst running ad-hoc, is this normal behaviour?

I am using postgresql 12 and also utilize Kafka, we have 4 slots allocated via our postgresql.conf max_replication_slots = 4 However we only use 3 slots for Kafka for each connector, when I run select ...
user avatar
  • 143
0 votes
1 answer
15 views

Postgresql: Why incremental size is negative at barman backup details?

Here my backup method is rsync and reuse_backup=link. Everything doing well, but why incremental size showing negative value. Necessary details given below.
user avatar
1 vote
0 answers
14 views

How to activate barman_slot in PostgreSQL?

I am using PostgreSQL cluster with Barman backup tools. I just recovered one of my backups. After recover, I found barman slot is inactive. Later, I deleted that inactive barman slot and created ...
user avatar
1 vote
1 answer
58 views

Postgres CREATE DATABASE Not Working

I'm on a Mac using Monterey. It's a Mac M1 which had a bit of an issue using homebrew to install postgres when I first started working on this machine. I moved to the GUI postgres.app which just ...
user avatar
  • 121
0 votes
1 answer
455 views

Postgres function for NVL

I'm trying to have NVL function in postgres. create or replace function nvl (anyelement, anyelement) returns anyelement language sql as $$ select coalesce(cast( $1 as decimal), cast( $2 as decimal)...
user avatar
  • 383
0 votes
1 answer
70 views

Postgres autovacuum keeps transaction id's around to 10% limit, causing aggressive vacuuming to trigger now and then which locks tables

We run multiple Postgres clusters in our infrastructure with 10-30 DB per cluster, ranging from 10 GB size to 1TB in size. I have recently noticed that on all our clusters the wraparound id is always ...
user avatar
1 vote
1 answer
122 views

Postgresql 12 - ddluser and dmluser for same schema dml permission denied on tables [duplicate]

I have created a postgres 12 instance and executed the below command sequence: psql --host localhost -U postgres -c "CREATE DATABASE mydb" psql --host localhost -U postgres -d mydb -f /tmp/...
user avatar
  • 111
0 votes
0 answers
20 views

How grant PRIVILEGES ON FUNCTIONS of a schema in postgresql to ALL the users past or future?

I have a schema "libs" where I put globally several functions that must be executed for all users present and future for the schemas: -libs -public -tenant1 user: user1 -tenant2 user: user2 ...
user avatar
  • 143
-1 votes
1 answer
245 views

To do encryption for postgresql db backup

Is this possible when someone takes backup of my postgres db it should be in encrypted form(i.e.if root user will access from command line(pg_dump) or from any tool). and how we can decrypt that ...
user avatar
0 votes
1 answer
262 views

Use pg_cron to run VACUUM on multiple tables

Reading the pg_cron documentation I saw that the examples only execute a command when scheduling a task. In a StackOverflow post I saw that a user tried to run multiple VACUUM when scheduling the task,...
user avatar
  • 285
0 votes
0 answers
52 views

pg_ctl error when using pg_upgrade

We are attempting to upgrade Postgres 10.10 to 12.4 using pg_upgrade on a Windows 2016 Server machine using the command "C:/Program Files/PostgreSQL/12/bin/pg_upgrade.exe” --old-datadir "C:/...
user avatar
0 votes
1 answer
63 views

When to run Vacuum Full

Reading the PostgreSQL documentation there is an excerpt that mentions that you should run a VACUUM FULL when there are many row lines. What percentage of bloat is considered necessary to perform ...
user avatar
  • 285
0 votes
1 answer
32 views

Restricted Functions for Use with Sessions in PostgreSQL + PgBouncer

I've always used PgBouncer in session mode, but its performance isn't so good, as PHP scripts that run for a long time end up with a long session connected to the database (even for minutes), without ...
user avatar
  • 285
1 vote
0 answers
34 views

How to speed up GroupAggregate

https://explain.depesz.com/s/I2IN I have a query against a TimescaleDB 1.7 hypertable on Postgresql 12. Lots of joins to other table. When I do the grouping it seems to take a lot of time, I switched ...
user avatar
  • 179
1 vote
3 answers
1k views

High cost in PostgreSQL query

I have a table that has more than 10.000.000 records, and I'm creating a query that returns about 4436 records. It just so happens that it gives me the impression that the query cost to get to the ...
user avatar
  • 285
1 vote
2 answers
308 views

PostgreSQL: INSERT in a sequence and increment all values above the INSERT (make "room" for the INSERT) [duplicate]

I have a table that requires storing my item in a specific position, and the user can "move" the items' positions to "make room" for the new item. Here is what the table looks ...
user avatar
  • 121
1 vote
2 answers
208 views

AWS Aurora Postgres 12 Setting Collation

I posted this on stack overflow as well, but didn't get much traction over there. If I am not able to get some thoughts on here, then I will probably open a support ticket with AWS. I am trying to set ...
user avatar
0 votes
1 answer
101 views

Postgres custom type with constraints

I was wondering if it were possible (and/or desirable) to create custom types with constraints on the values. Say I have a custom type for height/width/length: CREATE TYPE dimensions AS (w float, h ...
user avatar
  • 103
0 votes
1 answer
18 views

postgresql-12 Archiving not responding properly

A newbie here and asking for your help. I did the the archiving settings for WAL LEVEL = replica Archiving Mode = ON, mentioned the location for backup: /home/beekay/archive for online backup for ...
user avatar
  • 3
0 votes
1 answer
162 views

Incompatible function reference when upgrading to Postgres 13

I want to upgrade a Postgres 9.6 database on CloudSQL (GCP) up to version 13. As SQL export/import is the only supported upgrade path, I've exported the PG9.6 database and attempted to import it into ...
user avatar
  • 155
0 votes
1 answer
50 views

Log occurrences when Row Level Security Policies filter out rows

I'm currently working on implementing a 2 layer security, meaning we have orm query filtering in our application code as well as in our database. In the database I'm using Postgre Row Level Security. ...
user avatar
  • 197
2 votes
2 answers
204 views

Why is this SQL query with UNION significantly faster than the same query without?

I'm trying to optimize a query, which never completes on Postgres 12.7. It takes hours, even days, make the CPU goes 100%, and never returns: SELECT "id", "counter", "item_id&...
user avatar
  • 123
0 votes
1 answer
26 views

Postgresql log uploading service on Ubuntu Linux

I am trying to create a systemctl service to collect + upload logs for Postgresql 12 on Ubuntu Linux. However, I am expert at none of the two. While I have set up the server modifying /etc/postgresql/...
user avatar
0 votes
0 answers
187 views

How can I exclude Materialised Views from a pg_dump

I'm doing a pg_dump of an AWS RDS database so I can use it for local development, but there are some large tables I want to exclude. I've successfully removed them using the --exclude-table=blah* flag ...
user avatar
2 votes
1 answer
61 views

Exclude tables from shared_buffers in postgres

Is there a way to exclude tables from being added to the shared_buffers? I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. There ...
user avatar
  • 121
0 votes
0 answers
131 views

RDS PostgreSQL 12.5 bind slow

I have a PostgreSQL 12.5 db.r5.large instance on AWS RDS (16GB of RAM, 2 vCPU) While I'm troubleshooting some slow queries, I have seen these in log quite often: 2021-07-28 12:31:14 UTC:172.21.12.81(...
user avatar
  • 1
0 votes
1 answer
27 views

Need to Alter PostgreSQL Startup on CentOS 7?

I am planning a move of Postgres' data directory from one disk to another on a CentOS 7 server. All of the data and configuration files reside in /var/lib/pgsql/12/data/. I have previously migrated ...
user avatar
  • 37
0 votes
1 answer
115 views

Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english. I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on ...
user avatar
0 votes
0 answers
65 views

What is the alternative of PL/Java for PostgreSQL 11 and 12?

Understand from: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/11/EDB_Postgres_Advanced_Server_Guide.1.80.html that PL/Java is deprecated in Advanced ...
user avatar
  • 1
0 votes
0 answers
63 views

PostgreSQL - intermittent slowness (very simple queries)

I'm experiencing intermittent slowness when (simple) queries are executed (be it SELECT or UPDATE, I haven't checked inserts/deletes). For example, to execute the query below, usually, it takes less ...
user avatar
0 votes
0 answers
104 views

BUCARDO - PL/Perl function "validate_goat" at /usr/local/bin/bucardo

I'm new to Bucardo, I have been trying to replicate PostgreSQL-12 using Bucardo with no success due to the errors. DBD::Pg::st execute failed: ERROR: DBI connect('dbname=test1','bucardo',...) failed: ...
user avatar
  • 101
0 votes
1 answer
46 views

removing dates from generate_Series in postgreSQL

im trying to remove some dates from generate_Series in postgres liie this:- select min(dt)+'1 day' from generate_series(date_trunc('day',now()) , date_trunc('day',now()+'30 day'),'1 day') as s(dt) ...
user avatar
0 votes
0 answers
130 views

Postgres jsonb field: remove an element from an array by the element id

Figure a Postgres 12.5 jsonb field containing the following: [ {id:1, name: 'one'}, {id:2, name: 'two'}, {id:3, name: 'three'} ] How to write the request that will match: "drop the object ...
user avatar
  • 197
1 vote
1 answer
171 views

Adding a WHERE clause slows down query

We run a database on PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1, 64-bit We are trying to get the top rows of a result set where the most recent entry has a ...
user avatar
12 votes
3 answers
3k views

Why is an OR statement slower than UNION?

Database version: PostgreSQL 12.6 I have a table with 600,000 records. The table has the columns: name (varchar) location_type (int) enum values: (1,2,3) ancestry (varchar) Indexes: ancestry (btree)...
user avatar
2 votes
0 answers
44 views

PostgreSQL Replication disconnects after deleting DB

I've got a PostgreSQL Replication set up using Repmgr. Replication works fine, until one of the databases is deleted on the master node. Standby replica throws off 'WAL cannot be found' error and ...
user avatar
2 votes
1 answer
171 views

Canonical replacement for OIDs in PostgreSQL 12 onwards

I have an application which relies totally on PostgreSQL OIDs for its operation. It's currently at PostgreSQL 9.6.1, but I'm looking to upgrade to 13. Is there a recommended/standard mechanism (using ...
user avatar
  • 123
0 votes
1 answer
63 views

Is it possible to exclude a database from WAL and basebackup?

I'm planning to add a GIS database to an existing PostgreSQL 12 cluster with multiple databases, streaming (hot standby) replication and WAL archiving. Once created the new database will never be ...
user avatar
  • 246
0 votes
1 answer
27 views

Exclusion constraint for text values [closed]

I have two tables. A client table and a vehicle table, the two are linked in a unit lookup table. My constraint is that a vehicle cannot belong to a client if it already is associated with another ...
user avatar
  • 179
0 votes
0 answers
163 views

PostgreSQL remote connection failing

I've recently installed PostgreSQL on my PC as a requirement for my DB class, and as a practice, I decided to try and setup a remote connection for it so I can access it via online services. But after ...
user avatar
1 vote
1 answer
46 views

Different (much worse) query plan when using one view

I am running the following query: SELECT m.entityid, m.alias::text AS alias, m.measurement_ts, m.volume_m3::double precision AS volume_m3, m.pressure_mca, m.battery_volt, (m....
user avatar
  • 145
1 vote
1 answer
209 views

Postgres database insert become slow after 10 days, and then it needs a full vacuum

We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and ...
user avatar
0 votes
1 answer
102 views

In postgresql.conf, does the order of the listed parameters matter?

I'm relatively new to the DBA field and I have read the official documentation/guides on this topic, but I apologize if my question seems weird. I have deployed PostgreSQL 12 on my AWS instance and ...
user avatar