How to install PostgreSQL on Debian 9

We will show you how to install PostgreSQL on Debian 9. PostgreSQL is an object-relational database management system written in C.

It is a free and open-source system, whose main functions are to safely store data and return data as an answer to other application queries. Installing PostgreSQL on Debian 9 is quite an easy task and should not take more than 10 minutes to complete the installation of PostgreSQL on Debian. Let’s get started.

Before starting the installation phase, make sure you have full access to your Debian 9 VPS, or at least you have a system user with sudo privileges. If you do, connect to your server via SSH and make sure that all your system software is up-to-date. First, update the package index using the following command:

apt-get update

After updating the package index, you can update all your system software to the latest version using the following command:

apt-get upgrade

This may take from a few seconds to a few minutes depending on the software to be updated.

INSTALLING POSTGRESQL ON DEBIAN 9

The next step is to install the PostgreSQL database system on your Debian 9 VPS. This step is quite simple, just run the next command:

apt-get install postgresql

It will install the PostgreSQL database server, client, and other necessary tools.

To check if the PosgreSQL server client is installed, you can use the following command:

psql --version

It will show you the current version of PostgreSQL installed on your server:

# psql --version
psql (PostgreSQL) 9.6.7

POSTGRESQL MANAGEMENT ON DEBIAN 9

Now that the PostgreSQL installation is complete, it’s good to know how you can manage PostgreSQL service on your PostgreSQL VPS. To run the PostgreSQL service, run the following command on your terminal:

systemctl start postgresql.service

To stop the PostgreSQL service, you can use the following command:

systemctl stop postgresql.service

To restart the service, you can run the following command:

systemctl restart postgresql.service

To check the status of the PostgreSQL service, run the following command:

systemctl status postgresql.service

If PostgreSQL is running and running on your Linux VPS at the moment, the output will be similar to the following:

# systemctl status postgresql.service
postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2018-07-21 10:57:16 CDT; 40min ago
Main PID: 1018 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/postgresql.service

To enable the PostgreSQL service when booting the system, run the following command:

systemctl enable postgresql.service

If you want PostgreSQL to shut down at system startup, run the following command:

systemctl disable postgresql.service

If you want to manage PostgreSQL file locations, connections, and authentication, resource utilization, logging, etc., you need to edit the main configuration file. The main configuration file for PostgreSQL is in /etc/postgresql/VERSION-NUMBER/main. So, if your PostgreSQL version is 9.6, the location is /etc/postgresql/9.6/main/postgresql.conf.

You can use any text editor of your choice to open and edit a configuration file for PostgreSQL. The file consists of lines in the form NAME = VALUE and makes sure your changes are valid. Otherwise, PostgreSQL will not work. The configuration file is read when you start the server, so you need to restart the PostgreSQL service for the changes to take effect.

systemctl restart postgresql.service

Main use of PostgreSQL on Debian 9

To access the PostgreSQL command-line interface and manage databases, you must login as a postgres user. Perform the following command:

su - postgres

Now to enter the PostgreSQL interactive terminal, execute the following command:

PSQL

Your shell will change as follows:

~$ psql
psql (9.6.7)
Type "help" for help.

postgres=#

Now you can execute a command specific to PostgreSQL. For example, to list all current databases that you can enter \l clickEptag.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

You can also create databases and tables, insert data, extract data, and do more with PostgreSQL interactive terminal. For more information, you can check the official PostgreSQL documentation.

If you prefer to use the graphical user interface to manage databases and saved data, we can recommend you to install and use phpPgAdmin. This is a PostgreSQL web-based administration tool similar to phpMyAdmin for managing the MySQL database system.