Restoring from pg_dump with foreign key constraints
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
edited Oct 15 '15 at 10:36
dezso
22.1k116096
asked Oct 15 '15 at 9:46
JeromeJerome
14219
|
show 1 more comment
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
edited Oct 15 '15 at 10:36
dezso
22.1k116096
asked Oct 15 '15 at 9:46
JeromeJerome
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 aspg_dump app_environment > /archive/yymmdd.sqlthen on restorepsql app_environment < /archive/yymmdd.sql
– Jerome
Oct 15 '15 at 10:18
3
I'm not sure about the default settings ofpg_dumpand 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 withpg_restorewith the--cleanoption (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 -Fcandpg_restoreare almost always the way to go in preference to using SQL dumps andpsql. That way you can specify things like--cleanat load-time.
– Craig Ringer
Oct 15 '15 at 11:28
|
show 1 more comment
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
edited Oct 15 '15 at 10:36
dezso
22.1k116096
asked Oct 15 '15 at 9:46
JeromeJerome
14219
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
postgresql restore foreign-key postgresql-9.4 pg-dump
edited Oct 15 '15 at 10:36
dezso
22.1k116096
asked Oct 15 '15 at 9:46
JeromeJerome
14219
edited Oct 15 '15 at 10:36
dezso
22.1k116096
asked Oct 15 '15 at 9:46
JeromeJerome
14219
edited Oct 15 '15 at 10:36
dezso
22.1k116096
edited Oct 15 '15 at 10:36
dezso
22.1k116096
edited Oct 15 '15 at 10:36
dezso
22.1k116096
22.1k116096
asked Oct 15 '15 at 9:46
JeromeJerome
14219
asked Oct 15 '15 at 9:46
JeromeJerome
14219
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 aspg_dump app_environment > /archive/yymmdd.sqlthen on restorepsql app_environment < /archive/yymmdd.sql
– Jerome
Oct 15 '15 at 10:18
3
I'm not sure about the default settings ofpg_dumpand 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 withpg_restorewith the--cleanoption (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 -Fcandpg_restoreare almost always the way to go in preference to using SQL dumps andpsql. That way you can specify things like--cleanat load-time.
– Craig Ringer
Oct 15 '15 at 11:28
|
show 1 more comment
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 aspg_dump app_environment > /archive/yymmdd.sqlthen on restorepsql app_environment < /archive/yymmdd.sql
– Jerome
Oct 15 '15 at 10:18
3
I'm not sure about the default settings ofpg_dumpand 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 withpg_restorewith the--cleanoption (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 -Fcandpg_restoreare almost always the way to go in preference to using SQL dumps andpsql. That way you can specify things like--cleanat 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
|
show 1 more comment
2 Answers
2
active
oldest
votes
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.
edited Jun 20 '18 at 13:04
dezso
22.1k116096
answered May 24 '18 at 7:36
achimhachimh
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
add a comment |
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.
answered 17 mins ago
Suman AstaniSuman Astani
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
edited Jun 20 '18 at 13:04
dezso
22.1k116096
answered May 24 '18 at 7:36
achimhachimh
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
add a comment |
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.
edited Jun 20 '18 at 13:04
dezso
22.1k116096
answered May 24 '18 at 7:36
achimhachimh
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
add a comment |
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.
edited Jun 20 '18 at 13:04
dezso
22.1k116096
answered May 24 '18 at 7:36
achimhachimh
1113
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.
edited Jun 20 '18 at 13:04
dezso
22.1k116096
answered May 24 '18 at 7:36
achimhachimh
1113
edited Jun 20 '18 at 13:04
dezso
22.1k116096
edited Jun 20 '18 at 13:04
dezso
22.1k116096
edited Jun 20 '18 at 13:04
dezso
22.1k116096
22.1k116096
answered May 24 '18 at 7:36
achimhachimh
1113
answered May 24 '18 at 7:36
achimhachimh
1113
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
add a comment |
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
add a comment |
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.
answered 17 mins ago
Suman AstaniSuman Astani
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.
add a comment |
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.
answered 17 mins ago
Suman AstaniSuman Astani
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.
add a comment |
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.
answered 17 mins ago
Suman AstaniSuman Astani
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.
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.
answered 17 mins ago
Suman AstaniSuman Astani
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.
answered 17 mins ago
Suman AstaniSuman Astani
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.
answered 17 mins ago
Suman AstaniSuman Astani
1011
answered 17 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.
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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.sqlthen on restorepsql app_environment < /archive/yymmdd.sql– Jerome
Oct 15 '15 at 10:18
3
I'm not sure about the default settings of
pg_dumpand 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 withpg_restorewith the--cleanoption (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 -Fcandpg_restoreare almost always the way to go in preference to using SQL dumps andpsql. That way you can specify things like--cleanat load-time.– Craig Ringer
Oct 15 '15 at 11:28