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.
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):
We can now install the PostgreSQL server and other command-line tools using:
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!
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:
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
/var/lib/postgresql/13/main) and the location of the configuration
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:
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:
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:
The PostgreSQL server writes a log file, which you can check for more detailed
error messages. This file is located at
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:
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
change the lines:
We also need to tell Postgres to use password authentication for connections
coming in from these networks. For this, edit another configuration file
/etc/postgresql/13/main/pg_hba.conf and change the line:
(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
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:
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:
(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:
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:
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:
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:
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.
The SQL command file you created above can be restored like so:
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.
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.
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.