How to Set Up PostgreSQL on Debian

Edited 2 weeks ago by ExtremeHow Editorial Team

DebianPostgreSQLDatabaseServer SetupSQLLinuxOpen SourceSystem AdministrationCLISoftware

PostgreSQL is a powerful, open source object-relational database system. It has over 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. In this guide, we will walk you through a step-by-step process of installing and setting up PostgreSQL on a Debian system.

Prerequisites

Before we get started, there are a few prerequisites to ensure a smooth installation:

• You must have a Debian-based system with a non-root user with sudo privileges. This guide is applicable to Debian 10 (Buster) and other compatible versions.
• You need to have basic knowledge about terminal and command-line operations, as we will be installing PostgreSQL through terminal.
• A stable internet connection to download the required packages.

Step 1: Update and install dependencies

Before installing PostgreSQL, it is important to update the package lists for upgrades and new package installations. Run the following command to update your package lists:

sudo apt update

This will sync the package index files from their sources. Once complete, you may want to upgrade all installed packages on your system to their latest available versions:

sudo apt upgrade

In some cases, upgrading may not be necessary, but it is a good practice to keep your system updated. Once your system is updated, proceed by installing the required dependencies. You can install necessary resources like wget (network downloader) using the command:

sudo apt install wget

Step 2: Add the PostgreSQL APT repository

Debian's default repositories include PostgreSQL, but are often quite out of date compared to the official PostgreSQL repositories. We will be adding the PostgreSQL APT repository for the latest stability and features.

First, add the GPG key for the PostgreSQL repository to make sure the downloads are legitimate:

wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Next, create a catalog file for PostgreSQL, so your system knows where to download from:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

Re-update your package list to reflect the changes:

sudo apt update

Step 3: Install PostgreSQL

Once the repository is added, you can now install the PostgreSQL package. At the time of writing, PostgreSQL 15 is the latest version, but you can change the version number to suit your needs:

sudo apt install postgresql-15

This command will install the core database engine and supporting tools needed to manage the database.

Step 4: Verify the installation

Once the installation is complete, it is a good practice to verify that everything is working properly. You can start by checking the status of the PostgreSQL service with systemd:

sudo systemctl status postgresql

The output should show "Active (running)", indicating that PostgreSQL is functioning as expected. If necessary, start or enable the service to ensure it starts on boot:

sudo systemctl start postgresql sudo systemctl enable postgresql

Step 5: Basic PostgreSQL usage

PostgreSQL uses the concept of roles to handle authentication and permissions. At this point, a default role named postgres is created and set up with a default PostgreSQL database named postgres.

Accessing PostgreSQL

Switch to the postgres account with the following command. This account is mainly used to access the database:

sudo -i -u postgres

Once you are logged in, access the PostgreSQL prompt using the following:

psql

You will see a change in the command prompt, indicating that you are now interfacing with the PostgreSQL system. You can exit the PostgreSQL prompt at any time using this command:

\q

Creating roles and databases

In real-world scenarios, it is advisable to create a new role and database tailored to the needs of your application. Here's how to create the role and database:

Create a new role:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';

Create a new database owned by the newly created role:

CREATE DATABASE mydb OWNER myuser;

Make sure you replace “myuser”, “mypassword” and “mydb” with your desired username, password and database name.

Step 6: Connecting to PostgreSQL remotely

If you need to allow remote connections to your PostgreSQL server, you must edit the configuration files. These files are located in the /etc/postgresql/15/main/ directory.

Edit postgresql.conf

Open and edit the postgresql.conf file:

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

Find the line #listen_addresses = 'localhost'. Uncomment it and change it to listen_addresses = '*':

listen_addresses = '*'

Edit pg_hba.conf

Edit the pg_hba.conf file to include your connections. We recommend adding the following line to allow connections on the network from any IP address:

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

Add the following line at the bottom:

host all all 0.0.0.0/0 md5

This line allows any IP address to connect with any username using a password. Change according to your security needs.

Restart PostgreSQL

Restart the PostgreSQL service to apply the changes:

sudo systemctl restart postgresql

Conclusion

Congratulations, you have successfully installed and configured PostgreSQL on your Debian system. You have the ability to create roles and databases according to your project's needs and can also enable remote connections for extended access. PostgreSQL is now functional and ready to be used for your applications. While these are the basic steps, there is a lot more in terms of customization and security that can be customized to your specific needs.

Remember, PostgreSQL is an advanced, comprehensive system with high extensibility and can be used for many scalable and critical database applications. Continue to check out its detailed documentation and guides to take even more advantage of PostgreSQL's capabilities.

  Share This Article

If you find anything wrong with the article content, you can request an update