Manage tables and indexes EARLY ACCESS

Add and remove tables and indexes in universes with disaster recovery

When DDL changes are made to databases in replication for xCluster disaster recovery (DR) (such as creating, altering, or dropping tables or partitions), the changes must be:

  • performed at the SQL level on both the DR primary and replica, and then
  • updated at the YBA level in the DR configuration.

You should perform these actions in a specific order, depending on whether performing a CREATE, DROP, ALTER, and so forth, as indicated by the sequence number of the operation in the table below.

DB Change on DR primary On DR replica In YBA
1. CREATE TABLE 2. CREATE TABLE 3. Add the table to replication
2. DROP TABLE 3. DROP TABLE 1. Remove the table from replication.
1. CREATE INDEX 2. CREATE INDEX 3. Resynchronize
2. DROP INDEX 1. DROP INDEX 3. Resynchronize
1. CREATE TABLE foo PARTITION OF bar 2. CREATE TABLE foo PARTITION OF bar 3. Add the table to replication
2. ALTER TABLE or INDEX 1. ALTER TABLE or INDEX No changes needed

In addition, keep in mind the following:

  • If you are using Colocated tables, you CREATE TABLE on DR primary, then CREATE TABLE on DR replica making sure that you force the Colocation ID to be identical to that on DR primary.
  • If you try to make a DDL change on DR primary and it fails, you must also make the same attempt on DR replica and get the same failure.

Use the following guidance when managing tables and indexes in universes with DR configured.

Best practices

If you are performing application upgrades involving both adding and dropping tables, perform the upgrade in two parts: first add tables, then drop tables.

Tables

Add a table to DR

To ensure that data is protected at all times, set up DR on a new table before starting any workload.

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

Add tables to DR in the following sequence:

  1. Create the table on the DR primary (if it doesn't already exist).
  2. Create the table on the DR replica.
  3. Navigate to your DR primary and select xCluster Disaster Recovery.
  4. Click Actions and choose Select Databases and Tables.
  5. Select the tables and click Validate Selection.
  6. If data needs to be copied, click Next: Confirm Full Copy.
  7. Click Apply Changes.

Note the following:

  • If the newly added table already has data, then adding the table can trigger a full copy of that entire database from DR primary to replica.

  • It is recommended that you set up replication on the new table before starting any workload to ensure that data is protected at all times. This approach also avoids the full copy.

  • This operation also automatically adds any associated index tables of this table to the DR configuration.

  • If using colocation, colocated tables on the DR primary and replica should be created with the same colocation ID if they already exist on both the DR primary and replica prior to DR setup.

Remove a table from DR

When dropping a table, remove the table from DR before dropping the table in the DR primary and replica databases.

Remove tables from DR in the following sequence:

  1. Navigate to your DR primary and select xCluster Disaster Recovery.
  2. Click Actions and choose Select Databases and Tables.
  3. Deselect the tables and click Validate Selection.
  4. Click Next: Confirm Full Copy.
  5. Click Apply Changes.
  6. Drop the table from both DR primary and replica databases separately.

Indexes

Add an index to DR

Indexes are automatically added to replication in an atomic fashion after you create the indexes separately on DR primary and replica. You don't need to stop the writes on the DR primary.

CREATE INDEX 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 DR primary.

  2. Wait for index backfill to finish.

  3. Create the same index on the DR replica.

  4. Wait for index backfill to finish.

    For instructions on monitoring backfill, refer to Create indexes and track the progress.

  5. Resynchronize YBA.

Remove an index from DR

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

Remove indexes from replication in the following sequence:

  1. Drop the index on the DR replica.

  2. Drop the index on the DR primary.

  3. Resynchronize YBA.

Table partitions

Add a table partition to DR

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 DR, as DDL changes are not replicated automatically. Each partition is treated as a separate table and is added to DR 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 DR, follow the same steps for Add a table to DR.

Remove table partitions from DR

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

Resynchronize YBA

To ensure changes made outside of YugabyteDB Anywhere are reflected in YBA, resynchronize the YBA UI as follows:

  1. Navigate to your DR primary and select xCluster Disaster Recovery.
  2. Click Actions > Advanced and choose Reconcile Config with Database.