Getting started with PostgreSQL on Ubuntu

PostgreSQL is an object-relational database management system that can be used for persisting data in a Django web application. By default, a new Django project is configured to use SQLite. However, depending on your project's requirements you may not want to stick with it.

As we build out applications in future posts we'd be following The Twelve-Factor App methodology. One of the factors, dev/prod parity, teaches us that we should aim to keep our development, staging and production environments as similar as possible. Since we'd be using PostgreSQL in production it follows that we should be using it in development as well.

In this post I'd show you how to install and configure PostgreSQL for use as your development database.

Install

N.B. The instructions that follow were verified on a 64-bit Ubuntu 14.04 LTS system. You can find the latest Ubuntu release here.

PostgreSQL is available in all Ubuntu versions by default. However, Ubuntu "snapshots" a specific version of PostgreSQL that is then supported throughout the lifetime of that Ubuntu version. For Ubuntu 14.04 LTS that version of PostgreSQL is 9.3.

$ sudo apt-get update
$ apt-cache policy postgresql
postgresql:  
  Installed: (none)
  Candidate: 9.3+154ubuntu1
...

Thankfully, the PostgreSQL Global Development Group (PGDG) maintains an APT repository of PostgreSQL packages for Ubuntu. So to install the latest stable version of PostgreSQL, 9.6 as of this writing, you can do the following:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

$ sudo apt-get update
$ sudo apt-get install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6 libpq-dev pgadmin3

$ psql --version
psql (PostgreSQL) 9.6.1  

That's it. PostgreSQL is installed.

Here are some notes about the packages:

  • postgresql-9.6 - core database server
  • postgresql-client-9.6 - client libraries and client binaries
  • postgresql-contrib-9.6 - additional supplied modules
  • libpq-dev - libraries and headers for C language frontend development; psycopg depends on this package for proper installation
  • pgadmin3 - pgAdmin graphical administration utility

N.B. You can read here and here for more details.

The anatomy of the PostgreSQL installation

PostgreSQL uses a client/server architecture.

The server application manages the database files, accepts connections to the database from client applications, and performs database actions on behalf of the clients. The database server program is called postgres.

The client applications make requests to the server application to perform database operations. It could be a text-oriented tool like psql, a graphical application like pgAdmin, a Django web application or a specialized database maintenance tool.

PostgreSQL manages database access permissions using the concept of roles.

During installation the system bootstraps itself by creating an operating system user called postgres, a database "superuser" role named postgres and three databases named template0, template1 and postgres.

N.B.: You can learn more about the template databases here.

$ grep postgres /etc/passwd
postgres:x:109:115:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

# Login as operating system user postgres
$ sudo -iu postgres

# Connect to the postgres database as the database user postgres
$ psql
psql (9.6.1)  
Type "help" for help.

postgres=# \du  
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \l  
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \q

# Connect to the template1 database as the database user postgres
$ psql template1

The operating system user, postgres, has their home directory set to /var/lib/postgresql. It contains the data directory, /var/lib/postgresql/9.6/main, which is the location where the databases are stored.

The database "superuser" role, postgres, isn't created with a password. However, we're still able to connect to the database because peer authentication is enabled for the role.

The peer authentication method works by obtaining the client's operating system username from the kernel and using it as the allowed database username.

That's why we need to login as the operating system user postgres before we can connect as the database role postgres.

We can change the authentication method by updating the file /etc/postgresql/9.6/main/pg_hba.conf. However, the default that is enabled for Unix domain socket connections is fine for our local development purposes.

...
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# "local" is for Unix domain socket connections only
local   all             all                                     peer  
...

N.B.: In PostgreSQL, a user is a role with the login privilege.

For day-to-day usage, we usually create a non-superuser role with just enough privileges to create, connect to and work with a specific database. So, let's configure one now.

Configure

Firstly, invoke a login shell as the postgres operating system user.

# What's my operating system username
$ whoami
dwayne

# Login as the postgres operating system user
$ sudo -iu postgres
$ whoami
postgres  

Then, create a role specifying the permissions you want it to have. Name the role after your operating system username so that peer authentication will work.

$ createuser --createdb dwayne

The --createdb option allows the new user to create databases. See createuser to learn more.

Logout of the postgres user account.

$ logout
$ whoami
dwayne  

Now, let's attempt to connect to the template1 database as the new role/user dwayne.

$ psql template1
psql (9.6.1)  
Type "help" for help.

template1=> \du  
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 dwayne    | Create DB                                                  | {}

Finally, let's create our own database, add a table and populate it with some data.

$ createdb blog
$ psql blog
blog=> \l  
                                        List of databases
          Name          |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------------------+----------+----------+-------------+-------------+-----------------------
 blog                   | dwayne   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                        |          |          |             |             | postgres=CTc/postgres
 template1              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                        |          |          |             |             | postgres=CTc/postgres
(4 rows)

blog=> CREATE TABLE post (  
blog(>   id integer PRIMARY KEY,  
blog(>   title varchar(100),  
blog(>   body text  
blog(> );  
CREATE TABLE

blog=> INSERT INTO post VALUES (1, 'Hello, world!', 'This is my first post.');  
INSERT 0 1

blog=> SELECT * FROM post;  
 id |     title     |          body          
----+---------------+------------------------
  1 | Hello, world! | This is my first post.
(1 row)

Read these to learn more:

Bonus

If you intend to use the pgadmin3 graphical client then you'd have to connect using one of the password-based authentication methods.

The default for all users and all databases, with respect to local TCP/IP connections, is correctly set to md5 in /etc/postgresql/9.6/main/pg_hba.conf.

...
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5  
...

Therefore, you just need to set a password for postgres and everything would work fine.

$ sudo -iu postgres
$ psql
psql (9.6.1)  
Type "help" for help.

postgres=# ALTER ROLE postgres WITH PASSWORD 'password';  
ALTER ROLE

postgres=# \q

$ pgadmin3

You're all set.

Resources

P.S. I know that's a lot to take in. So if you have any questions concerning installing, configuring or using PostgreSQL then write to me in the comments below and I'd be very happy to answer them.