Re: Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

On 02/15/2014 01:22 PM, John R Pierce wrote:
> On 2/15/2014 10:15 AM, Antman, Jason (CMG-Atlanta) wrote:
>> I also asked this question on dba.stackexchange.com, where it
>> received a very detailed enumeration of the associated problems from
>> Craig Ringer:
>> http://dba.stackexchange.com/questions/58896/restore-postgres-data-tablespace-to-new-tablespace-at-new-mount-point/58967?noredirect=1#58967
>>
>>
>> Perhaps there's a postgres internals expert around, someone
>> intimitely familiar with pg_xlog/pg_clog/pg_control, who can comment
>> on whether it's possible to take the on-disk files from a single
>> database in a single tablespace, and make them usable by a different
>> postgres server, running multiple databases?
>
> Craig's answer covers all the bases, quite accurately. Because you
> don't like it, you think someone else is going to give you some magic
> to make all that go away?
>
>
>
1) The second-to-last paragraph in Craig's answer is,
"This is quite an interesting problem, and I suggest raising it on the
pgsql-general mailing list. Link back here for context (and so people
can explain all the things I got wrong in the above explanation)."

2) I was hoping that someone else would either (a) correct "all the
things that [Craig] got wrong in the above explanation", or (b) give me
some pointers on possible ways to do this. If the answer is "well, it
would be a lot easier if you imported a schema-only pgdump of each
database, and then mucked with the internals of the tablespace", that's
a good starting point. Honestly, if the answer is "this simply will not
happen unless you hire a postgresql core developer to code it", that's
still a more complete answer to bring to management than "not possible."
I work in a software development shop... there's a general hatred of the
idea of "impossible" (as opposed to impractical) when it comes to software.

Thanks,
Jason

--

Jason Antman | Systems Engineer | CMGdigital
jason(dot)antman(at)coxinc(dot)com | p: 678-645-4155