By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

pg_dump backup doesnt restore everything - things missing... restore howto required

P: 1
I have a cronned perl script that runs on a different server.

when I try to restore it to the server below things dont copy across correctly.

the target server is described below.

debian 4.0 - Linux testbox.jumboentertainment.com.au 2.6.18-4-686 #1 SMP Mon Mar 26 17:17:36 UTC 2007 i686 GNU/Linux

with the latest apt-get installation of postgres

PostgreSQL version 7.4.17 (With schemas)
/etc/postgresql/7.4/main/pg_hba.conf

I am creating a database that has sql_ascii encoding, owner is postges,
priviledges granted public to all, and I try to restore from the following script.

the backups from the other server, are the output of the following script,
which has been slightly modded for publication reasons - but runs fine.

#!/bin/sh

HOME=~root/
STAMP=`date "+%Y%m%d"`
TIME=`date "+%H-%M-%S"`
DATABASENAME=???????
BACKUPDIR=/var/lib/postgresql/7.4/backups
BACKUPFILE=${DATABASENAME}_backup_${STAMP}_${TIME} .custom

echo ----------------------------------------------------
echo BEGINNING galaxy DATABASE BACKUP
echo SCHEDULE @ 05 mins - 01, - 10, 12, 14, 16,
echo 17, 19, 21, 23,
echo "--- This being at - ${STAMP}_${TIME}" ---
echo ----------------------------------------------------
echo -----
echo START
echo -----
echo -------------
echo GETTING FILES
echo -------------
cd $BACKUPDIR
echo "Backing up $DATABASENAME to file $BACKUPFILE in directory $BACKUPDIR"
echo ---------------
echo CREATING BACKUP
echo ---------------
pg_dump -Fc -f -Z 9 $BACKUPFILE -U postgres $DATABASENAME
echo "Finished creating backup file. Exit status=$?"
#echo .
#echo ----------------------
#echo LOCATE FREAK SERVER AND SEND
#echo ----------------------
#echo .
#BACKUPFTPSERVER=10.0.0.9
#echo "Sending backup file to $BACKUPFTPSERVER"
#echo "bin
# cd jumbo
# put $BACKUPFILE
# bye
# " | ftp $BACKUPFTPSERVER
#echo "Finished sending backup file to $BACKUPFTPSERVER. Exit status=$?"
#echo .
#echo ----------------------
#echo LOCATE SERVER AND SEND
#echo ----------------------
#echo .
#BACKUPFTPSERVER=
#echo "Sending backup file to $BACKUPFTPSERVER"
#echo "bin
#cd jumbo
#put $BACKUPFILE
#bye
#" | ftp $BACKUPFTPSERVER
#echo "Finished sending backup file to $BACKUPFTPSERVER. Exit status=$?"
#
echo .
echo ----
echo DONE
echo ----
echo .
echo ------ FINISHED backup testbox.jumboentertainment.com.au ------


when I restore this database - its missing all kinds of things

-The public schema is present, but the catalog and sync schemas are missing
-The user groups are missing, and have to be copied via sql pad.
-The language isnt created,
-Triggers have to be manually copied into the
-sql pad of pgadmin III - then run on the testbox server, to copy the sql that
is missing - is there a better way to ensure that everything about this database
is backed up and restored - in one howto or something?

any help would be greatly appreciated - send email to development@jumboentertainment.com.au
Sep 19 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.