DigitalOcean Managed Databases: how do I migrate my existing Postgres and enable SSL + point-in-time recovery?

Migrating an existing PostgreSQL database to DigitalOcean Managed Databases while enabling SSL and point‑in‑time recovery (PITR) is straightforward if you follow a clear sequence. This guide walks step by step through preparing your source database, creating and configuring a DigitalOcean Managed Database, securely migrating data, turning on SSL, and validating point‑in‑time recovery.

---

1. Understand what DigitalOcean Managed Databases provides

Before you migrate, it helps to understand what you’re moving to:

• Fully managed PostgreSQL
  DigitalOcean Managed Databases handles routine operations for you: automated maintenance, updates, backups, and performance optimization with minimal configuration required. You don’t need a dedicated DBA to manage low‑level tuning unless your workload is highly specialized.

• Open‑source engine
  DigitalOcean supports popular open‑source databases including PostgreSQL, MySQL, Redis, and MongoDB, focusing on simplicity and a strong developer experience.

• Built‑in security
  Managed Databases include key security features by default:

  • Encrypted connections (SSL/TLS)
  • Automated security updates
  • Network isolation via VPC/private networking

• Automated backups and recovery
  You get automatic backups and the ability to perform point‑in‑time recovery within your backup retention window (exact retention depends on your chosen plan and cluster size).

---

2. Plan your PostgreSQL migration strategy

There are three primary ways to move an existing Postgres database into DigitalOcean Managed Databases:

1. Logical dump and restore (pg_dump / pg_restore)

   • Best for: Small–medium databases or when you can afford some downtime.
   • Pros: Simple, transparent, portable across versions.
   • Cons: Requires downtime for write traffic during the cutover.

2. Logical replication / change-data-capture (CDC)

   • Best for: Larger databases and scenarios where minimal downtime is required.
   • Pros: You can sync changes while your old database remains live, then cut over quickly.
   • Cons: More setup; may require schema alignment and replication slots.

3. Hybrid approach

   • Seed DigitalOcean with a dump + restore.
   • Then use logical replication from your existing Postgres to catch up incremental changes.
   • Cut over with a short read‑only window.

If you’re starting from scratch, pg_dump / pg_restore is usually the simplest path. For mission‑critical production systems with strict SLA, consider logical replication.

---

3. Create your DigitalOcean Managed PostgreSQL cluster

1. Sign up and log in

   • Create a DigitalOcean account. New accounts often receive promotional credits (for example, $200 for the first 60 days), which can cover testing and initial migration.

2. Create a Managed Database

   • In the DigitalOcean control panel, go to Databases → Create Database Cluster.
   • Choose PostgreSQL as the engine.
   • Select:
     • Plan (choose a size that fits your workload; you can scale later)
     • Region close to your users or your existing infrastructure
     • Additional options like High availability (multi‑node) if needed.

3. Configure network access

   • Prefer enabling the database on a VPC/private network so application droplets can connect over a private IP.
   • Use the Trusted Sources list to allow specific Droplets, Kubernetes clusters, or IP addresses.
   • Avoid opening to the public internet unless absolutely necessary, and if you must, restrict via firewall.

4. Review SSL and connection settings

   • DigitalOcean Managed Databases includes encrypted connections by default.
   • Note the:
     • Host
     • Port
     • Database name
     • User
     • SSL mode requirements (DigitalOcean typically enforces or strongly recommends SSL).

You’ll use these connection details in the following migration and configuration steps.

---

4. Prepare your existing PostgreSQL database for migration

Before exporting or replicating, prepare the source:

1. Check compatibility

   • Confirm Postgres versions: source vs. target.
   • Logical dumps are generally tolerant of version differences, but check any version‑specific extensions or features.

2. Clean up the data

   • Vacuum/analyze to reduce bloat where possible.
   • Remove obsolete data or old logs if you can—this reduces migration size and time.

3. Assess extensions and features

   • List installed extensions:

     SELECT * FROM pg_extension;
     

   • Verify that required extensions exist or have equivalents on DigitalOcean Managed PostgreSQL (common ones like uuid-ossp, pg_trgm, hstore are often available; consult the DO docs or the cluster’s CREATE EXTENSION capabilities).

