PostgreSQL and SQLite

Help Desk

Theta

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](https://www.enterprisedb.com/download-postgresql-binaries).

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](https://www.postgresql.org/docs/9.6/auth-pg-hba-conf.html) 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](https://docs.djangoproject.com/en/2.2/ref/settings/#databases), 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](https://www.postgresql.org/docs/9.6/tutorial-sql-intro.html).

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()

Systems