Questions tagged [postgresql-12]
Specifically for PostgreSQL version 12
174
questions
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 ...
- 1
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 ...
- 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 ...
- 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 ...
- 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 &...
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 ...
- 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 ...
- 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 ...
- 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.
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 ...
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 ...
- 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)...
- 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 ...
- 1
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/...
- 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
...
- 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 ...
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,...
- 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:/...
- 1
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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.
...
- 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&...
- 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/...
- 103
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 ...
- 101
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 ...
- 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(...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 121
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:
...
- 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)
...
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 ...
- 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 ...
- 113
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)...
- 131
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 ...
- 21
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 ...
- 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 ...
- 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 ...
- 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 ...
- 21
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....
- 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 ...
- 111
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 ...
