Thread: check if database is correctly created

On Wed, Aug 15, 2007 at 10:08:36AM +0200, Alain Roger wrote:

> i would like to check (via PHP or C#) if my database has been correctly
> created.
> for that i use the following SQL :
> select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy';
> this i repeat till i check all tables.
>
> But how to check sequences, index, functions, and so on ?

Use psql with -E and then issue any variety of \d style
commands to find out what psql does to display indexes,
functions, sequences etc.

However, be aware that checking for the existence of an
appropriately named table/function doesn't give any
guarantuee about what they really *are*.

We do something similar during database upgrade migrations:
we calculate a hash over our tables with columns and column
datatypes (tables only as they hold the real data). Only if
the hash matches an expected value do we migrate (change)
the tables themselves. Views, functions, indexes,
constraints can all be re-run from scratch upon failure
without affecting the data in the tables.

http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?root=gnumed&view=markup

    and now

http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/sql/v5-v6/dynamic/gm-schema.sql?root=gnumed&view=markup

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

"Alain Roger" <raf.news@gmail.com> writes:
> i would like to check (via PHP or C#) if my database has been correctly
> created.
> for that i use the following SQL :
> select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy';
> this i repeat till i check all tables.
> But how to check sequences, index, functions, and so on ?

Instead of re-inventing the wheel, why not run "pg_dump -s" and diff its
output against that from a known good database?

I think there is a more sophisticated "database diff" tool out there,
too ... maybe on pgfoundry.

            regards, tom lane