TheDBA

Does ADD COLUMN Lock a Table?

ALTER TABLE table ADD COLUMN IF NOT EXISTS timestamp TIMESTAMPTZ;

TL;DR: Yes — but only briefly.

This command does acquire an ACCESS EXCLUSIVE lock, which is the highest level of lock in PostgreSQL. While this lock is held, no other operations (not even reads) can occur on the table. But the good news is:

It’s fast, even on large tables with millions of rows.
It doesn’t rewrite the table or touch existing rows.
Existing rows will simply show NULL in the new column.

2025-09-25

Best Practices for Configuring Autovacuum in PostgreSQL

Configuring autovacuum in PostgreSQL is essential for maintaining database health, ensuring that tables do not grow excessively large due to dead tuples, and optimizing query performance. Autovacuum helps automatically vacuum tables to reclaim space and analyze them to update statistics for the query planner.

Here are some best practices for configuring autovacuum in PostgreSQL:

1. Understand Default Settings

PostgreSQL comes with a set of default autovacuum settings. It’s important to first understand the defaults before adjusting them:

2025-09-23

Barman configuration to backup PostgreSQL.

1. Prepare the Backup Server and Database Server

To begin, you’ll need to install the following packages on Debian-based installations:

For the Barman Backup Server:

python3-barman-latest_version barman_3-latest_version barman-cli_3-latest_version
For the PostgreSQL Server:

python3-barman-latest_version barman-cli_3-latest_version`

Once the installation is complete on both nodes, configure SSH access between the servers using the barman (on the backup server) and postgres (on the PostgreSQL server) user accounts.

Additionally, you’ll need to create the necessary users on PostgreSQL and configure it to archive data to the Barman backup server.

2025-03-03

Temp fix for Zabbix PostgreSQL plugin for version 17 and above.

Issue Overview:

As per the official PostgreSQL documentation and also here Pgpedia, the pg_stat_bgwriter table has undergone changes in PostgreSQL version 17, compared to versions 16 and below. As a result, Zabbix’s PostgreSQL plugin may throw errors in the PostgreSQL log files, such as the following:

zbx_monitor@postgres LOCATION:  errorMissingColumn, parse_relation.c:3716
zbx_monitor@postgres STATEMENT:  
	  SELECT row_to_json (T)
	    FROM (
	          SELECT
	              checkpoints_timed
	            , checkpoints_req
	            , checkpoint_write_time
	            , checkpoint_sync_time
	            , buffers_checkpoint
	            , buffers_clean
	            , maxwritten_clean
	            , buffers_backend
	            , buffers_backend_fsync
	            , buffers_alloc
	          FROM pg_catalog.pg_stat_bgwriter
			  ) T ;

Temporary Fix:

To resolve this issue temporarily, you can update the Zabbix plugin. Here’s how:

2025-02-11

Loading test data into PostgreSQL for testing.

Loading Test Data into PostgreSQL

To efficiently load test data into PostgreSQL, you can use a simple query like the following:

INSERT INTO new.students (fname, fuid, lname, dob, joined)
VALUES (   (array['Oswald', 'Henry', 'Bob', 'Vennie', 'Ivan', 'Stefan'])
[floor(random() * 6 + 1)],   uuid_generate_v4(),   
(array['Leo', 'Jack', 'Den', 'Daisy', 'Woody', 'Ivanov'])
[floor(random() * 6 + 1)],   '1980-01-01'::date + trunc(random() * 366 * 12)::int,   
generate_series('1/1/1990'::date, '12/01/2024'::date, '1 day') );

This query will insert random data into the students table. However, before running the query, you’ll need to prepare your database.

2025-02-01

Setting up logical replication between PostgreSQL 17 (master) and PostgreSQL 16 (replica)

Setting up logical replication between PostgreSQL 17 (master) and PostgreSQL 16 (replica), and vice versa, involves several key steps. The main goal is to ensure both systems are properly configured for logical replication, and then to establish the replication connection from PostgreSQL 17 (master) to PostgreSQL 16 (replica).

Prerequisites

PostgreSQL 17 (master) and PostgreSQL 16 (replica) should both be installed.
Both systems should be accessible from each other (network connectivity).

2024-12-15

PostgreSQL configuration for Debizium and Kafka.

In PostgreSQL, max_wal_senders is a configuration parameter that determines the maximum number of concurrent processes allowed to send Write-Ahead Logging (WAL) data to other systems, such as for replication or backup purposes. Properly configuring max_wal_senders is crucial for ensuring that your replication setup, backup processes, or logical replication needs are met without negatively impacting your primary database’s performance.

Here are some best practices for tuning max_wal_senders:

1. Understand the Role of WAL Senders

WAL senders are processes that transmit WAL data to replica servers or external systems. Each connected replica (physical or logical) requires one WAL sender process. Additionally, tools like logical replication, streaming replication, or backup systems (e.g., pg_basebackup) also use WAL sender processes to stream data.

2024-11-29