Questions tagged [postgresql-12]
Specifically for PostgreSQL version 12
213
questions
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 ...
- 1
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 ...
- 185
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 ...
- 101
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
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 ...