Getting Started With Postgres 13 on Ubuntu 20.04

PostgreSQL 13, the latest release of the Postgres database software, comes with many under-the-hood improvements. While being the most popular and versatile open-source RDBMS around, it is not the easiest to setup and get started. Read on to learn how you can get going with the latest version of Postgres on the latest LTS version of the Ubuntu server.

Installation

Ubuntu 20.04 comes with Postgres 12 from it’s universe repository. Since we want version 13, we can directly use the PostgreSQL project’s official APT repository. This repository contains binaries for Ubuntu 20.04, and also includes packages for various extensions that you might want to install later.

Let’s setup the repository like this (note that “focal” is the code name for Ubuntu 20.04):

# add the repository
sudo tee /etc/apt/sources.list.d/pgdg.list <<END
deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main
END

# get the signing key and import it
wget https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo apt-key add ACCC4CF8.asc

# fetch the metadata from the new repo
sudo apt-get update

We can now install the PostgreSQL server and other command-line tools using:

sudo apt-get install -y postgresql-13

The installation does a few things:

It installs the PostgreSQL server, utilities and a command-line client called psql.
It creates a Linux system user called postgres. All data files are owned by this user, and all processes run as this user.
It creates a database cluster (see below). In this cluster, it creates a database, also called postgres.
It creates one PostgreSQL user (not the Linux system user), also called postgres. This PostgreSQL user has superuser privileges.

You can see this is beginning to get confusing!

Database Clusters

In Postgres terms, we now have a single database cluster up and running. A single database cluster can contain one or more databases. In the database cluster that we now have, there is a database called “postgres”. (There are also a couple of “template” databases that we can ignore for now.)

A database cluster is managed by a main postgres process called the postmaster. It spawns various child processes that either perform various system tasks or handle incoming client connections. Have a look at the currently running processes:

alice@ubu:~$ ps -o uname,pid,ppid,cmd -H -U postgres
USER         PID    PPID CMD
postgres    4880       1 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
postgres    4882    4880   postgres: 13/main: checkpointer
postgres    4883    4880   postgres: 13/main: background writer
postgres    4884    4880   postgres: 13/main: walwriter
postgres    4885    4880   postgres: 13/main: autovacuum launcher
postgres    4886    4880   postgres: 13/main: stats collector
postgres    4887    4880   postgres: 13/main: logical replication launcher

Here the postmaster process is 4880 and it has spawned 6 child processes that handle various housekeeping activities. You can also see the location of the cluster (/var/lib/postgresql/13/main) and the location of the configuration file (/etc/postgresql/13/main/postgresql.conf).

Reloading and Restarting

At various times, you may need to reload or restart your Postgres server. Reloading causes Postgres to re-examine it’s configuration files and apply the changes. If there are no changes to the configuration files, nothing bad happens. Reloading does not disturb the currently connected clients. To reload your Postgres server, you can do:

sudo systemctl reload postgresql

Some configuration changes will take effect only after you restart the server. This is more disruptive and will disconnect all connected clients. To restart, you can:

sudo systemctl restart postgresql

Log Files

As you can see, there is a systemd service called postgresql that you can use to control the postmaster. If the service does not start, you can check it’s status to check for error messages:

alice@ubu:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2020-10-29 04:52:29 UTC; 25min ago
   Main PID: 4557 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 1075)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Oct 29 04:52:29 ubu systemd[1]: Starting PostgreSQL RDBMS...
Oct 29 04:52:29 ubu systemd[1]: Finished PostgreSQL RDBMS.

The PostgreSQL server writes a log file, which you can check for more detailed error messages. This file is located at /var/log/postgresql/postgresql-13-main.log:

alice@ubu:~$ cat /var/log/postgresql/postgresql-13-main.log
2020-10-29 04:52:34.096 UTC [4880] LOG:  starting PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
2020-10-29 04:52:34.097 UTC [4880] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-10-29 04:52:34.099 UTC [4880] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-10-29 04:52:34.106 UTC [4881] LOG:  database system was shut down at 2020-10-29 04:52:31 UTC
2020-10-29 04:52:34.112 UTC [4880] LOG:  database system is ready to accept connections

Connecting to Your Postgres Server

Now that we have our server up and running, let’s try to connect to it. By default, the server listens only for:

TCP connections from 127.0.0.1 on port 5432, and
Unix domain sockets in /var/run/postgresql

