Unlock seamless database management: explore the step-by-step process of dumping and restoring a postgresql database. learn the essential skills to efficiently handle your data, ensuring a smooth and secure management experience. dive into our comprehensive guide for expert insights on postgresql database operations
Mastering PostgreSQL Database Management: A Comprehensive Guide to Dumping and Restoring Data
When dealing with production or staging environments, discrepancies between your localhost database setup and the live environment can lead to app crashes. A simple method to diagnose if the database is the culprit involves restoring a dump from the staging or production environment onto your localhost.
Dump Your PostgreSQL Database
Step 1
SSH to the staging/production server.
Step 2
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.
Step 3
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.
Example:
scp marcin@8.8.8.8:/home/my_app/backups/my_app_database_20160527.sql my_app_database_20160527.sql
Restore Your PostgreSQL Dump
Step 1
If you want to use the current localhost database, you must drop it first:
psql template1 -c 'drop database database_name;'
Step 2
Create a new database on the localhost:
psql template1 -c 'create database database_name with owner your_user_name;'
Step 3
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.