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
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.
apt update && apt upgrade
apt install postgresql postgresql-client
Connect to PostgreSQL console
After the installation completes, let’s try to connect. First, we need to switch to ‘postgres’ user.
su - postgres
Then, run ‘psql’ command
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
systemctl restart postgresql
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.