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-upgradeCheck if a reboot is required after the upgrade
[ -f /var/run/reboot-required ] && sudo reboot -fAfter 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-clientConfirm 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 md5Do 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/24Restart postgresql service after the changes.
sudo systemctl restart postgresql apache2To access the phppgadmin dashboard, open the URL http://(hostname_or_IP_address/phppgadmin/.
Books for Learning PostgreSQL database:
More guides:
