Using Postgres Schemas

Who needs to know about this?

Many software users do not need to know anything about this. You need to be aware of what is going on if

You will be maintaining calibration DB tables
Creating new tagged releases

What are schemas?

You can think of schemas as directories or namespaces where database relations live. Given our usage of postgres, this only involves tables, views, and indexes. Most important feature (for us) is that different schemas can have tables with identical names.
To select a table from a particular schema, prepend schema name to table name like

SELECT * FROM development.trkchamgeo;
SELECT * FROM R06_12_22.trkchamgeo;

If schema name is omitted, the first table in namespaces listed in search_path variable will be used. That is, if I have tables A.T and B.T and my search_path=B,A, then

SELECT * FROM T;

will default to B.T.
Database on mippsrv's are setup to default to development schema. No other schema will be included into search_path.

Why do we use schemas?

MIPP uses schemas to create snap-shots of database for tagged releases. This allows one to use a tagged offline software release and the corresponding state of DB for processing at any time in the future.

What is the naming convention?

MIPP releases are defined as RYY.MM.DD, with potential trailing '+' signs if the release was updated on the same day. Corresponding database schemas are named RYY_MM_DD and '+' signs replaced by 'p'.
Once you setup to use a release,

mipp-release

will print release name, and

mdbSchema

will print release schema name.

How do I see existing schemas?

From psql prompt, type

\dn

Alternatively PSQL command which is useful in scripts or executables:

SELECT nspname from pg_namespace;

How do I see tables in a schema?

Postgres prompt shortcut

\dt

will only show the first table from the search_path variable. That is, if I have development.cmapcham and R06_12_22.cmapcham, and my search_path=development, only the former table will be displayed.
PSQL command to look at all tables is

SELECT schemaname,tablename FROM pg_tables;

or a slightly more useful call

SELECT tablename FROM pg_tables WHERE schemaname='development';

Available Tools

PSQL command to copy table T from namespace A to namespace B is
```
SELECT * INTO B.T FROM A.T;
```
As far as I know, there is no automated way to copy all tables from one schema to another, that's why
```
mdbSchemaCopy <DB name> <Schema A> <Schema B>
```
executable in MIPP offline software release was created. It will copy all tables from schema A to schema B, creating schema B if necessary, and will not overwrite any of the tables that might already be there.
Most useful for making a new DB schema when creating a new tagged release is
```
make-db-schema <base schema> <new schema>
```
script, which will loop through all necessary databases and setup a new schema on the basis of an existing one.

Andre Lebedev - Dec 25, 2006