Make manual DDL changes YSQL

The following instructions are for performing DDL operations (such as creating, altering, or dropping tables, indexes, or partitions) on databases in manually configured transactional xCluster replication. For instruction on making DDL changes in semi-automatic transactional xCluster replication, see Making DDL changes.

When making DDL changes, the statements must be executed on both the Primary/Source and Standby/Target and the xCluster configuration must be updated.

You should perform these actions in a specific order, depending on the type of DDL, as indicated in the following table.

Tables

Create table

To ensure that data is protected at all times, set up replication on a new table before inserting any into it.

If a table already has data before adding it to replication, then adding the table to replication can result in a backup and restore of the entire database.

Add tables to replication in the following sequence:

1. Create the table on the Primary.

2. Create the table on the Standby.

3. Add the table to the replication.

   For instructions on adding tables to replication, refer to Adding tables (or partitions).

Drop table

Remove tables from replication in the following sequence:

1. Remove the table from replication.

   For instructions on removing tables from replication, refer to Removing objects.

2. Drop the table from both Primary and Standby databases separately.

Indexes

Create index

Indexes are automatically added to replication in an atomic fashion after you create the indexes separately on Primary and Standby. You do not have to stop the writes on the Primary.

Note: The Create Index DDL may kill some in-flight transactions. This is a temporary error. Retry any failed transactions.

Add indexes to replication in the following sequence:

1. Create an index on the Primary.

2. Wait for index backfill to finish.

3. Create the same index on Standby.

4. Wait for index backfill to finish.

   For instructions on monitoring backfill, refer to Monitor index backfill from the command line.

Drop index

When an index is dropped it is automatically removed from replication.

Remove indexes from replication in the following sequence:

1. Drop the index on the Standby universe.

2. Drop the index on the Primary universe.

Table partitions

Create table partition

Adding a table partition is similar to adding a table.

The caveat is that the parent table (if not already) along with each new partition has to be added to the replication, as DDL changes are not replicated automatically. Each partition is treated as a separate table and is added to replication separately (like a table).

For example, you can create a table with partitions as follows:

CREATE TABLE order_changes (
  order_id int,
  change_date date,
  type text,
  description text)
  PARTITION BY RANGE (change_date);

CREATE TABLE order_changes_default PARTITION OF order_changes DEFAULT;

Create a new partition:

CREATE TABLE order_changes_2023_01 PARTITION OF order_changes
FOR VALUES FROM ('2023-01-01') TO ('2023-03-30');

Assume the parent table and default partition are included in the replication stream.

To add a table partition to the replication, follow the same steps for Add a table to replication.

Drop table partitions

To remove a table partition from replication, follow the same steps as Remove a table from replication.

Alters

You can alter the schema of tables and indexes without having to stop writes on the Primary.

Note: The ALTER DDL may kill some in-flight transactions. This is a temporary error. Retry any failed transactions.

Alter the schema of tables and indexes in the following sequence:

1. Alter the index on the Standby universe.

2. Alter the index on the Primary universe.