Getting started with Postgres

Here’s how to set up one of the most popular open source databases around

Laptop showing the Postgres logo

Postgres is one of the most popular online databases in existence. It powers countless web applications around the world. In this tutorial, we're going to cover how to set up and manipulate this relational database, creating a foundation for a future web application based around Python's Flask framework.

To start, let's install Postgres. The PostgreSQL project provides ready-to-use packages or installers for Linux, macOS, Windows, Solaris, and BSD, but on our Debian-based Linux system we'll just use the package manager by typing: apt-get install postgresql.

Installing the system should start Postgres running as a server daemon. Check this with systemctl status postgresql. If it isn't running, you can start it yourself:

sudo service postgresql start

If you want to stop it, then:

sudo service postgresql stop

But don't do that just now, because we want to play with it. To start, we're going to become the postgres user. In Linux, you do that with a simple sudo -u postgres -i.

From here, we can access psql, which is a terminal-based interface to Postgres that lets us enter queries and see the responses. To do this, just type psql.

The default user doesn't ship with a password, but leaving it that way will trip us up later when we use Python to access the database. Let's use psql to create a password for Postgres. For demonstration purposes, we'll use one you'd never use in production:

ALTER USER postgres PASSWORD 'postgres';

If the system returns ALTER ROLE, you're all set.

Psql uses the \list or \l command to show the current databases.

Name

Owner

Encoding 

Collate 

Ctype 

Access privileges

postgres 

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

 

template0 

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

=c/postgres   +
postgres=CTc/postgres

template1 

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

=c/postgres   +
postgres=CTc/postgres

postgres is the default database that the database is created with. The other two databases are templates. Postgres uses template1 when you create a database yourself, enabling you to define a standard schema by editing this template. template0 is a standard schema that you can use to replace template1 with an out-of-the-box template in case it becomes corrupted.

Rather than using the standard Postgres database, we'd like to create our own. We're going to document old video games, so let's call it retronerds.

CREATE DATABASE retronerds;

\list your databases again and there it is:

Name

Owner

Encoding 

Collate 

Ctype 

Access privileges

postgres 

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

 

retronerds

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

 

template0 

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

=c/postgres   +
postgres=CTc/postgres

template1 

postgres 

UTF8 

en_CA.UTF-8

en_CA.UTF-8

=c/postgres   +
postgres=CTc/postgres

Our video games database needs a table to contain its data. We'd like a title, the game's description, its year of release, a publisher, and a genre.

We'll start by connecting to our database. From within psql, type \c retronerds. Then:

CREATE TABLE games (id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, description VARCHAR(1024) NOT NULL, yor INTEGER NOT NULL, publisher VARCHAR(255) NOT NULL, genre VARCHAR(255) NOT NULL);

id gives us an ID unique to each record, called a PRIMARY KEY. The VARCHAR instructions tell us the maximum characters allowed in that field. NOT NULL means that we can't leave that field blank.

Now, check that the table is there with a \dt (for database tables):

List of relations

Schema 

Name

Type

Owner 

public 

games

table

postgres

You can also examine the structure of the table - its schema - with \d games:

Table "public.games"

Column 

Type 

Collation 

Nullable 

Default 

id 

integer 

 

not null

nextval('games_id_seq'::regclass)

title 

character varying(255)

 

not null

 

description 

character varying(1024)

 

not null

 

yor 

integer 

 

not null

 

publisher 

character varying(255) 

 

not null

 

genre 

character varying(255) 

 

not null

 

Note that because we used the SERIAL descriptor for id when creating our table, the schema tells us that it will automatically increase id every time we add a record. It has also set up the database to index the records using id.

So far, so good, but there's nothing in our table yet. Querying the table in psql with SELECT * FROM games; gives us:

id 

title 

description 

yor 

publisher 

genre 

      

Let's change that. We could use psql to manually add records at this point, but eventually we want to use a Flask app to control our database, which means getting a Python program to do it for us. To make that work, we'll use a python library called psycopg2.

Start a new terminal to keep your psql session alive in the original window. In our new terminal, with Python 3 installed, we'll make a virtual environment to support psycopg2. We don't technically need to do this, but it's a way to keep our system tidy and reliable. Think of it as a room where Python can keep all the libraries it needs for one project without cluttering things up for other projects. Python 3 uses the venv command for this, which we had to install manually on Ubuntu:

