Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (1.0)
Subject: Re: Database migration across different operating systems
From: Natalie Wenz <nataliewenz@gmail.com>
In-Reply-To: <20190108090144.rdrtfh6wznoolsww@office.hexack.fr>
Date: Tue, 8 Jan 2019 08:32:12 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <2862230A-6B04-42F0-83EE-D079AD0236A8@gmail.com>
References: <A740E5D2-34BE-46AA-A9FE-50F510E771EC@gmail.com>
 <20190108090144.rdrtfh6wznoolsww@office.hexack.fr>
To: =?utf-8?Q?St=C3=A9phane_KANSCHINE?= <stephane@hexack.fr>
Precedence: bulk

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

Then of course there=E2=80=99s the excitement of having to freeze everything=
 on the subscriber side, which can take weeks.=20

> On Jan 8, 2019, at 3:01 AM, St=C3=A9phane KANSCHINE <stephane@hexack.fr> w=
rote:
>=20
>=20
> Hey !
>=20
> Le lun.  7 janv., vers 22:51, Natalie Wenz exprimait :
>>=20
>> 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.=20
>>=20
>> I understand that the recommended, safest, approach would be to
>> dump/restore. We intend to do that wherever possible.=20
>=20
> 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.
>=20
>> However, we have some databases that require high
>> availability/minimal downtime that are also very large. (50 TB, for
>> example)
>=20
> With logical replication, is it's permanently up to date in
> miliseconds, you can switch when you want.
>=20
>> If it helps: The cpu architecture is the same on both hosts, and
>> we=E2=80=99ll be running Postgres with the same version and same build
>> options.=20
>=20
> Your OS or cpu architecture doesn't matter, really :-)
>=20
> Regards,
> --=20
> St=C3=A9phane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io.=
/
> =EF=BC=A0 stephane@hexack.fr
> =EE=88=81  +33 6 64 31 72 52