PostgreSQL is a popular open-source object-relational database system. It is very popular due to its reliability, robustness of its features, and performance. It has many advanced features that allow developers to build fault-tolerant and complex applications. At the time of writing this tutorial, the latest available version is PostgreSQL 14.
In this post, we will explain how to install and configure PostgreSQL 14 on Ubuntu 20.04.
Prerequisites
- A server running Ubuntu 20.04 on the Atlantic.Net Cloud Platform
- A root password configured on your server
Step 1 – Create Atlantic.Net Cloud Server
First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Ubuntu 20.04 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.
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
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 server and database servers are hosted on a different server, then you will need to 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
In the above post, you learned how to install and configure PostgreSQL 14 on Ubuntu 20.04. You can now start developing high-performance and complex applications with the PostgreSQL database backend. Try it on dedicated hosting from Atlantic.Net!
