1 To dump mir database <dbname>:
2 pg_dumplo -a -d <dbname> -s /dumpdir/dir
3 pg_dump -c -D -f /dumpdir/<dbname>.sql <dbname>
4 this generates a dir with all blobs and a sql-file with all data.
6 To restore the database:
7 psql <dbname> < <dbname>.sql
8 pgdump_lo -i -d <dbname> -s /dumpdir/dir
10 Every once in a while (or make a script) postgresql database should
11 be "cleaned". See postgresql docs for commands VACUUM and VACUUM ANALYZE
13 Every once in a while (or make a script) postgresql database should
14 be "cleaned". See postgresql docs for commands VACUUM and VACUUM ANALYZE
16 We also have misc. perl scripts to merge Large Objects from other DB's.
17 these can be used as a basis to write your own custom scripts.
19 USER MANAGMENT IN POSTGRESQL
21 -- set owner of datbase
22 select * from pg_database;
23 select * from pg_user;
24 update pg_database set datdba=ID_FROM_PG_USER where datname=DATABASENAME
26 -- find all tables to grant privs / select is just building sql
29 select 'grant all on '||relname||' to "de_indy";'
31 where relname not like 'pg%'
34 -- alter table owner / select is just building sql
36 select 'alter table '||relname||' owner to "de_indy";'
38 where relname not like 'pg%'