duplicating a postgresql DB

e.g. for testing purposes before touching valuable data…

set the DB names

ORIG_DB=“orig_db_name”
NEW_DB=“new_db_name”

create a dump (make sure to use a user having access rights to the db)

TSTAMP=$(date +%F_%H-%M)
FILE_ORIG=”${ORIG_DB}_${TSTAMP}.sql”
pg_dump -f ${FILE_ORIG} —blobs —create ${ORIG_DB}

adjust the DB name in the dump and check everything went ok (the diff should be just in CREATE, ALTER and \connect statements)

FILE_NEW=”${NEW_DB}_based-on_${ORIG_DB}_${TSTAMP}.sql”
sed “s/${ORIG_DB}/${NEW_DB}/g” ${FILE_ORIG} > ${FILE_NEW}
vimdiff ${FILE_ORIG} ${FILE_NEW}

adjust permissions for the new db (add the new DB name to the desired user(s)) and reload the postgres config
```
sudo vim /etc/postgresql/8.4/main/pg_hba.conf
sudo /etc/init.d/postgresql-8.4 reload
```
load the new DB’s SQL-code into postgres
```
psql -f ${FILE_NEW}
```
to set identical permissions on the new DB, create a full dump using pg_dumpall, check the GRANT commands in the dump and apply them to the new DB as well…