Thread: Problem with pg_dump / pg_restore
Hi everybody,
I have a problem with dump / restore commands on PostgreSQL:
pg_dump doesn't care about objects dependencies:
- I create a view V1 on a table and then another view V2 on the
same table
- I modify V1 to use V2
pg_dump doesn't detect that V1 is using V2. So the command batch
generated contains in first the creation of the view V1 which uses V2
and then the creation on the view V2 and it's impossible to restore a
database with this generated file.
My question is : how can I use pg_dump / pg_restore to generate a good
backup file ?
Thanks for your answers (and sorry for my poor english!!!)
Armel
Hi everybody,
I have already posted this message, but nobody answered to it.
I can't imagine that I'm the first one who have this problem, so
I post it again:
I have a problem with dump / restore commands on PostgreSQL:
pg_dump doesn't care about views dependencies:
- I create a view V1 on a table and then another view V2 on the
same table
- I modify V1 to use V2 and not directly the table
pg_dump doesn't detect that V1 is using V2. So the command batch
generated contains in first the creation of the view V1 which uses V2
and then the creation on the view V2 and it's impossible to restore a
database with this generated file.
My question is : how can I use pg_dump / pg_restore to generate a good
backup file ?
Postgres versions : 7.4
Thanks for your answers (and sorry for my poor english!!!)
Armel
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
Hi Mark,
Thanks for your answers: you're the first one to do it!
your anwers confirm things than I did'nt want to do : make my own structure
backup...
But it is very strange that pg_dump/pg_restore doesn't works in all cases.
I'll do my own backyp app... ;-(
Thanks
Armel (and again, sorry for my poor english!)
-----Message d'origine-----
De : pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]De la part de Mark Carew
Envoyé : vendredi 28 novembre 2003 23:12
À : pgsql-admin@postgresql.org
Objet : Re: [ADMIN] Problem with pg_dump / pg_restore
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Hi Armel,
Why not do a structure only dump to plain text and see if you can use
that as your start .sql ext file for 'psql -e .. etc'. Eliminates the
necessity to have an active client side data dictionary. BTW, your english
is fine.
Regards
markcarew@
magicwanddept.com.au
Hi Mark,
in fact, the problem is in the structure backup: this command doesn't care
about views dependencies. It take views in creation order to put its in
the .sql. So I have to reorder manually this view creations.
I made a view (another...) to look for dependencies. Maybe it's the good
way.
This view give, for each view oid, the dependencies:
SELECT dep.objid,
(SELECT pg_rewrite.ev_class
FROM pg_rewrite
WHERE (pg_rewrite.oid = dep.objid )) AS trueoid,
dep.refobjid
FROM pg_depend dep, pg_rewrite rew, pg_class "class"
WHERE dep.objid >= 30000
AND rew.oid = dep.objid
AND dep.refobjid = "class".oid
AND "class".relkind = 'v'
AND rew.ev_class <> dep.refobjid;
Armel
-----Message d'origine-----
De : pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]De la part de Mark Carew
Envoyé : lundi 1 décembre 2003 10:00
À : pgsql-admin@postgresql.org
Objet : Re: [ADMIN] Problem with pg_dump / pg_restore
Hi Armel,
Why not do a structure only dump to plain text and see if you can use
that as your start .sql ext file for 'psql -e .. etc'. Eliminates the
necessity to have an active client side data dictionary. BTW, your english
is fine.
Regards
markcarew@
magicwanddept.com.au
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)