Re: Schema as versioning strategy

On Wed, 2007-04-25 at 12:47 -0400, Owen Hartnett wrote:
> I want to "freeze" a snapshot of the database every year (think of
> end of year tax records). However, I want this frozen version (and
> all the previous frozen versions) available to the database user as
> read-only.

First, I'd rename the current-year schema to a more meaningful name (eg,
taxes2006). Each year you could do a schema-only dump of the current
year, tweak the schema name in the dump to reflect the new year, and
restore just the schema into the same database. The benefit of this
approach is that the data stay in place (ie, you don't dump public and
restore into a new schema). Conceptually, something as simple as the
following pipe might suffice to dump, rename, and restore into a new
schema:
$ pg_dump -s -n taxes2006 | sed -e 's/taxes2006/taxes2007/g' | psql
-qa
(This is a little dangerous because I've assumed that the string
'taxes2006' occurs only as a schema name. I've also assumed Unix/Linux
and I have no idea what you'd do on a windows box.)

PostgreSQL doesn't have a read-only mode per se. The closest you can get
is to write a script to revoke insert/update/delete on all tables in an
archived schema; that's also pretty easy:
$ psql -Atc "select 'REVOKE INSERT,UPDATE,DELETE FROM '||
nspname||'.'||
relname||' FROM someuser;' from pg_class C join pg_namespace N on
C.relnamespace=N.oid and N.nspname='taxes2006' WHERE C.relkind='r'"
\
| psql -qa
(In general, you should reduce everything to a one-liner.)

One of the advantages of having archived schemas in a single database is
that you'll be able to write queries that involve multiple years. You
wouldn't be able to do that (easily*) if you archived the full database.

-Reece

* This is where David Fetter will mention dblink.

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0