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.
Read more about pg_dump here.
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.