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:

The birth of PostgreSQL

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.

Ingres becomes Postgres

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.

Bye POSTQUEL, hello SQL

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:

• Apple
• Spotify
• Instagram
• Netflix
• Uber
• Reddit
• Airbnb
• Slack
• Twitch
• Skype

And more are using PostgreSQL to store data for their applications.

PostgreSQL vs. other databases

Pros of using PostgreSQL

1. Open source: PostgreSQL is an open-source database system, which means it's free to use, modify, and distribute. This can lead to cost savings compared to proprietary databases like Oracle Database.
2. Data integrity: PostgreSQL is known for its strong emphasis on data integrity, with features like ACID (Atomicity, Consistency, Isolation, Durability) compliance, reliable transaction handling, and advanced concurrency control mechanisms.
3. SQL compliance and standards conformance: PostgreSQL adheres closely to SQL standards, making it easier to migrate data and applications from other database systems.
4. Advanced data types: PostgreSQL supports a wide range of advanced data types, including JSON, XML, arrays, geometric data types, and more, making it well-suited for storing and querying complex data structures.
5. Extensibility: PostgreSQL allows for the creation of custom data types, functions, operators, and even entire programming languages through its extensible architecture.
6. Scalability: PostgreSQL can handle large databases and high workloads, thanks to features like partitioning, replication, and parallel query processing.
7. Active community and support: PostgreSQL has a large and active community of developers and contributors, ensuring ongoing development, support, and extensive documentation.

Cons of using PostgreSQL

1. Performance for read-heavy workloads: While PostgreSQL performs well for read-heavy workloads when data is cached in memory, some specialized in-memory or NoSQL databases may be better optimized for read-intensive workloads, particularly when the entire dataset can fit in memory and take full advantage of in-memory data structures and caching mechanisms.
2. Lack of native graphical tools: PostgreSQL lacks native graphical tools for administration and development tasks, although third-party tools are available.
3. Complexity for beginners: PostgreSQL's rich feature set and advanced capabilities can make it more complex for beginners or those new to relational database management systems (RDBMS). Something like SQLite could be considered a bit easier to start out with.

Common use cases for PostgreSQL

1. Web applications: PostgreSQL is widely used as the database backend for web applications, particularly those built with frameworks like Ruby on Rails, Django (Python), and Node.js.
2. Enterprise applications: PostgreSQL's robustness and scalability, makes it suitable for enterprise-level applications that require high data integrity and reliability.
3. Geographic information systems (GIS): PostgreSQL's support for spatial data types and extensions like PostGIS make it a popular choice for GIS applications and location-based services.
4. Data warehousing and business intelligence: With its support for advanced data types, PostgreSQL can be used for data warehousing and business intelligence applications, particularly when combined with tools like PostgreSQL's Foreign Data Wrappers (FDWs) and extensions like CitusData for distributed query processing.
5. Content management systems (CMS): Many popular CMS platforms, such as Drupal and WordPress, support or can be configured to use PostgreSQL as the database backend.
6. Scientific and research applications: PostgreSQL's ability to handle complex data structures and perform advanced computations makes it a suitable choice for scientific and research applications in fields like bioinformatics, finance, and physics.

Setting up a PostgreSQL database on Debian 12

Alrighty, now that you know a bit more about what PostgreSQL is all about, let's set up a PostgreSQL database.

Step 1: Install dependencies

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

Step 2: Create a PostgreSQL database

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.

Step 3: Set up a Python web application

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

Conclusion

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 ;-).