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:
1 | brew update && brew doctor |
Once the above command finishes running execute the following to install PostgreSQL:
1 | brew 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 apostgres
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 thepostgres
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 thepsql
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 is5432
, 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:
1 | 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
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:
1 2 3 4 5 | 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:
1 | \dt |
Create a database for Postgres
Use the CREATE DATABASE
SQL keyword to create a new database for the instance:
1 | CREATE DATABASE orkb_db; |
Once you’ve created a database you can connect to it with the \c
command followed by the database name:
1 | \c orkb_db |
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:
1 | listen_addresses = '*' |
You can also put just the server’s IP address in there:
1 | listen_addresses = '123.456.789.10' |
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:
1 2 | 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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started