All that said, my guess is you're doing this to support horizontal scale-out, which means you'll probably need to do this more than once, and it'd presumably be nice for you and your customers if this didn't require downtime. I would look at having a way to create a partial replica using londiste (or BDR if it'd support it). The trick there is having a way to identify whether you want each individual row on a replica. If you add some kind of cluster_id field to every table that makes doing that filtering pretty easy; IIRC londiste supports that out of the box.
Jim, thank you for your input. The reason for the split is partially for horizontal scale-out (current system is well-provisioned for the near future), partly for localization concerns arising from legacy code. We are under a bit of a deadline to finish this split, so selective replication is not feasible at this time. The tools you mention do look very promising for our needs, however.
I decided to do a combination of A and C...
- load a schema-only dump from the original db to the new db (filtering out triggers and constraints)
- do COPY TO/FROMs (I would have to generate all the WHEREs for Jim's option D anyway, the data I am after is only tiny fraction of the original db...)
- restore the filtered constraints/triggers
Dave Owens
