Install PostgreSQL on Ubuntu 20.04

Let's install a recent version of Postgresql on Ubuntu 20.04. You will need a unix account with sudo privilege. At the end of this post we will do some introductory database commands.

Get familiar with the Linux install:
$ uname -a
Linux d990 5.4.0-37-generic #41-Ubuntu SMP Wed Jun 3 18:57:02 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
 
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04 LTS
Release:        20.04
Codename:       focal
 
$ df -k
Filesystem      1K-blocks     Used  Available Use% Mounted on
udev              8093132        0    8093132   0% /dev
tmpfs             1627360     1260    1626100   1% /run
/dev/sda2      1921800384 30477352 1793631080   2% /
tmpfs             8136796        0    8136796   0% /dev/shm
tmpfs                5120        0       5120   0% /run/lock
tmpfs             8136796        0    8136796   0% /sys/fs/cgroup
/dev/loop0         160000   160000          0 100% /snap/chromium/1165
/dev/loop2          56320    56320          0 100% /snap/core18/1754
/dev/loop3          63616    63616          0 100% /snap/gtk-common-themes/1506
/dev/loop1          56320    56320          0 100% /snap/core18/1705
/dev/loop7          27776    27776          0 100% /snap/snapd/7264
/dev/loop6          71040    71040          0 100% /snap/lxd/15457
/dev/loop4         160000   160000          0 100% /snap/chromium/1182
/dev/loop5          31104    31104          0 100% /snap/snapd/7777
/dev/loop8          71040    71040          0 100% /snap/lxd/15359
tmpfs             1627356        8    1627348   1% /run/user/1004


Update Ubuntu Linux:
$ sudo apt-get update
$ sudo apt-get upgrade


Read these instructions to set up apt to get the recent Postgresql release. Simply follow-along with the instructions from the link.

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get install postgresql

The command "install postgresql" will run for a minute or two. It should end with:

Success. You can now start the database server using:
    pg_ctlcluster 12 main start

Look at the new unix account "postgres". Note it does have a password to log in to unix:
$ cat /etc/group|tail -1
postgres:x:118:
$ cat /etc/passwd|tail -1
postgres:x:112:118:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

$ sudo grep postgres /etc/shadow
postgres:*:18427:0:99999:7:::


Look at what is running:
$ ps -fu postgres
UID          PID    PPID  C STIME TTY          TIME CMD
postgres  148055       1  0 21:06 ?        00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/po
postgres  148060  148055  0 21:06 ?        00:00:00 postgres: 12/main: checkpointer
postgres  148061  148055  0 21:06 ?        00:00:00 postgres: 12/main: background writer
postgres  148062  148055  0 21:06 ?        00:00:00 postgres: 12/main: walwriter
postgres  148063  148055  0 21:06 ?        00:00:00 postgres: 12/main: autovacuum launcher
postgres  148064  148055  0 21:06 ?        00:00:00 postgres: 12/main: stats collector
postgres  148065  148055  0 21:06 ?        00:00:00 postgres: 12/main: logical replication launcher
postgres  149432  149431  0 21:16 pts/1    00:00:00 -bash


Check the service manager to see if the database startup is automated:
$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2020-06-13 21:06:29 MDT; 14h ago
   Main PID: 147715 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18968)
     Memory: 0B
     CGroup: /system.slice/postgresql.service



To allow connections from outside the machine, edit the postgresql.conf file and add a line for listen_addresses. Then restart postgresql.
$ grep listen /etc/postgresql/12/main/postgresql.conf
listen_addresses = '*'
You may also need to edit pg_hba.conf, to allow connections from outside the machine.


Software versions:
$ psql -V
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg20.04+1)

Let's create a database, list the databases, create a table with a couple rows, and select from the table. From unix command-line, connect via psql:
$ psql
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
Type "help" for help.


postgres=# create database datadb;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 datadb    | postgres | 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)

postgres=# \c datadb
You are now connected to database "datadb" as user "postgres".
datadb=# create table testtable (columna text not null, columnb int not null);
CREATE TABLE
datadb=# insert into testtable values ('insertrowone', 1);
INSERT 0 1
datadb=# insert into testtable values ('insertrowtwo', 2);
INSERT 0 1
datadb=# select * from testtable;
   columna    | columnb
--------------+---------
 insertrowone |       1
 insertrowtwo |       2
(2 rows)

datadb=# \q