Re: Problem with pg_dump / pg_restore - Mailing list pgsql-admin

Hi Armel,

 I have searched hi and low on the net to try to get answers regarding
pg_dump and pg_restore, specifically to be allow them to run with crontab on
the red hat servers that I look after as programmer and general IT dogs
body. I would appear from the general brevity of the replys ; that nobody
has the answers or if they do its a well garded trade secret. I hope I don't
get a knock on the door later tonight from the knowledge police but maybe my
experience can be of assistance to you.

The only way that I have been able to successfully backup and restore is to
adhere to the following strategy.

 As a programmer, I have developed a data dictionary that sits on the client
side in windows and is able to contain within its classes sufficient
information to be able to write out the complete sql statements for the
creation of all the components of my postgresql databases. This includes
tables, views, indices, foreign keys, sequences etc. I issue a "dbfmain PSQL
", at the windows command prompt,  and out pops a ???xxxPG.txt file specific
to the system I'm working on.

This sql containg text file has drop and create statements and is able to
create an empty database with sequences preset to starting numbers and all
tables and indices with no data. {It gets starting sequences values by
looking up existing tables (if they exist) obtaining the highest primary key
numeric value}

Thus I have an empty-database generation script that I can point at any
database with .....    psql -e ???xxx < ???xxxPG.txt     .....   as user
postgres .

Now I'll skip a few steps  and get to the nitty gritty. The following file
lives in the / directory of each file server, away from samba shared folders
and runs each night
automatically - courtesy of an entry in /etc/crontab. It backs up data but
not schema. This allows me (with my simplistic understanding of the whole
process) to restore the backed up data to other databases than the one from
which the data was extracted. I may even, heaven forbid, restore the data to
 the original database from which it came. This way the views that you
created remain intact, just the data is restored.

I have had a few glitches with sequences being out of wack but as they are
always used by me a distinct primary key values, the postgres data manager
soon straigtens out any anomalies and the sequences quickly grow beyond
issuing any contentious values.

--------------->>>>>>>>>>>>>>>>>>>>>

#!/bin/bash
#
# script to include in crontab for 0 1 * * * root /postgresback
# for backup and vacuum of the following two databases
#
# written by Mark Carew 28-11-2003
#
# Dump only the data
# pg_dump -a .... etc
#
# The data base structure is recreated by issuing
# as postgres user when in /mnt/samba
# psql -e hcc??? < HCC???PG.txt
# e.g. hcc??? == hcchel, hcckal, hcctest
# e.g. HCC???.txt == HCCHELPG.txt, HCCKALPG.txt
#
# to restore a database first recreate the structure as above
# then use pg_restore as follows
# pg_restore -a -Ft -d hcc??? HCC???.SQL
# this command only restores the data
# so that the database should be an empty structure
# as above (psql -e ....etc) makes it this way
#
/bin/su - postgres -c '/usr/bin/pg_dump -a -f  /mnt/samba/HCCHEL.SQL
 hcchel -Ft'
/bin/su - postgres -c '/usr/bin/pg_dump -a -f  /mnt/samba/HCCKAL.SQL
 hcckal -Ft'
#
#  reindex and pack
#
/bin/su - postgres -c '/usr/bin/vacuumdb hcchel'
/bin/su - postgres -c '/usr/bin/vacuumdb hcckal'
#
exit 0

--------------------------------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>

off topic

I suppose from the reading that I have done regarding 7.2 to 7.4 conversion
that I will have to write my 7.2 data back out to .dbf files so that I can
then
use my data dictionary windows program to convert this tempory .dbf data
back to 7.4 sql?

 Does anybody know if the -F{t,c,p} options all work in 7.4. I only seem to
be able to use -Ft successfully in 7.2 within pg_dump and pg_restore?

Regards
Mark Carew
Brisbane Australia
markcarew@magicwanddept.com.au