Date: Thu, 15 May 2025 10:21:36 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to have a smooth migration
Message-ID: <20250515082136.h3vy2ntxlp3xwvft@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAB+=1TUU2rgjNzz6T05RPwCi1xjKr=9cwhX=Jdyo4zPiakH-zQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="6m6xtqf4ui3ljpq3"
Content-Disposition: inline
In-Reply-To: <CAB+=1TUU2rgjNzz6T05RPwCi1xjKr=9cwhX=Jdyo4zPiakH-zQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20250515082136.h3vy2ntxlp3xwvft%40hjp.at>
Precedence: bulk


--6m6xtqf4ui3ljpq3
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2025-05-15 11:39:39 +0530, veem v wrote:
> Hi,
> Its postgres database behind the scenes.
>=20
> We have a use case in which the customer is planning to migrate data from=
 an
> older version (V1) to a newer version (V2). For V2, the tables will be ne=
w, but
> their structure will be similar to the V1 version with few changes in
> relationship might be there.

Are V1 and V2 different databases or do plan to do this in-place?

> We want to have this migration approach happen in multiple phases

What is the purpose of doing it in multiple phases? Do you have lengthy
acceptance tests during which new data will accumulate?

> in which each time the delta data from version V1 will be moved
> to version- V2 and then final cutover will =A0happen to V2 if all looks g=
ood or
> else rollback to V1.

By "rollback" do mean a transaction rollback or some other means of
restoring the previous state?

> The tables are smaller in size like max ~100K records in tables.
>=20
> My question is, is it a good idea to have an approach in which we will ha=
ve
> procedures created to move the delta data in every phase and schedule tho=
se
> using some tasks for each table. Or any other strategy should we follow?

That sounds definitely doable and I have done similar things in the
past.Especially for a relatively small database getting the diff to
apply is not much of a problem. Doing it in the right order might be a
bit of a challenge but deferring constraints should help. Also be
mindful of what should happen if data in V2 is changed between the
phases (e.g. by a test that creates new records).


> Also another thing to note , we have used sequences as primary keys in so=
me
> tables and they have FK relationships with other tables, so the same sequ=
ence
> number in version V2 will cause issues/conflict, so how should we handle =
this
> scenario? Should we just create new sequences with higher start values?

If you can use the same key values in V2 as in V1, just update the
sequences to the new start point at the end of each migration. If they
are in the same database you could even use the same sequences to avoid
conflicts. If you need to generate new key values (for example, you are
merging two tables into one), you will need a translation table (which
could be just some extra columns in the new table).

        hjp


--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--6m6xtqf4ui3ljpq3
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmglpAcACgkQ8g5IURL+
KF3cnQ//ZuyCll9N8x3Ig4TzeIB6twAHmKToEsLvoW6kEfMLVeG27w4TqJzkd/RK
IbOh03iCoZvhRux4pqG+gi/DQjpbZfZmSRM+u1gKjP8acrLTNvNNOXNoEgeKW0bE
v6ENiuE7JwztzG2avZ6UNwlgLEETWhN5keFrN/MIIP+xW2cVpH3VaqNhbF1HdjuT
IXKbLFPIXvDhBk4ajQJYjcPzVFyzNtTYl3LGWaSyiUpKrAH0P+iIxfd4XEPFY/mo
VZO3ccL7PgkPc4iBAzCbsGOZDEGLvvG/WG8xfFy1K0W4F+yMc9RKS48gCckDUVil
ibPVzByruIim1jYDMfGv542O3OkjfB6tKn+FEmoq+ehVKSFauVpzWWsrAWwCzjvl
DxUINvU4Grh7fUge8Q0q/prefWxezN+VMhiyv6enj6igzaNjBCLquXjMIxNPnYff
D0iKieUZq3AOvDaPUl4RbNQJx26smvhZcDGkuq2XpaAfzOlyPeHwfenCybf01qwx
hqp2dG7U5o+CqkMrKjJv1X5Nm5rvO8FeZvZTYdzcFV/JT3e5TfeDGLdHB9nV667c
+51wcOTcZz3jN68oHhaJpE9G9de0qVpt6fKiOUAnkcEZ2E2d0I5mfqh27448sn3p
Kmnt/POooCfjn6B64zUOIcjPbnKhflNZGVhYG+ZfG0IVcrFTVuc=
=Cpdv
-----END PGP SIGNATURE-----

--6m6xtqf4ui3ljpq3--