How to Install PostgreSQL (psql) on Debian 12 Linux Systems

PostgreSQL, also known as Postgres, is a powerful, open-source object-relational database system. It’s known for its robustness, extensibility, and standards compliance. This guide will walk you through the steps to install PostgreSQL on a Debian 12 system.

Prerequisites

Before you start, ensure you have:

A running Debian 12 system
A user account with sudo privileges
Internet connectivity to download packages

Step 1: Update Your System

First, update your system to ensure all existing packages are up to date.

$ sudo apt update
$ sudo apt upgrade -y

Step 2: Install PostgreSQL

Install PostgreSQL from the Default Repository

Debian’s default repository contains PostgreSQL. You can install it using the apt package manager.

$ sudo apt install postgresql postgresql-contrib -y

This command installs both the PostgreSQL server and the contrib package, which provides additional utilities and functionalities.

Start and Enable PostgreSQL

After installation, PostgreSQL should start automatically. You can verify its status and enable it to start on boot.

$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

Check PostgreSQL Status

To check the status of the PostgreSQL service, use the following command:

$ sudo systemctl status postgresql

You should see output indicating that PostgreSQL is active and running.

Step 3: Configure PostgreSQL

Switch to the PostgreSQL User

PostgreSQL creates a default user called postgres. Switch to this user to perform administrative tasks.

$ sudo -i -u postgres

Access the PostgreSQL Command Line Interface

Once you are the postgres user, you can access the PostgreSQL command line interface (psql).

$ psql

You should see the PostgreSQL prompt:

postgres=#

Set a Password for the PostgreSQL User

By default, the postgres user does not have a password. Set a password for enhanced security.

\password postgres

You will be prompted to enter and confirm the new password.

Step 4: Create a New Database and User

Create a New Database

To create a new database, use the following command in the psql interface:

CREATE DATABASE mydatabase;

Create a New User

Create a new user and set a password for this user:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

Grant Privileges

Grant all privileges on the new database to the new user:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Step 5: Configure Remote Access (Optional)

If you need to access PostgreSQL from a remote machine, you will need to configure PostgreSQL to listen on all IP addresses and set up authentication methods.

Edit PostgreSQL Configuration File

Edit the postgresql.conf file to allow PostgreSQL to listen on all IP addresses:

$ sudo nano /etc/postgresql/15/main/postgresql.conf

Find the line that starts with #listen_addresses and change it to:

listen_addresses = '*'

Edit pg_hba.conf File

Edit the pg_hba.conf file to set up authentication methods:

$ sudo nano /etc/postgresql/15/main/pg_hba.conf

Add the following line at the end of the file to allow password authentication from any IP address:

host    all             all             0.0.0.0/0            md5

Restart PostgreSQL

Restart PostgreSQL to apply the changes:

$ sudo systemctl restart postgresql

Step 6: Verify the Installation

To verify that PostgreSQL is installed and running correctly, connect to the database using the new user:

$ psql -h localhost -U myuser -d mydatabase

You will be prompted to enter the password for myuser. Once authenticated, you should see the psql prompt for mydatabase.

Conclusion

You have successfully installed and configured PostgreSQL on your Debian 12 system. PostgreSQL is now ready for you to use, whether for developing web applications, data analysis, or any other database-related tasks. For more detailed configurations and advanced usage, refer to the official PostgreSQL documentation.