Best way is to use files, which can be version-controlled in git or hg.
Here's my approach:
Create a schema_UP.sql file with the following content:
create table test
id serial primary key,
--- Grant privileges ON 'test' table TO 'test' user
GRANT select, insert, update, delete on test to test ;
Create a schema_DOWN.sql file with the following content:
drop table IF EXISTS test;
Execute either of the following as required as you evolve your initial/early stage schema.
- $ psql -f
schema_UP.sql -U postgres
- $ psql -f
schema_DOWN.sql -U postgres
As your database grow you may need to refactor the schema. To such end you may need to ALTER the database schema objects (tables, indexes, constraints, columns, triggers, etc). What I do is create chronologically named UP and DOWN scripts to
apply (UP) or
revert (DOWN) the changes.
In the case you want to constraint the
name column size to 100 char long, you might create and execute the following scripts:
With these scripts ready, you can can apply the changes via $ psql -f
<scriptname> -U postgres
Notice that to guarantee that every schema change is applied transactionally, I enclose all the instructions with a
end;. This way the script is atomic and it's either
Let me know if you need further assistance. =D