sudo apt-get install python3-venv

Then we'll create the virtual environment, calling it flask-app.

python3 -m venv flask-app

Then, 'enter' that room by activating the virtual environment:

source flask-app/bin/activate

Your terminal prompt will change to reflect the virtual environment that you're using. Don't forget to deactivate the virtual environment when you want to install Python libraries for projects other than this one.

For a production system, you'd install the library from source. For our purposes, we can use the Python package manager to do it for us.

python -m pip install psycopg2-binary

Now it's time to connect to the database. Here's a Python program that will import psycopg2 and create a database connection before creating a nested list with three video game records. For the purposes of the exercise, these are currently hard-coded into the program, although future tutorials will look at cleaner ways to automate the process of adding more entries. 

The program then loops through each of those records, building an SQL query for each one by using string formatting to insert the necessary values from each list into the query. The cursor.execute command queues each of these queries, and the conn.commit command writes them to Postgres:

import psycopg2

#set up db connection
conn = psycopg2.connect('dbname=retronerds user=postgres host=localhost password=postgres')
cursor = conn.cursor()

#create and run transactions
videogames = [
    ['Jet Set Willy', 'Split-screen platformer in which Miner Willy has to collect all the items left in his house after a party.', '1984', 'Software Projects','Platform'],
    ['Valhalla', 'Collect six mythical Norse objects while kicking around Asgard', '1983', 'Legend', 'Adventure'],
    ['Chuckie Egg', 'Collect all the eggs before the time expires. Watch out for the hens!', '1983', 'A&F Software', 'Platform']
]
cols = ['title', 'description', 'yor', 'publisher', 'genre']
for game in videogames:
    SQL = "INSERT INTO games (title, description, yor, publisher, genre) VALUES (%s, %s, %s, %s, %s);"
    cursor.execute(SQL, game)

#commit the transactions
conn.commit()

#close the db
conn.close()

Save this as insert-game.py and then run it with python3 insert-game.py. Now, pop back into Postgres and connect to the retronerds database using the steps outlined above. A quick SELECT * FROM games; reveals that our titles are all there.

id 

title 

description 

yor 

publisher 

genre 

1

Jet Set Willy

Split-screen platformer in which Miner Willy has to collect all the items left in his house after a party.

1984 

Software Projects 

Platform

2

Valhalla 

Collect six mythical Norse objects while kicking around Asgard

1983 

Legend 

Adventure

3

Chuckie Egg

Collect all the eggs before the time expires. Watch out for the hens!

1983 

A&F Software  

Platform

Notice how our Python program didn't specify an ID for any of these records. The database schema knew to insert one each for them and increment it automatically.

We've set up Postgres and shown how to manipulate it using both the native psql tool and Python, via a special library. It's the basis for building a create/read/update/delete (CRUD)-style system that you could use to keep records of any kind. 

Other things that you could explore in the future include adding more tables to create a more complex record system with more entities. For example, you might pull the publisher field into its own table, so that you could store more information about each publisher. You could then use a foreign key to link a game to one or more publishers.

However, a system like this also needs a front end so that people who aren't psql ninjas can use it, and in a future tutorial, we'll look at the basics of creating a Python application that will interact with the system in more meaningful ways, including being able to add extra records without directly coding them into a Python program.

Featured Resources

The ultimate law enforcement agency guide to going mobile

Best practices for implementing a mobile device program

Free download

The business value of Red Hat OpenShift

Platform cost savings, ROI, and the challenges and opportunities of Red Hat OpenShift

Free download

Managing security and risk across the IT supply chain: A practical approach

Best practices for IT supply chain security

Free download

Digital remote monitoring and dispatch services’ impact on edge computing and data centres

Seven trends redefining remote monitoring and field service dispatch service requirements

Free download

Most Popular

Apple MacBook Pro 15in vs Dell XPS 15: Clash of the titans
Laptops

Apple MacBook Pro 15in vs Dell XPS 15: Clash of the titans

11 Oct 2021
Best Linux distros 2021
operating systems

Best Linux distros 2021

11 Oct 2021
HPE wins networking contract with Birmingham 2022 Commonwealth Games
Network & Internet

HPE wins networking contract with Birmingham 2022 Commonwealth Games

15 Oct 2021