How To Install PostgreSQL on Debian 10

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Updating the system

We first update the system to make sure that all our installed packages are up to date. Your Debian system can be updated easily with the following command.

apt update

apt upgrade

Install PostgreSQL on Debian

If sudo is not installed, install sudo package using the following command:

apt-get install sudo

Install PostgreSQL on Debian by using the following command.

sudo apt install postgresql postgresql-contrib

PostgreSQL Roles and Databases

We'll switch to a postgres account for this.

sudo -i -u postgres

you can access a PostgreSQL prompt using the psql utility.

psql

Ouput:

postgres@vps:~$ psql
psql (11.12 (Debian 11.12-0+deb10u1))
Type "help" for help.

To exit out of the PostgreSQL shell type.

\q

To Create PostgreSQL Role

sudo -u postgres createuser --interactive

Output:

Enter name of role to add: john
Shall the new role be a superuser? (y/n) y

To create a PostgreSQL database

sudo -u postgres createdb john

To open a Postgres Prompt with the New Role.

sudo adduser john

To switch over and connect to the database.

sudo -u john psql

Output:

root@vps:~# sudo -u john psql
could not change directory to "/root": Permission denied
psql (11.12 (Debian 11.12-0+deb10u1))
Type "help" for help.

Once you logged in as john and check your current connection information.

\conninfo

Output:

john=# \conninfo
You are connected to database "john" as user "john" via socket in "/var/run/postgresql" at port "5432".