Message-ID: <efa851fec1b04047ef4af045831b628834c00a8a.camel@cybertec.at>
Subject: Re: Migrating tables to new schema with minimal downtime
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Joseph Hammerman <joe.hammerman@datadoghq.com>,
	pgsql-admin@lists.postgresql.org
Date: Tue, 07 Mar 2023 07:29:19 +0100
In-Reply-To: 
 <CAHs7QM8aCJ_a=Rcf=eYAS27eY9Ue6NV5CO+00+ZJnRWbSbxgVg@mail.gmail.com>
References: 
	<CAHs7QM8aCJ_a=Rcf=eYAS27eY9Ue6NV5CO+00+ZJnRWbSbxgVg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/efa851fec1b04047ef4af045831b628834c00a8a.camel%40cybertec.at>
Precedence: bulk

On Mon, 2023-03-06 at 13:00 -0800, Joseph Hammerman wrote:
> I am working on splitting up tables that are currently in a single schema=
 into multiple,
> context specific schemas. I would like to do this with minimal downtime f=
rom=C2=A0the client
> perspective. I will have=C2=A0prepopulated=C2=A0the=C2=A0existing applica=
tion client search_path with
> the new schema name.
>=20
> When inquiring about this in postgresql IRC a concern was raised about da=
ta safety,
> due to locks concurrently executing transactions might have on the relati=
on=C2=A0in question
> when the ALTER=C2=A0TABLE ... SET SCHEMA TO ... command is executed (even=
 if done in a transaction).
>=20
> Would it be data safe if I took an ACCESS EXCLUSIVE lock on the table?
>=20
> My testing shows that a session with both the old schema and the new sche=
ma in its
> path will cleanly determine the new location of the relation - does anyon=
e have any
> caveats or know of any sharp edges I should be aware of here?

I don't think there is any concern with the integrity or consistency of you=
r data;
the transaction system will take care of that.  The potential problem is th=
at the
ALTER TABLE will be blocked by concurrent transactions that use the table, =
and it
will block such concurrent transactions in turn.

So make sure that there are no long running transactions that use the table=
, then
you should be fine, as long as you COMMIT your transactions right after the=
 ALTER TABLE.

Yours,
Laurenz Albe