Introduction

PostgreSQL is an advanced, open-source object-relational database system (ORDMS) renowned for performance, feature robustness, and reliability. This database system utilizes SQL language together with its powerful features to scale and store most multifaceted data workloads. It helps developers create applications and enables administrators to safeguard the integrity of data and develop fault-tolerant environments. Besides, PostgreSQL makes it easy to manage data regardless of the size of the dataset. PostgreSQL runs seamlessly on all Linux systems and this tutorial will help you install this database management system on your Debian 9 server. Ready? Let’s go!

Step 1 –
installing PostgreSQL

First, log in to your Debian 9 server and issue the following command to update the local package index:

$ sudo apt update

Next, run the command below to install PostgreSQL contrib and PostgreSQL server. These packages provide extra features for your PostgreSQL database.

$ sudo apt install postgresql postgresql-contrib

The PostgreSQL application will start automatically once the two packages are installed. To check if the service was installed correctly, execute the command below:

$ sudo -u postgres psql -c "SELECTversion();"

This will use the psql utility to connect to a PostgreSQL server and deliver the output below showing the database server version.

version                                                  
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.10onx86_64-pc-linux-gnu, compiledbygcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

Alternatively, you can issue the command below to check the version of PostgreSQL you have installed:

$ sudo psql --version

This will give you an output similar to the one below:

psql (PostgreSQL) 9.6.10

Step 2 –
Accessing PostgreSQL

When PostgreSQL is installed on Debian 9, a superuser called postgres is created automatically. This superuser is equivalent to the root user in MySQL. Now, if you want to log in to your PostgreSQL server as the superuser (postgres), then you must first switch to this user. This will permit you to use the psql utility to access the PostgreSQL’s interactive terminal.

$ sudo su - postgres
$ psql

From here its easy to interact with the PostgreSQL instance. If you want to exit the psql shell, issue the command below:

In addition, you can access the PostgreSQL service prompt using the sudo command below:

$ sudo -u postgres psql

To exit the PostgreSQL command line, execute the command below:

exit

Note: The superuser (postgres) is used from a local host, for this reason, you should not set any password for this user.

Step 3 –
Managing PostgreSQL

To start your PostgreSQL service, execute the command below:

$ sudo systemctl start postgresql.service

If you want to stop this service, issue the command below:

$ sudo systemctl stop postgresql.service

To stop the service, run the command below:

$ sudo systemctl stop postgresql.service

Then, to restart the PostgreSQL service, use the command below:

$ sudo systemctl restart postgresql.service

Besides, you can always check the status of your PostgreSQL service using the command below:

$ sudo systemctl status postgresql.service

This will give you an output similar to the one below:

postgresql.service-PostgreSQLRDBMS
   Loaded: loaded(/lib/systemd/system/postgresql.service;enabled;vendor preset: enabled)
   Active: active(exited)sinceTue2018-10-1612:16:02UTC;40minago
 Main PID: 5867(code=exited,status=0/SUCCESS)
       Tasks: 0(limit:4915)
   CGroup: /system.slice/postgresql.service

If you want to enable PostgreSQL, then execute the command below:

$ sudo systemctl enable postgresql.service

Next, if for any reason you want to disable PostgreSQL, run the command below:

$ sudo systemctl disable postgresql.service

To manage PostgreSQL’s file locations, resource usage, logging, connections, and authentication, you should edit its main configuration file. The PostgreSQL’s main configuration file is found in the directory, /etc/postgresql/VERSION-NUMBER/main. For instance, if you have installed PostgreSQL version 9.6, then this configuration file is in the location, /etc/postgresql/9.6/main/postgresql.conf. Use your favorite text editor to open this file and make all the required changes. Once you make the changes run the command below to restart the PostgreSQL service. This allows all these changes to take effect:

$ systemctl restart postgresql.service

Step 4 –
Creating Roles And Databases in PostgreSQL

It’s a breeze to create roles and databases in PostgreSQL from the command line. Here, we’ll use the createuser command to fashion new roles. To create a role/user called david, execute the command below:

$ sudo su - postgres -c "createuser david"

Note: Remember to replace the placeholder “david” with your preferred name.

Next, to create a PostgreSQL database, run the command below:

$ sudo su - postgres -c "createdb daviddb"

The command above will create a database called “daviddb” Now, if you want to grant “david” privileges to perform basic database management tasks, first, run the command below to connect to the PostgreSQL command line.:

$ sudo -u postgres psql

Next, execute the command below to give permissions to the user:

grant all privilegesondatabase daviddb to david;

That is it! The new user “david” has the permission to perform basic tasks on the database “daviddb”.

Conclusion

Congratulation! You have successfully installed PostgreSQL on your Debian 9 system and learned how to manage this service. For in-depth information regarding PostgreSQL database management system, visit the official PostgreSQL Documentation page.