PostgreSQL is an open-source and one of the most popular relational database management systems. It implements the SQL query language and offers strong stability, performance, and data integrity. You can use the PostgreSQL database to run mission-critical applications. PostgreSQL has features like Nest transactions, multi-version concurrency control, table inheritance, asynchronous replication, foreign key referential integrity, and more.
This post will show you how to install PostgreSQL database on Ubuntu 22.04.
Prerequisites
- A server running Ubuntu 22.04.
- A root password is configured on the server.
Getting Started
First, update and upgrade all system packages to the latest version using the following command:
apt update -y
apt upgrade -y
Once your system is up-to-date, you can install other required dependencies using the following command:
apt install wget curl gnupg2 -y
After installing all the dependencies, you can proceed to install InfluxDB.
Add PostgreSQL Repository
By default, the latest version of PostgreSQL is not available in the Ubuntu 22.04 default repository. So you will need to add the PostgreSQL official repository to your system.
you can add the PostgreSQL repository and GPG key using 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 -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
Next, update the PostgreSQL repository using the following command:
apt update -y
Install PostgreSQL on Ubuntu 22.04
You can now install PostgreSQL 15 by running the following command:
apt install postgresql postgresql-client -y
Once the PostgreSQL has been installed, verify the status of the PostgreSQL using 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 Mon 2022-12-05 14:28:18 UTC; 1min 57s ago
Main PID: 10824 (code=exited, status=0/SUCCESS)
CPU: 4ms
Dec 05 14:28:18 ubuntu2204 systemd[1]: Starting PostgreSQL RDBMS...
Dec 05 14:28:18 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 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit (1 row)
Configure PostgreSQL for Remote Connection
By default, PostgreSQL allows connections only from the localhost. If you want to allow remote connection then you will need to edit the PostgreSQL configuration file and define the authentication method. You can do it by editing the following file:
nano /etc/postgresql/15/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 done. Then, edit the PostgreSQL main configuration file and change the listen port:
nano /etc/postgresql/15/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
Create an Administrative User
It is a good idea to create an administrative user to manage other users and databases. First, log in to the PostgreSQL shell using the following command:
sudo -u postgres psql
Once you are logged in, you will get the following shell:
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1)) Type "help" for help. postgres=#
Next, create an admin user and set a password with the following command:
CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'password';
Next, verify the created user using 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 | {}
Create a Database and User in PostgreSQL
In this section, we will show you how to create a database and user in PostgreSQL.
To create a database named wpdb in PostgreSQL, run the following command:
create database wpdb;
To create a user named wpuser in PostgreSQL, run the following command:
create user wpuser with encrypted password 'password';
To grant all the privileges to the wpdb database, run the following command:
grant all privileges on database wpdb to wpuser;
You can list all databases using the following command:
\l
You should get a list of all databases in the following output:
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
wpdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =Tc/postgres +
| | | | | | | postgres=CTc/postgres+
| | | | | | | wpuser=CTc/postgres
(4 rows)
How to Connect PostgreSQL from the Remote Machine
At this point, PostgreSQL is installed and configured to allow connections from the remote hosts. To verify the PostgreSQL remote connection, run the following command:
psql 'postgres://wpuser:password@postgres-ip-address:5432/wpdb?sslmode=disable'
Conclusion
Congratulations! you have successfully installed and configured the PostgreSQL server on Ubuntu 22.04. You can now use PostgreSQL in a production environment to achieve high performance and data integrity. Feel free to ask me if you have any questions.
