The error "unable to connect to source database server" in Google Cloud Database Migration Services (DMS) during a migration from a self-hosted PostgreSQL to Cloud SQL for PostgreSQL can be attributed to several potential issues. Addressing these involves examining network connectivity, PostgreSQL configuration, DMS service account permissions, and connection profiles.
Firstly, network connectivity issues often stem from improper firewall rules or routing configurations. It's crucial to ensure that both the source VM's firewall and the Shared VPC firewall allow inbound traffic on PostgreSQL's default port (5432) from the IP ranges used by the DMS service. Additionally, routes in the Shared VPC must be correctly set up to permit traffic between the VM and the Cloud SQL instance, avoiding any conflicts or overly restrictive network tags. If a hostname is used in the connection profile, DNS resolution within the Shared VPC must be verified; using the IP address directly can aid in troubleshooting. Ensuring the private IP address used in the connection profile is reachable from the DMS service may also require configuring Private Service Connect or Cloud VPN if direct access within the Shared VPC is not available.
Secondly, PostgreSQL configuration issues can prevent successful connections. The PostgreSQL configuration file (postgresql.conf) should have the listen_addresses parameter set to either '*' (to listen on all interfaces) or include the specific IP address of the interface connected to the Shared VPC. Local firewalls on the VM, managed by tools like iptables or firewalld, must also allow connections on the PostgreSQL port. The pg_hba.conf file should be configured to permit connections from the IP ranges used by the DMS service and authenticate the appropriate user.
Thirdly, the DMS service account must have the necessary IAM permissions. The service account should have roles such as roles/cloudsql.client to connect to the Cloud SQL instance and network-related permissions if using Private Service Connect or Cloud VPN.
Moreover, the connection profile must accurately reflect the correct IP address or hostname of the network interface connected to the Shared VPC. Ensuring the username and password in the connection profile are correct and have the necessary privileges to access the PostgreSQL database is equally important.
Using troubleshooting tools can further help identify connectivity issues. The connectivity test feature in the DMS migration job setup can verify if DMS can reach the source database. Examining logs from the DMS migration job, Cloud SQL instance, and PostgreSQL server on the VM can provide insights into any error messages or connection attempts.
An example entry in the pg_hba.conf file to allow connections from the DMS service might look like this:
# Allow connections from the DMS service (replace with the actual IP range)
host all all <DMS-service-IP-range>/32 md5
To simplify troubleshooting, temporarily allowing all traffic from the Shared VPC to the PostgreSQL port on the VM can help rule out firewall issues. Referring to Google Cloud documentation for specific guidance and reaching out to Google Cloud support if necessary can provide additional assistance.
By addressing these areas—network connectivity, PostgreSQL configuration, service account permissions, and connection profiles—you can systematically identify and resolve the issue preventing the connection between your self-hosted PostgreSQL and Cloud SQL for Postgres.
LinkedIn
Twitter
