Restoring from pg_dump with foreign key constraints












5















In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:



ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL: Key (channel_id)=(1) is not present in table "channels".


Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.



caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...



version: PostgreSQL 9.4.4.



How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?










share|improve this question




















  • 1





    pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:04











  • Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql

    – Jerome
    Oct 15 '15 at 10:18






  • 3





    I'm not sure about the default settings of pg_dump and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring with pg_restore with the --clean option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:28








  • 2





    @ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.

    – dezso
    Oct 15 '15 at 10:38








  • 2





    Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.

    – Craig Ringer
    Oct 15 '15 at 11:28


















5















In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:



ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL: Key (channel_id)=(1) is not present in table "channels".


Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.



caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...



version: PostgreSQL 9.4.4.



How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?










share|improve this question




















  • 1





    pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:04











  • Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql

    – Jerome
    Oct 15 '15 at 10:18






  • 3





    I'm not sure about the default settings of pg_dump and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring with pg_restore with the --clean option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:28








  • 2





    @ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.

    – dezso
    Oct 15 '15 at 10:38








  • 2





    Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.

    – Craig Ringer
    Oct 15 '15 at 11:28
















5












5








5


2






In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:



ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL: Key (channel_id)=(1) is not present in table "channels".


Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.



caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...



version: PostgreSQL 9.4.4.



How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?










share|improve this question
















In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:



ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL: Key (channel_id)=(1) is not present in table "channels".


Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.



caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...



version: PostgreSQL 9.4.4.



How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?







postgresql restore foreign-key postgresql-9.4 pg-dump






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Oct 15 '15 at 10:36









dezso

22.1k116096




22.1k116096










asked Oct 15 '15 at 9:46









JeromeJerome

14219




14219








  • 1





    pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:04











  • Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql

    – Jerome
    Oct 15 '15 at 10:18






  • 3





    I'm not sure about the default settings of pg_dump and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring with pg_restore with the --clean option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:28








  • 2





    @ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.

    – dezso
    Oct 15 '15 at 10:38








  • 2





    Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.

    – Craig Ringer
    Oct 15 '15 at 11:28
















  • 1





    pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:04











  • Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql

    – Jerome
    Oct 15 '15 at 10:18






  • 3





    I'm not sure about the default settings of pg_dump and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring with pg_restore with the --clean option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.

    – ypercubeᵀᴹ
    Oct 15 '15 at 10:28








  • 2





    @ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.

    – dezso
    Oct 15 '15 at 10:38








  • 2





    Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.

    – Craig Ringer
    Oct 15 '15 at 11:28










1




1





pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?

– ypercubeᵀᴹ
Oct 15 '15 at 10:04





pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?

– ypercubeᵀᴹ
Oct 15 '15 at 10:04













Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql

– Jerome
Oct 15 '15 at 10:18





Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql

– Jerome
Oct 15 '15 at 10:18




3




3





I'm not sure about the default settings of pg_dump and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring with pg_restore with the --clean option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.

– ypercubeᵀᴹ
Oct 15 '15 at 10:28







I'm not sure about the default settings of pg_dump and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring with pg_restore with the --clean option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.

– ypercubeᵀᴹ
Oct 15 '15 at 10:28






2




2





@ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.

– dezso
Oct 15 '15 at 10:38







@ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.

– dezso
Oct 15 '15 at 10:38






2




2





Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.

– Craig Ringer
Oct 15 '15 at 11:28







Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.

– Craig Ringer
Oct 15 '15 at 11:28












2 Answers
2






active

oldest

votes


















1














You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.



But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.



See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.






share|improve this answer





















  • 4





    Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

    – Colin 't Hart
    May 24 '18 at 8:35






  • 1





    Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

    – achimh
    May 25 '18 at 9:20





















0














Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.






share|improve this answer








New contributor




Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f118117%2frestoring-from-pg-dump-with-foreign-key-constraints%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.



    But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.



    See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.






    share|improve this answer





















    • 4





      Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

      – Colin 't Hart
      May 24 '18 at 8:35






    • 1





      Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

      – achimh
      May 25 '18 at 9:20


















    1














    You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.



    But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.



    See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.






    share|improve this answer





















    • 4





      Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

      – Colin 't Hart
      May 24 '18 at 8:35






    • 1





      Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

      – achimh
      May 25 '18 at 9:20
















    1












    1








    1







    You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.



    But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.



    See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.






    share|improve this answer















    You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.



    But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.



    See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Jun 20 '18 at 13:04









    dezso

    22.1k116096




    22.1k116096










    answered May 24 '18 at 7:36









    achimhachimh

    1113




    1113








    • 4





      Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

      – Colin 't Hart
      May 24 '18 at 8:35






    • 1





      Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

      – achimh
      May 25 '18 at 9:20
















    • 4





      Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

      – Colin 't Hart
      May 24 '18 at 8:35






    • 1





      Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

      – achimh
      May 25 '18 at 9:20










    4




    4





    Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

    – Colin 't Hart
    May 24 '18 at 8:35





    Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.

    – Colin 't Hart
    May 24 '18 at 8:35




    1




    1





    Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

    – achimh
    May 25 '18 at 9:20







    Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.

    – achimh
    May 25 '18 at 9:20















    0














    Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.






    share|improve this answer








    New contributor




    Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.

























      0














      Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.






      share|improve this answer








      New contributor




      Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.























        0












        0








        0







        Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.






        share|improve this answer








        New contributor




        Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.










        Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.







        share|improve this answer








        New contributor




        Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        share|improve this answer



        share|improve this answer






        New contributor




        Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        answered 12 mins ago









        Suman AstaniSuman Astani

        1011




        1011




        New contributor




        Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.





        New contributor





        Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






        Suman Astani is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f118117%2frestoring-from-pg-dump-with-foreign-key-constraints%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh