Restore local .sql dump to Hatchbox

Restore .sql dump to Hatchbox

Add your SSH public key in the Hatchbox web GUI if you have not.

HWGUI: Hatchbox web GUI

~/.ssh/config example

Host *
  AddKeysToAgent yes
  IdentityFile ~/.ssh/id_ed25519

Start SSH proxy

Find SSH host in HWGUI on your database, in this example 16.171.240.56. Username should be deploy. Run the following command to start proxy local port 15432 to remote 5432 (postgres database):

ssh -L 15432:localhost:5432 deploy@16.171.240.56

Clear existing database

Find Private Connection URI in HWGUI on your database. Like this:

postgresql://username:password@host:5432/database

Use the connection string details to modify the following commands and run them on your local machine (not in ssh proxy):

# Drop database
psql postgresql://<username>:<password>@localhost:15432/postgres -c 'DROP DATABASE <database>'

# Create database
psql postgresql://<username>:<password>@localhost:15432/postgres -c 'CREATE DATABASE <database>'

You are now ready to restore from dump (in the same terminal):

psql postgresql://<connection-string-from-aboveusername>:<password>@localhost:15432/<database> -f dump.sql

Note the <database> suffix, do not restore into the postgres database

Close SSH proxy

deploy@ip-10-0-1-71: exit