From: Natalie Wenz <nataliewenz@gmail.com>
To: Stéphane KANSCHINE <stephane@hexack.fr>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Database migration across different operating systems
Date: Tue, 8 Jan 2019 08:32:12 -0600
Message-ID: <2862230A-6B04-42F0-83EE-D079AD0236A8@gmail.com> (raw)
In-Reply-To: <20190108090144.rdrtfh6wznoolsww@office.hexack.fr>
References: <A740E5D2-34BE-46AA-A9FE-50F510E771EC@gmail.com>
<20190108090144.rdrtfh6wznoolsww@office.hexack.fr>
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@hexack.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@hexack.fr
> +33 6 64 31 72 52
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: nataliewenz@gmail.com, stephane@hexack.fr, pgsql-admin@lists.postgresql.org
Subject: Re: Database migration across different operating systems
In-Reply-To: <2862230A-6B04-42F0-83EE-D079AD0236A8@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox