How to practically rename tables and columns in PostgreSQL on a production system?

user_90848 0
Viewed 681

It often happens that the original name something is given is not the best name. Maybe requirements shifted slightly, or maybe as time went on, a better understanding of the concept being represented developed. Sometimes one name is used in developing a feature, but testing with real users reveals that a better name is needed, and it'd be nice to have the names for things in the DB match the names used in the UI.

PostgreSQL lets you rename tables and columns via alter table, but often this isn't feasible in a production without significant downtime. If existing clients are using the old name, you can't just yank it out from under them.

I was hoping there was some way to "add a name" to a table or column, so that old_name and new_name will both work, and then at a later time remove the old name. Then a name migration could work like this:

  • add new_name
  • modify all clients to use new_name instead of old_name
  • once all clients have been updated, remove old_name

Is there a way to do this? If not, is there a recommended procedure for renaming a column while minimizing downtime? How about a table?

postgresql
1 Answers

Some recipes for renaming tables and/or columns in a production system that seem to work. However, I've only tested these on a small test database, not on a large production system. Renaming and view creation are both supposed to be very fast, though.

Renaming a table

  1. Rename the table, and temporarily add an updatable view with the old name:

    begin;
    alter table old_table rename to new_table;
    create view old_table as select * from new_table;
commit;

  2. Migrate clients to use the new table name.

  3. Once all clients are migrated, drop the view:

    drop view old_table;

Renaming columns without renaming the table

Renaming a column without renaming the table is a bit more complicated, because we can't have a view shadow a table (apparently).

  1. Rename the column(s), temporarily rename the table, and add an updatable view that adds the old name for the column with the table's correct name:

    begin;
    alter table my_table rename column old_name to new_name;
    alter table my_table rename to my_table_tmp;
    create view my_table as
        select *, new_name as old_name from my_table_tmp;

commit;

  2. Migrate clients to use the new column name.

  3. Once all clients are migrated, drop the view, rename the table back:

    begin;
    drop view my_table;
    alter table my_table_tmp rename to my_table;
commit;

Renaming a table and some of its columns simultaneously

  1. Rename the table and columns, and temporarily add an updatable view with the old name and old columns:

    begin;
    alter table old_table rename to new_table;
    alter table new_table rename column to old_name to new_name;
    create view old_table as
        select *, old_name as new_name from new_table;
commit;

    Instead of select *, old_name as new_name, it might be better to only have the original set of columns, so clients have to migrate to the new table name to get the new column names:

    create view old_table as
    select unchanged_name, old_name as new_name from new_table;

  2. Migrate clients to use the new table and column names.

  3. Once all clients are migrated, drop the view:

    drop view old_table;
user_908480
Related