PostgreSQL

Introduction

Our next project will incorporate a database to save users’ data. The plan is to allow a user to register a username and password to the site to be able to save favourites to the account. This post will document the process I took to set up a local PostgreSQL database for use in this project.

Environment set up and package installation

As a first step in many new projects, we will create a new conda environment to keep all our installed packages separate and organized.

1
2
source ~/anaconda3/bin/activate
conda create -n <new_env>

Once created we will activate and install the necessary PSQL packages.

1
2
conda activate <new_env>
conda install psycopg2 postgresql

Since our app will be written in Python, we will use the psycopg2 package to allow our Python code to integrate with our PSQL database.

Creating and starting a new PSQL database cluster

Once everything is installed, we will make a new PSQL database cluster: 

1
initdb <cluster_name>

We can then start and stop the cluster server in detached mode using:

1
2
3
pg_ctl -D <cluster_name> start

pg_ctl -D <cluster_name> stop

We can also start the cluster server in attached mode to monitor logs using:

1
postgres -D <cluster_name>

Creating a new database

Once the database server cluster is running, we can now create a new database and user and enter the PSQL shell:

1
2
3
createdb <database_name>
createuser <user_name>
psql <database_name> <user_name>

The purpose of creating a new user is to strip superuser privileges so that we do not unintentionally compromise our database cluster.

Databases and users can be removed using:

1
2
dropdb <database_name>
dropuser <user_name>

In the PSQL shell, there are new functions that will be useful for managing databases and users.

1
2
3
4
5
6
\conninfo # gives information about the current database and user
\l # lists all available databases
\c <database_name> # connect to <database_name> as current user
\du # lists all users
\dt # lists all tables
\q # exit PSQL shell