Debug connectivity

You've set up connectivity between your source and destination databases, but how do youknow they're connected? When your communications fail between them, how canyou find out what's gone wrong and where?

The most basic tools areping andtraceroute.

Ping

Ping performs a basic test to determine if the destination("remote host") is available from the source.Ping sends anICMP Echo Request packet to a remote host, and it expects anICMP Echo Reply in return. Ifping doesn't succeed,then there's no route from the source to the destination. Success, however,doesn't mean that your packets can get through, only that in general, theremote host can be reached.

Whileping can tell if a host is alive and responding, it's notguaranteed to be reliable. Some network providers blockICMP as asecurity precaution, which can make connectivity debugging more difficult.

Traceroute

Traceroute tests the complete route network packets take from one hostto another. It shows all the steps ("hops") that the packet takesalong the way, and how long each step takes. If the packet doesn't go throughall the way to the destination,traceroute doesn't complete, butends with a series of asterisks. In this case, look for the last IP address thatwas successfully reached along the way. This is where connectivity broke down.

Traceroute can time out. It can also fail to complete if a gatewayalong the way isn't configured correctly to pass the packet along to the nexthop.

Whentraceroute fails to complete, you might be able to figure outwhere it stopped. Find the last IP address listed in thetracerouteoutput, and do a browser search forwho owns [IP_ADDRESS]. Resultsmay or may not show the owner of the address, but it's worth a try.

mtr

Themtr tool is a form oftraceroute that remainslive and continuously updated, similar to how thetop commandworks for local processes.

Locate your local IP address

If you don't know the local address of your host, then run theip -br address show command. On Linux, this shows the network interface,the status of the interface, the local IP, and MAC addresses. For example:eth0 UP 10.128.0.7/32 fe80::4001:aff:fe80:7/64.

Alternatively, you can runipconfig orifconfig to seethe status of your network interfaces.

Locate the outgoing IP address

If you don't know the IP address that the source and destination databases use to communicate with each other (the outgoing IP address), then complete the following steps:

1. Go to the SQL Instances page in the Google Cloud console.

   Go to the Cloud SQL Instances page

2. Click the name of the instance that's associated with the migration job that you're debugging.

3. Scroll down until theConnect to this instance pane appears. In this pane, the outgoing IP address appears.

Open local ports

To verify that your host is listening on the ports you think it is, run thess -tunlp4 command. This tells you what ports are open andlistening.For example, if you have a PostgreSQL database running, then port 5432 should beup and listening. For SSH, you should see port 22.

All local port activity

Use thenetstat command to see all the local port activity. Forexample,netstat -lt shows all the currently active ports.

Connect to the remote host using telnet

To verify that you can connect to the remote host usingTCP, runthetelnet command. Telnet attempts to connect to the IP address andport you give it.

On success, you see the following:

Trying 35.193.198.159...

Connected to 35.193.198.159..

On failure, you seetelnet stops responding until you force-closethe attempt:

Trying 35.193.198.159...

^C..

Client authentication

Client authentication is controlled by a configuration file, which is namedpg_hba.conf (HBA stands for host-based authentication).

Make sure the replication connections section of thepg_hba.conffile on the source database is updated to accept connections from theCloud SQL VPC's IP address range.

Cloud Logging

View logs

gcloudloggingread"projects/[PROJECT_ID]/logs/cloudsql.googleapis.com/postgres.log"--limit=10

Private IP addresses

Connections to a Cloud SQL instance using a private IP address areautomatically authorized forRFC 1918address ranges. Non-RFC 1918 address ranges must be configuredin Cloud SQL asauthorizednetworks. You also need to update the network peering to Cloud SQL toexport any Non-RFC 1918 routes. For example:gcloud compute networks peerings update cloudsql-postgres-googleapis-com --network=NETWORK --export-subnet-routes-with-public-ip --project=PROJECT

The IP range172.17.0.0/16 is reserved for the Docker bridgenetwork. Any Cloud SQL instances created with an IP address in that range will beunreachable. Connections from any IP address within that range to Cloud SQLinstances using private IP address will fail.

VPN troubleshooting

See theGoogle Cloud Cloud VPN troubleshooting page.

Troubleshooting reverse SSH tunnel issues

SSH tunneling is a method to forward some communication on top of an SSHconnection. Reverse SSH tunneling allows setting up an SSH tunnel, but maintainingthat the destination network is the one that initiates the tunnel connection.This is useful when you don't want to open a port in your own network forsecurity purposes.

What you're trying to achieve is to setup the following:Cloud SQL DB ---> Compute Engine VM bastion --->tunnel --->source network bastion --->source DBAlthough Database Migration Service migrates data from a source to a destination, you should enable connectivity from the destination to the source.

It's assumed that:

• TheCompute Engine VM bastion can access theCloud SQL DB.

• Thesource network bastion can access thesource DB (this is achieved by peering the Cloud SQL network to the Compute Engine VM network).

You then set up an SSH tunnel from thesource network bastion to theCompute Engine VM bastion, which routes any incoming connections tosome port on theCompute Engine VM bastion through the tunnel to thesource DB.

Each link in the above scenario can be setup improperly and prevent the entireflow from working. Troubleshoot each link, one by one:

source network bastion --->source DB

1. Connect to thesource network bastion using SSH, or from the terminal if it's the local machine.
2. Test connectivity to the source DB using one of the following methods:
   • telnet [source_db_host_or_ip] [source_db_port] - expect to see the telnet connection strings, ending withConnected to x.x.x.x.
   • [db_client] -h[source_db_host_or_ip] -P[source_db_port] - expect to see access denied

If this fails, then you need to verify that you enabled access from thisbastion to the source DB.

Compute Engine VM bastion --->source DB

1. SSH to theCompute Engine VM bastion (usinggcloud compute sshVM_INSTANCE_NAME)
2. Test connectivity to the source DB using one of the following methods:
   • telnet 127.0.0.1 [tunnel_port] - expect to see the telnetconnection strings, ending withConnected to x.x.x.x.
   • [db_client] -h127.0.0.1 -P[tunnel_port] - expect to see accessdenied

If this fails, then you need to verify that the tunnel is up and running properly.Runningsudo netstat -tupln shows all listening processes onthis VM, and you should seesshd listening on thetunnel_port.

Cloud SQL DB --->source DB

This is best tested bytesting the migration job from Database Migration Service.If this fails, then it means there's some issue with VPC peering or routingbetween the Cloud SQL network and theCompute Engine VM bastionnetwork.

The source database server's firewall must be configured to allow the entireinternalIP range allocated for theprivate service connection of theVPC network that the Cloud SQL destination instance is going to use as theprivateNetwork field of itsipConfiguration settings.

To find theinternal IP range in the console:

1. Go to the VPC networks page in the Google Cloud console.

2. Select the VPC network that you want to use.

3. SelectPrivate services access>Allocated IP ranges for services.

4. Find theInternal IP range associated with the connection created byservicenetworking-googleapis-com.

You can also view the traffic between the Cloud SQL instance and the Compute Engine VM instance intheCloud Logging console in theCloud VPN gateway project. In the Compute Engine VM logs,look for traffic coming from the Cloud SQL instance. In the Cloud SQLinstance's logs, look for traffic from the Compute Engine VM.