PostgreSQL and SQLite

Help Desk



The module `postgresql/9.6.12` is available on Theta.  Use `module load postgresql` to add the Postgres 9.6 binaries to your search `PATH`.


On Cooley, the Postgres 9.6 binaries can be added to your search `PATH` via:

source /soft/datascience/balsam/

Downloading binaries

To install your own Postgres binaries visit [](

Creating a New Database Cluster

To create a new database cluster, use:

initdb -D <Database Path> -U $USER

Inside the database directory, you will need to edit the file `postgresql.conf` as follows. Set the options below:

listen_addresses = '*'

port = 12345 # choose a random high-numbered port that isn't yet bound on the host

To allow connections on all interfaces without requiring authentication, you will need to edit the Host-Based Authentication rules as follows.  Edit the file `pg_hba.conf` by appending the following line:

host all all trust

The above instructions assume you are operating in a secure environment and are willing to blindly accept connections from any host in the network. This a convenient but insecure option.

If you want to secure connections to your database, refer to the documentation on [host-based authentication]( and consider using `md5` rather than `trust` based authentication.


Starting and Stopping the Database Cluster

You can start the database located at path `./foo` with with:

pg_ctl -D ./foo start

Stop the database with:

pg_ctl -D ./foo stop

or send a `SIGTERM` to the leader `postgres` process, which will shut down gracefully.

Connecting to a database:

Assuming the database is running on port `12345` of host `thetalogin1`, you can start a psql session in the terminal as follows:

psql -p 12345 -h thetalogin1 -d postgres

The `-h` argument defaults to localhost as is therefore not necessary if you are connecting from the same machine that's currently hosting the database cluster.


If using a Python ORM like [Django](, you will need to configure the `DATABASES` option in your `` to point at the same host and port, according to the documentation.


Note that we connected to the default superuser database called `postgres`. You will want to create a new database under your cluster as follows:

createdb <Database Name> -h <Host> -p <Port>

For example, let's create a database called ToDoList and then connect to it:

createdb ToDoList -h thetalogin1 -p 12345

psql -p 12345 -h thetalogin1 -d ToDoList

Creating tables


To get started with SQL follow the [Postgres tutorial](

A minimal example looks like:

CREATE TABLE todo_list (

   task varchar(200),

   date date,

   priority int



INSERT INTO todo_list VALUES ('Learn SQL', '2019-10-30', 4);


SELECT * FROM todo_list;


import sqlite3

conn = sqlite3.connect('example.db')

c = conn.cursor()