How to Install PostgreSQL on Debian 11 Bullseye

PostgreSQL is a highly stable and reliable database management system that has been used for more than 20 years. It’s supported by an active community who contribute their time to its development, which results in higher levels of resilience, integrity, and correctness. PostgreSQL powers many web applications, including those from Google Earth Engine Geospatial datastore for analytics software like predictive modeling toolsets built using machine learning algorithms created within R/Rapidminer packages

In the following tutorial, you will learn how to install PostgreSQL on Debian 11 Bullseye using the command line terminal and basic configuration and setup tips.

Update Debian

First, update your system with the following command to ensure all packages are up-to-date to avoid conflicts.

sudo apt update && sudo apt upgrade -y

Install Required Packages

The following packages must be installed to assist in installing the database software.

sudo apt install software-properties-common apt-transport-https wget -y

Import PostgreSQL Repository

First, you will need to import the PostgreSQL GPG key to verify the authenticity of the installation package.

sudo wget -O- https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg

Next, you can import either the stable or testing repository depending on the installation you want. For production use, use the stable repository as it’s often updated quickly since you are installing directly from PostgreSQL repositories. The testing is for testing, as sometimes it can be behind or ahead, unsuitable for production.

Import PostgreSQL Stable (Recommended)

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main | sudo tee /etc/apt/sources.list.d/postgresql.list

Import PostgreSQL Testing

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg-testing main | sudo tee /etc/apt/sources.list.d/postgresql-testing.list

Install PostgreSQL

Now that you have imported the stable or testing repository of PostgreSQL, you need to update your repository sources list to reflect the new addition with the following command.

sudo apt-get update

Now proceed to install PostgresSQL as follows.

sudo apt install postgresql-client postgresql -y

Next, verify the status to ensure the software is installed and activated without errors using the following command.

systemctl status postgresql

Example output:

How to Install PostgreSQL on Debian 11 Bullseye

Service Commands for PostgreSQL

The PostgreSQL database server runs as a service under the name “PostgreSQL,” which can be managed with the systemd with the following command examples.

Stop PostgreSQL server

sudo systemctl stop postgresql

Start PostgreSQL server

sudo systemctl start postgresql

Restart PostgreSQL server

sudo systemctl restart postgresql

Reload PostgreSQL server

sudo systemctl reload postgresql

Check PostgreSQL status

systemctl status postgresql

Configure the PostgreSQL Server

Switching to Postgres account

Before you continue, do note that only superusers and roles with create role privilege can create new roles in Postgres. A user account called Postgres associated with the default Postgres role was created during the installation.

Engage with the account. You can do the following command.

sudo -i -u postgres

By entering the above, you can immediately access the PostgreSQL prompt by typing “psql”.

psql

Next, you will notice that the terminal has changed with “postgres=#” in the terminal command line. This means you have successfully connected to the database.

To exit the Postgres database, you can do this by typing the following:

exit

Alternative to switching Postgres account

An alternative way to interact with the Postgres database without changing user accounts is to use a sudo command to connect directly. You can do this by typing:

sudo -u postgres psql

This is ideal to use more, as it saves time logging you directly without having to do extra terminal bash commands.

Just as the first option, you can exit by typing the following to exit the terminal.

exit

Create User & Database

Now you can create user roles if you wish. Only superusers and roles with “createrole” privilege can create new roles.

Create a new user; type the following.

sudo su - postgres -c "createuser <name>"

Now you can create a PostgreSQL database for the new user you created:

sudo su - postgres -c "createdb <namedb>"

To finish off, you need to switch to the superuser Postgres account to grant permission to the new database.

Connect to Postgres superuser account

sudo -u postgres psql

Grant access to <username database> to <username>

GRANT ALL PRIVILEGES ON DATABASE <usernamedb> TO <name>;

Example:

How to Install PostgreSQL on Debian 11 Bullseye

Once done, to exit, type the following

exit

Configure UFW Firewall

You will need to create a UFW firewall rule to allow PostgreSQL to communicate on its default port 5432, which is required if you plan to use anything besides localhost.

First, ensure UFW is enabled.

sudo ufw enable

This can be done by typing the following command.

Subnet range:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Individual IP:

sudo ufw allow proto tcp from 192.168.1.0 to any port 5432

Remote Access to PostgreSQL

If you require default access to PostgreSQL, this can be quickly done by changing the default listening to the local interface of (127.0.0.1) to an IP, subnet, or even all interfaces in the configuration file.

Remember to ensure that UFW has been set up correctly and does not block remote access as per the previous section.

First, determine which version of Postgresql you are using the ls command.

ls /etc/postgresql/

The tutorial has one of the latest versions installed, which is 14. With this, open up the “postgresql.conf” file using the nano text editor.

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

Now scroll down until you find “Connection Settings” and change the (listen_addresses = ‘localhost’) to the <IP> address. Most would opt to listen to all interfaces, especially when running in multiple servers requiring connecting to the PostgreSQL database by changing localhost to (*).

Example:

How to Install PostgreSQL on Debian 11 Bullseye

Now save the file (CTRL+O), exit (CTRL+X), and restart your PostgreSQL instance.

sudo systemctl restart postgresql

To check that the changes you have done are live, users can do this with the ss utility built into Debian.

ss -nlt | grep 5432

Example output:

How to Install PostgreSQL on Debian 11 Bullseye

For further customization and securing PostgreSQL, you can configure the server to accept remote connections by editing the “pg_hba.conf” file using the nano text editor.

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

From this point, you can set up various options, such as changing “all” to a specific username and database along with the address and authentication methods.

How to Install PostgreSQL on Debian 11 Bullseye

Comments and Conclusion

PostgreSQL is worth considering if you’re looking for a powerful open-source database system that can handle even the most strenuous loa. With its wide array of features and robustness, it’s no wonder developers have made it their go-to choice for enterprise applications.

For more information on using PostgreSQL, visit the official documentation.



Follow LinuxCapable.com!

Like to get automatic updates? Follow us on one of our social media accounts!