Thread: Pg_restore and dump -- General question
Hello,
I am cleaning up a database and I have a list of unused tables, views and column and I want to drop these entities.
I have the following constraints:
- The database is live and thats mean I need to be able to revoke the changes as quick and possible.
- I need to restore a certain set of the deprecated table,... But not the whole database
- I need to automate this Job because the number of objects that I need to drop is over than 200
I want to use the cascade option when dropping a table, column, and view, which will cause the deletion of foreign key constraints and views if you drop tables and column, and the deletion of other views if you drop a view ( am I right ....). I had a quick look on the pg_dumb and pg_restore and there is the t option which I think I can use for this task.However, I am a little bit afraid of the approach that I am going to implement. For the following reasons.
For views, I do not think I will have a problem because the view is stateless. i.e the definition will not change.
Tables and column might have different scenarios such as
suppose that table b depends on a, and let us say that other tables depends on b.
What will happen if
1. dropped table a
2. insert data on b and the other relations
3. restore table a and it's dependency (table b).
Is there is a general drop and restore strategy for my case. Also, what are your advices?
Regards
For views, I do not think I will have a problem because the view is stateless. i.e the definition will not change.
Tables and column might have different scenarios such as
suppose that table b depends on a, and let us say that other tables depends on b.
What will happen if
1. dropped table a
2. insert data on b and the other relations
3. restore table a and it's dependency (table b).
Is there is a general drop and restore strategy for my case. Also, what are your advices?
Regards
On 04/04/11 3:47 AM, salah jubeh wrote: > I am cleaning up a database and I have a list of unused tables, views > and column and I want to drop these entities. > .... > suppose that table b depends on a, and let us say that other tables > depends on b. doesn't this dependency cancel the 'unused' part?
Most probably it is . I have just analysed the dependency for one level. and I am planning to do the restore for also one level. i.e suppose that c-->b -->a if I dropped a and turned out that a is used for some reasons, I will restore a and b only without c.
I want to do that for the following reasons. If I restore the whole dependency tree I might end up of restoring the database and the data which are newly inserted will be lost. This situation is actually rare because most of the entities are empty, I just want to make sure.
From: John R Pierce <pierce@hogranch.com>
To: pgsql-general@postgresql.org
Sent: Mon, April 4, 2011 12:55:41 PM
Subject: Re: [GENERAL] Pg_restore and dump -- General question
On 04/04/11 3:47 AM, salah jubeh wrote:
> I am cleaning up a database and I have a list of unused tables, views and column and I want to drop these entities.
> ....
> suppose that table b depends on a, and let us say that other tables depends on b.
doesn't this dependency cancel the 'unused' part?
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I want to do that for the following reasons. If I restore the whole dependency tree I might end up of restoring the database and the data which are newly inserted will be lost. This situation is actually rare because most of the entities are empty, I just want to make sure.
From: John R Pierce <pierce@hogranch.com>
To: pgsql-general@postgresql.org
Sent: Mon, April 4, 2011 12:55:41 PM
Subject: Re: [GENERAL] Pg_restore and dump -- General question
On 04/04/11 3:47 AM, salah jubeh wrote:
> I am cleaning up a database and I have a list of unused tables, views and column and I want to drop these entities.
> ....
> suppose that table b depends on a, and let us say that other tables depends on b.
doesn't this dependency cancel the 'unused' part?
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 04/04/2011 11:47 AM, salah jubeh wrote:
Simple advice would be to create a script on an offline system for testing - when you are happy with the results - do it on the online system - after making a backup of course! Anything else would be suicidal.What will happen if
1. dropped table a
2. insert data on b and the other relations
3. restore table a and it's dependency (table b).
On Mon, Apr 4, 2011 at 5:40 AM, Howard Cole <howardnews@selestial.com> wrote: > On 04/04/2011 11:47 AM, salah jubeh wrote: > > What will happen if > > 1. dropped table a > 2. insert data on b and the other relations > 3. restore table a and it's dependency (table b). > > Simple advice would be to create a script on an offline system for testing - > when you are happy with the results - do it on the online system - after > making a backup of course! Anything else would be suicidal. Agreed. AND on the production system first take a backup and THEN run the drop cascade inside a transaction in case it does crazy things you didn't foresee. begin; drop object yada cascade; then rollback if things get too scary.
Most probably it is . I have just analysed the dependency for one level. and I am planning to do the restore for also one level. i.e suppose that c-->b -->a if I dropped a and turned out that a is used for some reasons, I will restore a and b only without c.
I want to do that for the following reasons. If I restore the whole dependency tree I might end up of restoring the database and the data which are newly inserted will be lost. This situation is actually rare because most of the entities are empty, I just want to make sure.
From: John R Pierce <pierce@hogranch.com>
To: pgsql-general@postgresql.org
Sent: Mon, April 4, 2011 12:55:41 PM
Subject: Re: [GENERAL] Pg_restore and dump -- General question
On 04/04/11 3:47 AM, salah jubeh wrote:
> I am cleaning up a database and I have a list of unused tables, views and column and I want to drop these entities.
> ....
> suppose that table b depends on a, and let us say that other tables depends on b.
doesn't this dependency cancel the 'unused' part?
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I want to do that for the following reasons. If I restore the whole dependency tree I might end up of restoring the database and the data which are newly inserted will be lost. This situation is actually rare because most of the entities are empty, I just want to make sure.
From: John R Pierce <pierce@hogranch.com>
To: pgsql-general@postgresql.org
Sent: Mon, April 4, 2011 12:55:41 PM
Subject: Re: [GENERAL] Pg_restore and dump -- General question
On 04/04/11 3:47 AM, salah jubeh wrote:
> I am cleaning up a database and I have a list of unused tables, views and column and I want to drop these entities.
> ....
> suppose that table b depends on a, and let us say that other tables depends on b.
doesn't this dependency cancel the 'unused' part?
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank you all, I will take that in account
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Howard Cole <howardnews@selestial.com>
Cc: salah jubeh <s_jubeh@yahoo.com>; pgsql-general@postgresql.org
Sent: Mon, April 4, 2011 1:44:28 PM
Subject: Re: [GENERAL] Pg_restore and dump -- General question
On Mon, Apr 4, 2011 at 5:40 AM, Howard Cole <howardnews@selestial.com> wrote:
> On 04/04/2011 11:47 AM, salah jubeh wrote:
>
> What will happen if
>
> 1. dropped table a
> 2. insert data on b and the other relations
> 3. restore table a and it's dependency (table b).
>
> Simple advice would be to create a script on an offline system for testing -
> when you are happy with the results - do it on the online system - after
> making a backup of course! Anything else would be suicidal.
Agreed. AND on the production system first take a backup and THEN run
the drop cascade inside a transaction in case it does crazy things you
didn't foresee.
begin;
drop object yada cascade;
then rollback if things get too scary.