Questions tagged [postgresql-12]
Specifically for PostgreSQL version 12
237
questions
0
votes
0
answers
8
views
Benefits of using pglogical 2.4.1 over Postgres 12 build-in logical replication?
I'm migrating a Postgres version 12.14 database. I've tested the migration twice: once using the pglogical extension 2.4.1 and once using Postgres' built-in logical replication. It seems like ...
- 129
0
votes
1
answer
38
views
Outliers produce a bad query plan
I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns:
CREATE TABLE document
(
id serial PRIMARY KEY,
organizationid INTEGER,
status_1 ...
1
vote
1
answer
52
views
PostgreSQL 12 generates WAL files even if it is disabled
I am having problems disabling the generation of WAL files in a non-productive database.
Although in the postgresql.conf file I have disabled WAL generation, I see that they are still generated with ...
- 143
0
votes
0
answers
18
views
Verify a postgresql index can be dropped securely
Indexes use resources because they have to be updated when data changes.
How to check if an index can be securely dropped (ie. not used at all)?
This request seems to return the unused indexes, ...
- 235
0
votes
1
answer
30
views
postgres was not the same version as pg_ctl in PostgreSQL 12
As per the log files, I am getting the below errors.
The program "postgres" was found by "/usr/lib/postgresql/12/bin/pg_ctl"
but was not the same version as pg_ctl.
Check your ...
- 101
0
votes
0
answers
11
views
postgresql 12 and archive_cleanup_command ona primary (read/write) PostrgreSQL system vs. the standby (read only) systems
I formulated a similar question with the PGPOOL-II forum, but I think my questions are more related to PostgreSQL itself.
Configuration - 3 nodes cluster (primary and 2 standby systems) with pgpool-II ...
- 1
0
votes
1
answer
97
views
PostgreSQL partition pruning problem
Let us suppose that I have a table d_day containing two columns, id_day, and date, and a table fact that contains two columns, id_day and fact. My table fact is partitioned across id_day.
The request
...
- 11
0
votes
1
answer
47
views
Is this Postgres EXCLUDE constraint equivalent to this UNIQUE constraint?
The constraints
Currently, a table on a Postgres version 12 DB has the following EXCLUDE constraint:
ALTER TABLE ONLY example_table
ADD UNIQUE CONSTRAINT example_table_range_excl EXCLUDE USING gist
(...
- 129
0
votes
0
answers
32
views
On select specific single row showing different result in PostgreSQL
Selecting a specific single row showing different result in PostgreSQL which is very strange. There is a table named Employee. When I search like " SELECT UserId FROM Employee WHERE UserId='3123'&...
0
votes
0
answers
41
views
Backing up the replica using barman in postgres
I'm currently backing up my master postgres db server to barman using rsync.
I want to start backing up from the standby.
Currently the master is archiving wals to barman.
I tried to just have barman ...
- 171
-1
votes
2
answers
154
views
Unable to drop database because of special character?
Unable to drop database whose name is shown below in the screenshot. In front of the database name, there is a plus sign I don't understand how to drop this database. Is there any format to drop this? ...
1
vote
1
answer
129
views
How indexes work in composite key and how changing the order affects the performance in Postgres 12?
CREATE TABLE public.ABC (
it_id TEXT NOT NULL,
item_id varchar(50) NOT NULL,
PRIMARY KEY (it_id,item_id)
);
When I use above query then index scan is applied when i query only by it_id or ...
0
votes
0
answers
215
views
WAL segment has already been removed for logical replication
I have setup logical replication (wal_level=logical) between Postgresql dbs (v12) with replication slot enabled. But I still get "WAL segment has already been removed" error:
ERROR: ...
- 111
1
vote
0
answers
306
views
How to understand why logical replication timeout
I have set up N-1 Postgresql (v12) logical replications: N publisher dbs to 1 subscriber db. And there are replication timeout logs in both pub and sub dbs sometimes:
Subscriber:
2023-04-13 15:41:37....
- 111
0
votes
1
answer
185
views
How can I finish a "RANGE type" partition in PostgreSQL?
I have a 7 TB table in a PostgreSQL 12.3 database. I want to create a "RANGE type" partition on this column. The other thing to note is that this year column is a text field rather than an ...
-1
votes
1
answer
41
views
How to tell Postgresql what tables will be in a transaction?
RDS Postgresql 12.12
CREATE INDEX CONCURRENTLY idx_foo ON foo (f1, f2, f3); is blocked by a stored procedure which only touches tables bar and baz.
BEGIN and START TRANSACTION don't seem to have a ...
- 605
0
votes
0
answers
60
views
i have disabled autovacuum on a table autovacuum process running for that table
I have disabled autovacuum on a table. We are running vacuum manually to speedup process. But I still see autovacuum process running for that table. I am using postgres 12.10
please share your thought
3
votes
1
answer
43
views
Calculate user availability base on schedule table
I have got a table structure as below:
create table schedule_tab
(
schedule_id serial
primary key,
user_id integer not null,
type varchar(64) not null,
...
- 141
0
votes
0
answers
1k
views
Postgres AES encryption with pgcrypto encrypt for text of length >15 characters is not compatible with other platforms
I am in a need of joining 2 tables with stringed identifiers, the key in first table is encrypted with AES ECB and the one in the second table is a raw form of that field in first table.
I am trying ...
- 101
1
vote
0
answers
277
views
PostgreSQL - (declarative) List vs. Range partitioning for DATE's; avoid recheck
PostgreSQL 12.8 - AWS Aurora
Given a large data-set (~10B rows / 5TB size) residing in a partitioned table
CREATE TABLE large_table (
partition_date DATE,
prime_entity INT,
attribute_1 ...
- 21
1
vote
0
answers
42
views
maximum number of physical replication on one server
I have a server with physical replication. It has been working very well till now.
I follow the procedure here : https://linuxhint.com/setup-physical-replication-postgresql/ to create the replication.
...
- 167
0
votes
0
answers
250
views
tuning big partitioned table to make it faster
I have a partitioned table, call it Tabel1, that is based on partition by LIST on ID_X column. This ID_X column is also indexed and it is part of a composite primary key with ID_Y column.
I do this
...
- 167
1
vote
0
answers
1k
views
pg_restore error could not execute query: ERROR: relation does not exist
I am trying to restore, on the same server but different database, a specific table from a dump file using pg_restore. This table is located on public schema and I want to restore it on public schema ...
- 167
0
votes
1
answer
109
views
CRONTAB fail to work
I have cronjob suddenly fail to work. I have pgdump to execute the following command:
pg_dump -h 127.0.0.1 -Fc --dbname=postgresql://postgres:[email protected]:5432/db_XP -f /b ...
- 167
2
votes
1
answer
4k
views
Which command to use to restart postgresql 12 service
I am about to restart postgresql 12 service running on Ubuntu 18.
First, I check it's status : sudo service postgresql status. Its says :
postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/...
- 167
0
votes
0
answers
57
views
How postgresql works with memory?
In one recent research, i fails to understand of how PostgreSQL work with physical memory when executing a queries. Perhaps, someone had experience in the analysis of this issue.
All tests was acted ...
0
votes
1
answer
542
views
find error log for streaming replication
Recently the streaming replication fails after I restart the primary. I can not find the record using select * from pg_stat_replication
Now, I want to see it from the system log file at /var/log/...
- 167
2
votes
1
answer
707
views
Restart master in physical replication
I have 2 servers involving master-slave replication (physical replication).
I see the replication using select * from pg_replication_slots; there is a record saying :
user : rep_user`
application : ...
- 167
0
votes
1
answer
659
views
VACUUM takes very long and can not be cancelled
I just issue this command to a table and its been an 2 hours but not finished: vacuum verbose people_data
I can not even cancel the process because I can not see the PID using this query:
SELECT pid,
...
- 167
-1
votes
1
answer
429
views
Copy between database using pg_dump and SSH
I want to copy a tabel from one server to another using pg_dump and SSH.
Here is the script that I run from the source database :
pg_dump -h 127.0.0.1 -Fc --dbname=postgresql://postgres:[email protected]....
- 167
0
votes
0
answers
369
views
Error about UUID UUID_GENERATE_V
After migrating to a new db server, I find these errors :
[ERR] 9> create or replace function "public"."uuid_generate_v1mc"()
returns "pg_catalog"."uuid" AS '...
- 167
0
votes
1
answer
306
views
Backup master - slave replication
On a master-slave replication setup (1 master with 2 slaves. the slaves are hot standby in case the master fails).
How can I do a backup under normal circumstances? Are all nodes are backed up ...
- 167
1
vote
0
answers
178
views
Multi master with some slaves vs single master with some slaves
I need some advice on architecture.
I have a national database that relies on postgres and some architectural design that involves orchestrating several components, very much similar to the picture ...
- 167
0
votes
1
answer
76
views
WALs increase for some reason
This is new to me. I have been searching the net but no result.
I have a server, Server A, that has been running for a few weeks, it is a new server. I set up FDW for this server to access all tables ...
- 167
0
votes
0
answers
3k
views
best way to backup and restore partitioned table
I have a table with 600 partitions and 68 millions of rows.
I successfully run backup using
pg_dump -h 127.0.0.1 -Fc --dbname=postgresql://postgres:[email protected]:5432/postgres -t "...
- 167
0
votes
1
answer
291
views
AWS RDS database uses more space after pg_dump/pg_restore
I've got an RDS Postgresql 12 instance that allocated 11,000,000 MB of io1 disk space, and there's 7,300,000 MB of Free Storage Space.
That means the database is only using 11TB - 7.3TB = 3.7TB of ...
- 605
0
votes
1
answer
1k
views
postgresql 12 streaming replication not working
I want to set up streaming replication on the Slave.
On the Primary Server (Server A. Ubuntu 18.04; Postgres 12) :
sudo nano /etc/postgresql/12/main/postgresql.conf
Set listen_addresses = '*'
The ...
- 167
0
votes
1
answer
132
views
streaming replication consideration
I am asking for guidance.
I want to set up a replication between 3 servers : 1 master A, 2 slaves B and C. The master streams to 2 slaves. A to B is logical replication, whereas A to C is streaming ...
- 167
2
votes
1
answer
3k
views
postgresql database cluster vs one server with many databases
Forgive my stupid question.
I want to know what are the differences between postgreSQL database cluster (1 server, N databases, N ports) vs one server hosting multiple databases (1 server, N databases,...
- 167
4
votes
2
answers
4k
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
38
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
908
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 ...
- 167
1
vote
1
answer
443
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
2
answers
311
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 ...
- 167
-1
votes
1
answer
142
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
916
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]{...
- 167
0
votes
1
answer
422
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 ...
- 167
1
vote
0
answers
433
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 ...
- 167
0
votes
0
answers
694
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 ...
- 167
1
vote
1
answer
336
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....
- 167