The system tables are the implementation of database objects. So if you, say, call a function foo()
, the DBMS looks in pg_proc
to see if there is a function foo
and what the arguments and the source code and so on are. The layout and arrangement of the system catalogs are merely the way the implementors of various features over time made them. You already pointed out the documentation of the catalogs. In many cases, there is a simple mapping, say, between a function and pg_proc
. But in other cases, such as for an index, it's a bit more complicated. You will have to dig that information out of the documentation or perhaps the many examples of system catalog queries flying around.
The information schema is specified by the SQL standard. The principle there is, if you enter these DDL commands, then a query of the information schema should give these results. In many cases, there is again a simple mapping between objects and information schema views, but it's not straightforward in all cases. So maintaining a separate documentation of the mapping of this information would be cumbersome and probably useless. The principle is DDL in, information schema out, not what happens in the PostgreSQL system catalogs.
Ultimately, if you want to know whether a sequence would be found in a catalog table or an information schema view, you need to query these tables. The reality is too complicated for this to be simpler. (I think. Send a patch if you have a better idea.)
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.
Best Answer
Per the fine documentation, I think this might be what you're looking for.