PostgreSQL itself installs in one command. The part people get wrong is remote access: they open port 5432 to the world, set a password, and wonder why the logs fill up with login attempts. This guide installs Postgres on Ubuntu 24.04, creates a database, and shows two safe ways to connect from outside.
1. Install
apt update && apt install -y postgresql
systemctl status postgresql --no-pager
On Ubuntu 24.04 that's PostgreSQL 16. It listens only on localhost by default — keep it that way unless you have a reason.
2. Create a user and a database
sudo -u postgres psql <<'SQL'
CREATE USER app WITH PASSWORD 'use-a-long-random-password';
CREATE DATABASE appdb OWNER app;
SQL
Apps running on the same server connect with postgresql://app:PASSWORD@localhost:5432/appdb.
3. Connect from your laptop: SSH tunnel (recommended)
Nothing to open, nothing to configure on the server. The tunnel forwards a local port through SSH:
ssh -N -L 5433:localhost:5432 root@203.0.113.10
On a NAT plan, add your personal SSH port from the dashboard:
ssh -N -p 20123 -L 5433:localhost:5432 root@your-nat-host
While the tunnel runs, point your database client at localhost:5433. Everything is encrypted by SSH, and port 5432 stays closed to the internet. GUI tools like DBeaver and pgAdmin have an "SSH tunnel" tab that does the same thing.
4. Direct access for another server (allowlist)
If an app server elsewhere must connect directly, allow only its IP. This needs a dedicated-IP plan for the database server, since NAT plans don't accept inbound connections apart from SSH.
In /etc/postgresql/16/main/postgresql.conf:
listen_addresses = '*'
At the end of /etc/postgresql/16/main/pg_hba.conf:
hostssl appdb app 198.51.100.20/32 scram-sha-256
Open the firewall for that one address and restart:
ufw allow from 198.51.100.20 to any port 5432 proto tcp
systemctl restart postgresql
hostssl forces an encrypted connection — Ubuntu's package ships a self-signed certificate, so clients should connect with sslmode=require.
5. Sensible first tuning
For a server with 2 GB RAM, in postgresql.conf:
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 8MB
Restart after changing. That's enough for most small apps; tune further once you see real queries.
6. Backups
A nightly dump, kept for a week:
mkdir -p /var/backups/pg && chown postgres /var/backups/pg
cat > /etc/cron.d/pg-backup <<'EOF'
30 3 * * * postgres pg_dump -Fc appdb > /var/backups/pg/appdb-$(date +\%u).dump
EOF
-Fc produces a compressed dump you restore with pg_restore. Copy dumps off the server as well; Managed Backups add daily restore points of the whole machine.
Comments
No comments yet. Be the first.