Issue

A user is trying to run/replicate a DDL that is not supported in xCluster Automatic Mode. When they attempt to do so, they see an error such as:

yugabyte=# create publication my_publication for table foo;
ERROR:  Unsupported DDL: CREATE PUBLICATION
To manually replicate, run DDL on the source followed by the target with SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = true

Resolution

Validation

First step is to determine what / why the user is trying to replicate this DDL.

XCluster Automatic mode already supports most of the DDLs that are supported by YB and that should be replicated. Other DDLs that fall outside of this group likely have a reason that they are unsupported, so check with Support / Engineering to verify.

If this is indeed a DDL that should be replicated, then create a GitHub issue and Engineering will add support for it (for example, in this doc I am using CREATE PUBLICATION as an example, but we will add support for this in https://github.com/yugabyte/yugabyte-db/issues/29340).

Steps

If there is a valid reason to replicate the DDL, then follow these steps to manually replicate the DDL.

1. Ensure replication is in a healthy state (see https://support.yugabyte.com/hc/en-us/articles/29809348650381-How-to-troubleshoot-xCluster-replication-lag-and-errors):
   1. Verify xCluster lag and xCluster safe time metrics and ensure that these values are within acceptable limits
   2. Verify the /xcluster page on the target master leader and ensure that there are no errors in the Status column
2. Pause running DDLs on the source cluster. Having other DDLs run during this operation will complicate the process and could cause issues.

3. On the source cluster, open a ysql session, connect to your database and run:

   SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = true;

   followed by the DDL. After running the DDL, close the session or run:

   SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = false

    

   For example:

   yugabyte=# SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = true;
   SET
   yugabyte=# create publication my_publication for table foo;
   CREATE PUBLICATION
   yugabyte=# SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = false;
   SET

4. On the target cluster, open a ysql session, connect to your database and run:

   SELECT yb_data->>'query' AS DDLQuery FROM yb_xcluster_ddl_replication.ddl_queue WHERE yb_data->>'manual_replication' = 'true' ORDER BY ddl_end_time DESC LIMIT 5;

   Ensure that you see the DDL that you ran on the source here.

    

   For example:

   yugabyte=# SELECT yb_data->>'query' AS DDLQuery FROM yb_xcluster_ddl_replication.ddl_queue WHERE yb_data->>'manual_replication' = 'true' ORDER BY ddl_end_time DESC LIMIT 5;
                       ddlquery
   -------------------------------------------------
    create publication my_publication for table foo
   (1 row)

   If this is not present, then reverify xCluster replication health (metrics and /xcluster endpoint) for errors.

5. Continue on the target cluster and run:

   SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = true;

   followed by the DDL. After running the DDL, close the session or run:

   SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = false

    

   For example:

   yugabyte=# SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = true;
   SET
   yugabyte=# create publication my_publication for table foo;
   CREATE PUBLICATION
   yugabyte=# SET yb_xcluster_ddl_replication.enable_manual_ddl_replication = false;
   SET

6. Reverify replication to ensure everything is healthy.
7. Can now run other DDLs on the source again.

Notes

• Extensions that perform data manipulation during creation are not allowed to be created while replication is running. For any new extension that is not automatically replicated, you have to delete the xCluster config, create the extension and then re-setup xCluster.

  • XCluster Automatic mode supports a list of extensions (see https://docs.yugabyte.com/stable/architecture/docdb-replication/async-replication/#transactional-automatic-mode), for other extensions, reach out to Support / Engineering.