Thread: pg_restore - selective restore use cases. HINT use DROP CASCADE
I have needs to do selective schema or table restorations and the pg_restore
utility seems to have hooks for this, yet seems deficient for this type of problem.
It appears that I have to develop a custom script to do what I think would
be a standard kind of restorations.
---
Given: Postgres 9.3 and I have dumped my database via:
pg_dump –Fc –U <superuser> my_db –f archive_file
I have no problem doing a total restoration
e.g.
pg_restore –c –U <supeuser> -d my_db archive_file
Assuming I have not had a catastrophic error but merely wish to address schemas
or tables used for decision making that have been altered by users into sub-optimal
condition, then if I attempt to do selective restorations of a schema or tables in the database:
( e.g. pg_restore –c -n <some_schema> -U <superuser> -d my_db archive_file )
I encounter restoration problems over dependencies with suggestions/Hints:
“ HINT: Use DROP ... CASCADE to drop the dependent objects too.”
So it appears that I now have to develop a custom script to do what I think would
be a standard kind of restoration.
I would think that there ought to be an some additional options to pg_restore.
i.e an option that turns a DROP into a DROP with CASCADE and or DISABLES constraint checking while the schema/table is being restored.
In addition I would think that with “-a” , data only option, there ought to be an assistive option that allows for the table to truncated/cleaned so that
the generated COPY commands do not append to the table resulting in PRIMARY KEY violations.
In any event I have not found a straight forward way of using pg_restore to do selective restorations or have found some ways of doing certain tables but had to remove Foreign Keys to make it work which seems like a poor bargain. I think I know how to customize the output to do the task, it just seems that pg_restore should be
able to do this without my additional efforts.
I am hopeful that there might be some instructive thoughts on selective restorations that have not occurred to me.
( options that I have played with: -n –t –section=data –a –c –disable-triggers -1 )
Regards
Dave Day
On 01/09/2014 08:51 AM, Day, David wrote: > I have needs to do selective schema or table restorations and the pg_restore > > utility seems to have hooks for this, yet seems deficient for this type > of problem. > > It appears that I have to develop a custom script to do what I think would > > be a standard kind of restorations. > > --- > > Given: Postgres 9.3 and I have dumped my database via: > > pg_dump –Fc –U <superuser> my_db –f archive_file > > I have no problem doing a total restoration > > e.g. > > pg_restore –c –U <supeuser> -d my_db archive_file > > Assuming I have not had a catastrophic error but merely wish to address > schemas > > or tables used for decision making that have been altered by users into > sub-optimal > > condition, then if I attempt to do selective restorations of a schema > or tables in the database: > > ( e.g. pg_restore –c -n <some_schema> -U <superuser> -d my_db > archive_file ) > > I encounter restoration problems over dependencies with suggestions/Hints: > > “ HINT: Use DROP ... CASCADE to drop the dependent objects too.” Well for what it is worth, that is documented. http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html "Note: When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database." > > So it appears that I now have to develop a custom script to do what I > think would > > be a standard kind of restoration. Well I think that is the issue that it is not really standard or at least easy. The possible combinations of schemas, dependencies across schemas, ownership across objects makes this complicated to do on a selective basis. Furthermore when you are using -n using are asking for a specific schema, it sort of breaks the contract to drag in objects from other schemas. > > I would think that there ought to be an some additional options to > pg_restore. > > i.e an option that turns a DROP into a DROP with CASCADE and or > DISABLES constraint checking while the schema/table is being restored. So you want to start dropping objects outside the schema you are restoring, seems like a foot gun to me. > > In addition I would think that with “-a” , data only option, there ought > to be an assistive option that allows for the table to > truncated/cleaned so that Probably because it is relatively easy to roll your own solution to this. > > the generated COPY commands do not append to the table resulting in > PRIMARY KEY violations. > > In any event I have not found a straight forward way of using pg_restore > to do selective restorations or have found some ways of doing certain > tables but had to remove Foreign Keys to make it work which seems like a > poor bargain. I think I know how to customize the output to do the task, > it just seems that pg_restore should be > > able to do this without my additional efforts. In the foreseeable future that is how you will need to handle it. Where the choice is either roll your own script or use one of the existing migration solutions, e.g Alembic, Sqitch, etc > > I am hopeful that there might be some instructive thoughts on selective > restorations that have not occurred to me. > > ( options that I have played with: -n –t –section=data –a –c > –disable-triggers -1 ) > > Regards > > Dave Day > -- Adrian Klaver adrian.klaver@gmail.com
Adrian, Thank you for your response. I would note that the original dump archive created by pg_dump included all schemas and that I only intend to restore a schema from it that is self contained, or a group of related tables from it. I acknowledge the dangers inherent in selective restoration, it just seems that a couple of additional options ( disable constraints, drop cascade ) to pg_restore would improve this utility to users who have put some thought into laying out the database design and failure cases from which they would like to recover. To have a pg_restore selective restoration options, (-n, -t ), and have it fail simply because there are foreign keys amongst the tables within that schema seems like to much protection or protection that I would at least like to have option to over-ride. It may well be that I could shoot myself in the foot, but I'd still like to own the firearm :+) Regards Dave Day -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Thursday, January 09, 2014 3:47 PM To: Day, David; pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE On 01/09/2014 08:51 AM, Day, David wrote: > I have needs to do selective schema or table restorations and the > pg_restore > > utility seems to have hooks for this, yet seems deficient for this > type of problem. > > It appears that I have to develop a custom script to do what I think > would > > be a standard kind of restorations. > > --- > > Given: Postgres 9.3 and I have dumped my database via: > > pg_dump -Fc -U <superuser> my_db -f archive_file > > I have no problem doing a total restoration > > e.g. > > pg_restore -c -U <supeuser> -d my_db archive_file > > Assuming I have not had a catastrophic error but merely wish to > address schemas > > or tables used for decision making that have been altered by users > into sub-optimal > > condition, then if I attempt to do selective restorations of a schema > or tables in the database: > > ( e.g. pg_restore -c -n <some_schema> -U <superuser> -d my_db > archive_file ) > > I encounter restoration problems over dependencies with suggestions/Hints: > > " HINT: Use DROP ... CASCADE to drop the dependent objects too." Well for what it is worth, that is documented. http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html "Note: When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) mightdepend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves intoa clean database." > > So it appears that I now have to develop a custom script to do what I > think would > > be a standard kind of restoration. Well I think that is the issue that it is not really standard or at least easy. The possible combinations of schemas, dependencies across schemas, ownership across objects makes this complicated to do on a selective basis. Furthermore when you are using -n using are asking for a specific schema, it sort of breaks the contract to drag in objects from other schemas. > > I would think that there ought to be an some additional options to > pg_restore. > > i.e an option that turns a DROP into a DROP with CASCADE and or > DISABLES constraint checking while the schema/table is being restored. So you want to start dropping objects outside the schema you are restoring, seems like a foot gun to me. > > In addition I would think that with "-a" , data only option, there ought > to be an assistive option that allows for the table to > truncated/cleaned so that Probably because it is relatively easy to roll your own solution to this. > > the generated COPY commands do not append to the table resulting in > PRIMARY KEY violations. > > In any event I have not found a straight forward way of using pg_restore > to do selective restorations or have found some ways of doing certain > tables but had to remove Foreign Keys to make it work which seems like a > poor bargain. I think I know how to customize the output to do the task, > it just seems that pg_restore should be > > able to do this without my additional efforts. In the foreseeable future that is how you will need to handle it. Where the choice is either roll your own script or use one of the existing migration solutions, e.g Alembic, Sqitch, etc > > I am hopeful that there might be some instructive thoughts on selective > restorations that have not occurred to me. > > ( options that I have played with: -n -t -section=data -a -c > -disable-triggers -1 ) > > Regards > > Dave Day > -- Adrian Klaver adrian.klaver@gmail.com
On 01/09/2014 01:51 PM, Day, David wrote: > Adrian, > > Thank you for your response. > > I would note that the original dump archive created by pg_dump included all schemas and that I only intend > to restore a schema from it that is self contained, or a group of related tables from it. I just tried that here and succeeded. I did a pg_dump and then restored only the public schema which in this database is self contained. I did get the HINT because I used the -c switch and it tried to drop the public schema and there where existing objects dependent on it. The restore threw the HINT and a subsequent ERROR over trying to CREATE SCHEMA public where it already existed, but it completed the restore. > > I acknowledge the dangers inherent in selective restoration, it just seems > that a couple of additional options ( disable constraints, drop cascade ) to pg_restore > would improve this utility to users who have put some thought into laying out the > database design and failure cases from which they would like to recover. > > To have a pg_restore selective restoration options, (-n, -t ), and have it fail simply > because there are foreign keys amongst the tables within that schema seems like > to much protection or protection that I would at least like to have option to over-ride. We will probably need to see more detail on why that failed in your case because I did not see that in mine. Another way to influence the outcome is to use the -l and -L options to pg_restore. -l returns the -Fc dump file table of contents(TOC) as a list. You can redirect that to a file and in that file comment out(using ;) items and rearrange the order of the TOC to suit your needs. Then you use pg_restore with the -L option to feed it the edited TOC. http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html > > It may well be that I could shoot myself in the foot, but I'd still like to own the firearm :+) > > > Regards > > > Dave Day > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian. Based on your earlier remarks and further investigation I find that the restoration of a schema ( -n ) goes smoothly if thereare no foreign key References to the tables being restored from a schema that is not part of the restoration. I had a couple of those thatI had not initially appreciated and was able to redesign to accommodate that. Similarly if using the -t table restoration option of tables within a schema, one has to include all tables that have aforeign key reference to the table(s) being restored. I have to rethink some layout based on this but at least I understandthis all now. I still think a drop cascade or defer constraints options might be useful. I can probably pipe the pg_restore output toa perl script that could "tweak" the pg_restore output and in turn pipe that to psql if I really need this capability. Thanks again for your assistance. Dave -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Thursday, January 09, 2014 6:09 PM To: Day, David; pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE On 01/09/2014 01:51 PM, Day, David wrote: > Adrian, > > Thank you for your response. > > I would note that the original dump archive created by pg_dump > included all schemas and that I only intend to restore a schema from it that is self contained, or a group of related tablesfrom it. I just tried that here and succeeded. I did a pg_dump and then restored only the public schema which in this database is self contained. I did get the HINT because I used the -c switch and it tried to drop the public schema and there whereexisting objects dependent on it. The restore threw the HINT and a subsequent ERROR over trying to CREATE SCHEMA publicwhere it already existed, but it completed the restore. > > I acknowledge the dangers inherent in selective restoration, it just > seems that a couple of additional options ( disable constraints, drop > cascade ) to pg_restore would improve this utility to users who have > put some thought into laying out the database design and failure cases from which they would like to recover. > > To have a pg_restore selective restoration options, (-n, -t ), and > have it fail simply because there are foreign keys amongst the tables > within that schema seems like to much protection or protection that I would at least like to have option to over-ride. We will probably need to see more detail on why that failed in your case because I did not see that in mine. Another wayto influence the outcome is to use the -l and -L options to pg_restore. -l returns the -Fc dump file table of contents(TOC)as a list. You can redirect that to a file and in that file comment out(using ;) items and rearrange the orderof the TOC to suit your needs. Then you use pg_restore with the -L option to feed it the edited TOC. http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html > > It may well be that I could shoot myself in the foot, but I'd still > like to own the firearm :+) > > > Regards > > > Dave Day > > -- Adrian Klaver adrian.klaver@gmail.com