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