How to dump a Render database to local?

1

When I used Heroku, I had a script dump_prod_to_local.sh which I ran from my local docker DB container. Basically it consisted on:

  1. Capturing a new prod backup using the CLI
  2. Downloading this prod backup using the CLI
  3. Dropping the current database and running pg_restore command.

Since there is no CLI for now in Render, I’m wondering what’s the best way to do it.

I want to avoid manual steps like triggering a capture and downloading the file from the dashboard, then copying the backup file to my Docker, etc.

Thanks.

2

Hi David,

Thanks for reaching out.

Heroku’s “PGBackups”, is just a wrapper for pg_dump source_. _

So an alternative, for steps 1 & 2 could simply be a local pg_dump command run against the external connection string of your Render Postgres instance. As a very basic example, something like:

$ pg_dump -d {RENDER_PG_EXTERNAL_CONNECTION_STRING} > my_db.dump

You’d need to investigate if there are any other pg_dump options you’d want to use.

You could then pg_restore it to your local DB.

However, there would likely be other considerations, for example if your DB is heavy utilised, very large or configured to not be accessible externally.

  • pg_dump might affect performance if the DB is heavily utilised and/or has large datasets. A workaround if this scenario is a concern may be to provision a read-replica and use that to take the backups.

  • Using pg_dump to a remote database would also require the database to exposed externally (although, this could be restricted to known/trusted IPs). If you wanted to keep the DB private, a workaround to this may be to use Jobs, to run a script on Render to pg_dump, compress and upload the backup to a file store such AWS S3 for you to retrieve.

Hope that helps

Kind regards

Alan

4 Likes
3

Thanks a lot for detailed informations. For those interested, here is the updated script for my render project. I’m running it in a PostgreSQL Docker container used for my local developments.

#!/bin/sh
set -e

# Config ---------------------------------------------------------------------
BACKUPS_PATH=/root/backups
BACKUP_FILENAME=dump.sql
RENDER_PG_EXTERNAL_CONNECTION_STRING=postgres://dbadmin:mypassword@frankfurt-postgres.render.com/mydb
# ----------------------------------------------------------------------------

echo "Removing potential existing backups..."
if [ -d "$BACKUPS_PATH" ]; then rm -Rf $BACKUPS_PATH; fi

echo "Creating a folder to handle backups"
mkdir $BACKUPS_PATH && cd $BACKUPS_PATH

echo "Backuping remote render db..."
pg_dump -d $RENDER_PG_EXTERNAL_CONNECTION_STRING > ${BACKUP_FILENAME}

echo "Dropping local db..."
dropdb $POSTGRES_DB

echo "Recreating local db..."
createdb $POSTGRES_DB --owner=$POSTGRES_USER

echo "Restoring local db from backup..."
psql -d $POSTGRES_DB -f $BACKUP_FILENAME

POSTGRES_DB and POSTGRES_USER are env var containing my local database infos.

2 Likes