Aller au contenu principal

Installing PostgreSQL on Debian

In today's tutorial we talk about installing PostgreSQL, one of the most advanced open-source relational database systems, on a Debian-based Linux system. PostgreSQL is renowned for its robustness, scalability, and feature set, making it a popular choice for developers and enterprises alike. This guide will walk you through the process of setting up PostgreSQL on your Debian system with ease.

Why PostgreSQL?​

Before we dive into the installation process, let's briefly discuss why PostgreSQL stands out in the crowded database market:

  • Extensibility: PostgreSQL allows users to define their own data types and functions, which can be a game-changer for complex data models.
  • ACID Compliance: It adheres to the ACID properties, ensuring reliable transaction processing.
  • Open Source: With a vibrant community, PostgreSQL is continuously improved and is free to use.
  • Performance: It is designed to perform well under heavy loads, making it suitable for large-scale applications.
  • SQL Standards Compliance: PostgreSQL conforms closely to SQL standards, which means it's compatible with a wide range of tools and frameworks.

Prerequisites​

  • A Debian-based system (e.g., Debian, Ubuntu, Linux Mint)
  • Sudo privileges or root access
  • Basic knowledge of using the terminal

Step 1: Updating the Package List​

Before installing any new software, it's a good practice to update your package list to ensure you have access to the latest versions of packages. Open your terminal and run the following command:

sudo apt update

You should see a list of repositories being updated. This process might take a few moments, depending on the speed of your internet connection and the last time you updated your system.

Step 2: Installing PostgreSQL​

With your package list updated, you can now install PostgreSQL using the apt package management tool. Run the following command:

sudo apt install postgresql postgresql-contrib

The postgresql-contrib package contains additional utilities and functionality that are often useful for managing and working with PostgreSQL databases.

Output:: You'll see a series of messages as the packages are downloaded and installed. Upon completion, you should see a message indicating that the installation was successful.

Step 3: Verifying the Installation​

To verify that PostgreSQL has been installed correctly, try starting the service and checking its status:

sudo systemctl start postgresql.service
sudo systemctl status postgresql.service

Output:: The status command should show that the service is active and running.

● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Wed 2023-04-05 15:00:00 UTC; 12s ago
Process: 1234 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1234 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 4915)
CGroup: /system.slice/postgresql.service

Step 4: Configuring PostgreSQL​

PostgreSQL is now installed, but before you start using it, you might want to configure it to suit your needs. The primary configuration file is postgresql.conf, and the user/role management is handled in pg_hba.conf.

Accessing the PostgreSQL Command Line​

To configure PostgreSQL, you'll often use the psql command-line interface. To access it, run:

sudo -u postgres psql

Output:: You should be greeted by the PostgreSQL interactive terminal.

psql (13.4 (Debian 13.4-1.pgdg100+1))
Type "help" for help.

postgres=#

Changing the PostgreSQL Password​

For security reasons, it's important to change the default password for the postgres user. While in the psql prompt, you can change the password by running:

ALTER USER postgres PASSWORD 'your_new_password';

Replace 'your_new_password' with a strong password of your choice.

Configuring postgresql.conf​

To edit the postgresql.conf file, you can use a text editor like nano:

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

Here, 13 refers to the version of PostgreSQL installed on your system. Make the necessary changes, such as adjusting the listen_addresses to allow remote connections, and then save and close the file.

Configuring pg_hba.conf​

Similarly, you can edit the pg_hba.conf file to manage client authentication:

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

Add or modify the rules as needed to control access to your databases.

Step 5: Restarting PostgreSQL​

After making changes to the configuration files, you need to restart the PostgreSQL service for the changes to take effect:

sudo systemctl restart postgresql.service

Step 6: Creating a New Database and User​

With PostgreSQL configured, you can now create a new database and user for your applications. Here's how to do it:

  1. Access the psql prompt as the postgres user:
sudo -u postgres psql
  1. Create a new database:
CREATE DATABASE mydatabase;
  1. Create a new user:
CREATE USER myuser WITH PASSWORD 'mypassword';
  1. Grant the necessary privileges to the new user:
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Output:: You should receive confirmation messages for each successful command.

Step 7: Accessing the New Database​

To access the newly created database as the new user, you can exit the current psql session (\q) and reconnect using the new credentials:

psql -d mydatabase -U myuser

You will be prompted to enter the password for myuser.

What to Watch Out For​

  • Firewall Settings: Ensure that your firewall allows traffic on the PostgreSQL port (default is 5432).
  • Remote Connections: If you plan to allow remote connections, be cautious and properly secure your PostgreSQL server.
  • Strong Passwords: Always use strong passwords for database users to prevent unauthorized access.
  • Regular Backups: Implement a backup strategy to protect your data.
  • Version Compatibility: When installing extensions or additional tools, ensure they are compatible with your PostgreSQL version.

What we've done​

We have now successfully installed PostgreSQL on your Debian system, configured it for basic use, and set up a new database with a dedicated user. This database system is incredibly powerful and can handle a wide range of use cases. Enjoy your journey with PostgreSQL.