Welcome to today’s guide on how to install PostgreSQL Database Server on Ubuntu 20.04 (Focal Fossa). PostgreSQL is a popular and powerful open source relational database management system which has been adopted for running mission critical applications. PostgreSQL is based on POSTGRES 4.2.

If you want to check all the cool features of PostgreSQL database, visit the Feature Metrix page to learn more. This guide will dive straight to the installation of PostgreSQL on Ubuntu 20.04 (Focal Fossa) Linux system.

Step 1: Update System

We need to work on an updated system to ensure we don’t get any dependency issues.

sudo apt update
sudo apt -y upgrade

After the system update, we can proceed to install PostgreSQL database server on Ubuntu 20.04, Focal Fossa Linux.

Step 2: Install PostgreSQL Database Server on Ubuntu 20.04 (Focal Fossa)

We’ll install the default version of PostgreSQL database server available on Ubuntu 20.04 without configuring the project’s upstream repositories.

sudo apt install postgresql postgresql-client

Confirm packages installation to proceed.

Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  apport apport-symptoms at bc bcache-tools bolt byobu cryptsetup cryptsetup-run ethtool fonts-ubuntu-console fwupd fwupd-signed git git-man htop
  initramfs-tools-bin klibc-utils kpartx landscape-common libarchive13 liberror-perl libevent-2.1-7 libfl2 libfwupd2 libfwupdplugin1 libgcab-1.0-0
  libgpgme11 libgusb2 libklibc libmspack0 libsgutils2-2 libsmbios-c2 libtss2-esys0 liburcu6 libutempter0 libxmlb1 libxmlsec1 libxmlsec1-openssl lz4
  open-vm-tools pastebinit patch pollinate python3-apport python3-attr python3-automat python3-click python3-colorama python3-constantly
  python3-debconf python3-debian python3-hamcrest python3-hyperlink python3-incremental python3-newt python3-problem-report python3-pyasn1
  python3-pyasn1-modules python3-service-identity python3-systemd python3-twisted python3-twisted-bin python3-zope.interface run-one screen sg3-utils
  sosreport tmux tpm-udev update-notifier-common
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libllvm9 libpq5 libsensors-config libsensors5 postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common sysstat
Suggested packages:
  lm-sensors postgresql-doc postgresql-doc-12 libjson-perl isag
The following NEW packages will be installed:
  libllvm9 libpq5 libsensors-config libsensors5 postgresql postgresql-12 postgresql-client postgresql-client-12 postgresql-client-common
  postgresql-common sysstat
0 upgraded, 11 newly installed, 0 to remove and 4 not upgraded.
Need to get 30.0 MB of archives.
After this operation, 116 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

The version of PostgreSQL installed is 12. This is the latest stable release as of this article writing.

The service is automatically started upon installation. You can confirm if it is running with the command:

$ systemctl status postgresql.service 
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2019-12-26 07:15:55 UTC; 1min 46s ago
   Main PID: 3953 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 614)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Dec 26 07:15:55 ubuntu20 systemd[1]: Starting PostgreSQL RDBMS...
Dec 26 07:15:55 ubuntu20 systemd[1]: Started PostgreSQL RDBMS.

Step 3: Changing Service Listen IP (Optional)

If you need your network applications to connect to the central database, you’ll need to change the listen_addresses line to allow bind to all addresses or a specific IP address available on the server.

# Allow bind to all addresses
listen_addresses = '*'

# Allow bind to one IP addresses
listen_addresses = '192.168.10.20'

For multiple IP addresses, list them and separate with a comma. After the change, restart the service.

sudo systemctl restart postgresql 

Step 4: Update PostgreSQL admin user’s password

The postgresql database admin user is created with the installation of PostgreSQL database server. We need to set a secure password for this user.

sudo su - postgres
psql -c "alter user postgres with password '[email protected]'"

Try create a test database and user.

createuser dbuser
createdb testdb -O dbuser

$ psql testdb 
psql (12.1 (Ubuntu 12.1-1))
Type "help" for help.

testdb=# alter user dbuser with password 'StrongPassword'; 
ALTER ROLE
testdb=# \q
[email protected]:~$ dropdb testdb 

List created databases:

$ psql -l 
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 testdb    | dbuser   | UTF8     | C.UTF-8 | C.UTF-8 | 
(4 rows)

Step 5: Install phpPgAdmin Administration Interface

We can now install the phpPgAdmin administration interface that can be used to manage PostgreSQL database operations.

sudo apt -y install phppgadmin php-pgsql 

To allow login with privileged user accounts such as root or postgres, set the following line to false.

$conf['extra_login_security'] = false;

To only show databases owned by logged in user, set below line.

$conf['owned_only'] = true;

Set IPv4 allowed local connections.

host    all             all             127.0.0.1/32            md5
host    all             all             192.168.10.0/24         md5
host    all             all             10.20.5.0/24            md5

Do the same for web UI access.

sudo nano /etc/apache2/conf-enabled/phppgadmin.conf 

Only localhost connections are allowed, add other IP addresses like below.

Require local
Require ip 192.168.10.0/24
Require ip 10.10.0.0/24

Restart postgresql service after the changes.

sudo systemctl restart postgresql apache2

To access the phppgadmin dashboard, open the URL http://(hostname_or_IP_address/phppgadmin/.

More guides:

Best Books To learn Docker and Ansible Automation

Best Linux Books for Beginners & Experts

Install pgAdmin 4 on Ubuntu

Manage MySQL / MariaDB & PostgreSQL Database Server with Adminer

How To Dockerize Django Application With PostgreSQL Database