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;
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 R06_12_22.trkchamgeo;
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,
will print release name, and
will print release schema name.
How do I see existing schemas?
From psql prompt, type
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
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';
- 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
schema to another, that's why
mdbSchemaCopy <DB name> <Schema A> <Schema B>
executable in MIPP offline software release was created. It will
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
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.
Lebedev - Dec 25, 2006