How to Connect to DigitalOcean PostgreSQL

Published December 2, 2024 | By Matt

DigitalOcean Managed Databases offer a convenient way to set up and manage PostgreSQL databases without the overhead of handling the underlying infrastructure. Connecting to your DigitalOcean PostgreSQL database allows you to interact with your data, perform queries, and integrate the database with your applications. This guide walks through the steps to securely connect to a PostgreSQL database hosted on DigitalOcean.

Prerequisites to Connect a Droplet to PostgreSQL

Click here for free DigitalOcean credit

DigitalOcean Account: Access to your DigitalOcean account where your PostgreSQL database is hosted.
Managed PostgreSQL Database: An existing PostgreSQL database set up in DigitalOcean.
PostgreSQL Client: Installed on your local machine or server (e.g., psql, pgAdmin, or any PostgreSQL-compatible client).
SSL Certificate (Optional but Recommended): For secure connections, especially when connecting over public networks.

Step-by-Step Guide

Step 1: Access Your Managed Database Details

Log in to the DigitalOcean Control Panel:
- Visit cloud.digitalocean.com and sign in.
Navigate to Databases:
- Click on the “Databases” option in the left-hand menu.
- Select your PostgreSQL database from the list.
View Connection Details:
- In the database’s overview page, you’ll find the “Connection Details” section.
- Note the following information:
  - Host: The hostname or IP address of the database.
  - Port: Default is 25060 for SSL connections.
  - Database Name: The default database or any specific database you’ve created.
  - User: The database user (e.g., doadmin).
  - Password: The password associated with the user.
  - SSL Mode: Typically required.

Step 2: Install a PostgreSQL Client (If Not Already Installed)

On macOS:
bash
brew install postgresql
On Ubuntu/Debian:
bash
sudo apt update
sudo apt install postgresql-client -y
On Windows:
- Download and install pgAdmin or the PostgreSQL installer from the official website.

Step 3: Download the SSL Certificate (Optional)

DigitalOcean provides a CA certificate for secure connections.
In the “Connection Security” section of your database’s page, click on “Download CA certificate”.
Save the certificate file (e.g., ca-certificate.crt) to a secure location on your machine.

Step 4: Connect Using psql Command-Line Tool

Open Your Terminal.
Use the psql Command:
bash
psql “sslmode=require host=your_host port=25060 dbname=your_db_name user=your_username password=your_password”
- Replace your_host with the host address from DigitalOcean.
- Replace your_db_name, your_username, and your_password accordingly.
Alternative Method Using Parameters:
bash
psql -h your_host -p 25060 -U your_username -d your_db_name sslmode=require
- Enter your password.
Using the SSL Certificate (If Downloaded):
bash
psql “sslrootcert=path_to_certificate sslmode=verify-full host=your_host port=25060 dbname=your_db_name user=your_username password=your_password”
- Replace path_to_certificate with the path to the downloaded ca-certificate.crt file.

Step 5: Connect Using a GUI Client

a. Using pgAdmin

Launch pgAdmin.
Create a New Server Registration:
- Right-click on “Servers” and select “Create” > “Server…”.
Configure Connection Settings:
- General Tab:
  - Name: Enter a name for the connection (e.g., “DigitalOcean PostgreSQL”).
- Connection Tab:
  - Host name/address: Enter the host provided by DigitalOcean.
  - Port: 25060
  - Maintenance database: Your database name.
  - Username: Your database user.
  - Password: Your database password.
- SSL Tab:
  - SSL mode: Select Require or Verify-CA if using the certificate.
  - Root Certificate: If using Verify-CA, provide the path to the ca-certificate.crt file.
Save and Connect.

b. Using DBeaver

Install DBeaver (if not already installed).
Create a New Connection:
- Click on “New Database Connection”.
Select PostgreSQL and click “Next”.
Enter Connection Details:
- Host: Your DigitalOcean database host.
- Port: 25060
- Database: Your database name.
- Username: Your database user.
- Password: Your database password.
Configure SSL Settings:
- Go to the “SSL” tab.
- Check “Use SSL”.
- Set SSL mode to require or verifyFull.
- If using verifyFull, provide the SSL certificate path.
Test Connection and Finish.

Step 6: Whitelist Your Connection Source (If Necessary)

By default, DigitalOcean Managed Databases require Trusted Sources for connections.
In the database’s “Overview” page, find the “Trusted Sources” section.
Click “Add Trusted Source”.
- For Your Local Machine:
  - Choose “Add Droplets” and select “Add current connection’s IP“.
- For Droplets:
  - Select the Droplets that will connect to the database.

Step 7: Test the Connection

Run a simple SQL command to verify:
sql
SELECT version();
- This should return the PostgreSQL version information.

Troubleshooting Tips

Authentication Failures:
- Double-check your username and password.
- Ensure that the user has the necessary permissions.
Connection Timeouts:
- Verify that your IP address is whitelisted in the Trusted Sources.
- Ensure there are no network issues blocking the connection.
SSL Errors:
- Make sure you’re using the correct SSL mode (require, verify-ca, or verify-full).
- If using certificates, confirm the path to the ca-certificate.crt file is correct.

Security Considerations

Use SSL Encryption: Always connect using SSL to encrypt data in transit.
Limit Trusted Sources: Only whitelist IP addresses or Droplets that need access.
Manage Database Roles and Permissions: Grant users the least privileges necessary.

Conclusion

You’ve successfully connected to your DigitalOcean PostgreSQL database! You’re now ready to perform database operations, run queries, and integrate your database with applications.

Next Steps

Set Up Database Backups: Ensure your data is backed up regularly.
Implement Connection Pooling: Use tools like PgBouncer for efficient connection management.
Optimize Performance: Monitor your database performance and adjust configurations as needed.