How To Install PostgreSQL on Debian 9

Cloud VPS Hosting

We will show you how to install PostgreSQL on Debian 9. PostgreSQL is an object-relational database management system written in C. It is free and open-source database system which main features are to store the data securely and to return the data as response to other applications requests. Installing PostgreSQL on Debian 9 is fairly easy task, and it shouldn’t take more then 10 minutes to finish installing PostgreSQL on Debian 9. Let’s get started.

Before you start with the installation steps, make sure that you have a full root 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 all your system software is up to date. First, update the package index using the following command:

apt-get update

Once the package index is updated, you can go ahead an update all your system software to the latest version using the command below:

apt-get upgrade

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

Install PostgreSQL on Debian 9

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

apt-get install postgresql

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

To check an verify that PosgreSQL server/client are installed, you can use the following command:

psql --version

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

# psql --version
psql (PostgreSQL) 9.6.7

Manage PostgreSQL on Debian 9

Now that the installation of PostgreSQL is completed, it is good to know how you can manage the PostgreSQL service on your PostgreSQL VPS. To start the PostgreSQL service, run the following command on the 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 up and running on your Linux VPS at the moment, the output will be similar to the one below:

# 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 on system boot, run the following command:

systemctl enable postgresql.service

In case you want PostgreSQL disabled on system boot, run the following command:

systemctl disable postgresql.service

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

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

systemctl restart postgresql.service

Basic PostgreSQL usage on Debian 9

To access the PostgreSQL command line interface and manage the databases, you should log in as postgres system user. Run the following command:

su - postgres

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

psql

Your shell will change as follows:

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

postgres=#

Now you can execute command specific to PostgreSQL. For example, to list all current databases you can type \l and press Enter

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, retrieve data and do many other things using the PostgreSQL interactive terminal. For more information you can check the official PostgreSQL documentation.

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


Installing PostgreSQL on Debian 9Of course, you don’t have to install PostgreSQL on Debian 9, if you use one of our PostgreSQL VPS Hosting plans, in which case you can simply ask our expert Linux admins to install PostgreSQL on Debian 9 for you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on how to install PostgreSQL on Debian 9, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.