Why PostgreSQL is a Critical Choice Over Excel or MySQL?
When your data exceeds 1GB or starts reaching millions of records, Excel will begin to hang. At that point, you need a real Database Management System (DBMS). PostgreSQL isn’t just a legacy name; it’s a performance beast capable of handling over 10,000 transactions per second if tuned correctly.
Many developers often hesitate between MySQL and PostgreSQL. Based on my experience deploying Fintech projects that require precision down to the last decimal, I choose PostgreSQL. It adheres to stricter ACID standards, supports lightning-fast JSONB data types, and allows you to write complex functions in various languages. If your application needs long-term scalability, don’t hesitate to choose Postgres from the start.
I have personally set up over 50 database clusters on Ubuntu Server, and here is the most streamlined process for you to have a stable system running.
Steps to Install PostgreSQL on Ubuntu 22.04
By default, Ubuntu 22.04 provides PostgreSQL version 14 in its official repositories. Installing via apt ensures you receive automatic security patches from the OS.
Step 1: Clean and Update the Package List
Never skip this step. Updating the cache helps avoid downloading outdated versions.
sudo apt updateStep 2: Install PostgreSQL and Essential Extensions
The postgresql-contrib package is crucial. It provides features like fuzzy string matching and data encryption that the core version lacks.
sudo apt install postgresql postgresql-contrib -yAfter installation, the service will activate automatically. You can rest assured that Postgres will restart itself if the server reboots unexpectedly.
Setting Up Roles and Databases for Your Project
PostgreSQL manages permissions using “Roles,” which are similar to users but more flexible. A common mistake is using the postgres account for application code. This is extremely risky for security.
Open the PostgreSQL Terminal
To get started, switch to the postgres system user to gain administrative privileges:
sudo -i -u postgres
psqlAt this point, the command prompt will change to postgres=#. You are now ready to issue commands to the database.
Create a Dedicated User for the Application
Suppose you are building an inventory management app. Create a separate user and database to make management and backups easier later on.
CREATE ROLE app_user WITH LOGIN PASSWORD 'SuperHardPassword2024';
CREATE DATABASE inventory_db OWNER app_user;Type \q to exit. Your application now has its own secure “home”.
Configuring Secure Remote Access
By default, Postgres only listens on localhost. If you want to use TablePlus or DBeaver from your local machine to inspect data, you must open the door for it.
1. Edit the Address Configuration File
Open the main configuration file with nano:
sudo nano /etc/postgresql/14/main/postgresql.confFind the listen_addresses line. Change it from localhost to '*' to allow external connections:
listen_addresses = '*'2. Grant Access in pg_hba.conf
This is the second layer of security. You need to define which IPs are allowed to knock on the server’s door.
sudo nano /etc/postgresql/14/main/pg_hba.confAdd the following line to the end of the file. Replace 0.0.0.0/0 with your static IP if you want absolute security:
host all all 0.0.0.0/0 md53. Apply Changes
Every modification to a .conf file requires a restart for the system to load the new parameters:
sudo systemctl restart postgresqlQuick Monitoring and Optimization Tips
Just because the database is running doesn’t mean it’s healthy. Keep these quick check commands in your back pocket.
Check Service Status
sudo systemctl status postgresqlIf you see a bright green active (running), you are 99% successful.
Check Port 5432
Ensure the firewall isn’t blocking the default Postgres port:
ss -nlt | grep 5432A small tip for you: The log directory at /var/log/postgresql/ can take up several GBs of disk space after a month if you enable query logging (slow query log). Regularly check the capacity using the df -h command to avoid the server “freezing” due to running out of memory. With just 10 minutes of setup, you now have an industry-standard database foundation for your project!
