Questions tagged [postgresql-12]

Specifically for PostgreSQL version 12

Filter by
Sorted by
Tagged with
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 ...
trouble_bucket's user avatar
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 ...
Bunny Boss's user avatar
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 ...
lk2_89's user avatar
  • 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, ...
Slim's user avatar
  • 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 ...
Anubhav Ranjan's user avatar
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 ...
Henri 's user avatar
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 ...
Doe Jowns's user avatar
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 (...
trouble_bucket's user avatar
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'&...
Sheikh Wasiu Al Hasib's user avatar
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 ...
Rubinsh's user avatar
  • 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? ...
Sheikh Wasiu Al Hasib's user avatar
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 ...
Utkal keshari Sahu's user avatar
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: ...
Hanwei Tang's user avatar
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....
Hanwei Tang's user avatar
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 ...
RubyRedGrapefruit's user avatar
-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 ...
RonJohn's user avatar
  • 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
jaspreet singh's user avatar
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, ...
Robert's user avatar
  • 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 ...
Kush's user avatar
  • 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 ...
t.ry's user avatar
  • 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. ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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/...
padjee's user avatar
  • 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 ...
gravitoz gravity's user avatar
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/...
padjee's user avatar
  • 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 : ...
padjee's user avatar
  • 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, ...
padjee's user avatar
  • 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]....
padjee's user avatar
  • 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 '...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 "...
padjee's user avatar
  • 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 ...
RonJohn's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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,...
padjee's user avatar
  • 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 ...
starting dba jr's user avatar
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....
rboarman's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
NAREN PUSHPARAJU's user avatar
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 ...
padjee's user avatar
  • 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 ...
blad0506's user avatar
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]{...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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 ...
padjee's user avatar
  • 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....
padjee's user avatar
  • 167

1
2 3 4 5