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.
Of 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.