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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
