1

I have two apps, with same tables. One of app collecting data from web. I want to send the datas to my second(web app)'s app database.

With the code below, I have created the file with datas:

pg_dump -U username -t public."table_name" -d database name --inserts > table_name.sql

The problem is that I just want to insert data's which does not exist in second database.

If I try the code below, I get a lot of already exists errors:

psql -U username second_database_name < table_name.sql

One of error:

multiple primary keys for table "table_name" are not allowed

Another one:

relation "table_name_attribute_442....c74_uniq" already exists

--clean , --if-exists ... What should I do?

Samican
  • 11
  • 3
  • I believe you try to recreate the tables. If you already have the tables, edit the postgresql dump to delete the "create table" stuff and keep the "insert into" stuff. – Cătălin Matei Nov 30 '15 at 09:18
  • Thanks a lot, it worked and I understand what was wrong.. – Samican Nov 30 '15 at 12:22
  • I'm glad. Remember to back up :}. – Cătălin Matei Nov 30 '15 at 12:25
  • Sir, I have another question now. What if table names are same and app names are different. In sql file which I created, the commands start with: "INSERT INTO appname1_table name VALUES".. I try to insert these datas to appname2_table. There is a confliction I couldn't solve. – Samican Dec 01 '15 at 17:23
  • 1
    I have solved the problem. Just edited dump file, changed appname1_table to appname2_table. So easy... – Samican Dec 01 '15 at 17:44

1 Answers1

0

The way I did it, was to do a pg_dump that creates a compressed archive suitable for use with pg_restore, which has the needed flags to allow the data to be imported without throwing errors.

For example: 

pg_dump -Fc -h 127.0.0.1 {db_name_here} > {dump_file_name_here}

The "-Fc" gets you the file-type that pg_restore wants; it will reject a dump made without those magic letters.

Now you can restore the file with:

pg_restore -O -h 127.0.0.1 --clean --disable-triggers -d {target_db_name} {dump_file_name_here}

Voila - the data is now in the target_db.

If the 'psql' command has the equivalent flags, I don't know what they are, and did not find them in SO searching. But, hopefully, this provides a DB dump/restore that 'just works' for those who need to get back to using the DB, instead of fiddling with trying to get a simple dump/restore to happen with the expected behavior.

Also note, if you do not specify ...

-h 127.0.0.1

... it will go looking for a unix-socket, which may or may not be configured correctly. Chances are, your use-case for the DB addresses the db that way, so you never manually-configured the unix-socket to match whatever the command defaults to trying to find, which is different than how setup sets the default socket (of course, because things "just working," so you can "just use it" is just not possible).

JosephK
  • 668
  • 10
  • 18