How to Install PostgreSQL on Debian 10

Install PostgreSQL on Debian 10

In this article, we will have explained the necessary steps to install PostgreSQL on Debian 10. Before continuing with this tutorial, make sure you are logged in as a user with sudo privileges. All the commands in this tutorial should be run as a non-root user.

PostgreSQL is an open source, powerful, advanced, high performance and stable relational-document database system. It uses and enhances the SQL language coupled with a large number of features for secure data storage and management.

Install PostgreSQL on Debian 10

Step 1. The first command will update the package lists to ensure you get the latest version and dependencies.

sudo apt update
sudo apt upgrade

Step 2. Install the PostgreSQL on Debian.

Install the PostgreSQL server and contrib package:

sudo apt install postgresql postgresql-contrib

To verify the installation, use the psql tool to print the server version:

sudo -u postgres psql -c "SELECT version();"

Step 3. PostgreSQL Roles and Databases.

PostgreSQL supports multiple authentication methods. The most commonly used are:

  • Trust – With this method, the role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
  • Password – A role can connect by providing a password. The passwords can be stored as scram-sha-256 md5 and password (clear-text)
  • Ident – This method is only supported on TCP/IP connections. Works by obtaining the client’s operating system user name, with an optional user name mapping.
  • Peer – Same as Ident but it is only supported on local connections.

You can log in to PostgreSQL using below command:

sudo su - postgres
psql

To exit from here type following in the terminal:

\q

Step 4. Create PostgreSQL Database and Role.

  • Create a new PostgreSQL RoleThe following command will create a new role named ramona:
    sudo su - postgres -c "createuser meilana"
  • Create a new PostgreSQL DatabaseCreate a new database named ramona using the createdb command:
    sudo su - postgres -c "createdb meilanadb"
  • Grant privilegesTo grant permissions to the ramona user on the database we created in the previous step, connect to the PostgreSQL shell:
    sudo -u postgres psql

    and run the following query:

    grant all privileges on database meilanadb to meilana;

Step 5. Enable remote access to PostgreSQL server.

Use the following steps to enable remote access to the PostgreSQL server:

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

Update listen_addresses like given below:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'

Now restart the PostgreSQL service with systemctl command:

sudo systemctl restart postgresql

Verify the changes with the ss utility:

ss -nlt | grep 5432
LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

Finally update pg_hba.conf file to configure server remote connections using below examples:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user test_user will be able access all databases from all locations using a md5 password
host    all             meilana            0.0.0.0/0                md5

# The user test_user will be able access only the test_db from all locations using a md5 password
host    test_db         meilana            0.0.0.0/0                md5

# The user test_user will be able access all databases from a trusted location (192.168.03.46) without a password
host    all             meilana            192.168.1.77            trust

Congratulation, you have learned how to install and configure PostgreSQL on Debian 10 Buster. If you have any question, please leave a comment below.