DUMP & RESTORE

To dump the database use pg_dump

  pg_dump -Ft -b -u -f dump.tar [name_of_database]

To restore do the following

  pg_restore -d [name_of_database] -U [dbuser] -Ft dump.tar 


VACUUM and VACUUM ANALYZE

Every once in a while (or make a script) postgresql database should
be "cleaned". See postgresql docs for commands VACUUM and VACUUM ANALYZE

MISC

We also have misc. perl scripts to merge Large Objects from other DB's.
these can be used as a basis to write your own custom scripts.


USER MANAGMENT IN POSTGRESQL

-- set owner of datbase
select * from pg_database;
select * from pg_user;
update pg_database set datdba=ID_FROM_PG_USER where datname=DATABASENAME

-- find all tables to grant privs / select is just building sql
-- to be exectued

select 'grant all on '||relname||' to "de_indy";'
from pg_class
where relname not like 'pg%'
order by relname;

-- alter table owner / select is just building sql
-- to be exectued
select 'alter table '||relname||' owner to "de_indy";'
from pg_class
where relname not like 'pg%'
order by relname;