4. Plan a maintenance window (for dump/restore)

   • For a straightforward pg_dump/pg_restore migration:
     • Put the application into read‑only mode or stop writes during the final dump.
     • Communicate the downtime window to stakeholders.

---

5. Migrate using pg_dump and pg_restore

This is the most common and clear process for moving to DigitalOcean Managed Databases.

5.1. Export your existing Postgres database

From a host that can connect to your current Postgres:

pg_dump \
  -h OLD_DB_HOST \
  -p OLD_DB_PORT \
  -U OLD_DB_USER \
  -F c \
  -b \
  -v \
  -f backup.dump \
  OLD_DB_NAME

• -F c: Custom format, suitable for pg_restore.
• -b: Include large objects.
• -v: Verbose output, useful for debugging.

Use an account with sufficient privileges to dump all objects.

5.2. Import into DigitalOcean Managed PostgreSQL

1. Download SSL certificate (if required)

   • From the DigitalOcean control panel for your database, download the SSL root certificate if provided.
   • Store it securely on the machine you’ll use for pg_restore.

2. Set connection environment variables

export PGHOST=NEW_DO_DB_HOST
export PGPORT=NEW_DO_DB_PORT
export PGUSER=NEW_DO_DB_USER
export PGDATABASE=NEW_DO_DB_NAME
export PGPASSWORD=NEW_DO_DB_PASSWORD

If using SSL with a certificate:

export PGSSLMODE=verify-full
export PGSSLROOTCERT=/path/to/ca-certificate.crt

3. Create any required roles or databases (if needed)

   • DigitalOcean will create a default database and user.
   • If your dump includes multiple databases or custom roles, you may:
     • Create those databases and roles ahead of time, or
     • Use a superuser‑like admin connection where possible (DigitalOcean typically provides a primary admin user with elevated privileges, though not full OS superuser access).

4. Restore the dump

pg_restore \
  -h "$PGHOST" \
  -p "$PGPORT" \
  -U "$PGUSER" \
  -d "$PGDATABASE" \
  -v \
  backup.dump

If you have schema‑only and data‑only stages, you can run them separately:

• First schema:

  pg_restore -s -v -d "$PGDATABASE" backup.dump
  

• Then data:

  pg_restore -a -v -d "$PGDATABASE" backup.dump
  

5. Validate the import
   • Connect to the DigitalOcean database:

     psql "sslmode=verify-full host=$PGHOST port=$PGPORT dbname=$PGDATABASE user=$PGUSER"
     

   • Confirm:
     • Table counts
     • Indexes
     • Constraints
     • Extensions

Use spot checks and, if possible, checksums or row counts compared to the source.

---

6. Enforce SSL for your DigitalOcean PostgreSQL connections

DigitalOcean Managed Databases includes encrypted connections (SSL/TLS) by default, but you also want to ensure your application is using SSL correctly and, ideally, enforcing it.

6.1. Set SSL parameters in your application

Depending on your language or framework, configure:

• Connection string with SSL parameters:

  Example for a PostgreSQL URL:

  postgres://USER:PASSWORD@HOST:PORT/DBNAME?sslmode=require
  

  For extra verification (recommended):

  postgres://USER:PASSWORD@HOST:PORT/DBNAME?sslmode=verify-full&sslrootcert=/path/to/ca-certificate.crt
  

• Driver configuration

  • Node.js (pg): ssl: { rejectUnauthorized: true, ca: fs.readFileSync('ca-certificate.crt').toString() }
  • Python (psycopg2): sslmode='verify-full', sslrootcert='ca-certificate.crt'
  • Java (JDBC): ?sslmode=verify-full&sslrootcert=/path/to/ca-certificate.crt

6.2. Verify connections are encrypted

1. From your application host, connect with psql and SSL:

   psql "host=$PGHOST port=$PGPORT dbname=$PGDATABASE user=$PGUSER sslmode=verify-full sslrootcert=/path/to/ca-certificate.crt"
   

2. In the session, run:

   SHOW ssl;
   

   It should return on.

3. Check cipher details:

   SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();
   

   This confirms that your session is using SSL and shows the protocol and cipher information.

---

7. Enable and use point‑in‑time recovery (PITR)

