What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

Question

I have my application hosted on DigitalOcean with a PROD environment, including the production application, bucket, and PostgreSQL database.

I need to provision a DEV PostgreSQL database and restore a copy of the PROD database into it. I have currently created a new DEV database cluster within the same DigitalOcean project and attempted to restore a PROD database dump into it.

The restore process appears to complete partially, but the expected database objects are not available when validating the DEV database. During the restore, I encountered connection drops, authentication failures, SSL/connection-policy errors, and subsequent object/dependency errors.

I have already performed read-only checks on the database and validated the restore results. The PROD database has not been modified or affected.

I would like guidance on the recommended architecture and restore approach for this setup, specifically:

Is it recommended to keep the DEV database cluster within the existing PROD DigitalOcean project, or should DEV be isolated into a separate project/environment?
What database/user permissions and SSL settings should be configured for the DEV cluster before performing the restore?
What is the safest recommended process for restoring a PROD PostgreSQL dump into DEV without establishing any write path back to PROD?
What could cause pg_restore to partially complete while the resulting DEV database contains no expected tables/objects?
Are there any DigitalOcean-specific connection, networking, or PostgreSQL configuration considerations that should be verified before retrying the restore?

Goal: Establish a functional DEV PostgreSQL environment containing the required PROD database structure/data while maintaining complete isolation from the PROD database and application.

KFSys · Answer

Heya, Good instinct isolating this properly before you get it working. Going through your questions in order, and then the two things I think actually caused your failed restore. Same project or a separate one? Keep DEV as a completely separate cluster, and I’d put it in a separate DigitalOcean project too. Projects in DO are organizational rather than a hard security boundary, but separating them keeps billing, trusted-source rules, and “which connection string am I holding” clean, which is exactly what protects you from accidental cross-writes. The real isolation comes from DEV having its own cluster, its own credentials, and its own trusted sources, never shared with PROD. Permissions and SSL to set up first Two things about Managed PostgreSQL that matter here: SSL is mandatory. Managed PG rejects non-SSL connections, so your connection string needs sslmode=require. The SSL/connection errors you saw are almost certainly this. doadmin is not a superuser. A full PROD dump often contains ownership, role, and extension statements that a non-superuser cannot replay, which cascades into “dependent object not created” errors. Restore as doadmin, into a fresh database you create first, and strip ownership (see below). Safest restore path with no write path to PROD Go through a dump file, never a live connection between the two clusters. A file in the middle means there is physically no write path back to PROD. Dump from PROD (this is read-only, PROD is safe): Dump from PROD (this is read-only, PROD is safe): pg_dump -Fc “postgres://doadmin:PASS@prod-host :25060/mydb?sslmode=require” -f prod.dump Restore into DEV: pg_restore --no-owner --no-privileges -d “postgres://doadmin:PASS@dev-host :25060/mydb?sslmode=require” prod.dump –no-owner --no-privileges is the key part. It drops the PROD role/ownership references that would otherwise error out and skip your tables. Why pg_restore “partially completes” but you get no tables This is the important bit, and it’s almost always one of these two: Format mismatch. pg_restore only works with custom (-Fc), directory, or tar dumps. If your PROD dump is a plain .sql file, pg_restore won’t load it properly and you get a near-empty result. For a plain SQL dump you must use psql -f dump.sql instead. So first check: is your dump -Fc or plain SQL? The connection dropped mid-restore. Your auth/SSL/connection errors mean the restore aborted partway, leaving a handful of objects and none of the rest. Fixing SSL and trusted sources (below) usually clears this. Also double check you’re validating the right database and schema. It’s easy to restore into mydb but inspect defaultdb, or check as a read-only user that can’t see the schema, and conclude “nothing’s there.” DO-specific things to verify before you retry Trusted Sources. Add the machine you’re restoring from to the DEV cluster’s trusted sources, or connections get dropped. This alone explains a lot of “connection drops.” Use the direct port (25060), not the connection pool. Managed PG offers a connection pool on a different port, and PgBouncer in transaction mode breaks pg_restore. Always restore against the direct database port. Watch connection limits. Smaller plans allow few connections, so avoid parallel restore (-j) on small DEV clusters, since it opens many connections and can trip drops. Run it from a stable host, ideally a Droplet in the same region/VPC as the cluster. Restoring a large dump over a flaky laptop connection is a common cause of mid-way drops. Version check. Use a pg_dump at least as new as your server, and restore into the same or a newer PostgreSQL major version. The quick retry recipe Confirm your dump is -Fc (if it’s plain .sql, switch to psql -f). Add your restore machine to DEV’s Trusted Sources. Restore against port 25060 with sslmode=require, using --no-owner --no-privileges. Validate with \dt inside the correct database as doadmin. That combination fixes the vast majority of these. My bet is your case is the format mismatch and/or the connection pool port, so check those two first. Hope that gets your DEV environment stood up cleanly.

alexdo · Answer

Heya, KFSys’s answer above covers the format mismatch and connection-drop causes thoroughly, and --no-owner --no-privileges is exactly right for the ownership problem. One more concrete cause worth checking if you’re still seeing partial restores after fixing those two: PostgreSQL extensions. DigitalOcean Managed PostgreSQL only allows a specific set of extensions, and a fresh DEV cluster starts with none of them enabled beyond the defaults. If your PROD dump includes CREATE EXTENSION statements for something like postgis, uuid-ossp, pgcrypto, or vector, and that extension isn’t already enabled on DEV, that statement fails, and pg_restore then cascade-fails every object that depends on it, custom types, indexes using extension-provided operators, columns using an extension type. This produces exactly your symptom: restore appears to complete but expected objects are missing. --no-owner and --no-privileges don’t fix this, since it’s a separate issue from ownership. Before retrying, check what extensions PROD actually uses: grep -i "CREATE EXTENSION" prod.dump (or for a custom-format dump, pg_restore -l prod.dump | grep -i extension), then enable each supported one on DEV first as doadmin: CREATE EXTENSION IF NOT EXISTS postgis; before running the restore. DigitalOcean’s supported extensions list covers the common ones (postgis, uuid-ossp, pgcrypto, pg_stat_statements, vector, and 50+ others on Standard clusters), but not everything in stock PostgreSQL, worth checking your specific ones are on that list before assuming this is your issue. Regards

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

Related questions