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:
For instance, if I have a database called note, I would dump it as (this is done in shell not in psql):
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
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,
Now, you can import the data with the following command in shell:
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.