Questions tagged [postgresql-12]

Specifically for PostgreSQL version 12

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

Why the second publication always in "potential" state and always fail

I created 2 publications. One of them always fails after several days of running well. Now, This is the 5th time it fails. This is how I create them : First, I create replication slots, one for each ...
  • 43
2 votes
2 answers
44 views

Optimizing select count result in Postgresql

I'm trying to optimize a table containing 80million+ rows. It takes 20+ minutes to get the row count results. I've tried clustering, vacuum full and reindex but the performance didn't improve. What do ...
0 votes
0 answers
27 views

List all users and their roles - runs too slow

I am using this query to list out all users and their parent roles using Postgres 12.8 running on RDS. select distinct on (user_role_name) pgr.oid as user_role_id, pgr.rolname as user_role_name, pgr....
  • 101
0 votes
1 answer
39 views

pg_dump can not create directory after increase directory space

I recently run pg_dump to backup database using this command using user postgres : time pg_dump -j 4 -Fd -Z 3 db_profile -f /backup-nfs/ Previously, the size of backup-nfs was 100 G. The pg_dump ...
  • 43
0 votes
1 answer
25 views

How to access OLD/NEW vaules in a trigger dynamically?

I have a code in the DB trigger like _association_id bigint; if TG_OP = 'INSERT' then select concat('NEW.',association_field_id) INTO _association_id from association_audit_configs where ...
0 votes
1 answer
11 views

Grouping user into Role to access FDW table

Recently I setup Forward Data Wrapper. Here is what I do: CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.125.1', port '5432', dbname 'template1 '); IMPORT ...
  • 43
-1 votes
1 answer
18 views

How to upgrade from psql 9.5 to 12 with short downtime and no corruption

We have two databases in two different servers with PostgreSQL 9.5 One database size is 800GB and the other is 1.8 TB. What would be the most efficient and safe way of upgrading both clusters from ...
0 votes
1 answer
28 views

understanding pg_wal and archiving