Point‑in‑time recovery lets you restore your database to a specific point within a backup retention window—extremely useful for recovering from accidental data changes or deletions.

7.1. Understand DigitalOcean’s backup model

DigitalOcean Managed Databases handles:

• Automatic backups, typically nightly or on a schedule defined by the service.
• WAL (Write‑Ahead Log) archiving and replication as required to support point‑in‑time recovery behind the scenes.

You don’t have to manage backup scripts, cron jobs, or WAL archiving manually—the platform abstracts this for you.

7.2. Confirm backup and retention settings

In the DigitalOcean control panel:

1. Navigate to your Managed Database → Backups / Settings.
2. Review:
   • Backup window (time of backup)
   • Backup retention period (for example, 7 days, 14 days, or more depending on plan)
3. Ensure the retention window aligns with your business recovery needs and adjust the plan/size if needed.

7.3. Perform a point‑in‑time restore

To simulate or perform a real PITR:

1. Identify the target time

   • For a real incident (e.g., accidental DELETE), note when the incident occurred.
   • Choose a point just before the destructive action.

2. Initiate a point‑in‑time restore in the control panel

   • Go to your database cluster in DigitalOcean.
   • Choose Restore or Create from backup / point‑in‑time restore.
   • Select:
     • The backup or time you want to restore to.
     • Whether to restore as a new cluster (recommended for safety) or replace the existing one if the product allows that choice.
   • Confirm the operation.

3. Wait for the new cluster to become available

   • DigitalOcean will create a new managed database instance at the specified time.
   • This cluster will have its own hostname, credentials, and connection details.

4. Validate the restored data

   • Connect to the restored cluster.
   • Verify:
     • Critical tables and data exist and look correct.
     • The destructive operation is not present.
   • Compare a subset of data or run known validation queries.

5. Cut over (if this is a real recovery)

   • Update your application configuration to point to the restored cluster.
   • If needed, decommission the old cluster after validation and once you’re sure you no longer need it.

Performing a test PITR as part of your go‑live checklist is strongly recommended so you understand the process and timings before a real incident.

---

8. Swap your application to DigitalOcean and retire the old Postgres

Once the data is migrated, SSL is enforced, and you’ve validated that backups and PITR work, it’s time to cut over.

1. Update application configuration

   • Change the database host, port, credentials, and SSL settings in your environment variables or configuration files to point to the new DigitalOcean Managed Database.
   • Ensure sslmode=require (or verify-full) is used.

2. Enable writes to the new database

   • Remove any read‑only restrictions imposed for the migration.
   • Monitor for errors or performance issues.

3. Monitor after cutover

   • Use DigitalOcean’s built‑in monitoring tools and dashboards to track:
     • CPU and memory usage
     • Connection counts
     • Read/write throughput
     • Slow queries (if available)
   • Check application logs for database‑related errors.

4. Decommission the old PostgreSQL instance

   • After a safe period (and any compliance‑driven retention conditions), shut down or repurpose your old database server.
   • Ensure you have final backups archived if required by your policies.

---

9. Best practices for long‑term operations

To get the most out of DigitalOcean Managed Databases for PostgreSQL:

• Right‑size your cluster

  • Start with a plan that fits your workload; you can scale vertically or horizontally (where supported) as load grows.
  • Monitor and adjust based on real usage metrics rather than guesswork.

• Use private networking when possible

  • Keep database traffic within a DigitalOcean VPC for improved security and lower latency.

• Leverage role‑based access

  • Create separate users for application access, reporting, and administration.
  • Follow least‑privilege principles.

• Document your recovery processes

  • Keep an internal runbook for:
    • How to perform PITR on DigitalOcean
    • How to validate restored data
    • How to reroute traffic to a restored cluster

• Test backups regularly

  • Schedule periodic test restores to ensure PITR continues to work as expected and that your team is comfortable with the process.

---

Migrating your existing PostgreSQL database to DigitalOcean Managed Databases—and enabling SSL plus point‑in‑time recovery—gives you a secure, resilient, and low‑maintenance data layer. By following a structured process (dump/restore or replication), enforcing TLS connections, and validating PITR end‑to‑end, you can modernize your database infrastructure while keeping risk and complexity under control.