Learn how to - Install PostgreSQL Database Server on Ubuntu 22.04|20.04

Felix Williams Databases Linux Tutorials March 7, 2023

Learn how to – Install PostgreSQL Database Server on Ubuntu 22.04|20.04.

Welcome to today’s guide on how to install PostgreSQL Database Server on Ubuntu 22.04|20.04. 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 22.04|20.04 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 full-upgrade

Check if a reboot is required after the upgrade

[ -f /var/run/reboot-required ] && sudo reboot -f

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

Step 2: Install PostgreSQL Database Server

We’ll install the default version of PostgreSQL database server available on Ubuntu 20.04|22.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 'MySt0ngDBP@ss'"

Try create a test database and user.

createuser dbuser
createdb testdb -O dbuser

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

testdb=# alter user dbuser with password 'StrongPassword'; 
ALTER ROLE
testdb=# q
postgres@ubuntu20:~$ 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.

$ sudo vim /etc/phppgadmin/config.inc.php
$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/.

Udemy courses you can use to Learn PostgreSQL Administration:

More guides:

Cart

Learn how to – Install PostgreSQL Database Server on Ubuntu 22.04|20.04