PostgreSQL was one of those things I kept hearing about but didn't bother learning because MySQL worked fine for what I was doing. Then I hit a query that MySQL was handling slowly, asked around, and was told PostgreSQL would handle it better. Switched one project over as an experiment.
I've used PostgreSQL almost exclusively for new projects since then. The query planner is smarter, the JSON support is genuinely useful, and the EXPLAIN ANALYZE output actually helps you understand what's happening. It takes a bit more initial configuration to get right, but it's worth understanding properly.
This guide covers installation, the pg_hba.conf authentication setup (the part that confuses most newcomers), basic performance tuning with pgtune, and setting up automated backups.
I run PostgreSQL on Tencent Cloud Lighthouse. The 4 GB RAM plan is a good starting point for running PostgreSQL and your application on the same instance. Lighthouse's CBS cloud disk expansion is particularly useful for PostgreSQL — as your database grows, you can attach additional storage volumes without migrating servers or reconfiguring the database. The snapshot feature gives you a point-in-time full-server backup before major migrations, which is valuable when PostgreSQL migration operations are complex to reverse.
- Key Takeaways
| Feature | PostgreSQL | MySQL |
|---|---|---|
| Standards compliance | Excellent | Good |
| JSON support | Native JSONB | JSON type (less capable) |
| Full-text search | Built-in | Requires setup |
| Window functions | Full support | Limited |
| Concurrency (MVCC) | Excellent | Good (InnoDB) |
| Read performance | Good | Excellent |
| Ecosystem | Django, Rails, Laravel | WordPress, PHP apps |
Choose PostgreSQL for complex applications with advanced query needs. MySQL is fine for simpler applications and has better raw read performance for straightforward queries.
The Ubuntu default repos include PostgreSQL, but for the latest version use the official PostgreSQL apt repository:
# Install from Ubuntu repos (easier, slightly older version)
sudo apt update
sudo apt install -y postgresql postgresql-contrib
# Or install latest version from official repo:
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install -y postgresql-16
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql
Check version:
psql --version
# psql (PostgreSQL) 16.x
PostgreSQL creates a system user called postgres during installation. This user has superuser access to the database.
# Switch to postgres user and connect
sudo -u postgres psql
# Check current connection
\conninfo
# You should see: connected as user "postgres" to database "postgres"
# Exit
\q
The main configuration files are:
| File | Purpose |
|---|---|
/etc/postgresql/16/main/postgresql.conf |
Server settings |
/etc/postgresql/16/main/pg_hba.conf |
Authentication rules |
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'strong_postgres_password';"
sudo -u postgres psql
-- Create database
CREATE DATABASE myapp_db
WITH ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0;
-- Create user
CREATE USER myapp_user WITH PASSWORD 'strong_app_password';
-- Grant connection and usage
GRANT CONNECT ON DATABASE myapp_db TO myapp_user;
-- Connect to database and set up schema access
\c myapp_db
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;
-- Future tables and sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO myapp_user;
\q
Test the user:
psql -U myapp_user -d myapp_db -h localhost
# Enter password
# \q to exit
PostgreSQL's pg_hba.conf controls which users can connect, from where, and using what authentication method.
sudo nano /etc/postgresql/16/main/pg_hba.conf
The default configuration looks like this:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
For application users connecting via TCP (from the app on the same server):
# Allow myapp_user to connect to myapp_db via TCP with password
host myapp_db myapp_user 127.0.0.1/32 scram-sha-256
After editing pg_hba.conf:
sudo systemctl reload postgresql
The default PostgreSQL configuration is conservative (designed to run on minimal hardware). Tune it for your server:
sudo nano /etc/postgresql/16/main/postgresql.conf
Adjustments based on server RAM (4 GB example):
# Memory
shared_buffers = 1GB # 25% of RAM
effective_cache_size = 3GB # 75% of RAM (estimation for query planner)
work_mem = 16MB # Per sort operation; multiply by max_connections
maintenance_work_mem = 256MB # For VACUUM, CREATE INDEX, etc.
# Write performance
wal_buffers = 16MB
checkpoint_completion_target = 0.9
synchronous_commit = off # Faster writes, tiny risk of last-second data loss on crash
# Connections
max_connections = 100 # Reduce if using connection pooler
# Query planner
random_page_cost = 1.1 # For SSD storage (default 4.0 is for spinning disk)
effective_io_concurrency = 200 # SSD concurrent I/O
# Logging
log_min_duration_statement = 1000 # Log queries over 1 second
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
A quick way to get reasonable defaults for your hardware: use pgtune.leopard.in.ua — input your server specs and it generates optimized settings.
sudo systemctl restart postgresql
# On your local machine — tunnel port 5433 to server's PostgreSQL
ssh -L 5433:127.0.0.1:5432 ubuntu@YOUR_SERVER_IP -N
# Connect with psql
psql -h localhost -p 5433 -U myapp_user -d myapp_db
sudo nano /etc/postgresql/16/main/postgresql.conf
# Change: listen_addresses = 'localhost' to:
# listen_addresses = '*'
sudo nano /etc/postgresql/16/main/pg_hba.conf
# Add:
# host myapp_db myapp_user YOUR_IP/32 scram-sha-256
# Open port in UFW for specific IP only
sudo ufw allow from YOUR_IP to any port 5432
sudo systemctl restart postgresql
# Dump a single database
pg_dump -U myapp_user -d myapp_db -F c -f myapp_backup.dump
# Dump all databases (as postgres superuser)
sudo -u postgres pg_dumpall > all_databases.sql
# Restore single database
pg_restore -U myapp_user -d myapp_db myapp_backup.dump
# Restore SQL dump
psql -U myapp_user -d myapp_db < myapp_backup.sql
sudo -u postgres psql -d myapp_db
-- Analyze tables (update statistics for query planner)
ANALYZE;
-- Vacuum (reclaim space from deleted rows)
VACUUM ANALYZE;
-- Check table sizes
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Check active connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
The most confusing PostgreSQL error for new users:
FATAL: Ident authentication failed for user "myapp_user"
or
FATAL: password authentication failed for user "myapp_user"
Both usually come down to pg_hba.conf. PostgreSQL's authentication is controlled entirely by this file — a valid username and correct password are not enough if pg_hba.conf doesn't have a matching rule.
Debug steps:
# 1. Check what pg_hba.conf says for your connection type
sudo cat /etc/postgresql/16/main/pg_hba.conf
# 2. Check PostgreSQL logs for the actual error
sudo tail -20 /var/log/postgresql/postgresql-16-main.log
# 3. Test with the postgres superuser to isolate the issue
sudo -u postgres psql -d myapp_db
Common fixes:
peer for local socket, scram-sha-256 for TCP)sudo systemctl reload postgresql)# Connect
sudo -u postgres psql # Superuser via socket
psql -U USER -d DB -h localhost # User via TCP
# In psql shell
\l # List databases
\c DATABASE # Connect to database
\dt # List tables
\d TABLE # Describe table
\du # List users
\conninfo # Show current connection
\q # Quit
# Service
sudo systemctl start|stop|restart|reload postgresql
sudo systemctl status postgresql
# Logs
sudo tail -f /var/log/postgresql/postgresql-16-main.log
| Issue | Likely Cause | Fix |
|---|---|---|
| Connection refused | Service not running or wrong port | Check systemctl status SERVICE and verify firewall rules |
| Permission denied | Wrong file ownership or permissions | Check file ownership with ls -la and use chown/chmod to fix |
| 502 Bad Gateway | Backend service not running | Restart the backend service; check logs with journalctl -u SERVICE |
| SSL certificate error | Certificate expired or domain mismatch | Run sudo certbot renew and verify domain DNS points to server IP |
| Service not starting | Config error or missing dependency | Check logs with journalctl -u SERVICE -n 50 for specific error |
| Out of disk space | Logs or data accumulation | Run df -h to identify usage; clean logs or attach CBS storage |
| High memory usage | Too many processes or memory leak | Check with htop; consider upgrading instance plan if consistently high |
| Firewall blocking traffic | Port not open in UFW or Lighthouse console | Open port in Lighthouse console firewall AND sudo ufw allow PORT |
Do I need a separate server for PostgreSQL?
For most individual projects and small teams, running PostgreSQL on the same server as your application is practical. The zero-latency between app and database (localhost communication) is a genuine advantage. Separate database servers are warranted at significant scale.
How do I back up a PostgreSQL database?
Use the database's built-in dump tools for logical backups (recoverable to any host). Combine with Lighthouse snapshots for full-server point-in-time recovery. Set up automated daily cron jobs for consistent backups.
How do I connect to PostgreSQL remotely for development?
Use SSH tunneling: ssh -L LOCAL_PORT:localhost:DB_PORT user@server. This routes your local database client through SSH without exposing the database port to the internet.
What user permissions should database application users have?
Application users should have only the permissions they need — typically SELECT, INSERT, UPDATE, DELETE on their specific database. Never use the root/admin database account in application connection strings.
htop or your database's status commands and upgrade if you see consistent memory pressure.Set up PostgreSQL today:
👉 Tencent Cloud Lighthouse — Ubuntu VPS for PostgreSQL
👉 View current pricing and promotions
👉 Explore all active deals and offers