How to Install PostgreSQL on Debian 9

Install PostgreSQL on Debian 9

In this tutorial we are going to learn how to install PostgreSQL on Debian 9. We will also install and configure its prerequisites. PostgreSQL is a free, open-source object-relational database management system (object-RDBMS), similar to MySQL, and is standards-compliant and extensible. It is commonly used as a back-end for web and mobile applications.

I recommend to use a minimal Ubuntu server setup as a basis for the tutorial, that can be a virtual or a root server image with an Debian 9 Stretch minimal install from a web hosting company or you use our minimal server tutorial to install a server from scratch.

Install PostgreSQL on Debian 9

Step 1. First, ensure your system and apt package lists are fully up-to-date by running the following:

apt-get update -y
apt-get upgrade -y

Step 2. Installing PostgreSQL on Debian 9.

Run the following command to install the PostgreSQL 9 package:

sudo apt install postgresql postgresql-contrib

When the installation is completed, the PostgreSQL service will start automatically. To check an verify that PosgreSQL server/client are installed, you can use the following command:

psql --version

Step 3. Basic PostgreSQL usage on Debian 9.

Let’s log in to postfix to verify that installation completed successfully:

su - postgres

Use psql command to access PostgreSQL prompt with admin privileges:

$ psql

psql (9.6.7)
Type "help" for help.

postgres=#

You may create password for user postgres for security purpose:

postgres=# \password 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.

Congratulation’s! You have successfully install and configured PostgreSQL on your Debian Stretch. Thanks for using this tutorial installing PostgreSQL on Debian 9 (Stretch) system.