PostgreSQL, a popular open-source object-relational database system, is renowned for its reliability, feature robustness, and performance, facilitating the development of complex, fault-tolerant applications.
This post will explain how to install and configure PostgreSQL 14 on Ubuntu. This procedure is compatible with Ubuntu 20.04 and Ubuntu 22.04.
Step 1 – Update Server and Install Pre-Requisites
Once you are logged in to your server, run the following command to update your base system with the latest available packages.
apt-get update -y
Install the following prerequisites:
apt install gnupg gnupg2 gnupg1 -y
Step 2 – Install PostgreSQL 14
The latest version of PostgreSQL is not included in the Ubuntu default repository, so you will need to add the PostgreSQL official repository to the APT.
You can add it with the following command:
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Next, download and add the PostgreSQL GPG key using the following command:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
Next, update the repository cache and install PostgreSQL 14 using the following command:
apt-get update -y apt-get install postgresql-14 -y
Please be aware that the configured PostgreSQL repository mentioned above is capable of downloading the most recent version of PostgreSQL. As of this update, PostgreSQL is operating on version 15, with the release of version 16 imminent. To ensure the installation of the latest version at any time, modify the command above as follows:
apt-get -y install postgresql
After installing PostgreSQL, check the status of the PostgreSQL with the following command:
systemctl status postgresql
You will get the following output:
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2021-11-26 04:57:30 UTC; 43s ago
Main PID: 17268 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 2353)
Memory: 0B
CGroup: /system.slice/postgresql.service
Nov 26 04:57:30 ubuntu2004 systemd[1]: Starting PostgreSQL RDBMS...
Nov 26 04:57:30 ubuntu2004 systemd[1]: Finished PostgreSQL RDBMS.
You can also verify the PostgreSQL version using the command below:
sudo -u postgres psql -c "SELECT version();"
You will get the following output:
version ---------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
Step 3 – Create a Database and User in PostgreSQL
First, you can connect to PostgreSQL with the following command:
su - postgres psql
You will get the following shell:
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1)) Type "help" for help. postgres=#
Next, create a superuser named root with the following command:
CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'securepassword';
Run the following command to verify the superuser:
\du
You will get the following output:
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
To create a database named dbname, run:
create database dbname;
To create a new user named dbuser, run:
create user dbuser with encrypted password 'dbpassword';
To grant all privileges to the dbname to dbuser, run:
grant all privileges on database dbname to dbuser;
To list all databases, run:
\l
You will get the following output:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dbname | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | dbuser=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
To exit from the PostgreSQL shell, run:
\q exit
Step 4 – Configure PostgreSQL for Remote Access
By default, PostgreSQL is configured for local access only. If your application and database servers are hosted on a different server, then you must configure PostgreSQL for remote access. You can configure it by editing the file pg_hba.conf:
nano /etc/postgresql/14/main/pg_hba.conf
Find the following line:
local all all peer
And replace it with the following line:
local all all trust
Next, add the following line:
host all all 0.0.0.0/0 md5
Save and close the file when you have finished.
Next, you will also need to edit the PostgreSQL main configuration file and change the listening port:
nano /etc/postgresql/14/main/postgresql.conf
Change the following line:
listen_addresses='*'
Save and close the file, then restart the PostgreSQL service to apply the changes:
systemctl restart postgresql
You can now check the PostgreSQL listening port using the following command:
ss -antpl | grep 5432
You will get the following output:
LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=19030,fd=5))
LISTEN 0 244 [::]:5432 [::]:* users:(("postgres",pid=19030,fd=6))
Now, go to the remote system and connect to the PostgreSQL instance using the following command:
psql 'postgres://root:[email protected]:5432/postgres?sslmode=disable'
If everything is fine, you will get the following shell:
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1)) Type "help" for help. postgres=#
Conclusion
You learned how to install and configure PostgreSQL 14 on Ubuntu in the above post. You can now start developing high-performance and complex applications with the PostgreSQL database backend. Try it on dedicated hosting from Atlantic.Net!
