4 To dump the database use pg_dump
6 pg_dump -Ft -b -u -f dump.tar [name_of_database]
8 To restore do the following
10 pg_restore -d [name_of_database] -U [dbuser] -Ft dump.tar
13 VACUUM and VACUUM ANALYZE
15 Every once in a while (or make a script) postgresql database should
16 be "cleaned". See postgresql docs for commands VACUUM and VACUUM ANALYZE
20 We also have misc. perl scripts to merge Large Objects from other DB's.
21 these can be used as a basis to write your own custom scripts.
24 USER MANAGMENT IN POSTGRESQL
26 -- set owner of datbase
27 select * from pg_database;
28 select * from pg_user;
29 update pg_database set datdba=ID_FROM_PG_USER where datname=DATABASENAME
31 -- find all tables to grant privs / select is just building sql
34 select 'grant all on '||relname||' to "de_indy";'
36 where relname not like 'pg%'
39 -- alter table owner / select is just building sql
41 select 'alter table '||relname||' owner to "de_indy";'
43 where relname not like 'pg%'