When you synchronize tables that have no primary keys in PolarDB for PostgreSQL to other databases by using the logical replication method, errors may be reported
for the operations on the tables. You can specify the
polar_create_table_with_full_replica_identity parameter to resolve this issue.
Logical replication of PolarDB for PostgreSQL uses a publish and subscribe model. The operations on the publisher side can be executed on the subscriber side in a similar way to Structural Query Language (SQL) so that data can be synchronized. The replica identities of the tables must be configured on the publisher side so that the data to be updated or deleted on the subscriber side can be identified.
The following types of replica identities are supported:
- Primary key
- Unique index
- FULL (a full row of data)
By default, the replica identity is the primary key. If logical replication is implemented on a table that has no primary key, an error occurs for the change operations. As a result, services cannot run as expected. The following error message is returned:
ERROR: cannot delete from table "polardb_test" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
FULL. For example, when you use Data Transmission Service (DTS) to synchronize data, comply with this rule.
PolarDB for PostgreSQL provides the following two methods for you to change the replica identities of tables
- Run the following command to change the replica identity of an existing table to
ALTER TABLE <table_name> REPLICA IDENTITY FULL;
- Specify the
polar_create_table_with_full_replica_identityparameter as on to set the default replica identity of a newly created table to
The default value of the
polar_create_table_with_full_replica_identityparameter is off. You cannot modify this parameter in the console. If you need to modify this parameter,Submit a ticket to contact technical support.