Use Psql to Connect to a Database for ObjectRocket

Introduction

ObjectRocket has a reliable and stable cloud platform for PostgreSQL databases, and they make it very easy for you to manage your data. This article will show you how to connect to your Postgres cluster instance running on the cloud, using the psql command line interface, in order to create databases for your Postgres tables.

Prerequisites

If you haven’t done so already, you’ll need to setup an instance of Postgres in the Mission Control panel for your ObjectRocket account.

Install PostgreSQL

You’ll need to have v11 or v12 of PostgreSQL installed on your local machine depending on which version you have specified in your ObjectRocket instance of Postgres.

Install PostgreSQL on a Mac

On macOS you can use Homebrew to install PostgreSQL, or you can download a DMG installer from the PostgreSQL website.

Use brew to install Postgres

If you’d like to use Homebrew to install Postgres you should first update and check for problems with your Homebrew installed by running the following in your macOS terminal:

Once the above command finishes running execute the following to install PostgreSQL:

PostgreSQL version

Use the psql -V and postgres -V commands to get the version numbers for both the psql CLI and Postgres.

PostgreSQL create user example for ObjectRocket

You’ll need to create a Postgres user and password, with admin access to your PostgreSQL cluster instance, so that you can use it to connect to a database on the cluster remotely.

How to set username and password in PostgreSQL

Click on the VIEW MORE DETAILS button for your ObjectRocket instance, and then navigate to the “USERS” tab, and create one if you haven’t done so already. Make sure to check the “Admin” checkbox so that you create the user as a Postgres superuser, and create a strong password for the user.

NOTE: If you’re planning on connecting to your ObjectRocket instance in a production environment on a server, then you’ll need to whitelist the IP address for that server as well before you can access it. Only the localhost 0.0.0.0/0 IP address is whitelisted by default in the instance.

Use psql to connect to a database

Open a terminal on your local machine that you’d like to use to remotely connect to your ObjectRocket instance of Postgres.

Use the psql command in a terminal or command prompt window to connect to your ObjectRocket database and user. If you get a command not found error then it means Postgres wasn’t installed correctly, or you need to export the path for PostgreSQL and psql first.

How to connect to a PostgreSQL database

The psql command to access the client interface takes the following arguments to connect to a database:

Postgres database name

• -d — The -d flag is for the database name. By default you should have a postgres database on your ObjectRocket instance that you can connect to. Once you access psql you can create more databases and then connect to the new database via the \c command in psql.

Default username in PostgreSQL

• -U — The -U option is for the username. The value passed to this parameter should be the username that you created for the cluster in your ObjectRocket instance. The default user for PostgreSQL is the postgres admin user.

Host name for PostgreSQL

• -h — You’ll have to navigate to the “CONNECT” tab in your ObjectRocket Postgres instance to get the “ingress” host domain, and then pass that to the -h flag when executing the psql command.

Port for PostgreSQL

• -p — The port for the cluster can also be found under the instance’s “CONNECT” tab, and it should be a four-digit number that follows the ingress host domain separated by a colon. The default port for Postgres is 5432, and psql will attempt to access a cluster via that port number if none is specified.

Use psql to connect to a database for ObjectRocket

The following is an example of the psql command being used to connect to an ObjectRocket instance that passes the connection values for the instance using the flags and options mentioned above:

Once you press return Postgres should prompt you for the user password. The psql command above attempts to connect to the postgres database with the orkb username as an example.

Use psql to run a SQL command

The psql command also accepts the -c flag which allows you to pass and execute an SQL string to your ObjectRocket instance without connecting to it.

The following multiline psql bash command will connect to a database called orkb_db just to execute an SQL statement and have Postgres return some table information:

Once you input the above command and press Return psql will prompt you for the user password, and then return the table information.

Use psql to create a database

Now that you’ve accessed a database on the ObjectRocket cluster instance you should be able to list, create, and connect to databases on that cluster, assuming that the username you created for the instance has admin privileges.

Postgres list databases command

Once you’re inside the psql CLI you can use the following command to list your databases stored on the ObjectRocket instance:

Create a database for Postgres

Use the CREATE DATABASE SQL keyword to create a new database for the instance:

Once you’ve created a database you can connect to it with the \c command followed by the database name:

NOTE: Built-in psql commands that start with a backslash (\) to not have to be terminated with semicolons (;), however, standard SQL-compliant statements need to be terminated with a semicolon, or psql will assume that it’s a multiline SQL statement.

Once you’re finished with psql you can end your connection to the ObjectRocket cluster instance using the \q command.

Configure PostgreSQL for remote connection

If you have any connection issues while attempting to connect to a whitelisted IP address for a remote server, then you’ll have to locate the postgresql.conf for your server’s Postgres service, and configure the listen_addresses value to accept your server’s IP.

You can set a wildcard (*) for the variable so that Postgres will listen for all IP addresses:

You can also put just the server’s IP address in there:

The listen_addresses variable should be set to 'localhost' by default. Make sure to restart Postgres once you’ve save your changes for the modification to take effect.

‘pg_hba.conf’ location and remote connections

You’ll also want to locate the pg_hba.conf (PostgreSQL “host-based authentication”) file for your Postgres service that’s running, and make sure that you allow access to your server’s IP as well.

In that file you should see something like the following towards the end of the file, and you’ll need to modify it to match your server’s settings:

NOTE: It’s highly recommended that you use the md5 (MD5 message-digest algorithm) form of authentication in lieu of the less-secure trust authentication, so that a password is required whenever someone attempts to access the Postgres databases.

Make sure to replace those values with your ObjectRocket user and your server’s IP address so that your server has access to the Postgres cluster, and make sure to restart the PostgreSQL service on your machine or server for the changes to take effect.

Conclusion

We now know how to setup an ObjectRocket instance of a PostgreSQL cluster, and connect to it using the psql CLI for Postgres in order to create databases for the instance. Keep in mind that you can use those same connection parameters for programming language database adapters (like psycopg2 for Python, or Node’s “pg” adapter) as well.