Known errors in the migration service for Azure Database for PostgreSQL

This article contains error message numbers and their description for premigration validation.

The following tables provide a comprehensive list of error codes for the migration service feature in Azure Database for PostgreSQL. These error codes help you troubleshoot and resolve issues during the migration process. Each error code has an error message and other details that provide further context and guidance for resolving the issue.

Migration failures often manifest through error messages that indicate connectivity issues. These issues can arise during the data transfer process. When such failures occur, they result in the following error messages:

• server closed the connection unexpectedly
• SSL SYSCALL error: EOF detected
• unexpected EOF on client connection
• couldn't receive data from client: Connection reset by peer

The underlying cause for these symptoms is a connection timeout. This generally occurs when the server or the client expects to receive data within a certain time frame, but no data is sent or received, leading the connection to time out. The specific reasons for a connection time-out can vary, but common factors include network congestion, misconfigured network settings, or overly aggressive time-out settings.

In the context of migration service in Azure Database for PostgreSQL, a connection time-out between the source and the migration service or between the migration service and the target can interrupt the data transfer process, resulting in the symptoms described above.

• To address the connection time-out issues, adjust the TCP parameters on both the source and target servers as follows:

  • tcp_keepalives_idle=10
  • tcp_keepalives_interval=10
  • tcp_keepalives_count=60

These settings help maintain the connection by sending keepalive probes to prevent time-outs due to inactivity. Importantly, modifying these TCP parameters doesn't require a restart of the source or target PostgreSQL instances. Changes can be applied dynamically, allowing for a seamless continuation of service without interrupting the database operations.

• The Migration service encounters a connection termination message.
• Logs display the error: terminating connection due to idle-in-transaction timeout.

This error occurs when a database connection remains idle within a transaction for longer than the value specified in the idle_in_transaction_timeout parameter. PostgreSQL automatically terminates such connections to avoid resource locking issues.

• Set the idle_in_transaction_timeout parameter to 0 to disable the time-out during the migration process.
• Example command to apply this setting:

ALTER SYSTEM SET idle_in_transaction_timeout = 0;

• Ensure to reset this parameter to its original value after the migration to maintain database performance and prevent prolonged idle connections.

The migration process halts unexpectedly. Logs display the error: out of shared memory.

This error indicates that PostgreSQL has exhausted the shared memory allocated for locks. It typically occurs when the migration process involves many locks beyond the current setting of the max_locks_per_transaction parameter.

• Increase the value of the max_locks_per_transaction parameter to accommodate the another locks required during the migration process.
• Example command to modify this setting: ALTER SYSTEM SET max_locks_per_transaction = <<>>;
• Ensure that, max_locks_per_transaction * max_connections > Number of tables + Number of indexes
• If the issue persists, consider increasing the shared_buffers parameter to ensure sufficient shared memory is available for lock management.

The migration process halts unexpectedly. Logs display the error: pg_restore: error: could not execute query: ERROR: must be superuser to create a base type .

To create a new base type, you must be a superuser (This restriction is made because an erroneous type definition could confuse or even crash the server).

• Custom base type has to be deleted and/or replaced by a recognized base type.
• More details here.

• Troubleshoot the migration service for Azure Database for PostgreSQL
• Best practices for seamless migration into Azure Database for PostgreSQL
• Known issues and limitations