Debian 12 is the latest stable Linux distribution that introduces numerous features and improvements, including over 11,000 new packages. These notable features encourage Linux users to update their system to Debian 12.
PostgreSQL is a highly stable object-relational database that is backed by 30+ years of active development. It is compatible with several platforms/operating systems like MacOS, Linux, etc. This Multi-platform compatibility allows us to install Postgres on Debian 12 and use its extraordinary features elegantly.
This Postgres blog will show you how to download, install, and use PostgreSQL on Debian 12 using the following outlines:
- Prerequisite For Postgres Installation on Debian 12
- How to Install Postgres on Debian 12 Using its Default Repository?
- How to Install Postgres on Debian 12 Using PostgreSQL Repository?
- How to Setup and Use Postgres on Debian 12?
- How to Uninstall Postgres on Debian 12?
Before you start installing Postgres on Debian 12, make sure you meet the below-listed requirements:
- A Debian 12 (Bookworm) Server.
- A User With Sudo/Admin Rights.
The default repository of Debian 12 contains all the popular packages, and Postgres is no exception. Debian 12’s default repository is a convenient, reliable, and recommended approach to installing any package or software on Debian 12. So, it is a good practice to install Postgres from the default repository of Debian 12.
Execute the below-given steps to install Postgres on Debian 12 using its default repository:
Step 1: Update System Packages
Updating the system packages is good practice to begin the installation of any particular software package like Postgres. To do that, execute the following apt command with sudo privileges:
sudo apt update
Step 2: Install Postgres
Once the system packages are updated, users can execute the “apt install postgresql” command with the contributed modules for Postgres to install Postgres on their Debian machine.
sudo apt install postgresql postgresql-contrib
Step 3: Verify Postgres Installation
Execute the below command to verify the Postgres installation status:
sudo systemctl status postgresql
From the above snippet, it is clear that Postgres has been successfully installed on Debian 12.
Installing PostgreSQL from the official repository ensures a reliable, secure, and up-to-date database system. For this purpose, go through the following steps:
Step 1: Add Postgres Repository
The PostgreSQL Global Development Group (PGDG) repository offers Postgres packages for Debian-based systems that are built from the latest Postgres source code. To add/import pgdg on a Debian machine, execute the command:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Step 2: Add the Signing Key of the Postgres Repository
The signing key enables the system to verify the authenticity of packages signed with that key during installations or updates. Use the below command to import/add the PostgreSQL signing key from the specified URL to the system's trusted key list.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Step 3: Install PostgreSQL
Before Postgres installation, first, execute the update command to update the system packages:
sudo apt update
Now you are just one command away to install Postgres on your Debian machine. Use the given apt command to install Postgres without any hassles:
sudo apt install postgresql -y
Once you have successfully installed Postgres on your Debian 12 machine, you can set it up and avail its extraordinary features like security, data integrity, and many more.
Step 1: Setup Postgres
Setting up a valid and strong password for your databases is an essential practice that enhances security and prevents unauthorized access or cybercrime. To do that, execute the command:
sudo passwd postgres
Step 2: Switch to Postgres
“postgres” is a default superuser of PostgreSQL. Use the below command to switch from “debianuser” to “postgres”:
su -- postgres
Step 3: Access SQL Shell
psql or SQL Shell is the default terminal-based tool for Postgres. It can be used to perform any database operation using SQL queries or meta-commands. To access SQL Shell, simply type in “psql” and hit the ENTER key:
Step 4: Verify Postgres Version
In the SQL Shell, we can execute any command or query to perform a specific functionality. For instance, the following command shows the currently installed Postgres version on our Debian 12 machine:
Step 5: List Databases
As a beginner, one of the must-know meta-commands is “\l” which shows the list of available databases:
Step 6: Create a New Database
Apart from the default “postgres” database, we can create a database of our own choice using the following SQL command:
CREATE DATABASE cmd_db;
Step 7: Verify Database Creation
It's a good practice to ensure the creation of a newly created “cmd_db” database. For this purpose, run the command:
The presence of “cmd_db” in the list of available databases confirms the creation of the selected database.
Step 8: Switch to New Database
To use the newly created database, first, we need to access/switch to it. To do this, the below-given meta-command is used in Postgres:
Step 9: Create Table
Once you are connected to the desired database, you can perform any particular database operation like table creation, deletion, data insertion, etc. For instance, the below query creates a new table in the selected database:
CREATE TABLE cmd_table( id SERIAL PRIMARY KEY, nam VARCHAR );
Step 10: Verify Table Creation
To confirm the table’s creation, execute the “\dt” command that will enlist the relation’s details like schema, table name, type, and owner name:
If you don't need Postgres anymore, you can completely remove it from Debian 12 to free up the space. For this purpose, the apt command can be executed with the purge option that will remove Postgres along with all of its associated packages and files.
sudo apt purge postgresql postgresql-contrib -y
That’s all about installing and setting up PostgreSQL on Debian 12.
PostgreSQL can be installed on Debian 12 either from Debian 12’s default repository or the PostgreSQL Repository. Use the “sudo apt install postgresql postgresql-contrib” command to install Postgres from Debian 12’s default repository or execute the “sudo apt install postgresql -y” command to install it from the PostgreSQL Repository.
In this Postgres blog, two different methods are discussed comprehensively to install PostgreSQL on Debian 12. In addition to this, different SQL queries and meta-commands are also discussed to perform several database operations. Finally, the uninstallation process of Postgres is also illustrated.