Installing PostgreSQL on Debian

One of the more sophisticated open-source relational database management systems (DBMS) is PostgreSQL. On Debian 11, it can be installed from either the official PostgreSQL repository or the operating system repository.

In this article, you will learn both methods, as well as how to perform common operations like creating roles and databases. Don't forget to check how to configure static IP address on Debian.

A quick scheme of how PostrgreSQL installation works

Installation from the Debian 11 repository

Copy link

On Debian, you can install PostgreSQL directly from the system repository.

First, update your package list. Launch the terminal and run:

sudo apt update && sudo apt upgrade

The PostgreSQL package is available in the Debian repository, so you can install it using the apt utility. To do this, run:

sudo apt install postgresql postgresql-contrib

Once the installation is complete, check the status of the service using the command:

sudo systemctl status postgresql

If the service does not start automatically, you can start it manually. To do this, run:

sudo systemctl start postgresql

To stop a running service, run:

sudo systemctl stop postgresql

Before configuring PostgreSQL on Debian, make sure the service is running.

Installation from the official PostgreSQL repository

Copy link

If you want to use only the latest versions of Postgres, we recommend using the official PostgreSQL repository for installation and subsequent updates.

First of all, you need to add the GPG signing key. This is a security requirement to verify the authenticity of the PostgreSQL repository. To do this, launch a terminal and run:

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg

Now you are ready to add the Postgres repository. Use the following command:

echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/bulseye-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

After successfully integrating the PostgreSQL library, you can install the DBMS. But before you do that, update the system repository using the command:

sudo apt update

After updating, run the following command to install PostgreSQL on Debian:

sudo apt install postgresql

Installation is completed. Now you can proceed to the basic configuration of PostgreSQL.

Basic setup

Copy link

When installing Postgres, the postgres user is automatically created. You can use this account for your first connection.

Switch to the postgres user:

sudo su - postgres

Run the psql utility which is a shell for managing PostgreSQL:

psql

You can now interact with the PostgreSQL server. To exit the shell, enter:

\q

You can use the following command to access the Postgres command line without switching users:

sudo -u postgres psql

However, the postgres user is usually only used from localhost. If, for example, you use cloud databases, it is better to create a new role for the connection.

Creating a role and a database

Copy link

The createuser command allows you to create new roles from the command line. Only superusers and roles with CREATEROLE privileges can create new roles.

In the example that follows, we will build a database called hostman_db and a new role called hostman. We will then give the new role the ability to handle the database.

First create a new role:

sudo su - postgres -c "createuser hostman"

Then create a new database:

sudo su - postgres -c "createdb hostman_db"

To grant the user permissions to the database, connect to the shell:

sudo -u postgres psql

Run the following query to grant the hostman user privileges to manage the hostman_db database:

GRANT ALL PRIVILEGES ON DATABASE hostman_db TO hostman;

You can create new roles and databases in the PostgreSQL shell. In this case, the syntax will be slightly different.

To create a new role with a password, run:

create user cloud with password 'hostmancloud';

To create a new database, run:

create database cloud_db;

Then you must also grant all privileges with the GRANT ALL PRIVILEGES ON DATABASE … TO … command.

Setting up remote access

Copy link

Only the local interface 127.0.0.1 is used by the Postgres server by default for listening. This might be a hassle. Suppose you have PostgreSQL installed on a server running on Hostman. Remotely connecting to it will be far more convenient. You must set up the server to listen to different network interfaces in order to accomplish this.

To change the configuration, open the postgresql.conf file using any editor. This example uses the nano editor:

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

Find the CONNECTIONS AND AUTHENTICATION section and the line #listen_addresses = 'localhost' in the configuration file. Change the line value to listen_addresses = '*'. If you want the server to listen not to all network interfaces, but only to the selected one, specify it instead of an asterisk.

Save the file and restart the Postgres service for the changes to take effect:

sudo service postgresql restart

The last step is to allow connections from the network. To install it, you need to edit the pg_hba.conf file. Open it in the editor:

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

Find the IPv4 local connections line. Specify the desired network. For example, like this:

You can use other authentication methods. For a complete list, see the PostgreSQL documentation.

Installing PostgreSQL on Debian is a simple and straightforward process

Conclusion

Copy link

There are two ways to install managed PostgreSQL on Debian.

The first option is to use the system repository. Its main advantage is speed. There is no need to install anything additional, just run one command. The downside is that the system repository does not always contain the latest version of the software.

The second installation option is to use the official PostgreSQL repository. This method ensures that you are using the latest version of the DBMS. But you will have to perform a few more steps: first, add the official repository itself and only then install Postgres from it.

Hostman can help you deploy NoSQL or SQL cloud database on one of the most popular engines in just seconds. With an intuitive interface and around-the-clock free support, deploying MySQL cloud or Postgres cloud becomes much easier.