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 definitive guide to warehouse efficiency

Get your free guide to creating efficiencies in the warehouse

Free download

The total economic impact™ of Datto

Cost savings and business benefits of using Datto Integrated Solutions

Download now

Three-step guide to modern customer experience

Support the critical role CX plays in your business

Free download

Ransomware report

The global state of the channel

Download now

Most Popular

How to find RAM speed, size and type
Laptops

How to find RAM speed, size and type

17 Sep 2021
What are the pros and cons of AI?
machine learning

What are the pros and cons of AI?

8 Sep 2021
Best MDM solutions 2020
mobile device management (MDM)

Best MDM solutions 2020

17 Sep 2021