How to Install PostgreSQL on Debian 9

Hello Debian users, welcome to a quick Debian Tutorial. Today, we are going to learn how to install PostgreSQL on Debian 9. PostgreSQL is another relational database system which has been used by millions of users out there. It is a robust, flexible and easy to manage database system. PostgreSQL supports almost any operating system including Linux. Installing PostgreSQL on Debian 9 is not difficult. We will try to guide you step by step from the beginning. 

Steps to Install PostgreSQL on Debian 9

If you have a direct access to your Debian server/desktop, you can use Terminal from your Debian system. Or, you can also connect to your Debian from other computer via SSH. In this example, I connect to my Debian from Windows machine using Termius ssh client.

Connect to Debian via SSH

Update Debian

Now, after we are in the Debian console, we can start installing PostgreSQL. But before that, let’s update it first. Please run the following commands as root.

Install PostgreSQL

Connect to PostgreSQL console

After the installation completes, let’s try to connect. First, we need to switch to ‘postgres’ user.

Then, run ‘psql’ command

Example:

At this point, we have successfully installed PostgreSQL on Debian 9. But, we are going to tweak it a little bit. 

Change the postgres user password

By default, there is only one user that can access the server. And there is no default password for this postgres user. See the following steps to add new password for postgres user. To change the user password, use this command

Enable Remote Connection to PostgreSQL

By default, we can connect to PostgreSQL from localhost only. If we need to connect or open it from the network, we need to enable remote connection to the PostgreSQL server. See how to enable the remote connection to PostgreSQL server below. 

Edit the postgresql.conf file. Usually, this file is located under /etc/postgresql/9.6/main. Please note that 9.6 is the version of the postgresql installed on my system. This number may vary. Use your favorite editor such as nano.

Scroll down until you find the following line:

#listen_addresses = ‘localhost’

Change it to:

listen_addresses = ‘*’

See the example below:

One last thing, let’s edit the pg_hba.conf, and add the following line to the bottom of the file.

host all all 0.0.0.0/0 md5

Finally, restart postgresql service

Connect to PostgreSQL Server 

There are many ways to connect and manage your server. I usually use Navicat Premium to manage my database server. It is a great database tool to use. It supports many database system including PostgreSQL.

Admin

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.