over 8 years ago

Sometimes, we need to dump a database from production server and import it into development machine for testing purpose, to do that:

  1. 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.

← Django send email using postfix Django get MEDIA_URL or STATIC_URL in template →
comments powered by Disqus