Postgresql – Impact of changing schema name

database-designpostgresql

I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1).

Not withstanding any code changes or environment files that need to be changed as a result, are there any dangers to renaming the schema of a PostgreSQL database? If so, what? Or will a simple ALTER SCHEMA be ok?

Best Answer

After a pg_dump from DB1 and a restore to DB2 (with psql -f, most likely), ALTER SCHEMA will take care of all internal reverences. Obviously, any code you store outside the database must be updated - you mentioned that already.

What might be less obvious: functions bodies have to be updated manually, if there should be any references to the schema name. Can only include functions you wrote yourself.

There is also less common stuff like external references via dblink, including self-references, that would need to be adapted. You'll know about it if you are using it.

Other than that, the search_path must match your respective setups in the two databases. This concerns general settings in postgresql.conf, but also settings per DATABASE or per ROLE and possibly options supplied with the connection from your apps.

Then you should be good to go.

OR, and that's what I would do, you hack the dump. After you dump from DB1, replace all occurrences of the old schema name with an editor of your choice. Then run the edited SQL script via psql -f to restore to DB2. All done.

Of course, if the name is not unique across the whole dump, you will have to be careful to replace only relevant strings.

If your setup is huge and the schema name is not very selective, you might want to restore to DB2 as is, ALTER SCHEMA, pg_dump and delete from DB2 again, and do the rest in your editor before you restore for good.

Related Question