duplicating a postgresql DB

23 Aug

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

  1. set the DB names
  2. create a dump (make sure to use a user having access rights to the db)
    TSTAMP=$(date +%F_%H-%M)
    pg_dump -f ${FILE_ORIG} —blobs —create ${ORIG_DB}
  3. adjust the DB name in the dump and check everything went ok (the diff should be just in CREATE, ALTER and \connect statements)
    sed “s/${ORIG_DB}/${NEW_DB}/g” ${FILE_ORIG} > ${FILE_NEW}
    vimdiff ${FILE_ORIG} ${FILE_NEW}
  4. 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
  5. load the new DB’s SQL-code into postgres
    psql -f ${FILE_NEW}
  6. 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…