How can I safely rename a sequence in PostgreSQL, ideally without downtime?
6
votes
2
answers
3580
views
We want to rename some sequences in our production database for [certain reasons](https://stackoverflow.com/a/38666275/6962) .
Is there a way to do this safely in production without having to first close all connections to the database?
It's fine if we get gaps in the ID sequence (e.g. it jumps from ID 123 to ID 200 or whatever) but we obviously don't want long locks causing delays/errors, don't want any risk of duplicate IDs and similar.
We're on PostgreSQL 9.5.21 on Heroku.
We've looked at [the docs](https://www.postgresql.org/docs/9.5/sql-altersequence.html) but are still uncertain about the consequences of running
ALTER SEQUENCE old_id_seq RENAME TO new_id_seq;
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');
in production. I guess one risk would be if stuff happens in
mytable
between those two commands. But what if we did something like this:
-- Starting at a much higher value than the currently highest ID.
CREATE SEQUENCE new_id_seq START 200;
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');
DROP SEQUENCE old_id_seq;
What are the risks of doing it that way?
Asked by Henrik N
(207 rep)
Apr 22, 2020, 09:14 AM
Last activity: Apr 22, 2020, 07:02 PM
Last activity: Apr 22, 2020, 07:02 PM