PSQL database transfer and errors

Question

I'm transferring a rails app to a new server, and have run into errors preventing some data from being transferred between apps with identical db schema.

If I run rake db:version on both servers, I get the same result. On my new server:

RAILS_ENV=production rake db:version # Returns 20181207224901

On my old server:

heroku run rake db:version # Returns 20181207224901

I got the data from the old server with the following command:

pg_dump --host=OMITTED_HERE_FOR_PRIVACY_SAKE --port=5432 --username=OMITTED_HERE_FOR_PRIVACY_SAKE --password --dbname=da466m517q6qf6 -t product_order_rows > pox4_product_order_rows.pg

I know this is the correct server, and have checked the contents of the pg file to make sure it gave me what I wanted, which I will omit from this post because it has too many rows.

I then try to place the dump in my new database like so:

sudo psql -U pox4 pox4_production < pox4_product_order_rows.pg

And I get the following errors:

SET
SET
SET
SET
 set_config 
------------

(1 row)

SET
SET
SET
SET
SET
ERROR:  relation "product_order_rows" already exists
ERROR:  role "wsgdzocxqkyzmj" does not exist
ERROR:  relation "product_order_rows_id_seq" already exists
ERROR:  role "wsgdzocxqkyzmj" does not exist
ALTER SEQUENCE
ALTER TABLE
ERROR:  insert or update on table "product_order_rows" violates foreign key constraint "fk_rails_7fc701b8a5"
DETAIL:  Key (model_id)=(17176) is not present in table "models".
 setval 
--------
   5021
(1 row)

ERROR:  multiple primary keys for table "product_order_rows" are not allowed
ERROR:  relation "index_product_order_rows_on_model_id" already exists
ERROR:  relation "index_product_order_rows_on_product_order_id" already exists
ERROR:  constraint "fk_rails_7fc701b8a5" for relation "product_order_rows" already exists
ERROR:  constraint "fk_rails_d38880b40c" for relation "product_order_rows" already exists

Checking the app where the data should be placed, I see none of the data that's suppose to be transferred over. I've gotten some of these errors on other table transfers, and the data somehow made it in. What I don't understand is how the data could even be in the first database in the first place if they have the same schema, or why it would be rejected in an identical database.

Answer 1

when you run heroku run rake db:version you create the schema of your database. pg_dump by default will dump the schema creation in your pox4_product_order_rows.pg what why you have an error.

You can add the --data-only option to pg_dump to dump the data without the schema. In your case:

pg_dump --host=OMITTED_HERE_FOR_PRIVACY_SAKE --port=5432 --username=OMITTED_HERE_FOR_PRIVACY_SAKE --password --dbname=da466m517q6qf6 -t product_order_rows --data-only > pox4_product_order_rows.pg

You can also use pg_restore to only restore the data:

pg_retore -U pox4 --data-only --dbname pox4_production pox4_product_order_rows.pg