PostgreSQL 17

PostgreSQL is a powerful, open-source object-relational database known for its reliability, standards compliance, and extensibility. It supports advanced data types, full-text search, JSON, and a wide range of extensions.

Software included

Component	Version
PostgreSQL	17.x
Ubuntu	24.04 LTS

Getting started

1. Connect to your VM

ssh ubuntu@<your-vm-ip>

2. Wait for first-boot configuration

On the first boot, a setup script runs automatically. It generates a random password for the postgres superuser and saves it to /etc/postgresql/postgres-password.txt. This takes under 30 seconds.

Track progress:

journalctl -u postgresql-first-boot.service -f

3. Retrieve credentials

sudo cat /etc/postgresql/postgres-password.txt

This file contains the postgres superuser password. It is only readable by root.

4. Connect to PostgreSQL

PG_PASS=$(sudo cat /etc/postgresql/postgres-password.txt)
psql -U postgres -h 127.0.0.1 -p 5432 -W

Enter the password from the credentials file when prompted.

To avoid the password prompt, set the PGPASSWORD environment variable:

PGPASSWORD="$PG_PASS" psql -U postgres -h 127.0.0.1

Managing PostgreSQL

# Check service status
systemctl status postgresql

# Restart
sudo systemctl restart postgresql

# View logs
sudo journalctl -u postgresql -f

Configuration directory: /etc/postgresql/17/main/

Key files:

postgresql.conf: server settings
pg_hba.conf: client authentication rules

To allow remote connections, set listen_addresses = '*' in postgresql.conf and add an entry in pg_hba.conf. Restart PostgreSQL and open the firewall for specific IPs only (see Security).

Security

Port 5432 is not open externally by default. UFW is enabled and allows SSH (port 22) only.

To allow access from a specific IP:

sudo ufw allow from <trusted-ip> to any port 5432

To connect without opening the firewall (recommended), use an SSH tunnel:

# Run this on your local machine
ssh -L 5432:localhost:5432 ubuntu@<your-vm-ip>

# Then connect locally
psql -U postgres -h 127.0.0.1