Diagnose issues for PostgreSQL

Troubleshoot an error

The migration job process might incur errors during runtime.

  • Some errors, such as a bad password on the source database, are recoverable, meaning they can be fixed and the migration job resumes automatically.
  • Some are unrecoverable, such as errors in data replication, meaning the migration job needs to be restarted from the beginning.

When an error occurs, the migration job status changes to Failed, and the substatus reflects the last status before failure.

To troubleshoot an error, navigate to the failed migration job to view the error and follow the steps outlined in the error message.

To view more details about the error, navigate to Cloud Monitoring using the link on the migration job. The logs are filtered to the specific migration job.

In the following table, you can find some examples of issues and how they can be solved:

For this problem... The issue might be... Try this...
Failure connecting to the source database instance. There was a connectivity issue between the source database instance and the destination instance. Follow the steps in Debugging connectivity.
Failure running migration job due to incompatible source and destination database versions. The source and destination database versions aren't a supported combination. Specifically, the source database version provided is incompatible with the destination database version. Make sure that the destination database version is the same or one major version above the source database version. Then, create a new migration job.
Data definition languages (DDLs) or data manipulation languages (DMLs) are blocked on the source. DDLs that require the ACCESS EXCLUSIVE lock and are running during the full dump phase are blocked.

During the initial sync process (full dump), DDLs or programs requiring ACCESS EXCLUSIVE locks such as ALTER TABLE or DROP TABLE should be avoided on the tables. Otherwise, the DDLs or programs will wait until the initial sync finishes.

For example, if a table is still in the initial sync process and an ALTER TABLE command is executed on the same table, then the command won't be run and subsequent DDL and DML commands will be blocked until the initial sync finishes.

Error Message: No pglogical extension installed on databases (X) One or more source databases doesn't have pglogical installed. Follow these guidelines to install pglogical on the databases on the source instance.
Error Message: Replication user 'x' doesn't have sufficient privileges. The user who's using Database Migration Service doesn't have the required privileges to perform the designated operation. Follow these guidelines to ensure that this user has the required privileges.
Error Message: Unable to connect to source database server. Database Migration Service can't establish a connection to the source database server. Make sure that the source and destination database instances can communicate with each other, and that you've completed all of the required prerequsities that appeared when you defined your settings for the migration job.
Error Message: The source database 'wal_level' configuration must be equal to 'logical'. The wal_level for the source database is set to a value other than logical. Set the wal_level to logical.
Error Message: The source database 'max_replication_slots' configuration is not sufficient. The max_replication_slots parameter wasn't configured correctly. Follow these guidelines to set this parameter correctly.
Error Message: The source database 'max_wal_senders' configuration is not sufficient. The max_wal_senders parameter wasn't configured correctly. Follow these guidelines to set this parameter correctly.
Error Message: The source database 'max_worker_processes' configuration is not sufficient. The max_worker_processes parameter wasn't configured correctly. Follow these guidelines to set this parameter correctly.

Error Message: Cleanup may have failed on source due to error: generic::unknown: failed to connect to on-premises database.

OR

Error Message: Error promoting EM replica: finished drop replication with errors.

The settings needed for replication can't be cleaned up during the promotion of a migration job.

For each database, run commands as a user with the superuser privilege.

For more information about which commands to run, see Clean up replication slots.

Error Message: x509 certificate signed by unknown authority.

The source CA certificate provided to Database Migration Service might contain only the root certificate. However, the source certificate requires both the root certificate and any intermediate certificates.

For example, for Amazon Relational Database Service, using the rds-ca-2019-root.pem certificate might result in this issue.

Create a combined source CA certificate that contains both the root certificate and all required intermediate certificates.

For the Amazon Relational Database Service use case, instead of the rds-ca-2019-root.pem certificate, use the rds-combined-ca-bundle.pem certificate.

Error Message: ERROR: Out of shared memory HINT: You might need to increase max_locks_per_transaction.

The value set for the max_locks_per_transaction parameter isn't sufficient. Set the value for this parameter to be at least {max_number_of_tables_per_database}/(max_connections + max_prepared_transactions).

Error Message: ERROR: no data left in message.

The pglogical package isn't installed correctly on the source instance. For more information on how to install this package properly, see Install the pglogical package on the source instance.

Error Message: Cannot assign TransactionIds during recovery.

The source that's configured is in recovery mode. Configure a source that isn't in recovery mode.

Clean up replication slots

You see one of the following messages:

  • Cleanup may have failed on source due to error: generic::unknown: failed to connect to on-premises database.
  • Error promoting EM replica: finished drop replication with errors.

The issue might be

When promoting a Cloud SQL instance, if the source instance isn't reachable from the Cloud SQL instance (for example, the source instance isn't running, or you removed the Cloud SQL instance from the allow list of source instances), then the settings needed for the replication can't be cleaned up during the promotion of a migration job. You must clean up the replication slots manually.

Things to try

For each database, run the following commands as a user with the superuser privilege:

  1. Get the replication slot names from the error message, and then run the following command to drop the slots, one by one:

    select pg_drop_replication_slot({slot_name});
  2. If the replication slot names aren't available in the error message, then run the following command to query for the existing replication slots:

    select pg_drop_replication_slot(slot_name) from pg_replication_slots where slot_name like '%cloudsql%' and active = 'f';
  3. If there are no Cloud SQL replicas using the source instance, then run the following command to clean up pglogical settings:

    select pglogical.drop_node(node_name) from pglogical.node where node_name like 'cloudsql';
  4. If the pglogical extension isn't needed anymore, then run the following command to uninstall the extension:

    DROP EXTENSION IF EXISTS pglogical;