database split

Greetings,

We are migrating a subset of our customers to a new set of servers. This
requires that we migrate their data stored in postgresql (v9.1.15, Linux)
to a new database. The new database happens to reside in the same cluster,
but in the future this may not be the case. We are using a separate
tablespace for the new database.

Our schema has several tiers of foreign key relationships, and most FKs
eventually lead back to a few core tables.

Here are the options I am considering:

A. Text-processing a dump of the original database, filtering only the rows
in which I am interested, while loading the dump into the new database.

B. Copying the original database (CREATE DATABASE smalldb WITH TEMPLATE
bigdb), then modifying the schema so that each FK constraint now includes
ON DELETE CASCADE, then deleting rows from the few core tables while
letting postgresql cascade the deletes down the hierarchy. Then, of
course, restoring the schema to its original state.

C. Generating the myriad SELECTs necessary to export only the required data
from every table, then importing those results (either using dblink or COPY
FROMs) into the new database that has been populated with schema from the
original database. Carefully ordering the imports to avoid missing FKs.

There are tradeoffs to each approach, of course.

A) does not require me to modify/restore our schema, but will take some
effort to build the intermediate processing code (for example: having to
account for gotchas where our FK columns are not predictably named).

B) would probably require the least amount effort to code up, but I am not
certain this will perform well (or even work!).

Ordering the imports for C) correctly will be a pain, but generating the
SELECTs and INSERTs programmatically is straightforward.

Of course, I would love to hear about options D) - Z) as well! Thanks in
advance for your input.

Dave Owens

541-359-2602
TU Corporate Website
<http://t.signauxun.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=b64a8344-b9e5-4e6c-ab3f-782da4a277a6>
| Swimming is Our Passion
<http://info.teamunify.com/swimming-is-our-passion-whats-your-story>
TU Facebook
<http://t.signauxun.com/link?url=https%3A%2F%2Fwww.facebook.com%2Fteamunify&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=832bda8d-6593-4bad-fa2a-31c8623aa381>
| Free OnDeck Mobile Apps
<http://t.signauxun.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2Fondeck%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=3316db21-b178-4e7b-82d8-f6b37b655825>
| TeamUnify Blog
<http://t.signauxun.com/link?url=http%3A%2F%2Fblog.teamunify.com%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=3ca8e429-69ba-43df-ef87-c59e9ca2f19f>