Re: Database migration across different operating systems

I thought about logical replication, but it seems that the initial sync is just a plain copy for each table, which will get us into xid trouble on the publisher side while large tables copy. Unless someone can say conclusively that the copy does NOT hold an xid open that would prevent autovacuums from completing on the publisher.

Then of course there’s the excitement of having to freeze everything on the subscriber side, which can take weeks.

> On Jan 8, 2019, at 3:01 AM, Stéphane KANSCHINE <stephane(at)hexack(dot)fr> wrote:
>
>
> Hey !
>
> Le lun. 7 janv., vers 22:51, Natalie Wenz exprimait :
>>
>> We are in an unusual circumstance, where we need to move all of our
>> Postgres 10 databases from their homes on servers running FreeBSD 11
>> with ZFS to servers in another data center running Red Hat Linux 7.6
>> (also with ZFS), with minimal downtime.
>>
>> I understand that the recommended, safest, approach would be to
>> dump/restore. We intend to do that wherever possible.
>
> It's not anymore since PostgreSQL have logical replication. You can
> initialize you FreeBSD cluster with a pg_basebackup and you follow
> updates with logical streaming.
>
>> However, we have some databases that require high
>> availability/minimal downtime that are also very large. (50 TB, for
>> example)
>
> With logical replication, is it's permanently up to date in
> miliseconds, you can switch when you want.
>
>> If it helps: The cpu architecture is the same on both hosts, and
>> we’ll be running Postgres with the same version and same build
>> options.
>
> Your OS or cpu architecture doesn't matter, really :-)
>
> Regards,
> --
> Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
> ＠ stephane(at)hexack(dot)fr
>  +33 6 64 31 72 52