🐘 Setup PostgreSQL — Relational Database
Deploy a powerful, open-source PostgreSQL database on Ubuntu with Docker. The script auto-generates secure credentials and displays connection details at the end.
📦 Resources & Setup Scripts
Grab the automated bash script from GitHub to follow along with the video.
Quick Install:
wget https://raw.githubusercontent.com/mhmdali94/Docker/main/databases/postgres/postgres-ubuntu.sh
chmod +x postgres-ubuntu.sh
sudo bash postgres-ubuntu.shTutorial Steps
1 Download & Run the Script
The script installs Docker, generates a secure random password, and starts PostgreSQL with a ready-to-use database and user.
wget https://raw.githubusercontent.com/mhmdali94/Docker/main/databases/postgres/postgres-ubuntu.sh
chmod +x postgres-ubuntu.sh
sudo bash postgres-ubuntu.sh2 Save Your Connection Details
The script displays the generated credentials at the end. Save them immediately:
| Field | Value |
|---|---|
| User | pgadmin |
| Database | pgdb |
| Password | auto-generated — shown at end of script |
| Port | 5432 |
3 Connect with psql
psql -h <server-ip> -U pgadmin -d pgdb4 Run a Test Query
-- Check connection
SELECT version();
-- Create a test table
CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test (name) VALUES ('Hello, PostgreSQL!');
SELECT * FROM test;Ports Used
| Port | Purpose |
|---|---|
| 5432 | PostgreSQL |
Overview
PostgreSQL is one of the world's most advanced open-source relational database management systems, with over 35 years of active development. It is fully ACID-compliant, supports advanced data types like JSON, arrays, and geospatial data (PostGIS), and handles complex queries with its powerful query planner.
Why Use It
PostgreSQL is the gold standard for relational databases in self-hosted infrastructure. It is the default backend for Gitea, Plausible, Umami, Listmonk, Outline, and dozens of other popular self-hosted apps. Unlike SQLite, PostgreSQL handles concurrent connections, complex transactions, and large datasets without degrading. Its JSON support means you get both relational integrity and document flexibility in one engine.
When You Need It
Who Should Use It
Real Use Cases
Main Features
How to Use After Installation
Security Best Practices
Ports and Firewall Notes
PostgreSQL listens on port 5432 by default. This port must never be exposed to the internet directly. Restrict it to localhost (listen_addresses = 'localhost' in postgresql.conf) or to the private IP of your application server. On UFW: ufw allow from <app-server-ip> to any port 5432. For multiple app servers, add a rule per IP. Remote access for development should go through an SSH tunnel: ssh -L 5432:localhost:5432 user@server.
Backup and Maintenance
Common Mistakes
Troubleshooting
Alternatives
MySQL/MariaDB is PostgreSQL's main competitor — faster for simple read-heavy workloads but lacks JSON, advanced indexing, and window functions. SQLite is excellent for single-user or embedded apps but doesn't handle concurrent writes. CockroachDB and YugabyteDB are distributed PostgreSQL-compatible databases for multi-region deployments — major overkill for self-hosted use. For most self-hosted apps, PostgreSQL is the recommended choice and has the broadest community support.
When Not to Use It
For simple single-user applications or tools with very low traffic, SQLite is lighter and requires zero configuration. If your app has a built-in SQLite mode (Gitea, for example), SQLite is fine for personal use — switch to PostgreSQL only when you need concurrent users or data integrity at scale. Don't use PostgreSQL if your team has no one with database administration experience for a critical production deployment — a managed service (Supabase, Neon) is safer until you build that expertise.
PrismaTechWork Professional Help
PrismaTechWork provides end-to-end infrastructure services — from initial deployment and security hardening to ongoing monitoring, automated backups, and dedicated support. Whether you need a single-server setup or a multi-site network, our team ensures your infrastructure is built right, secured properly, and maintained reliably.
Frequently Asked Questions
What is the difference between PostgreSQL and MySQL?
Both are relational databases but PostgreSQL is more standards-compliant and feature-rich. PostgreSQL supports advanced types (JSONB, arrays, custom), window functions, CTEs, full-text search, and Row-Level Security out of the box. MySQL is traditionally faster for simple read-heavy workloads. For most modern self-hosted applications, PostgreSQL is the recommended choice because it's the default for many apps (Gitea, Plausible, Outline).
Can I use PostgreSQL with Laravel?
Yes. Set DB_CONNECTION=pgsql, DB_HOST, DB_PORT=5432, DB_DATABASE, DB_USERNAME, and DB_PASSWORD in your .env file. Laravel's Eloquent ORM and query builder work identically with PostgreSQL. Run php artisan migrate to create tables. Make sure the pdo_pgsql PHP extension is installed: php -m | grep pgsql.
How do I connect multiple apps to the same PostgreSQL server?
Create a separate database and user for each application: CREATE DATABASE app1; CREATE USER app1user WITH ENCRYPTED PASSWORD 'pass'; GRANT ALL ON DATABASE app1 TO app1user;. Never share a database between unrelated applications. Each app gets its own credentials with access only to its own database, isolating data and limiting blast radius if one app is compromised.
How do I back up and restore a PostgreSQL database?
Back up: pg_dump -U pgadmin -Fc mydb > mydb.dump (custom format, compressed). Restore: pg_restore -U pgadmin -d mydb mydb.dump. For plain SQL format: pg_dump -U pgadmin mydb > mydb.sql and restore with psql -U pgadmin -d mydb < mydb.sql. Always test restores on a separate instance before relying on a backup for recovery.
What is the default postgres user and should I use it?
The postgres user is the superuser created during installation. You should NEVER use it for application connections — only for administrative tasks. Create a dedicated user with only the permissions needed for each application. The install script creates a pgadmin user and pgdb database; use those as the starting point and create per-app users from there.
How do I update PostgreSQL to a newer major version?
Major version upgrades (e.g., 15 → 16) require pg_upgrade or a dump-and-restore process because the data directory format changes. Steps: (1) Take a full pg_dump backup. (2) Install the new PostgreSQL version alongside the old one. (3) Run pg_upgrade -b /old/bin -B /new/bin -d /old/data -D /new/data. (4) Test the upgraded cluster. (5) Remove the old version. Minor version updates (e.g., 15.3 → 15.4) are safe to do with apt upgrade.
Is PostgreSQL suitable for high-traffic applications?
Yes, PostgreSQL handles millions of queries per day in production at large companies. The key is proper indexing, connection pooling (PgBouncer), and read replicas for heavy read workloads. For a self-hosted setup with hundreds of users, a well-tuned single PostgreSQL instance is more than sufficient. Add replicas and connection pooling only when you have measurable bottlenecks.
How do I monitor PostgreSQL performance?
Use pg_stat_statements (CREATE EXTENSION pg_stat_statements) to identify slow queries. Run SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10. Monitor active connections with SELECT * FROM pg_stat_activity. For dashboard monitoring, use postgres_exporter with Prometheus + Grafana, or check Netdata which includes PostgreSQL metrics out of the box.