Knowledge Base

How to set up a remote PostgreSQL connection

This article describes how to use a local computer to connect to your PostgreSQL databases stored remotely on A2 Hosting servers.

Choose a remote PostgreSQL connection method

A2 Hosting servers are behind a firewall that restricts remote access to PostgreSQL port 5432. Therefore, you must choose one of the following methods to access your PostgreSQL databases remotely:

  • SSH tunnel: This is the preferred, and more secure, method. You set up an SSH tunnel that forwards a port on your local computer to the remote PostgreSQL server. The PostgreSQL traffic is encrypted by the SSH tunnel, and firewall settings on the A2 Hosting server do not need to be changed.
  • Direct connection: You can set up a direct connection between your local computer and the remote PostgreSQL server. However, this method will not work until A2 Hosting opens a port in its firewall to allow connections to PostgreSQL port 5432 from your IP address. This method only works if your local site has a static IP address. If you need further assistance, please open a support ticket with our Guru Crew on the Customer Portal at https://my.a2hosting.com.

After you have set up a remote PostgreSQL connection, you can use a client application such as psql to work with your databases.

Method #1: Set up an SSH tunnel

The procedure you follow to set up an SSH tunnel between your local computer and the A2 Hosting server depends on the local computer's operating system.

Microsoft Windows

In order to use an SSH tunnel on a computer running Microsoft Windows, you need an SSH client program. A2 Hosting recommends using PuTTY, which you can download here. After you have downloaded the PuTTY executable to your local computer, you can set up an SSH tunnel.

To set up an SSH tunnel on a computer running Microsoft Windows:

  1. Start PuTTY.
  2. In the Category pane, expand Connection, expand SSH, and then click Tunnels.
  3. In the Source port text box of the Port Forwarding section, type 5432.  This is the local port to forward.
    If you are running a PostgreSQL server on your local computer, you might have to change the port number (for example, to 5433) to avoid port conflicts.
  4. In the Destination text box, type localhost:5432.
  5. Confirm that the Local and Auto radio buttons are selected.
  6. Click Add.
  7. In the Category pane, click Session.
  8. In the Host Name (or IP address) text box, type your web site's domain name or IP address.
  9. In the Port text box, type 7822.
  10. Confirm that the Connection type radio button is set to SSH.
  11. Click Open.
  12. If a PuTTY security alert about the server's host key appears, click Yes.
  13. When the login as prompt appears, type your A2 Hosting username, and then type your password.
  14. When the remote server's command line prompt appears, the SSH tunnel is established and you can use your PostgreSQL client applications on the local computer.
    To verify that PuTTY is forwarding ports correctly, you can click the icon in the top-left corner of the PuTTY session window, and then click Event Log. If port forwarding is working correctly, you see a line similar to:
    Local port 5432 forwarding to localhost:5432
Mac OS X and Linux

To set up an SSH tunnel on a computer running Mac OS X or Linux:

  1. Open a terminal window and type the following command at the command line. Replace username with your A2 Hosting username, and example.com with your site's domain name:
    ssh -p 7822 username@example.com -L 5432:localhost:5432
    The number before localhost is the port number at the local end of the tunnel. The number after localhost is the port number at the remote (server) end of the tunnel. If you are running a PostgreSQL server on your local computer, you might have to change the first 5432 to another port number (for example, to 5433) to avoid port conflicts.
  2. Type your password, and then press ENTER. When the remote server's command line prompt appears, the SSH tunnel is established and you can use your PostgreSQL client applications on the local computer.

    For example, if the psql client application is installed on your local computer, you can type the following command to access a database on the A2 Hosting server. Replace USERNAME with the database user, and DBNAME with the name of the database:

    psql -U USERNAME -h localhost -p 5432 DBNAME

Method #2: Set up a direct connection

To set up a direct connection between your local site and the PostgreSQL server, submit a request at http://my.a2hosting.com to open the firewall on port 5432 for your account. Remember that your local site must have a static IP address. In the request, make sure you include your local IP address. You can obtain your IP address at http://ipfinder.us.

More Information

To view the online documentation for PuTTY, please visit http://the.earth.li/~sgtatham/putty/0.60/htmldoc/index.html.