psql

psql is the official postgres command line tool and is commonly packaged with postgres distributions.

To install psql, refer to the installation instructions in the pg_dump documentation.

Using psql with bit.io

Once you get a connection password from the bit.io Connect Tab you can connect to bit.io through psql. You can connect easily using the Connection String from the Connect tab of your database page. Connect with psql <your_connection_string_here>

Alternately, you can provide the hostname, port, username, database name, and password to connect with psql. All of these details are available from the Connect tab.

Running one-off commands with psql

psql supports running single commands per invocation via the -c option, as opposed to opening the default full-blown postgres shell.

This example will execute the query select 1; against the given bit.io connection string, and then immediately exit.

psql postgresql://my-username:[email protected]/my-username/my-database -c "select 1;"

This may be useful if you wish to execute queries against bit.io from within a bash script, or if you just want to run a single query from your terminal without starting a full session.

Running a query on a fixed interval via the psql shell

Once you have a psql session to bit.io open, you can tell psql to execute any query at a given interval.

The following example will select the current time every 10 seconds until you send a termination signal from your shell (typically via Cntrl + c):

psql (13.5 (Ubuntu 13.5-0ubuntu0.21.04.1), server 14.4)
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, bits: 128, compression: off)
Type "help" for help.

steve/test=# select now();
              now
-------------------------------
 2022-07-21 21:03:34.598205+00
(1 row)

steve/test=# \watch 10
Thu Jul 21 21:03:37 2022 (every 10s)

              now
-------------------------------
 2022-07-21 21:03:37.452451+00
(1 row)

Other helpful psql shortcuts

psql has many built in shortcuts to make working with your postgres database easier. All of these shortcuts begin with a backslash and are typically short in length. Here are some examples:

\dt: display all tables

steve/test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | my-table | table | postgres
(1 row)

\l: show all databases

steve/test=# \l
                                 List of databases
      Name       |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------------+----------+----------+---------+---------+-----------------------
 postgres        | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 steve/test | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0       | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                 |          |          |         |         | postgres=CTc/postgres
 template1       | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                 |          |          |         |         | postgres=CTc/postgres
(4 rows)

\dn: list all schemas

steve/test=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)