Because of the default configuration, the only way to connect to the server right now is via the Unix socket from a process that is running as the system user postgres. Let’s run the standard interactive client psql like this:

alice@ubu:~$ sudo -u postgres psql postgres
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1))
Type "help" for help.

postgres=#

Here we’re running psql as the system user postgres (“sudo -u postgres psql”) and connecting to the database called “postgres” (the last “postgres” on the command-line.) The “postgres=#” prompt indicates the name of the currently connected database (“postgres”) and that we have superuser privileges (“#” as opposed to “$”).

The connection happened via Unix sockets (this is the default method in psql). Since by default the postgres user does not have a password and the default configuration requires password authentication for TCP connections, it is not possible to connect over 127.0.0.1:5432 right now.

Allowing Incoming Connections From an Internal Network

First let’s change the configuration to allow connections from an internal network. Assuming our server’s IP on this network is 10.1.2.3, we can edit the main configuration file at /etc/postgresql/13/main/postgresql.conf and change the lines:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

to:

listen_addresses = 'localhost,10.1.2.3'

We also need to tell Postgres to use password authentication for connections coming in from these networks. For this, edit another configuration file called /etc/postgresql/13/main/pg_hba.conf and change the line:

host    all             all             127.0.0.1/32            md5

to:

host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             10.1.0.0/16             scram-sha-256

(Assuming the internal network is 10.1.0.0/16.)

We’ve also changed the default md5 method to the newer and more secure scram-sha-256. All other occurances of md5 in the file should also be replaced with scram-sha-256. If your application or database driver does not support this method, continue to use the md5 method instead.

For these changes to take effect, you need to restart the server:

sudo systemctl restart postgresql

Creating a Regular User and Database

We’re almost there!

We can now create a regular user that our application can connect as, and a database over which it has full control. Connect as the superuser postgres locally from the server machine to do this:

alice@ubu:~$ sudo -u postgres psql postgres
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1))
Type "help" for help.

postgres=# SET password_encryption = 'scram-sha-256';
SET
postgres=# CREATE USER alice PASSWORD 's3cr3tp@ss';
CREATE ROLE
postgres=#

(Omit the first command if you want to use md5 instead.) This created a user called alice with the password s3cr3tp@ss. Let’s also create a database which this user will own:

postgres=# CREATE DATABASE app1 OWNER alice;
CREATE DATABASE
postgres=#

The database is called app1. Since alice owns this database, all operations within the database (like creating tables, inserting rows) are allowed if the application connects as the user alice.

Let’s try connecting as alice, over the network:

~$ psql -h 10.1.2.3 -U alice app1
Password for user alice:
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

app1=>

Cool! We’re now connected to the database app1 as the user alice.

Deleting Databases, Backing up and Restoring

Here are a few tricks that can help as you continue working with your Postgres server:

Deleting a database

You can delete the database you just created (“app1”), like this:

alice@ubu:~$ psql -h 127.0.0.1 -U alice app1
Password for user alice:
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

app1=> \c postgres
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "alice".
postgres=> DROP DATABASE app1;
DROP DATABASE
postgres=>

Note that you need to switch to another database first using the “\c” command of psql.

To create another database, or to recreate app1, connect as the superuser and do “CREATE DATABASE” like before.

Backup up the database

The easiest way to backup the data in your database is to use pg_dump like this:

alice@ubu:~$ pg_dump -h 127.0.0.1 -U alice -f backup.sql app1
Password:

This creates a SQL file called “backup.sql” that contains all the SQL commands required to recreate the schema and data in the database app1, in text format. You can execute these commands in any database, and the schema and data will get populated into that database.

Restoring data

The SQL command file you created above can be restored like so:

alice@ubu:~$ psql -h 127.0.0.1 -U alice app2
Password for user alice:
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

app2=> \i backup.sql
SET
SET
(..snip..)

Note that we restored the schema and data into another database, app2. The “\i” command of psql lets you execute the SQL commands from a file.

Next Steps

There are a whole bunch of articles, tutorials, videos and courses out there to help you get more proficient with PostgreSQL. Do spend some time however, with the official documentation here, that provides authoritative and extensive coverage of all PostgreSQL features, syntax and bundled utilities.

About pgDash

pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.

pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, diagnostics, alerting, teams and more. Checkout the features here or signup today for a free trial.