Until now, managing and updating Data Definition Language (DDL) schemas in PostgreSQL was a time-consuming, manual effort and especially difficult in a distributed environment.  With the introduction of automatic DDL replication, pgEdge makes it easier and faster to deliver distributed applications built on open, standard-based PostgreSQL.  In this blog, we delve into what Automatic DDL replication entails and how it can be leveraged effectively with pgEdge.

Understanding Automatic DDL Replication

First, let's unpack what DDL commands entail. DDL, or Data Definition Language, encompasses those statements responsible for creating and modifying database objects within PostgreSQL, including tables, indexes, views, and constraints. Commands such as CREATE, ALTER, and DROP fall into this category. On the other hand, Data Manipulation Language (DML) commands focus on operations performed on the data within these objects, such as SELECT, INSERT, UPDATE, and DELETE.

Traditionally, logical replication in PostgreSQL primarily facilitated the replication of DML statements, leaving DDL commands requiring manual intervention. This meant that any modifications to the database schema required manual execution of DDL commands on each node in the cluster—a cumbersome and error-prone process.

Configuring Automatic DDL Replication in pgEdge

With pgEdge automatic DDL replication, you can update the database schema on a single node, and the changes are seamlessly propagated to other nodes within the cluster. This significantly simplifies the management of distributed PostgreSQL applications, eliminating the need for manual intervention across multiple nodes.

Implementing automatic DDL replication in pgEdge is a straightforward process. Before enabling auto-DDL, you should ensure that the schema on each node within the cluster matches precisely. Then, enable auto-DDL replication after installing pgEdge Platform but before creating tables in a replicated database.  Ensure that the same users and roles exist on all nodes within the cluster.

It's essential to exercise caution when enabling auto-DDL, as certain DDL statements, such as DROP TABLE or CREATE TABLE AS, can potentially disrupt replication in an operational cluster. To enable auto-DDL replication, connect to your database with psql, and execute the following commands on each node:

ALTER SYSTEM SET spock.enable_ddl_replication=on; 

ALTER SYSTEM SET spock.include_ddl_repset=on; 

ALTER SYSTEM SET spock.allow_ddl_from_functions=on; 

SELECT pg_reload_conf(); 

Implications on Table Management

Enabling automatic DDL replication in pgEdge has several implications on table management:

  • Tables created with a primary key are automatically added to the default replication set.

  • Tables created without a primary key are added to the default_insert_only replication set,and  if a primary key is later added to the table, it will be moved to the default replication set..

  • Removing a primary key from a table results in the table being moved from the default replication set to the default_insert_only replication set.

  • Setting a table as unlogged removes it from replication, while setting it back to logged adds it to the replication set.

  • Detaching a partition does not remove the table from the replication set.

Conclusion

Automatic DDL replication with pgEdge represents a significant leap forward in database management, particularly for distributed PostgreSQL applications. By automating the propagation of schema changes across nodes within a cluster, your team can focus more on innovation and less on manual maintenance tasks. With careful configuration and adherence to best practices, your organization can unlock the full potential of automatic DDL replication, paving the way for enhanced scalability, reliability, and agility in database operations.

To learn more and get started, download pgEdge Platform at www.pgedge.com/get-started.  For more details on installing and using automatic DDL with pgEdge, see the documentation at https://docs.pgedge.com/platform/advanced/autoddl#configuring-automatic-ddl-replication.

Conclusion

Automatic DDL replication with pgEdge represents a significant leap forward in database management, particularly for distributed PostgreSQL applications. By automating the propagation of schema changes across nodes within a cluster, your team can focus more on innovation and less on manual maintenance tasks. With careful configuration and adherence to best practices, your organization can unlock the full potential of automatic DDL replication, paving the way for enhanced scalability, reliability, and agility in database operations.

To learn more and get started, download pgEdge Platform at www.pgedge.com/get-started.  For more details on installing and using automatic DDL with pgEdge, see the documentation at https://docs.pgedge.com/platform/advanced/autoddl#configuring-automatic-ddl-replication.