Thread: Removing a schema
I'm trying to remove a schema and move all the tables to another schema. I've manually run alter table on every table to move them, however all the foreign keys still reference the old schema, and there are too many to do by hand. Is there an easy way to update one of the system catalogs to do this? I want to change every reference of that schema to point to the new one, and then drop the schema, but not drop any data. Thanks, - Naz.
Naz Gassiep skrev: > I'm trying to remove a schema and move all the tables to another schema. > I've manually run alter table on every table to move them, however all > the foreign keys still reference the old schema, and there are too many > to do by hand. > > Is there an easy way to update one of the system catalogs to do this? I > want to change every reference of that schema to point to the new one, > and then drop the schema, but not drop any data. I have done something like this pg_dump old_schema in text format create new schema modify dump to set default schema to the new one import dump with psql drop old schema Nis
Naz Gassiep <naz@mira.net> writes:
> I'm trying to remove a schema and move all the tables to another schema.
> I've manually run alter table on every table to move them, however all
> the foreign keys still reference the old schema,
What? It works fine for me:
regression=# create schema s1;
CREATE SCHEMA
regression=# create table s1.t1(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
regression=# create table s1.t2(f1 int references s1.t1);
CREATE TABLE
regression=# create schema s2;
CREATE SCHEMA
regression=# alter table s1.t1 set schema s2;
ALTER TABLE
regression=# \d s1.t2
Table "s1.t2"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
Foreign-key constraints:
"t2_f1_fkey" FOREIGN KEY (f1) REFERENCES s2.t1(f1)
regards, tom lane
Here's what I want to do: Checkpoint the database in whatever way is appropriate. Make copies of the database on several laptops for use in the field (in automobiles) to do database changes. Record all the changes made since the checkpoint as the user makes them. Periodically take all the changes back into the office, take the changes made out in the field and apply them to the main database. Repeat the process. Notes: 1) Unless an user makes a mistake, there should be no changes to the same records by multiple users. (i.e. any concurrency violations should be registered as an exception.) 2) I'd prefer it to just record the sql commands executed by the database as text, then use psql < myFieldcommands to update the database. This will also help me isolate any concurrency exceptions, and I'd like to wrap the whole update in a transaction, so I can roll the whole thing back if it does detect concurrency problems anywhere in the process (then I can edit out the offending lines). 3) There's no particular rush to update the database - I don't need this real-time. 4) Users might make their checkpoint at a different time from other users. Since I'm relatively new to Postgres, (and I apologize if this has come up before), I'm hoping some respondents will provide me with the correct strategy. -Owen
How many users do you have? Have you considered giving each user a schema in which to make their changes? It sounds like you don't really have a multi-master replication issue, which makes things easier. On Tue, 7 Aug 2007, Owen Hartnett wrote: > > Here's what I want to do: > > Checkpoint the database in whatever way is appropriate. > > Make copies of the database on several laptops for use in the field (in > automobiles) to do database changes. Record all the changes made since the > checkpoint as the user makes them. > > Periodically take all the changes back into the office, take the changes made > out in the field and apply them to the main database. > > Repeat the process. > > Notes: > > 1) Unless an user makes a mistake, there should be no changes to the same > records by multiple users. (i.e. any concurrency violations should be > registered as an exception.) > > 2) I'd prefer it to just record the sql commands executed by the database as > text, then use psql < myFieldcommands to update the database. This will also > help me isolate any concurrency exceptions, and I'd like to wrap the whole > update in a transaction, so I can roll the whole thing back if it does detect > concurrency problems anywhere in the process (then I can edit out the > offending lines). > > 3) There's no particular rush to update the database - I don't need this > real-time. > > 4) Users might make their checkpoint at a different time from other users. > > Since I'm relatively new to Postgres, (and I apologize if this has come up > before), I'm hoping some respondents will provide me with the correct > strategy. > > -Owen > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On 8/7/07, Owen Hartnett <owen@clipboardinc.com> wrote: > > Here's what I want to do: > > Checkpoint the database in whatever way is appropriate. > > Make copies of the database on several laptops for use in the field > (in automobiles) to do database changes. Record all the changes made > since the checkpoint as the user makes them. > > Periodically take all the changes back into the office, take the > changes made out in the field and apply them to the main database. > > Repeat the process. > > Notes: > > 1) Unless an user makes a mistake, there should be no changes to the > same records by multiple users. (i.e. any concurrency violations > should be registered as an exception.) > > 2) I'd prefer it to just record the sql commands executed by the > database as text, then use psql < myFieldcommands to update the > database. This will also help me isolate any concurrency exceptions, > and I'd like to wrap the whole update in a transaction, so I can roll > the whole thing back if it does detect concurrency problems anywhere > in the process (then I can edit out the offending lines). > > 3) There's no particular rush to update the database - I don't need > this real-time. > > 4) Users might make their checkpoint at a different time from other users. Given that each person is likely to only be only operating on their own data set, I'd use an integer range for each person. Make an int field in each table, and give each use a 1,000,000 id range to play in, or something like that. You can even set it up so that the app uses sequences and have them start at whatever the user's first id is, and not cycling and stopping when it reaches the end to keep them from bumping into the next person's range. Heck, go with bigint and give each person a 1,000,000,000 range. Then you could still handle 9,223,372,035 or so users before you'd run out of sequences for each. Heck, you could even write a system of update functions that checked the userid against their numeric range and only updated the data if it was in their range. Send it to a coworker for approval if it's not. I'm having a few too mad scientist moments right about now. Got to get back to my data mining project...
At 2:15 PM -0700 8/7/07, Ben wrote: >How many users do you have? Have you considered giving each user a >schema in which to make their changes? It sounds like you don't >really have a multi-master replication issue, which makes things >easier. Maybe I'm not understanding the strategy, but I don't see what this buys me, as I have to end up with a single database schema that has incorporated all the changes. If I can "record" all the SQL a user does from the checkpoint on, then I can "psql <" it in to the main database. Once I've combined their data into the database that sits on the server, I don't need their database copies anymore. -Owen
At 5:13 PM -0500 8/7/07, Scott Marlowe wrote: >On 8/7/07, Owen Hartnett <owen@clipboardinc.com> wrote: >> >> Here's what I want to do: >> >> Checkpoint the database in whatever way is appropriate. >> >> Make copies of the database on several laptops for use in the field >> (in automobiles) to do database changes. Record all the changes made >> since the checkpoint as the user makes them. >> >> Periodically take all the changes back into the office, take the >> changes made out in the field and apply them to the main database. >> >> Repeat the process. >> >> Notes: >> >> 1) Unless an user makes a mistake, there should be no changes to the >> same records by multiple users. (i.e. any concurrency violations >> should be registered as an exception.) >> >> 2) I'd prefer it to just record the sql commands executed by the >> database as text, then use psql < myFieldcommands to update the >> database. This will also help me isolate any concurrency exceptions, >> and I'd like to wrap the whole update in a transaction, so I can roll >> the whole thing back if it does detect concurrency problems anywhere >> in the process (then I can edit out the offending lines). >> >> 3) There's no particular rush to update the database - I don't need >> this real-time. >> >> 4) Users might make their checkpoint at a different time from other users. > >Given that each person is likely to only be only operating on their >own data set, I'd use an integer range for each person. Make an int >field in each table, and give each use a 1,000,000 id range to play >in, or something like that. You can even set it up so that the app >uses sequences and have them start at whatever the user's first id is, >and not cycling and stopping when it reaches the end to keep them from >bumping into the next person's range. > >Heck, go with bigint and give each person a 1,000,000,000 range. Then >you could still handle 9,223,372,035 or so users before you'd run out >of sequences for each. > >Heck, you could even write a system of update functions that checked >the userid against their numeric range and only updated the data if it >was in their range. Send it to a coworker for approval if it's not. >I'm having a few too mad scientist moments right about now. Got to >get back to my data mining project... This would probably work, but it seems like overkill...I'll have to think about it some more... -Owen
You can group schemas with views, and it guarentees nobody will accidently overwrite somebody else's stuff. Merging a two schemas with identical table structure should also be quite trivial. Of course, if you have a lot of users, this might not work so well.... On Tue, 7 Aug 2007, Owen Hartnett wrote: > At 2:15 PM -0700 8/7/07, Ben wrote: >> How many users do you have? Have you considered giving each user a schema >> in which to make their changes? It sounds like you don't really have a >> multi-master replication issue, which makes things easier. > > Maybe I'm not understanding the strategy, but I don't see what this buys me, > as I have to end up with a single database schema that has incorporated all > the changes. If I can "record" all the SQL a user does from the checkpoint > on, then I can "psql <" it in to the main database. Once I've combined their > data into the database that sits on the server, I don't need their database > copies anymore. > > -Owen > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On 8/7/07, Owen Hartnett <owen@clipboardinc.com> wrote: > At 2:15 PM -0700 8/7/07, Ben wrote: > >How many users do you have? Have you considered giving each user a > >schema in which to make their changes? It sounds like you don't > >really have a multi-master replication issue, which makes things > >easier. > > Maybe I'm not understanding the strategy, but I don't see what this > buys me, as I have to end up with a single database schema that has > incorporated all the changes. If I can "record" all the SQL a user > does from the checkpoint on, then I can "psql <" it in to the main > database. Once I've combined their data into the database that sits > on the server, I don't need their database copies anymore. I'm not sure how you're planning to do this. PostgreSQL doesn't use SQL statements for Point in Time Recovery, it uses WAL logs applied against a database that's an exact physical copy of the database at a previous time. Are you going to write your own application that will let you save each SQL statement before it's applied to the user's local database? And if so, are you then going to have an individual database for each user? That might work. My way isn't all that much harder to do. It just lets you store all the data in one database and share it out with all the users. So it really depends on what you want in the end. With one database, it would be much easier to run a query across all your data at once. With individual databases you have very strong isolation between the data sets. Either way would work, each has its own advantages and disadvantages.
You can also have a trigger that records into a log table the id & table of each record inserted/updated/deleted, and then it's a simple matter of merging changes from a certain point forward by searching that table and using the values of the current records. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice