PostgreSQL and SQLite
postgresql/9.6.12 is available on Theta. Use
module load postgresql to add the Postgres 9.6 binaries to your search
On Cooley, the Postgres 9.6 binaries can be added to your search
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
SIGTERMto the leader
postgresprocess, 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
-hargument 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
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()