Oftentimes, when working with the production/staging environments, you may encounter differences with your localhost database setup causing crashes in your app.
Dump Your PostgreSQL Database
SSH to the staging/production server.
Dump the desired database:
pg_dump database_name > database_name_20160527.sql
You can name your dump as you wish - I'm using dates to distinguish multiple dumps.
Leave SSH and download your new SQL file using SCP.
scp login@host:path_to_dir_with/database_name_20160527.sql database_name_20160527.sql
This command logs you into your remote server using SSH and downloads a given file to the local directory specified by you. If you give no path to the local directory, the dump will be saved in your current working dir.
scp firstname.lastname@example.org:/home/my_app/backups/my_app_database_20160527.sql my_app_database_20160527.sql
Restore Your PostgreSQL Dump
Step 1If you want to use the current localhost database, you must drop it first:
psql template1 -c 'drop database database_name;'
Create a new database on the localhost:
psql template1 -c 'create database database_name with owner your_user_name;
And write your dump into the database:
psql database_name < database_name_20160527.sql
That's all! You now have the exact copy of production database available on your machine.
More posts by this author