In this tutorial, we will learn the commands to install and set up a PostgreSQL database server on Ubuntu 20.04 LTS Linux.
What is PostgreSQL?
PostgreSQL is an object-relational database system (ORDBMS). Being an open-source database project it is freely available and usable without a license fee. PostgreSQL was originally developed at the University of California and uses the simple BSD license.
It is a very advanced database system in the open-source area. PostgreSQL supports most parts of the SQL2003 standard and has a large number of its own extensions.
The user can expand the system with self-defined data types, operators, and functions. Apart from the support of referential integrity and advanced transaction management, PostgreSQL offers definitions of triggers and rules to regulate access to database objects.
Installing Postgresql Database server in Ubuntu 20.04 LTS
The steps given here will work for Ubuntu 21.04/19.04/18.04 and other Linux OS based on Ubuntu as well.
1. Open Command Terminal
We need to access the command line to install Postgresql on Ubuntu, for which open ‘Terminal’ on your system either from the Application launcher or using the keyboard shortcut command– CTRL+ALT+T. CLI users are already on the terminal.
2. Run Ubuntu 20.04 system update
Now, the first thing we have to do is running of system update, to make sure all the existing system packages and repository cache are up to date.
sudo apt update
3. Add PostgreSQL GPG key & repository
The packages to set up PostgreSQL on Ubuntu 20.04 are available in its base repository, hence we don’t need to add any third-party repo at all. However, currently while writing this article the version available to install from the base repo was PostgreSQL 12, therefore if you want 14 or 13, then we need to add its repository.
Add GPG Key:
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -
Add repository in Ubuntu 20.04:
Paste the below-given command block and hit the Enter key.
echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
Run system update command:
sudo apt update
4. Command to Install PostgreSQL on Ubuntu 20.04
Now, we can install all the latest stable versions of the PostgreSQL Database on our Ubuntu 20.04 server or desktop.
Choose the version of the Database you want to install:
For Version 12
sudo apt install postgresql-12
For Verison 13
sudo apt install postgresql-13
For other versions such as 11 / 10 / 9.6, simply change the number in the above command with the one you want to install.
5. Check the Database Server Service Status
PostgreSQL service will automatically start after the installation, however, let’s confirm that is working fine without any errors, run:
sudo systemctl status postgresql
6. Secure PostgreSQL default Database
To make sure our PostgreSQL is secured with a strong password, set a password for its system user and then change the default database admin user account using below given commands-
Change user password
passwd postgres
Login using Postgres system account-
su - postgres
Now, change the Admin database password-
psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"
Note: Replace your-password
in the above with a secure password that you want to set for the admin database user.
7. How to create a new database
Let’s say you want to create a database on PostgreSQL to use with some application, then here are the commands to follow-
Login with Postgre system user-
su - postgres
psql
Create a new user. Here we are creating a demo user protected by a password. Change them.
CREATE USER demo WITH PASSWORD 'password';
Create new PostgreSQL database-
CREATE DATABASE h2sdb;
Now, let’s Grant all rights or privileges on created database to the user:
GRANT ALL ON DATABASE h2sdb TO demo;
However, the owner of the Database still will be the default Admin user of PostgreSQL to change that you can use the command-
Syntax: ALTER DATABASE database_name OWNER TO user-name; Example: ALTER DATABASE h2sdb OWNER TO demo;
To check and confirm whether the database has been created or not simply type
\l
You will get the following kind of output
8. Configure PostgreSQL to access remotely
By default, this database server will listen only to the local host, to access it remotely, we need to perform some changes in its configuration file. We can limit it to some particular IP address or open it for all.
sudo nano /etc/postgresql/13/main/postgresql.conf
If you are using some other version of the Database then change number 13 with that in the above command.
In the Configuration file, find – listen_addresses
and first, remove the # given in front of it. After that replace localhost with * sign to allow all Ip-address to connect to the Database server, whereas to limit it to some particular Ip-addresses then type them instead of ‘*‘. Multiple addresses need to be separated with commas.
Save the file by pressing Ctrl+X, Y, and hit the Enter key.
Edit pg_hba.conf
sudo nano /etc/postgresql/13/main/pg_hba.conf
Change the host value as shown in the below screenshot.
host all all 0.0.0.0/0 md5 host all all ::0/0 md5
Save the file Ctrl+X, Y, and press the Enter key.
Restart the Database server to apply the changes:
sudo systemctl restart postgresql
Check whether it listening for all addresses or not, for that you can run:
sudo apt install net-tools sudo netstat -antup | grep 5432
output:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 11568/postgres tcp6 0 0 :::5432 :::* LISTEN 11568/postgres
Finally, open port ‘5432’ in the firewall:
sudo ufw allow 5432
9. Connect database locally or remotely
To access created database on localhost, you can use the following command syntax:
psql -h localhost -d DB-name -U DB-User
Replace DB-name and DB -User with the one you want to connect.
Well, to connect PostgreSQL from some remote Linux system, you have to install this database client, first.
sudo apt install postgresql-client
Now, change the bold values in the below command to connect remote PostgreSQL running server.
psql -h server-ip-address -d DB-name -U DB-User
Example:
Ending note:
This was the quick way to set up and start working with PostgreSQL on Ubuntu 20.04 Linux servers or Desktop systems. To understand more about you can refer to the official documentation guide.
Other Articles:
- How to access remote MySQL database in local phpMyAdmin
- Install PostgreSQL and pgAdmin in AlmaLinux / Rocky 8
- Install MongoDB Compass GUI on Ubuntu 20.04
- How to install & Configure Nessus on Ubuntu 20.04