How To Install PostgreSQL on Ubuntu 22.04

PostgreSQL, commonly known as Postgres, is a powerful open-source relational database management system (DBMS) used to store, organize, and manage data. A DBMS like PostgreSQL provides the tools needed to create, update, and retrieve data efficiently. It allows users and applications to interact with data in an organized way, making it possible to handle large amounts of information securely and reliably.

PostgreSQL supports the SQL language, which is a standard way to communicate with databases. It offers advanced features such as ensuring data accuracy even during errors, and allowing multiple users to access and modify data simultaneously without conflicts.

Compatibility

PostgreSQL is compatible with many operating systems, including various Linux distributions like Ubuntu, Debian, and Red Hat, as well as Windows, macOS, BSD, and Solaris, making it a versatile choice for a wide range of environments and projects.

This guide will walk you through the process of quickly installing and configuring PostgreSQL on an Ubuntu (22.04) based Linux server. You’ll learn how to install PostgreSQL, create a new user, and set up a database. Then we’ll test to ensure connection is successful before use.

Requirements

There are no specific requirements, though you’ll want to ensure sufficient RAM and disk space to run your database (and any other apps and services) and store the database’s data, as well as to accommodate anticipated growth.

Update System

First, make sure your system and packages are up to date:

sudo apt-get update

Install PostgreSQL

Now install the Postgres packages

sudo apt install postgresql postgresql-contrib

PostgreSQL creates a default superuser called postgres during installation. This user has full control over the entire database server, which is powerful and should be reserved for administrative tasks.

Switch to postgres user

To manage your databases and users safely, you need to switch to this default user and access the PostgreSQL command-line interface:

sudo -u postgres psql

Create User

For security reasons, you should not give a website or application’s database user superuser privileges. Superusers can do anything, including deleting data or changing server configurations, which is risky if compromised.

Instead, create a regular user role that has only the permissions needed to operate:

CREATE USER testuser WITH PASSWORD 'testpassword';

Replace “testuser” with your desired username and “testpassword” (keeping existing apostraphes) with a strong password.

Create Database

Now, create a database that your app can connect to, owned by the new user:

CREATE DATABASE testdatabase OWNER testuser;

Replace testuser with the username created prior and testdatabase with a name for your new database.

When done, you can exit PostgreSQL with:

\q

You can always switch to the PostgreSQL’s default superuser postgres for database management with:

sudo -u postgres psql

Test Database Connection

Before using your new database in your new site or app, test your connection from the terminal. Replace testuser and testdatabase with your values:

psql -U testuser -d testdatabase

Upon connection, you should see:

psql (version)

testdatabase=>

PostgreSQL is now installed with a new dedicate user and database for your new site or app and you’re now ready to start working with PostgreSQL and take advantage of its powerful capabilities.

Database management is an ongoing process, so review the official docs to explore more features and best practices as you go.