Use Psql to Connect to a Database for ObjectRocket
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.
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.
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:
brew update && brew doctor
Once the above command finishes running execute the following to install PostgreSQL:
brew install postgresql
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.
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
psql command to access the client interface takes the following arguments to connect to a database:
Postgres database name
-dflag is for the database name. By default you should have a
postgresdatabase 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
\ccommand in psql.
Default username in PostgreSQL
-Uoption 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
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
-hflag when executing the
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:
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U orkb -p 4142 -d postgres
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
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:
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \
-U orkb \
-p 4142 \
-d orkb_db \
-c "SELECT * FROM example_table;"
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
CREATE DATABASE SQL keyword to create a new database for the instance:
CREATE DATABASE orkb_db;
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
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:
listen_addresses = '*'
You can also put just the server’s IP address in there:
listen_addresses = '123.456.789.10'
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:
host all all 0.0.0.0/0 md5
host all all ::/0 md5
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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started