Re: Changeset Extraction v7.6.1

On 6/1/14, 10:49 AM, Euler Taveira wrote:
> On 01-06-2014 02:57, Andres Freund wrote:
>> On 2014-06-01 00:50:58 -0500, Jim Nasby wrote:
>>> On 5/31/14, 9:11 AM, Andres Freund wrote:
>>>> On 2014-02-21 15:14:15 -0600, Jim Nasby wrote:
>>>>> On 2/17/14, 7:31 PM, Robert Haas wrote:
>>>>>> But do you really want to keep that snapshot around long enough to
>>>>>> copy the entire database? I bet you don't: if the database is big,
>>>>>> holding back xmin for long enough to copy the whole thing isn't likely
>>>>>> to be fun.
>>>>>
>>>>> I can confirm that this would be epic fail, at least for londiste. It takes about 3 weeks for a new copy of a ~2TB database. There's no way that'd work with one snapshot. (Granted, copy performance in londiste is rather lackluster, but still...)
>>>>
>>>> I'd marked this email as todo:
>>>> If you have such a huge database you can, with logical decoding at
>>>> least, use a basebackup using pg_basebackup or pg_start/stop_backup()
>>>> and roll forwards from that... That'll hopefull make such huge copies
>>>> much faster.
>>
>>> Just keep in mind that one of the use cases for logical replication is upgrades.
>>
>> Should still be fine. Make a physical copy; pg_upgrade; catchup via
>> logical rep.
>>
> Have in mind that it is not an option if you want to copy *part* of the
> database(s) (unless you have space available and want to do the cleanup
> after upgrade). In a near future, a (new) tool could do (a) copy schema,
> (b) accumulate modifications while copying data, (c) copy whole table
> and (d) apply modifications for selected table(s)/schema(s). Such a tool
> could even be an alternative to pg_upgrade.

There's also things that pg_upgrade doesn't handle, so it's not always an option.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net