How To Install PostgreSQL on Debian 12

---

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

Output:

root@vps:~# sudo apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl libxslt1.1
  libz3-4 postgresql-15 postgresql-client-15 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc postgresql-doc-15 isag

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 (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.

postgres=#

To exit out of the PostgreSQL shell type.

\q

To Create PostgreSQL Role

sudo -u postgres createuser --interactive

Output:

root@vps:~# sudo -u postgres createuser --interactive
could not change directory to "/root": Permission denied
Enter name of role to add: john
Shall the new role be a superuser? (y/n) y
root@vps:~# 

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 (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.

john=#

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".
john=# 

Done!