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
