PostgreSQL is a free and open source relational database management system. It is very popular worldwide due to its high stability, performance and data integrity. It offers an implementation of the SQL query language. It was developed specifically for business-critical applications. It offers a variety of features such as nested transactions, multiversion concurrency control, table inheritance, asynchronous replication, referential integrity of foreign keys and much more.
In this article, we will show you how to install PostgreSQL on Ubuntu 22.04.
Prerequisites
- A server running Ubuntu 22.04.
- A root password is set up on the server.
Add PostgreSQL repository
By default, the latest version of PostgreSQL is not available in the standard Ubuntu 22.04 repository. Therefore, you need to add the official PostgreSQL repository to your system.
First install all required dependencies with the following command:
apt-get install gnupg2 curl wget -y
Once all dependencies are installed, add the PostgreSQL repository and GPG key 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' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
Next, update the PostgreSQL repository with the following command:
apt update -y
Install PostgreSQL 14 on Ubuntu 22.04
Now you can install PostgreSQL 14 by executing the following command:
apt install postgresql-14 -y
Once PostgreSQL is installed, check the status of PostgreSQL with the following command:
systemctl status postgresql
You should get the following output:
? postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2022-05-28 10:29:12 UTC; 11s ago
Process: 3107 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 3107 (code=exited, status=0/SUCCESS)
CPU: 1ms
May 28 10:29:12 ubuntu2204 systemd[1]: Starting PostgreSQL RDBMS...
May 28 10:29:12 ubuntu2204 systemd[1]: Finished PostgreSQL RDBMS.
To check the PostgreSQL version, run the following command:
sudo -u postgres psql -c "SELECT version();"
You will get the following output:
version ------------------------------------------------------------------------------------------------------------------------------ PostgreSQL 14.3 (Ubuntu 14.3-1.pgdg22.04 1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit (1 row)
Configure PostgreSQL
By default, PostgreSQL only allows connections from the localhost. If you want to allow a remote connection, you must edit the PostgreSQL configuration file and specify the authentication method. To do this, you can edit the following file:
nano /etc/postgresql/14/main/pg_hba.conf
Change the following lines:
local all all trust host all all 0.0.0.0/0 md5
Save and close the file when you are finished. Then edit the main PostgreSQL configuration file and change the list port:
nano /etc/postgresql/14/main/postgresql.conf
Change the following line:
listen_addresses='*'
Save and close the file and restart the PostgreSQL service to apply the changes:
systemctl restart postgresql
Create a superuser
It is a good idea to create an administrative user to manage other users and databases. First log in to the PostgreSQL shell with the following command:
sudo -u postgres psql
Once you are logged in, you will get the following shell:
psql (14.3 (Ubuntu 14.3-1.pgdg22.04 1)) Type "help" for help. postgres=#
Next, create a superuser and set a password with the following command:
CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'password';
Next, verify the created user with the following command:
du
You should see 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 | {}
Creating a database and user in PostgreSQL
To create a database named testdb in PostgreSQL, run the following command:
create database testdb;
To create a user named testuser in PostgreSQL, execute the following command:
create user testuser with encrypted password 'password';
To grant all rights to the database testdb, execute the following command:
grant all privileges on database testdb to testuser;
You can list all databases with the following command:
l
In the following output, you should get a list of all databases:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------- ---------- ---------- ------------- ------------- -----------------------
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
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
| | | | | postgres=CTc/postgres
| | | | | testuser=CTc/postgres
(4 rows)
Connect PostgreSQL from the remote host
Now PostgreSQL is installed and configured to allow connections from remote hosts. To check the PostgreSQL remote connection, run the following command:
psql 'postgres://testuser:password@postgres-ip-address:5432/testdb?sslmode=disable'
Conclusion
Congratulations! You have successfully installed and configured the PostgreSQL server on Ubuntu 22.04. Now you can use PostgreSQL as a database backend in a business-critical application. If you have any further questions, please feel free to contact me.
