gasilsugar.blogg.se - Postgresql rename

#POSTGRESQL RENAME PATCH#

Purpose of the tool, while the latter merely avoids an unnecessary The old and new directory layouts the former is necessary given the

#POSTGRESQL RENAME PATCH#

This patch modifies pg_upgrade and pg_basebackup to understand both "pg_xlog" directory are not critical data, leading to unpleasantĬonsequences.

Migrate clients to use the new table and column names."xlog" is not a particularly clear abbreviation for "write-ahead log",Īnd it sometimes confuses users into believe that the contents of the Select unchanged_name, old_name as new_name from new_table Name to get the new column names: create view old_table as The original set of columns, so clients have to migrate to the new table Instead of select *, old_name as new_name, it might be better to only have Select *, old_name as new_name from new_table Rename the table and columns, and temporarily add an updatable view with the old name and old columns: begin Īlter table new_table rename column to old_name to new_name Renaming a table and some of its columns simultaneously Once all clients are migrated, drop the view, rename the table back: begin Īlter table my_table_tmp rename to my_table Migrate clients to use the new column name. Select *, new_name as old_name from my_table_tmp View that adds the old name for the column with the table's correct name: begin Īlter table my_table rename column old_name to new_name Īlter table my_table rename to my_table_tmp Rename the column(s), temporarily rename the table, and add an updatable Renaming a column without renaming the table is a bit more complicated,īecause we can't have a view shadow a table (apparently).

Renaming columns without renaming the table Once all clients are migrated, drop the view: drop view old_table Migrate clients to use the new table name. Rename the table, and temporarily add an updatable view with the old name: begin Īlter table old_table rename to new_table Ĭreate view old_table as select * from new_table

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

once all clients have been updated, remove old_name.

modify all clients to use new_name instead of old_name.

Then a name migration could work like this: 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. If existing clients are using the old name, you can't just yank it out from under them. PostgreSQL lets you rename tables and columns via alter table, but often this isn't feasible in a production without significant downtime. 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. Maybe requirements shifted slightly, or maybe as time went on, a better understanding of the concept being represented developed. It often happens that the original name something is given is not the best name.