Understanding DDL Replication in PostgreSQL

Data Definition Language (DDL) operations are an essential part of managing a database system, as they define and alter the structure of database tables and other objects. PostgreSQL, a highly versatile and extensible open-source database, has robust mechanisms to support replication of DDL changes to handle high availability and horizontal scaling. This article dives into how DDL changes are replicated in PostgreSQL, covering technical explanations, practical examples, and summarizing key points in tables.

PostgreSQL Replication Overview

PostgreSQL offers several replication methods, with the most common being streaming replication and logical replication. Streaming replication is mostly used for high availability, while logical replication allows for more fine-grained, object-level replication.

Key Concepts in Replication

• Write-Ahead Logging (WAL): WAL is a critical component ensuring data integrity and durability. It logs all changes made to the database, which can be replayed to replicate these changes to standby servers.
• Logical Decoding: This transforms WAL entries into a form which can be understood and applied by subscribers, playing a crucial role in logical replication.

Streaming Replication and DDL

In streaming replication, the replication mechanism primarily relies on WAL. However, streaming replication is more suitable for data changes, where DDL operations are automatically replicated by virtue of being logged as WAL entries. Challenges include the need to always have identical schemas on master and replica databases, as streaming replication doesn’t allow for divergent schema evolution.

Logical Replication and DDL

Logical replication, available since PostgreSQL 10, provides replication from a source database to a subscriber database by streaming changes such as inserts, updates, and deletes. However, DDL operations are not replicated automatically under logical replication, thus requiring manual synchronization between nodes.

Example Scenario

Consider a setup with a main server replicating to a standby server.

1. Streaming Replication Setup:

• Main server setup:

     CREATE TABLE example (id SERIAL PRIMARY KEY, data TEXT);

• Stop Master and take Base Backup:

     pg_basebackup -h master_host -D /var/lib/postgresql/standby -U replication_user -Fp -Xs

• Restart Master and Configure Standby:
  • Edit postgresql.conf and set wal_level = replica.
  • On the standby, create recovery.conf to point to the master server for fetching WAL segments.

Through WAL, this setup will automatically replicate DDL changes across the nodes.

2. Logical Replication Setup:

• Publisher Node:

     CREATE PUBLICATION my_pub FOR ALL TABLES;

• Subscriber Node:

     CREATE SUBSCRIPTION my_sub CONNECTION 'host=master_host dbname=mydb user=myuser password=mypass' PUBLICATION my_pub;

For DDLs in this setup, manual execution of the same DDL on subscriber side is necessary:

   ALTER TABLE example ADD COLUMN new_col TEXT;
   -- Manually execute the above on subscribers

DDL Challenge in Logical Replication

While logical replication caters well for data changes, handling DDL requires:

• Trigger-based Solutions: Custom triggers for DDL logging and replication.
• Manual Synchronization: Coordinating schema changes across all databases.
• Event-Driven Approaches: Use logical decoding plugins to transform DDL changes into events, which can be applied on subscribers.

Summary Table: DDL Replication

Conclusion

PostgreSQL's replication mechanisms provide a robust framework for data integrity and availability. While streaming replication seamlessly handles DDL replication, logical replication offers flexibility at the cost of requiring careful manual management for DDL changes. Understanding these differences allows database administrators to choose the appropriate replication strategy based on usage requirements. For environments that require asynchronous, selective data replications, or multi-master architectures, logical replication provides significant advantages despite its DDL limitations.