Remotely accessing your Scalingo for PostgreSQL® database can sometimes be useful, for example, to conduct investigations, to check or compute data locally, to dump the database content,…

We provide several ways to access your database remotely. Chosing one mostly depends on your needs and preferences.

Using the Interactive Remote Console

1. Make sure you have correctly setup the Scalingo command line tool
2. From the command line, open a console for your PostgreSQL® addon:

   scalingo --app my-app pgsql-console
   

   The output should look like this:

   -----> Starting container one-off-7872  Done in 0.479 seconds
   -----> Connecting to container [one-off-7872]...  
   -----> Process 'pgsql-console my_app_4553' is starting...  
   
   ---> Download and extract the database CLI
   ---> Database CLI installed:
   psql (PostgreSQL) 14.6
   psql (14.6, server 14.8 (Debian 14.8-1.pgdg110+1))
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
   Type "help" for help.
   
   my_app_4553=>
   

3. To exit, type the following command from the PostgreSQL® console, or use the keyboard shortcut CTRL+D:

   \q
   

Using a One-Off

1. Start a one-off container
2. From the command line, use our dbclient-fetcher helper to download and install a bunch of PostgreSQL® tools:

   dbclient-fetcher pgsql
   

   The output should look like this:

   ---> Download and extract the database CLI
   ---> Database CLI installed:
   psql (PostgreSQL) 14.6
   

3. If you ever need a specific version, just add it as a second parameter:

   dbclient-fetcher pgsql 13
   

   The output should look like this:

   ---> Download and extract the database CLI
   ---> Database CLI installed:
   psql (PostgreSQL) 13.11
   

The dbclient-fetcher helper installs the following tools:

• psql
• pg_basebackup
• pg_controldata
• pg_dump
• pg_isready
• pg_recvlogical
• pg_restore
• pg_test_fsync
• pg_upgrade
• pg_archivecleanup
• pg_config
• pg_ctl
• pg_dumpall
• pg_receivewal
• pg_resetwal
• pg_rewind
• pg_test_timing
• pg_waldump

Using Third Party Tools

While using psql to query and administer a PostgreSQL® database is probably the ubiquitous choice for a lot of users, it’s not limited to that. The PostgreSQL® ecosystem indeed offers a very large panel of tools made and provided by third-parties. For example, some might feel more intuitive because of their Graphical User Interface. Some are better integrated with others tools, when some others are more data-visualization centric.

By default, and for security reasons, your PostgreSQL® database is not directly accessible from the Internet and therefore not directly usable with your third-party tool.

To access your database remotely you first need to make it reachable over the Internet:

• either locally, on your computer, via an encrypted tunnel
• or from any location, by enforcing TLS connection and enabling direct Internet access.

Once a secured connection has been established, you should be able to connect to your database with your tool of choice. If you don’t have one yet, we suggest you to take a look at pgAdmin.

Making the Database Reachable Over Internet

Using Our Command Line Tool

Our command line tool provides a db-tunnel command that creates an SSH encrypted tunnel between the computer running the command and our platform, allowing remote secured access to your PostgreSQL® addon.

1. Make sure you have correctly setup the Scalingo command line tool
2. Make sure you have correctly setup SSH authentication on your Scalingo account
3. From the command line, open the tunnel using this command:

   scalingo --app my-app db-tunnel SCALINGO_POSTGRESQL_URL
   

   The output should look like this:

   Building tunnel to my-app-4553.postgresql.a.osc-fr1.scalingo-dbs.com:33712
   You can access your database on:
   127.0.0.1:10000
   

4. The database is now reachable from 127.0.0.1 on port 10000
5. To specify the port number to bind to (default is 10000), use the --port option:

   scalingo --app my-app db-tunnel SCALINGO_POSTGRESQL_URL --port 12345
   

6. To close the tunnel, use the keyboard shortcut CTRL+C:

   ^Cinterrupt catched, aborting…
   

Using OpenSSH

Our command line tool handles it in a single command, but you might want to use the tunnel without it. With the standard OpenSSH client, the way to build the tunnel is:

1. Identify the connection URI for your database
2. Make sure to understand how the connection URI is built
3. From the command line, run the following command to create the tunnel:

   ssh -L <local_port>:<db_url>:<db_port> git@<ssh_hostname> -p 22 -N
   

   With:

   • local_port: local port number you want to bind to
   • db_url: URL of your database, from the environment variable
   • db_port: port
   • ssh_hostname: depends on your region:
     • For osc-fr1: ssh.osc-fr1.scalingo.com
     • For osc-secnum-fr1: ssh.osc-secnum-fr1.scalingo.com
   • (the SSH user is always git and the SSH port is always 22)

   The output should stay blank

4. The database is now reachable from 127.0.0.1 on local_port

Enabling Direct Access Over Internet

1. Make sure you have TLS enforced
2. From your web browser, open your database dashboard
3. Select the Overview tab
4. Locate the TLS/Internet Access block
5. Enable the Internet Accessibility option
6. The database is now available using the corresponding connection URI

Using pgAdmin

pgAdmin is probably the most popular and feature rich administration and development platform for PostgreSQL®. It’s open-source, it supports many platforms and comes with a Graphical User Interface, making it a reference tool for PostgreSQL®.

Accessing Through a DB Tunnel

1. Create a DB tunnel to make the database reachable from the computer running pgAdmin
2. Once the connection established, open pgAdmin
3. Select the Dashboard tab
4. Click the Add New Server shortcut
5. In the new window, select the General tab and give a name to the server:

6. Select the Connection tab and fill the required information:
   • Host name/address is now 127.0.0.1
   • Port is most probably 10000, unless you set it to something else
   • Username and Password must be gathered from the connection URI

7. Click the Save button

Accessing Through SSH

pgAdmin also allows to connect to the database through an SSH tunnel, like described above.

1. Open pgAdmin
2. Select the Dashboard tab
3. Click the Add New Server shortcut
4. In the new window, select the General tab and give a name to the server:

5. Select the Connection tab and fill in the required information from the connection URI:

6. Select the SSH Tunnel tab and toggle the Use SSH tunneling option
7. Fill in the required information:
   • Tunnel host depends on your region:
     • For osc-fr1: ssh.osc-fr1.scalingo.com
     • For osc-secnum-fr1: ssh.osc-secnum-fr1.scalingo.com
   • Tunnel port must be set to 22
   • Username is always git
   • Authentication: chose Identity file and select the SSH key linked to your Scalingo account in the Identity file field
   • Password: your SSH key password, if any

8. Click the Save button