This article goes into the setup and usage of automatic DDL with Spock replication
To use auto DDL and auto replicate in pgEdge, the schema on every node in the cluster has to match exactly at the time you enable the settings (preferably an empty database with no tables on any node). The following alter system commands have to be run on every node in the cluster:
- alter system set spock.enable_ddl_replication=on;
- alter system set spock.include_ddl_repset=on;
Right now, the user who is creating the tables needs to be able to use the role of your replication user, PgEdge will create a replication user with the same name as your OS user. Replace the pgedge in the following command with your os user name and admin with the user you want to be able to create tables that replicate:
GRANT pgedge TO admin WITH SET TRUE, INHERIT FALSE;
So if your OS username is Rocky and app_user was used to install pgEdge, you need to run the command as follows :
GRANT Rocky TO app_user WITH SET TRUE, INHERIT FALSE
This current version of automatic replication of DDL in pgEdge is recommended to be used when initializing your cluster. If the cluster is currently in use and replication is happening, we recommend using a maintenance window (where inserts and updates have stopped and replication has caught up) for any DDL statements that could break replication. Some examples of these types of DDL statements are:
- Alter table drop column
- Drop table
- Alter table, add column
The following commands are not replicated by automatic DDL replication:
- Create database
- Alter System and checkpoint
- Maintenance (vacuum, analyze, cluster, reindex)
Automatic DDL Replication:
- All DDL statements run in the replication database will be replicated to every node in the cluster by the ddl_sql replication set.
- Global objects created in the postgres database, or any database that is not set up with spock replication, will not be replicated. But global objects created in the replication database will.
- Alter system will be replicated- but not a restart if required.
Automatic Replication:
- Tables created without a primary key will be added to the default_insert_only replication set.
- Tables created with a primary key will be added to the default replication set.
- Adding a primary key to a table will move that table to the default replication set. Similarly, dropping a primary key will move that table to the default_insert_only replication set.
- Setting a table as unlogged will remove it from replication. Similarly, setting a table back to logged will add it to the replication set.
- Detaching a partition will not remove that table from the replication set.
Auto DDL Demo
In a pgEdge cluster where both auto ddl gucs are set, you would be able to run the following demo.
Initialized pgbench on n1:
pgbench -i demo
Check that the tables were added to the correct replication sets:
select * from spock.tables;
relid | nspname | relname | set_name
-------+---------+------------------+----------
32780 | public | pgbench_branches | default
32772 | public | pgbench_tellers | default
32776 | public | pgbench_accounts | default
32768 | public | pgbench_history | default_insert_only
(4 rows)
Go to n2 and check that the tables successfully replicated:
\dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+--------
public | pgbench_accounts | table | pgedge
public | pgbench_branches | table | pgedge
public | pgbench_history | table | pgedge
public | pgbench_tellers | table | pgedge
(4 rows)
And check that they were also added to the correct replication sets on n2:
select * from spock.tables;
relid | nspname | relname | set_name
-------+---------+------------------+----------
32780 | public | pgbench_branches | default
32772 | public | pgbench_tellers | default
32776 | public | pgbench_accounts | default
32768 | public | pgbench_history | default_insert_only
(4 rows)
On n2, alter the pgbench_history table to have a primary key, and check that it has been moved to the default replication set, so that inserts, updates, and deletes will all be replicated.
ALTER TABLE pgbench_history ADD COLUMN h_id INT PRIMARY KEY;
select * from spock.tables;
relid | nspname | relname | set_name
-------+---------+------------------+----------
32780 | public | pgbench_branches | default
32772 | public | pgbench_tellers | default
32776 | public | pgbench_accounts | default
32768 | public | pgbench_history | default
(4 rows)
Back on n1, check that the results from spock.tables matches.