Skip site navigation (1) Skip section navigation (2)

database development

From: Lee Doolan <rldoolanREMOVE(at)pacbell(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: database development
Date: 2001-10-21 22:52:17
Message-ID: 7oadyky5b2.fsf@stkitts.pacbell.net (view raw or flat)
Thread:
Lists: pgsql-admin


I am developing a website which has a postgres database as one
  of its components.  During development, it is not uncommon to
  have to add a new attribute to a table, nor is it uncommon to
  add an entirely new table to the schema when changes are made
  to the system design --usually subsequent to a meeting
  attended by the sales and mgt folks, but I shouldn't get
  started on _that_.

In order to maintain the database schema, I started out by using
  a script like this one which I kept stored in my source
  repository:

    SCRIPT>    #!/bin/bash
    SCRIPT>                            [. . .]
    SCRIPT>    ########################################################################
    SCRIPT>    #				   A S _ A N O N _ U S E R _ S E Q					   #
    SCRIPT>    ########################################################################
    SCRIPT>    #  this sequence is incremented every time a new anonymous user is
    SCRIPT>    #  created in the database
    SCRIPT>    echo "------------------------------------------------------------------------"
    SCRIPT>    echo "Creating as_anon_user_seq"
    SCRIPT>    psql -U "allseer"  $ALLSEER_DBNAME <<-EOF
    SCRIPT>        create sequence as_anon_user_seq;
    SCRIPT>    EOF
    SCRIPT>                            [. . .]
    SCRIPT>
    SCRIPT>    ########################################################################
    SCRIPT>    #						A S _ P R O J E C T S						   #
    SCRIPT>    ########################################################################
    SCRIPT>    echo "------------------------------------------------------------------------"
    SCRIPT>    echo "Creating as_projects"
    SCRIPT>    psql -U "allseer"  $ALLSEER_DBNAME <<-EOF
    SCRIPT>
    SCRIPT>        create table as_projects (
    SCRIPT>            proj_id serial PRIMARY KEY,
    SCRIPT>            proj_name text UNIQUE,
    SCRIPT>            proj_url text,
    SCRIPT>            proj_descr text,
    SCRIPT>
    SCRIPT>            created timestamp,
    SCRIPT>            modified timestamp default now());
    SCRIPT>
    SCRIPT>        grant all on as_projects to public;
    SCRIPT>
    SCRIPT>    EOF
    SCRIPT>                            [. . .]

Then, any time I wanted to change the schema, I would change the
  script, rerun it and, voila! an improved database.

Somewhere along the line, I decided to stop maintaining the
  script and to use pg_dump and pg_restore to maintain both the
  schema and the data.  This decision is one which I sometimes
  regret but, hey, I could get back to the shell script form in
  a few hours if I really wanted to.

My question is this: what techniques do other developers use in
  these circumstances?

-lee



Responses

pgsql-admin by date

Next:From: Sudheer PalaparambilDate: 2001-10-22 01:26:24
Subject: REMOTE PROCEUDRE CALLS
Previous:From: Stephan SzaboDate: 2001-10-20 01:01:33
Subject: Re: [repost] pg_restore doesn't work with custom format?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group