![Installing and Running PostgreSQL DB Server on Ubuntu 22](https://mangohost.net/blog/wp-content/uploads/2024/07/database_hub.jpg)
Installing and Running PostgreSQL DB Server on Ubuntu 22
PostgreSQL offers several advantages over MySQL, particularly in terms of advanced features and standards compliance. It supports complex queries, custom functions, and full ACID compliance, ensuring reliable transactions. PostgreSQL’s support for JSON and XML allows for seamless integration with modern applications. It also offers superior concurrency control, table inheritance, and a more robust indexing system. Additionally, its extensibility allows users to define their own data types, operators, and index methods, making PostgreSQL a powerful choice for complex, business-critical applications.
- Introduction
- Prerequisites
- Add PostgreSQL Repository
- Install PostgreSQL 14
- Configure PostgreSQL
- Create a Superuser
- Create a Database and User
- Connect PostgreSQL Remotely
- Conclusion
Introduction
PostgreSQL is a widely-used, free, and open-source relational database management system. It is renowned for its stability, performance, and data integrity. This robust SQL implementation is particularly suitable for mission-critical applications and offers features like nested transactions, multiversion concurrency control, table inheritance, asynchronous replication, and referential integrity of foreign keys.
Prerequisites
Ensure you have the following before proceeding:
- A server running Ubuntu 22.04.
- A root password set up on the server.
Add PostgreSQL Repository
The latest PostgreSQL version isn’t available in the default Ubuntu 22.04 repository, so you’ll need to add the official PostgreSQL repository to your system.
First, install the necessary dependencies:
apt-get install gnupg2 curl wget -y
Then, add the PostgreSQL repository and GPG key:
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 -
Update the PostgreSQL repository:
apt update -y
Install PostgreSQL 14
Now, install PostgreSQL 14:
apt install postgresql-14 -y
Check PostgreSQL’s status:
systemctl status postgresql
You should see an output similar to this:
? 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.
Verify the PostgreSQL version:
sudo -u postgres psql -c "SELECT version();"
The output should be:
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
PostgreSQL defaults to only allowing local connections. To permit remote connections, modify the configuration files.
Edit the authentication method in the pg_hba.conf file:
nano /etc/postgresql/14/main/pg_hba.conf
Change these lines:
local all all trust
host all all 0.0.0.0/0 md5
Next, edit the main PostgreSQL configuration file to change the listen addresses:
nano /etc/postgresql/14/main/postgresql.conf
Update the following line:
listen_addresses='*'
Restart the PostgreSQL service to apply changes:
systemctl restart postgresql
Create a Superuser
Create an administrative user for managing other users and databases. Log in to the PostgreSQL shell:
sudo -u postgres psql
Create a superuser with a password:
CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'password';
Verify the user:
\du
The output should be:
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
To create a new database:
create database testdb;
Create a new user:
create user testuser with encrypted password 'password';
Grant privileges to the new user:
grant all privileges on database testdb to testuser;
List all databases:
\l
You should see something like this:
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 Remotely
PostgreSQL is now set up to accept remote connections. To connect remotely:
psql 'postgres://testuser:password@postgres-ip-address:5432/testdb?sslmode=disable'
Conclusion
Congratulations! You have successfully installed and configured PostgreSQL on Ubuntu 22.04. PostgreSQL can now be used as a reliable backend for your mission-critical applications. If you have any questions, feel free to contact us.
![](/blog/wp-content/themes/defaults/img/register.jpg)
This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.
This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.