Re: Performance difference between Primary & Secondary in the query execution

Hi, You can check and verify the following points: *Check the cpu resources on both primary and secondary *Check the execution plans on both the primary and secondary by running EXPLAIN (ANALYZE, BUFFERS) for the problematic queries like the following explain (analyze,buffers) select * from test ;

Re: Backup

Hi, you can refer the following link where many backup methods are mentioned according to your need. Backups in PostgreSQL. In PostgreSQL, different types of… | by Usman Khan | Aug, 2024 | Medium On Thu, 17 Oct 2024 at 00:37

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

Hi, When you execute schema-altering operations on the publisher, these changes are not automatically replicated to the subscriber which causes the following error logical replication target relation "public.dummy_table" is missing replicated columns: "contact_email", "status", "phone_number", "us

Re: How to Copy/Load 1 billions rows into a Partition Tables Fast

Hi, There are many methods to achieve this and one of them is pg_bulkload utility as described in previous email but I always preferred using python multiprocessing which I think is more efficient. Below is the code which you can modify as per your requirement: import multiprocessing import psycop

Re: backup

Hi, You can refer the following: Automating PostgreSQL Backups with Python Using pg_basebackup | by Usman Khan | Sep, 2024 | Medium Automating PostgreSQL Backups with PG Back Web: The Com

Re: Basebackup fails without useful error message

Hi, You can enable verbose to get more detail about what is happening during backup pg_basebackup -h your_host -U your_user -D /mnt/base_backup/dir/ -v Also examine recent postgres logs Verify backup directory permissions On Sun, 29 Sept 2024 at 19:49, Koen De Groote wrote: > Having run a baseba

Re: Request for Insights on ID Column Migration Approach

Hi, Your approach to migrating the ID column from int4 to int8 with minimal downtime is generally sound but in my option, consider the following also: - Consider using PostgreSQL's CONCURRENTLY option when creating the unique index to avoid locking the entire table - Make sure to first

Re: MError after replication in postgresql

Hi, There are many causes of this issue. One possible solution to handle this issue is to make sure that proper dynamic linking as follows: export LD_LIBRARY_PATH=/path/to/pgsql/lib:$LD_LIBRARY_PATH On Wed, 25 Sept 2024 at 10:35, Danish Hajwane wrote: > > Hi Team, > > We are getting error after

Re: CREATE DATABASE command concurrency

Hi, In PostgreSQL, it's safe to run CREATE DATABASE at the same time from different places. If two commands try to create the same database, one will succeed, and the other will safely fail without causing any problems or incomplete database creation. On Wed, 18 Sept 2024 at 19:08, Wizard Brony w

Re: load fom csv

Hi, Try the following options: - Check if psql is working independently: psql -h $pgServer -d $pgDatabase -U $pgUser -c "SELECT 1;" - Check for permission issues on the CSV file - Run the command manually without variables psql -h your_host -d your_db -U your_user -c "\COPY your_ta

Re: update faster way

Hi, You can solve this problem using Citus in PostgreSQL, which is specifically designed for parallelism SELECT create_distributed_table('tab_part1', 'partition_key'); SELECT create_distributed_table('reference_tab', 'reference_key'); UPDATE tab_part1 SET column1 = reftab.code FROM reference_tab

Re: Recommendations on improving the insert on conflict do nothing performance

Hi, You can use the following approaches for optimization: - Instead of inserting one row at a time, perform bulk inserts, which will reduce the overhead of each individual transaction - Partitioning can improve write performance by splitting the data into smaller, more manageable chun

Re: Database schema for "custom fields"

Hi, There is not a properly defined solution but you can try the Entity-Attribute-Value (EAV) Model. This is an alternative approach, where a separate table is used to store custom fields as attributes for each record. New fields can be added without altering the schema. There will be no need for D

Re: barman with postgres server/s

Hi, I had the opportunity to work with barman. In my opinion, to configure barman, you should have a separate dedicated system. Configure WAL archiving properly to ensure that all transaction logs are streamed to Barman like the following archive_command = 'rsync %p barman@backup_server:/path/to/

Re: Faster data load

Hi, You can use pg_partman. If your table is partitioned, you can manage partitions in parallel by distributing the load across partitions concurrently. Or you can use citus. It can be an excellent solution, especially for handling large data volumes and parallelizing data operations On Fri, 6 S

Re: question on audit columns

Hi, In your scenario, triggers can add some overhead since they require extra processing after each update operation. Considering the size of your table and the high transaction volume, you need to observe that this might significantly affect performance. On Wed, 4 Sept 2024 at 17:50, yudhi s

Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16

Hi, You need to install plpython3u on your new server and also make sure to use python3. You need to make changes to your functions also to make them compatible with python3 On Tue, 3 Sept 2024 at 19:31, Sam Son wrote: > Hi Team, > > I am working on a project where we have applications run on o

Re: default privileges are npt working

Hi. I think the ALTER DEFAULT PRIVILEGES command affects only tables that are created after the command is executed. Tables created by the writer user before you executed the ALTER DEFAULT PRIVILEGES command would not automatically have select privileges granted to the reader user. You can try by

Re: where is postres installed?

Hi Arbol, You can try from the following commands: dpkg-query -L postgresql-16 which psql sudo find / -name "postgres" 2>/dev/null On Fri, 23 Aug 2024 at 05:35, Arbol One wrote: > After installing PostgreSQL on my Debian-12 machine, I typed 'postgres > --version' and got this msg: > *bash: pos

Re: How to validate restore of backup?

Hi Vince, For validation of databases, you can use the following approach /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > /var/lib/pgsql/db1.txt /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5420 | md5sum > /var/lib/pgsql/db2.txt diff db1.txt db2.txt By executing abo