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