How to Backup A Digital Ocean Managed Database (PostgreSQL)

Unfortunately there’s no “point-and-click” way to back up your managed Digital Ocean database. Here’s how you can do it with the pg_dump command (docs).

You’ll need to have pg_dump already installed, so I am going to assume you already have posgres installed locally. If not, install it via homebrew or with DBngin.

Once installed you’ll need to get the following information from your Digital Ocean DB, which can be found in the Connection Details section of the Database overview section in your Digital Ocean databases section:

• Host
• Port (mine was 25060, yours may be different)
• Database Name (not defaultDb most likely)
• Username
• Password

Once you have pg_dump and these values, simply run this from your command line:

pg_dump -v --no-owner -x --dbname=db_your_db_name --file="./your_db_name_dump.sql" --username=your_db_user_name --host=yourhost.some.subdomain.on.ondigitalocean.com --port=your_port_number

This will prompt you for your db password. Provide it, and press enter.

Since the -v switch is enabled you’ll see verbose detailed output.

This will output your data into a .sql file.

The --no-owner switch ensures that output commands do not set ownership of objects to match the original database. Since you’re dumping locally you wont have the same userid and password/etc likely.

The -x switch prevent dumping of access privileges (grant/revoke commands). Again, we don’t want the same access privileges that are on Digital Ocean.

Unique File Names

If you want the file name to be unique, here’s another variation that will use a current time stamp:

pg_dump -v --dbname=db_your_db_name --file="./your_db_name_$(date '+%Y_%m_%d_%H_%M_%S')_dump.sql" --username=your_db_user_name --host=yourhost.some.subdomain.on.ondigitalocean.com --port=your_port_number

This will output a file that looks like this:

your_db_name_2025_02_19_04_34_22_dump.sql

File Size Considerations

If your database is large, the resulting SQL file can be massive.

You can adjust your command by adding the -F c switch which provides “custom format” option which by default compresses the output into a binary archive file.

That command would look like this:

pg_dump -v -F c --dbname=db_your_db_name --file="./your_db_name_$(date '+%Y_%m_%d_%H_%M_%S').dump" --username=your_db_user_name --host=yourhost.some.subdomain.on.ondigitalocean.com --port=your_port_number

Restoring

If using the .sql file option, you’ll want to restore with the psql command locally (docs).

If you’re using a .dump (binary archive) you can use the pg_restore function (docs).

Here’s what my command looks like:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d mydbname_development ./latest-dump-file.dump

If you’re using Ruby on Rails, I recommend issuing the following commands before restoring (warning, this will delete all your local db data):

rails db:drop && rails db:create

Then run your restore and your db will be back in action as it is on the Digital Ocean managed instance.