Migrating tables to new schema with minimal downtime

From: Joseph Hammerman <joe(dot)hammerman(at)datadoghq(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Migrating tables to new schema with minimal downtime
Date: 2023-03-06 21:00:15
Message-ID: CAHs7QM8aCJ_a=Rcf=eYAS27eY9Ue6NV5CO+00+ZJnRWbSbxgVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi pgsql-admins,

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 from the client perspective. I will
have prepopulated the existing application client search_path with the new
schema name.

When inquiring about this in postgresql IRC a concern was raised about data
safety, due to locks concurrently executing transactions might have on the
relation in question when the ALTER TABLE ... SET SCHEMA TO ... command is
executed (even if done in a transaction).

Would it be data safe if I took an ACCESS EXCLUSIVE lock on the table?

My testing shows that a session with both the old schema and the new schema
in its path will cleanly determine the new location of the relation - does
anyone have any caveats or know of any sharp edges I should be aware of
here?

Thanks in advance for any advice or clarity anyone can provide,
Joe

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2023-03-07 06:29:19 Re: Migrating tables to new schema with minimal downtime
Previous Message Victor Sudakov 2023-03-06 09:05:12 Upgrading Patroni from 2.1.x to 3.0.1 ?