Re: Schema as versioning strategy

From: Reece Hart <reece(at)harts(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Cc: Owen Hartnett <owen(at)clipboardinc(dot)com>
Subject: Re: Schema as versioning strategy
Date: 2007-04-25 23:00:59
Message-ID: 1177542059.8204.34.camel@snafu.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-04-25 23:32:06 Re: Feature request - have postgresql log warning when new sub-release comes out.
Previous Message Ron Mayer 2007-04-25 22:52:17 Feature request - have postgresql log warning when new sub-release comes out.

Browse pgsql-hackers by date

  From Date Subject
Next Message Gregory Stark 2007-04-25 23:11:49 Re: database size estimates
Previous Message Jonathan Vanasco 2007-04-25 22:45:21 Re: Schema as versioning strategy