Sometimes, we need to dump a database from production server and import it into development machine for testing purpose, to do that:
- use the following command to dump the database first:
su - postgres
pg_dump database_name > file_name
For instance, if I have a database called note, I would dump it as (this is done in shell not in psql):
pg_dump note > note.db.bak
Before importing note.db.bak into your local postgres, a few things need to be taken care of first:
1) You have to have the same list of users on your local machine as on your server. Use \du inside psql to list users on your server. Then, recreate them on your local machine in psql
create user user_name;
Users created on your local machine do not have to mirror the same privileges they have on the server.
2) Create the database that you want to import the backup into. In psql,
create database note_server_bak;
Now, you can import the data with the following command in shell:
psql -1 note_server_bak < note.db.bak
Note that the option after psql is 'dash one' not 'dash L'. This option means while importing, everything will be wrapped in a transaction so that if the import fails, nothing will be stored in note_server_bak, in other words, note_server_bak stays clean.
If you don't use -1 and an error occurs during importing, all of the data before the error will be imported into the database, which leaves your database with incomplete data. When you import the next time, you are going to see even more errors because of this.