So I inherited this database. At the very beginning, there were already some files on pg_wal. Then, after some weeks, I do count SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{...
  • 43
0 votes
1 answer
22 views

pg_dump dumping files to ROOT

I run pg_dump last nite to backup some 111Gb database : time pg_dump -j 5 -Fd -Z 1 db_profans -f /backup-nfs/ I monitor the size growth But, suddenly this morning, the database is down. I check the ...
  • 43
0 votes
0 answers
14 views

sync_state pg_stat_replication

I created 2 tables replication with these procedure : Create publication : CREATE PUBLICATION pub_1 FOR TABLE table_1, table_2; Create subscription for table 1 : CREATE SUBSCRIPTION sub_1 CONNECTION ...
  • 43
0 votes
0 answers
16 views

pg_basebackup for one database only

I have a server that is hosting 9 databases at once. Only 1 of them is important. I do this query to find their cumulative size : SELECT pg_size_pretty( SUM(pg_database_size(datname))::bigint ) As ...
  • 43
0 votes
1 answer
16 views

pg_start_backup vs pg_basebackup

I read stuff about pg_start_backup from here and pg_base_backup here. I got confused. So, out of the 2 methods, which one is generally better ? Seems that pg_start_backup is better than pg_base_backup....
  • 43
0 votes
1 answer
27 views

which pg_wal files can I delete safely?

After I issue pg_basebackup -D /var/lib/postgresql/basebackup -l "backup main $(hostname) on $(date)" -P, i see there are 2 ".backup' files in pg_wal directory. See it . This is what ...
  • 43
0 votes
1 answer
47 views

Delete WAL archive files

I have around 200Gb of WAL archives. Can I just delete them all without considering any database restoration? I am just asking whether it is safe to delete them all or at least 50% of them. I read a ...
  • 43
0 votes
1 answer
55 views

could not write to file "pg_wal/xlogtemp.2399" no space left on device

I am generating dummy data with this query : INSERT INTO tbltest1 (id, kol1, kol2) SELECT generate_series(1,10000), substr(md5(random()::text), 1, 25), substr(md5(random()::text), 1, 45) FROM ...
  • 43
2 votes
1 answer
31 views

How to develop backup strategy of many databases in 1 server

I have a server with many databases...it contains like 20 databases. Some of them has 16 millions of rows, but not that many. There are usually very low to no activity at night but not that busy ...
  • 43
0 votes
0 answers
11 views

Why there are no WAL files formed in subscriber

I have logical replication set between 2 servers. The replication works fine, all tables have the same data. But then I notice that the subscriber WAL files do not increase in number, it is always 128....
  • 43
0 votes
0 answers
91 views

How to activate replication slot

I have 2 logical replication slots in Postgres 12. Suddenly one of them (100 fields, 16K records) failed couple of days ago. Obviously, the replication slot is inactive. Then I check a couple of ...
  • 43
0 votes
1 answer
20 views

Archive failed but new WAL never generated

Currently I am experiencing a weird situation. Archive failed_count soaring from 0 to 2475. Here some parameter that I check : select * from pg_stat_archiver; archived_count 3567; last_archived_time ...
  • 43
0 votes
0 answers
111 views

pg_dump permission denied for relation error for owner user

I am attempting to dump out a PostgreSQL database with pg_dump: /usr/bin/pg_dump -f /home/crmpicco/rfc_754622.sql -F c -x -E UTF8 -b --disable-triggers -U root -h tillman.ff1lvb.us-west-2.rds....
  • 145
0 votes
1 answer
68 views

logical replication initial phase

I am about doing replication for 16 millions record. Just want to ask about the initial phase of logical replication : Do I need to copy the initial table from publisher to subscriber first ? Or is ...
  • 43
0 votes
1 answer
35 views

How are HashAggregate row estimates calculated?

I have the following query: SELECT foo.* FROM foo WHERE foo."id" IN ((SELECT bar."id" FROM bar WHERE bar.user_id IN ( 100, 102 )) ...
  • 207
0 votes
0 answers
29 views

The best way to initialize table before logical replication

I am in the middle of setting up logical replication between 2 physical servers. It involves 2 tables from the master with millions of rows. One table got 15 millions, and another one is 7 millions. ...
  • 43
0 votes
0 answers
41 views

Postgres archiving not running

I am using logical replication. Currently I am using stored procedure to pump 3K data per hour to see if the WAL files increase. It does increase by 1 file every 12k records. But the WAL archiving is ...
  • 43
0 votes
1 answer
37 views

slave on logical replication does not get update real time

I successfully started logical replication on a testing environment. I generate insert on the master for some 60K records using procedure. While the insert process is running, i monitor the slave ...
  • 43
0 votes
0 answers
35 views

concurrency in running query

I am using Postgres12 and DBeaver. I insert thousands of data using a procedure like this : do $$ declare ctr_max_ID integer; ctr_loop Integer := 1; begin loop exit when ctr_loop = ...
  • 43
4 votes
1 answer
143 views

How to pass the current row to Generated Column function in Postgres?

Postgres 12+ supports generated columns: https://www.postgresql.org/docs/current/ddl-generated-columns.html From the docs, the syntax seems limited - it forces one to explicitly name the columns on ...
0 votes
1 answer
163 views

AWS RDS Postgresql and max_worker_processes

AWS RDS Postgresql 12.10 According to https://www.enterprisedb.com/postgres-tutorials/comprehensive-guide-how-tune-database-parameters-and-configuration-postgresql, max_worker_processes Set this to ...
  • 506
0 votes
1 answer
92 views

How to get slow query on PostgreSQL?

Basically I want to log those query which is taking more than 300ms to execute. I don't want to log those query which is less than 300ms. But at PostgreSQL showing all type of log whether it is below ...
0 votes
0 answers
21 views

Logical Replication and Archiving

Is it necessary to turn on archiving when replication is being used ? I notice that replication generates WAL files. Yes it can grow large, but what if hard disk space is not a problem ? I'm using ...
  • 43
0 votes
0 answers
14 views

postgres archiver not working

I have a problem with archiving. Here are some parameters : `SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'`; -- 4048 files checkpoint_completion_target : -- 0.9 ...
  • 43
0 votes
1 answer
154 views

Monitor logical replication using LSN

How do I monitor a logical replication in Postgresql 12 by seeing the lsn ? What I have done : Inspect some colomns on Publisher and Subscriber. Publisher side : select * from pg_stat_replication; -- ...
  • 43
0 votes
0 answers
16 views

Limiting subscription to just one table only

Icreate a Publication using : CREATE PUBLICATION TEST_publication2 FOR TABLE public.Table1; Then I create a Subscription using this CREATE SUBSCRIPTION test_mysub CONNECTION 'host=10.100.5.55 port=...
  • 43
0 votes
1 answer
98 views

WAL files management

I want to have advice about WAL management. I am expecting a large numbers of wal files (the database is very large and it has like 30 - 40 millions of records in totals) since I am setting up a ...
  • 43
0 votes
0 answers
28 views

FAIL creating subscription postgresql

I want to setup logical replication between 2 PGs 12 on different servers, one being the Publisher and the other Subscriber. On the Publisher side, I have this settings as follows: hot_standby = ON ...
  • 43
0 votes
0 answers
63 views

FQDN selection in "pg_hba.conf" does not work : DETAIL: Client IP address resolved forward lookup does not match

I want people connection from an IP associated with myLAN.lan to be able to connect so I have this configuration : $ grep listen_addresses /etc/postgresql/12/main/postgresql.conf listen_addresses = '*'...
  • 101
0 votes
2 answers
94 views

Postgres: How to find where database size growth is coming from

We have a PostgreSQL database that has grown significantly in size recently, from about 340GB to 571GB over the past couple of months, and we are not tracking any significant change in user behavior ...
0 votes
0 answers
68 views

Where, physically, to set the wal_level?

I am setting up replication for a table between 2 Postgresql 12 servers. What I have done : Enable wal_level = logical on the Publisher Create Publisher : '''CREATE PUBLICATION TEST_publication2 FOR ...
  • 43
0 votes
1 answer
47 views

POSTGRESQL table replication between 2 servers

I want to to replicate A TABLE from one server A to another B so that A and B always in-sync. This table on server A has like 4 - 5 million rows, whereas the table B is empty. What I want to do is ...
  • 43
1 vote
0 answers
35 views

How to push a "lower()"-like operator through the postgresql security barrier?

I have a set of tables protected by RLS, and those tables contain TEXT columns. I want to be able to query on these columns case-independently. Previously, this was done by simply querying á la lower(...
  • 207
0 votes
1 answer
175 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 vote
0 answers
108 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 ...
  • 207
3 votes
1 answer
76 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 ...
  • 207
0 votes
1 answer
145 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 ...
  • 195
0 votes
0 answers
102 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
63 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 ...
  • 195
1 vote
2 answers
636 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 ...
  • 437
0 votes
0 answers
44 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 ...
  • 195
0 votes
1 answer
23 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
33 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
2 3 4 5