PostgreSQL and SQLite

Theta

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.

Cooley

On Cooley, the Postgres 9.6 binaries can be added to your search PATH via: source /soft/datascience/balsam/postgres-envs.sh

Downloading binaries

To install your own Postgres binaries visit enterpriseDB.com.

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 0.0.0.0/0 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 settings.py 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;

SQLite

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()