Next, add the PostgreSQL GPG key and repository:

# Add GPG Key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Add Repository
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

Step 2: Install PostgreSQL 18

Now that the repository is added, update your package list and install the server and client:

sudo apt update
sudo apt install -y postgresql-18 postgresql-client-18

Step 3: Verify the Service

Ensure that the database service is running and set to start automatically on reboot:

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

You should see Active: active (running) in the output.

Step 4: Create Your User and Database

Instead of switching to the interactive shell, you can use this efficient one-liner to create a user and database simultaneously. This is great for automation scripts.

Replace my_user, secure_password, and my_db with your own credentials:

sudo -u postgres psql -c "CREATE USER my_user WITH PASSWORD 'secure_password';"
sudo -u postgres psql -c "CREATE DATABASE my_db OWNER my_user;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user;"

Step 5: Configure Remote Access (Optional)

By default, Postgres only listens on localhost. If you need to connect from a tool like TablePlus or pgAdmin on your local machine, or from a Docker container, follow these steps.

1. Update postgresql.conf

Edit the main configuration file:

sudo nano /etc/postgresql/18/main/postgresql.conf

Find the line listen_addresses = 'localhost' and change it to:

listen_addresses = '*'

2. Update pg_hba.conf

Now, tell Postgres which IP addresses are allowed to authenticate.

sudo nano /etc/postgresql/18/main/pg_hba.conf

Add this line to the end of the file to allow password authentication via scram-sha-256 (the secure standard):

host    all             all             0.0.0.0/0               scram-sha-256

3. Restart the Service

Apply the changes:

sudo systemctl restart postgresql

Step 6: Security Hardening (Crucial)

Opening your database to 0.0.0.0/0 (the whole internet) is risky. Use UFW (Uncomplicated Firewall) to restrict access to only your IP address.

# Allow SSH first (don't lock yourself out!)
sudo ufw allow ssh

# Allow Postgres ONLY from your specific IP (e.g., 203.0.113.1)
sudo ufw allow from 203.0.113.1 to any port 5432

# Enable the firewall
sudo ufw enable

Note: If you are connecting from a Docker container on the same server, you generally don't need to open port 5432 externally if you use the Docker network gateway IP.

Conclusion

You now have a production-ready PostgreSQL 18 server running on Ubuntu 24.04. You've secured it with a custom user, configured remote access, and locked down the firewall.

Happy Coding!