How to check if CRDTs are being used

William Ivanski
William Ivanski

If the customer needs to drop the BDR extension for any reason, if CRDTs are being used, then dropping the extension will cause data loss. So it's extremely important to check if CRDTs are being used before dropping the extension.

This article:

  • Explains about CRDTs and how data loss happens if the BDR extension is dropped;
  • Provides a query to check if CRDTs are being used;
  • Provides an alternative way to drop the BDR metadata without causing data loss.


CRDTs, or Conflict-free Replicated Data Types, are user-defined data types created when the BDR extension is created. Each CRDT handles a specific consistency scenario. More details about CRDTs can be found in the PGD documentation.

Risk of data loss

CRDTs are user-defined data types belonging to the BDR extension. As such, the user can have tables with fields associated with these data types.

On BDR 3.6 and 3.7, the BDR extension is dropped this way:


On PGD 4 and newer, the BDR extension is dropped this way instead:


In both ways, the CASCADE option is required. However, CASCADE also means that any user-created object (including table fields) depending on any other object belonging to the BDR extension, will also be dropped.

In other words, by running DROP EXTENSION bdr CASCADE, for example, all fields having any CRDT as a data type will also be dropped from their tables.

IMPORTANT!!: If the BDR extension is dropped, then fields of any CRDT data type will also be dropped, causing data loss!

Query to check if CRDTs are being used

The following query can check if CRDTs are being used or not:

SELECT table_name,
FROM information_schema.columns
WHERE data_type = 'USER-DEFINED'
AND udt_schema = 'bdr'
AND udt_name ~ 'crdt_';

If this query returns no rows, then the customer is not using CRDTs.

On the other hand, in the example output below, the customer has 1 table called mytable with 2 fields that are of a CRDT data type:

table_name | column_name | data_type | udt_name
mytable | mycount | USER-DEFINED | crdt_pncounter
mytable | myamount | USER-DEFINED | crdt_pnsum

So in this case we can't drop the BDR extension, otherwise these 2 fields will be dropped, causing data loss.

How to safely drop the BDR metadata if CRDTs are used

In the case above, we can't drop the BDR extension because CRDTs are being used. Instead, we can drop all the BDR metadata by dropping the local BDR node, like the steps below.

1- Get the local node name and state:

SELECT node_name,
FROM bdr.local_node_summary;

2- If the local node state is not PARTED, you can part it:

SELECT bdr.part_node(
node_name := 'NODE_NAME_FROM_ABOVE',
wait_for_completion := true,
force := false

3- Confirming the local node state is PARTED, then we can drop it:

SELECT bdr.drop_node(
node_name := 'NODE_NAME_FROM_ABOVE',
cascade := true,
force := false

Which will also drop all the BDR metadata (nodes, interfaces, configuration, etc), except the objects belonging to the BDR extension (tables, views, functions and CRDTs).

The method above can be safely used to drop the local BDR metadata instead of dropping the BDR extension, in cases the CRDTs are used. However, the method above might not be sufficient in situations there are catalog issues, for example. In these cases, consult a PGD SME or PGD L2 to understand how to proceed further.

Was this article helpful?

0 out of 0 found this helpful