Install PostgreSQL database server on Ubuntu 20.04 (Focal Fossa)

Welcome to today’s guide on how to install a PostgreSQL database server on Ubuntu 20.04 (Focal Fossa). PostgreSQL is a popular and powerful open source relational database management system that has been used to run mission-critical applications. PostgreSQL is based on POSTGRES 4.2.

If you want to check all the great features of the PostgreSQL database, visit the Feature Metrix page for more information. This guide will directly introduce the installation of PostgreSQL on Ubuntu 20.04 (Focal Fossa) Linux system.

Step 1: Update the system

We need to work on the updated system to ensure that there are no dependencies.

sudo apt update
sudo apt -y upgrade

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

Step 2: Install the PostgreSQL database server on Ubuntu 20.04 (Focal Fossa)

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

sudo apt install postgresql postgresql-client

Confirm that the package installation continues.

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 at the time of writing.

The service starts automatically after installation. You can confirm it is running with the following 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: Change the service listening IP (optional)

If you need a web application to connect to a central database, you need to change listen_addresses Allow binding to all addresses or specific IP addresses 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 them with commas. After making changes, restart the service.

sudo systemctl restart postgresql

Step 4: Update the password of the PostgreSQL administrator user

The PostgreSQL database administrator user is created by installing the 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 to 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 the databases created:

$ 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 the phpPgAdmin management interface

Now we can install the phpPgAdmin management interface, which can be used to manage PostgreSQL database operations.

sudo apt -y install phppgadmin php-pgsql

To allow login with a privileged user account (such as root or postgres), set the following line to false.

$conf['extra_login_security'] = false;

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

$conf['owned_only'] = true;

Set the allowed IPv4 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 allow local hosts to connect, add other IP addresses as shown below.

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

Restart the postgresql service after making 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

The best Linux books for beginners and experts

Install pgAdmin 4 on Ubuntu

Manage MySQL / MariaDB and PostgreSQL database servers with administrator

How to Dockerize a Django Application with a PostgreSQL Database

Step 1: Update the system

Step 2: Install the PostgreSQL database server on Ubuntu 20.04 (Focal Fossa)

Step 3: Change the service listening IP (optional)

Step 4: Update the password of the PostgreSQL administrator user

Step 5: Install the phpPgAdmin management interface

More related tutorials:

Last posts