2

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

if I am not mistaken, that only takes care of the dump side and not the loading into the other machine, right?
@deltaskelta yes, do you verify that you have an empty database before import?
no, that is the thing. I would like to be able to write over a database with stuff already in it. I could empty it out, but that would be a bit of a pain everytime I wanted to update my production environment
@deltaskelta in this case you can't do the import like you try to do it. Because you don't know wich rows have your production database at the moment of the import
@deltaskelta I edit your question with the last information providad by you in comments
|
0

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

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

Another answer has covered the issue with your chosen dump-and-restore solution. I'm going to cover what is the proper way to do this, because dump-and-restore does not scale.

The topics you're looking for are schema migration and data migration or simply "Database Change Management". Data and schema changes are related but separate things. A database dump and restore conflates them, which is why you're getting warnings, and will not scale.

In both cases you're updating a live database to a new standard. If you drop and recreate the tables that will lose production data. If you dump dev and try to restore that on top of existing production data, which you're doing, you'll get schema and data conflicts, which you got. Instead you need to write migration scripts to transform an existing database, kinda like patching code.

For example, let's say you have a table of countries. If you want to add a new country that is a data migration. Data migrations insert, update, and delete data. You'd write a script that inserts the new data and then run it on production.

If you wanted to add an ISO code to every country that means adding a new column. That is a schema migration. Schema migrations create, alter, and drop tables (and functions, types, etc). You'd write a script to alter the table to add the new column and run that on production. Then run a data migration to update each country with an ISO code.

That's the basic idea: scripts of mostly SQL statements which incrementally change the schema and data. There's more to it like making your migrations run in order, how to detect which migrations have been run, getting into that habit of using migrations even in dev, setting up a staging database to test migrations before risking it on production, and so on.

Many ORMs and database frameworks have their own schema and data migration systems built in. For example, Ruby On Rails. There are also stand alone migration systems such as Sqlitch.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.