Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Unanswered: restore only data to an existing schema

    Hi folks,

    I want to restore only data to my database[contains only schema] from dump.sql.

    i tried this command "psql -U postgres -h localhost -a mydb < dump.sql".

    but it is throwing error for all existing table in my database.

    ALTER TABLE ONLY users
    ADD CONSTRAINT fkfc6ecb094a74de9a FOREIGN KEY (address_fk) REFERENCES address_users(id);
    ERROR: constraint "fkfc6ecb094a74de9a" for relation "users" already exists

    Could you please help me on this?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You need to create the dump using pg_dump --data-only

  3. #3
    Join Date
    Oct 2009
    Posts
    5
    Hi shammat,

    I tried this. Now the problem is, "users table has reference in visa_details". so it is trying to insert record with reference userid 3361. users table is empty now. so i am getting error like

    COPY visa_details (id, country, visa_till_date, visa_type, userid) FROM stdin;
    ERROR: insert or update on table "visa_details" violates foreign key constraint "fk72040444c2059795"
    DETAIL: Key (userid)=(3361) is not present in table "users".

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Are you sure the source database has the same foreign key constraints as the target database? pg_dump writes the data in the correct order so it should work.

    You can verify if the order of the tables is correct by looking at the dump file and check if the users table is listed before the visa_details table.

    If the order in the dump file is indeed wrong then the only workaround would be to disable all constraints and re-enable them after the import

  5. #5
    Join Date
    Oct 2009
    Posts
    5
    yes source database has the same foreign key as target database.

    pg_dump writes the data in ascending order[table name].

    How do we disable all constraints and re-enables after import?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by mjpslm
    pg_dump writes the data in ascending order[table name]
    Ah, right.
    I forgot that this is a limitation of the --data-only parameter (I just tried it and saw the warning pg_dump shows on the command line. Error messages are useful)

    Why don't you just use the --clean parameter?
    That will drop all objects that exist in the target and the import everything. Most probably the easiest solution if you want an exact copy of the source

  7. #7
    Join Date
    Oct 2009
    Posts
    5
    Ok,

    In my case --clean wont help me.

    scenario : some time we change the database structure in entity level. Those changes will be updated when we start app server. Then we need to import only data.

    Details:
    step 1: i have to take dump. it can be with schema or with out.
    step 2: drop database.
    step 3: when start my app server, it will create database schema.
    step 4: i need to import only data.

    I remember, long back i used extra parameter in psql command itself to ignore constrains. Now i forgot that parameter name.

    So i need to disable the constraints when import data.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If the schema of the target is different to the schema of the database where you took the dump, how can you import the data?

    To "disable" constraints you need to drop them.

  9. #9
    Join Date
    Oct 2009
    Posts
    5
    target schema have more columns than source scheme. we are not going to change existing columns rather than add more columns

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    About how many tables are we talking about?

    What you could do is to use the native format. Before importing create a listing file (using pg_restore --list). In the listing file you can remove the table creation and re-order the tables according to their dependencies.
    That part can probably be automated using commandline tools like grep and sed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •