Restore dump.sql from backup to separate render instance

[jdawg]

I’m working to upgrade our environment and would like to use a pg_dump Postgres db backup file to restore to a separate Render instance - in this case our dev environment.

I’m using this command to backup:
pg_dump -d $SOURCE_RENDER_PG_EXTERNAL_CONNECTION_STRING > ${BACKUP_FILENAME}

And would like to use a similar command to restore. Something like:
psql $DESTINATION_RENDER_PG_EXTERNAL_CONNECTION_STRING -f $BACKUP_FILENAME

However, I’m running into issues of conflicting usernames, database names etc. I’m unable to figure out how to avoid this issue or drop the database prior to restoring etc.

Any help would be appreciated thanks!

Josh

[al_ps]

Hi there,

The Postgres docs have example backup/restore commands which may help, e.g.:

PGPASSWORD={PASSWORD} pg_dump -h oregon-postgres.render.com -U {DATABASE_USER} {DATABASE_NAME} -n public --no-owner > database_dump.sql

and

PGPASSWORD={PASSWORD} psql -h oregon-postgres.render.com -U {DATABASE_USER} {DATABASE_NAME} < database_dump.sql

I think the --no-owner option may help with the errors you’re seeing.

Alan

[jdawg]

Thanks so much, this is a huge help. Would I run the restore command from the shell on the running instance, or in a script during the build? If so, would I run it after the migrations? I appreciate the help.

[jdawg]

I believe I have figured it out. Here is what worked for me in case it is useful to others:

backup

mkdir -p tmp
echo -e “\n>>> creating pg_dump.sql backup”
DB_PASS=“xxx”
DB_USER=“xxx”
DB_NAME=“xxx”
DUMP_FILE=“tmp/pg_dump.sql”
HOSTNAME=“xxxxxxxx.oregon-postgres.render.com”
PGPASSWORD=“$DB_PASS” pg_dump --clean --no-privileges -h “$HOSTNAME” -U “$DB_USER” “$DB_NAME” -n public --no-owner -p 5432 > “$DUMP_FILE”

restore

echo -e “\n>>> restoring db from pg_dump.sql”
DB_PASS=“xxx”
DB_USER=“xxx”
DB_NAME=“xxx”
DUMP_FILE=“tmp/pg_dump.sql”
HOSTNAME=“xxxxxxxx.oregon-postgres.render.com”
PGPASSWORD=“$DB_PASS” psql -h “$HOSTNAME” -U “$DB_USER” “$DB_NAME” < “$DUMP_FILE”

The only thing I need to figure out is how to run this script as part of my dev build (when needed). Currently I’m using the External Connection. If anyone can recommend how I would modify the restore script to allow for the Internal database connection, that would be very much appreciated!

[al_ps]

Thanks for sharing your solution with the community.

If you’re only running this script occasionally as a one-off, maybe Jobs would help.

If you want it to run as part of a build process, you may need some other factor to conditionally check, e.g., an environment variable.

Alan