How To Setup a PostgreSQL Database on Debian 12 Bookworm
Learn more about PostgreSQL including how to set up a database on Debian 12 and display some information with a simple Python web application.
Publish date: 4/3/2024
Learn more about PostgreSQL including how to set up a database on Debian 12 and display some information with a simple Python web application.
Publish date: 4/3/2024
In this article, we'll be showing you how you can easily set up a PostgreSQL database on Debian 12 "Bookworm".
But before we do that, let's talk a little bit more about what PostgreSQL is all about, starting from the very beginning:
PostgreSQL's journey began at the University of California, Berkeley, where it originated from the Ingres project led by Professor Michael Stonebraker.
Ingres, which stood for "Interactive Graphics and Retrieval System," was a pioneering relational database management system developed in the 1970s.
Later on, in the early 1980s, Stonebraker and his team began working on a new project called Postgres, which aimed to address the limitations of existing database systems and introduce advanced concepts such as object-relational support and extensibility.
Postgres built upon the foundation laid by Ingres, but incorporated new ideas and features to push the boundaries of database technology.
Then in the mid-1990s, Andrew Yu and Jolly Chen, two Berkeley graduate students, made a significant contribution to the Postgres project. They replaced the original POSTQUEL query language with SQL, the standard query language for relational databases.
That gave birth to Postgres95, which introduced a more flexible and powerful system that could cater to the needs of modern applications.
To reflect its SQL compliance and distinguish itself from the original Postgres project, the system was renamed to PostgreSQL in 1996.
Since then, it's grown significantly.
PostgreSQL now ranks as the 4th most popular database management system, trailing behind industry giants such as Oracle, MySQL, and Microsoft SQL Server.
Today companies like:
And more are using PostgreSQL to store data for their applications.
| Database | Strengths | Weaknesses |
|---|---|---|
| PostgreSQL | Data integrity, advanced data types, SQL compliance, write performance | Slower for read-heavy workloads |
| MySQL | Read performance, simplicity | Limited data types, weaker data consistency |
| Oracle | Enterprise features, scalability, high concurrency | Proprietary, high cost |
| SQL Server | Microsoft ecosystem integration, scalability | Windows-centric, expensive |
| MongoDB (NoSQL) | Flexible data model, scalability, real-time analytics | Lacks ACID transactions, complex queries |
Alrighty, now that you know a bit more about what PostgreSQL is all about, let's set up a PostgreSQL database.
The first thing we'll need to do is install our dependencies, so go ahead and SSH into your Debian 12 server.
If you don't already have a server, xTom is an infrastructure-as-a-service provider that's been around longer than a decade. Our NVMe VPS line works perfectly for something like a PostgreSQL database.
After connecting, run the following commands:
sudo apt update
sudo apt install -y postgresql postgresql-contrib python3-venv
Or you want to install the latest version of PostgreSQL, you can add the PostgreSQL repository to your system by running the following commands:
sudo apt install curl ca-certificates gnupg dpkg
curl -sSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor > /usr/share/keyrings/postgresql.gpg
sudo sh -c 'echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/postgresql.list'
sudo apt update
sudo apt install -y postgresql
(Note: You don't need to install Python normally, but we'll be using it for testing our PostgreSQL database later. Also, it's recommended to use a non-root user with sudo privileges. )
Next, we'll need to open the PostgreSQL prompt as the postgres user so run the following command:
sudo -u postgres psql
Then run:
CREATE DATABASE myapp_db;
CREATE USER myapp_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;
\q
Replacing everything with your own desired information.
Congrats! You're now the proud owner of a PostgreSQL database.
To demonstrate this, we'll use a quick Python web application, so go ahead and run the following commands:
mkdir ~/myapp
cd ~/myapp
python3 -m venv venv
source venv/bin/activate
Next install Flask and the PostgreSQL adapter using pip, which you should already have:
pip install flask psycopg2-binary
Then run the following:
nano app.py
Copy and paste the following:
# app.py
import psycopg2
from config import DB_HOST, DB_NAME, DB_USER, DB_PASS
from flask import Flask
app = Flask(__name__)
@app.route('/')
def hello():
try:
conn = psycopg2.connect(
host=DB_HOST,
database=DB_NAME,
user=DB_USER,
password=DB_PASS
)
cur = conn.cursor()
cur.execute('SELECT version()')
version = cur.fetchone()
conn.close()
return f'Hello, World! PostgreSQL version: {version[0]}'
except (Exception, psycopg2.Error) as error:
return f'Error: {error}'
if __name__ == '__main__':
app.run(host='0.0.0.0', debug=True)
Then save and exit and once again use nano, running the following:
nano config.py
Copy and paste the following inside, replacing it with your own information:
# config.py
DB_HOST = 'localhost'
DB_NAME = 'myapp_db'
DB_USER = 'myapp_user'
DB_PASS = 'your_password'
Finally, run:
python app.py
After that, your Flask development server should be running and you should be able to open your Python app at your_ip:5000, receiving back something similar to the following if successful:
Hello, World! PostgreSQL version: PostgreSQL 15.6 (Debian 15.6-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
PostgreSQL is an incredible database system that has stood the test of time, continuously evolving and adapting to meet the ever-changing needs of modern applications.
From small applications that never leave the office computer to large-scale social media networks like Reddit, PostgreSQL's robust data integrity, advanced data types support, and SQL compliance make it a reliable and versatile choice for a wide range of use cases.
Whether you're building a web application with frameworks like Ruby on Rails, Django, Node.js, or developing enterprise-level applications that require high data reliability and scalability and beyond, PostgreSQL's feature-rich ecosystem and active community support ensure that you have the tools and resources to succeed.
Thanks for reading! If you're ready to set up your own PostgreSQL database, please consider using xTom. We might be a little biased, but we're confident you won't regret it ;-).