pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrating tables to new schema with minimal downtime
3+ messages / 3 participants
[nested] [flat]
* Migrating tables to new schema with minimal downtime
@ 2023-03-06 21:00 Joseph Hammerman <joe.hammerman@datadoghq.com>
0 siblings, 2 replies; 3+ messages in thread
From: Joseph Hammerman @ 2023-03-06 21:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Migrating tables to new schema with minimal downtime
@ 2023-03-07 06:29 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Joseph Hammerman <joe.hammerman@datadoghq.com>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2023-03-07 06:29 UTC (permalink / raw)
To: Joseph Hammerman <joe.hammerman@datadoghq.com>; pgsql-admin@lists.postgresql.org
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 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?
I don't think there is any concern with the integrity or consistency of your data;
the transaction system will take care of that. The potential problem is that 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Migrating tables to new schema with minimal downtime
@ 2023-03-07 11:59 hubert depesz lubaczewski <depesz@depesz.com>
parent: Joseph Hammerman <joe.hammerman@datadoghq.com>
1 sibling, 0 replies; 3+ messages in thread
From: hubert depesz lubaczewski @ 2023-03-07 11:59 UTC (permalink / raw)
To: Joseph Hammerman <joe.hammerman@datadoghq.com>; +Cc: pgsql-admin@lists.postgresql.org
On Mon, Mar 06, 2023 at 01:00:15PM -0800, Joseph Hammerman wrote:
> Would it be data safe if I took an ACCESS EXCLUSIVE lock on the table?
alter table gets ACCESS EXCLUSIVE lock on its own.
You can test it trivially in psql on some side table.
Best regards,
depesz
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-03-07 11:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-06 21:00 Migrating tables to new schema with minimal downtime Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-07 06:29 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-07 11:59 ` hubert depesz lubaczewski <depesz@depesz.com>
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