Hi Jeff,
> Thank you for the message. --- I like your idea, but one thing I
> forgot to mention is that my target postgres cluster has production
> DB's running on it already.
Oh, that's important information.
The only way to "merge" additional databases into an existing cluster
would be dumping and importing (in either direction -- either merge
existing target dbs into the rsync'ed cluster or vice versa).
My original suggestion would transfer the original cluster as-is to the
target machine into an empty directory, and create a new (possibly
additional) postgresql instance on the target.
In Ubuntu (and I guess in CentOS as well) there is built-in support for
multiple clusters (instances) on a single host (see pg_lsclusters or
pg_ctlcluster). Every cluster will listen on a different port.
This enables (but also forces) per-cluster management of replication,
backup and upgrades. However, that approach might not scale well and
tuning memory/io settings might be more difficult than with a single
cluster on the DB host.
> Perhaps I could stand up a 2nd postgres instance on the target
> server, and move the data there first? Then it would at least be on
> the same box/storage. Then I could pg_dump/pgrestore the 2 DB's I
> need into the production cluster, and shutdown the 2nd instance on
> the target server.
That would be an option, but consider that in this case the IO subsystem
of the target server would have to read and write your data at the same
time.
Unless you have source and target clusters on independent spindles or a
really slow source server, this will possibly take even longer than to
pipe the data over your GBIT network connection.
Best regards,
-hannes
