sqldatabasepostgresqlpg-dumpdatabase-dump

Proper way to migrate a postgres database?


I have a dev version and a production version running in django.

I recently started populating it with a lot of data and found that the django loaddata tries to load everything into memory before adding it into the db and my files will be too big for that.

What is the proper way to push my data from my dev machine to my production?

I did...

pg_dump -U user -W db ./filename.sql

and then on the production server I did...

psql dbname < filename.sql

It seems like it worked, all the data is there, but it came up with some errors such as

relation xxx already exists
constrain xxx for relation xxx already exists

and there were quite a few of them, but like I said everything appears to be there. Is this the right way to do it?

Edit: I have in the production machine the database with information and I don't want truncate the tables before import.


Solution

  • This is the script that I use:

     pg_dump -d DATABASE_NAME -U postgres --format plain --inserts > /FILE.sql
    

    Edit: As you says in comments that you don't want truncate the tables before import, you can't do this type of import into your production database. I suggest empty your production database before import the dev database dump.