Thread: restoring a database from a file copy
Can a database system be restored just from the data under the installation directory? I backed-up my database using "rsync /var/lib/pgsql <dest_directory>" Then I upgraded my rpms from 7.3.4 to 7.4. Then I tried to start the server as usual and it complained about version conflict (of course). I removed the 7.4 rpms and went back to 7.3.4 and restared server. My data are gone - only the template tables are listed. I copied back from my file backup and this, much to my horror, did not restore my database - it's as if the tables are all gone, but the file sizes indicate the data are there. Any way to recreate a catalog (or whatever) from the data? The server seemed to be down when I copied - can't see what I could have done to corrupt the files under /var/lib/pgsql/data/ I couldn't do a pg_dump due to bad OIDs - this was the motivation for the upgrade - so I seem to have lost everything. Any thoughts? -- Chris Fjell
On Fri, Jun 18, 2004 at 16:42:18 -0700, Chris Fjell <cfjell@bcgsc.ca> wrote: > Can a database system be restored just from the data under the > installation directory? Only if the postmaster has been shut down. For live backups you should be using pg_dumpall.
Hello, I have two servers running postgres 7.3 (Server Jupiter), and 7.4.3 (Sever Saturn), in my office. I have a master database in Jupiter, that contains a DB with images, OIDs (lo type). I'm trying to backup the database from Jupiter and restore it in Saturn. I do this, from Saturn: pg_dump -Ft -b -U dbuser -h saturn dbsample > db.tar Then, I create the user and database in Saturno, and perform this: pg_restore -Ft -v db.tar -d dbsample -U dbuser But, pg_restore stops, showing this message: pg_restore: no OID type columns in table photos1 pg_restore: fixing up large-object cross-reference for "inventory" pg_restore: no OID type columns in table inventory pg_restore: fixing up large-object cross-reference for "photos2" pg_restore: fixing large object cross-references for photos2.photo pg_restore: fixing up large-object cross-reference for "parts" pg_restore: fixing large object cross-references for parts.qltymemdoc pg_restore: [archiver (db)] error while updating column "qltymemdoc" of table "parts": ERROR: large object 609937 does not exist pg_restore: *** aborted because of error But this large object, exists. I "untar" the "db.tar" file, and there is a file called "blob_609937.dat", and it appears in the file "blobs.toc". Where is the problem ? How can I do a safety reliable backup in postgres, with databases that contains "lo" columns ? I have tried with diferents options (dumping and restoring), but always appear the same problem. This have seen this problem in databases using large objects (lo type), since version 7.3. It is a critical situation, because I have seen those problems in a production system, .... what will happend if the database would crack ? Thanks.
juanmime@ono.com writes:
> I do this, from Saturn:
> pg_dump -Ft -b -U dbuser -h saturn dbsample > db.tar
> Then, I create the user and database in Saturno, and perform this:
> pg_restore -Ft -v db.tar -d dbsample -U dbuser
> But, pg_restore stops, showing this message:
> pg_restore: fixing large object cross-references for parts.qltymemdoc
> pg_restore: [archiver (db)] error while updating column "qltymemdoc" of
> table "parts": ERROR: large object 609937 does not exist
> pg_restore: *** aborted because of error
I think what is happening is that dbuser is not a superuser (correct?)
and therefore is unable to disable triggers during the restore. But
you have to disable the lo_manage trigger to avoid errors, because
lo_manage will think it has to clean up the blob references in the
existing data.
In short: if you are using the LO type then blob restores have to be
done as superuser. I suppose this oughta be documented someplace...
regards, tom lane
>> I do this, from Saturn: >> pg_dump -Ft -b -U dbuser -h saturn dbsample > db.tar >> Then, I create the user and database in Saturno, and perform this: >> pg_restore -Ft -v db.tar -d dbsample -U dbuser >> But, pg_restore stops, showing this message: > >> pg_restore: fixing large object cross-references for parts.qltymemdoc >> pg_restore: [archiver (db)] error while updating column "qltymemdoc" of >> table "parts": ERROR: large object 609937 does not exist >> pg_restore: *** aborted because of error > >I think what is happening is that dbuser is not a superuser (correct?) >and therefore is unable to disable triggers during the restore. But >you have to disable the lo_manage trigger to avoid errors, because >lo_manage will think it has to clean up the blob references in the >existing data. > >In short: if you are using the LO type then blob restores have to be >done as superuser. I suppose this oughta be documented someplace... The problem continues I perform this: pg_restore -Ft -v db.tar -d test -U postgres -S postgres --disable-triggers And this is the result: pg_restore: fixing large object cross-references for parts.qltymemdoc pg_restore: [archiver (db)] error while updating column "qltymemdoc" of table "parts": ERROR: large object 609937 does not exist pg_restore: *** aborted because of error ¿ Why ? I suppose that the triggers are disabled (-S postgres --disable-triggers), and I don't understand why pg_restore reports that "large object 609937 does not exist". Thank you.
Thanks you very much Tom, Here is the solution to the problem: First: We have to restore (only) the structure (schema) of the database pg_restore -Ft -v -s db.tar -d database_name -U postgres -S postgres --disable-triggers Second: Restore the datas pg_restore -Ft -v -a db.tar -d database_name -U postgres -S postgres --disable-triggers I think, that in the first step, we can drop "-S postgres --disable-triggers" options. Thanks.
juanmime@ono.com writes:
> Here is the solution to the problem:
> First: We have to restore (only) the structure (schema) of the database
> pg_restore -Ft -v -s db.tar -d database_name -U postgres -S postgres --disable-triggers
> Second: Restore the datas
> pg_restore -Ft -v -a db.tar -d database_name -U postgres -S postgres --disable-triggers
[ scratches head... ] Definitely seems that there is a bug in there
somewhere, if you have to do it that way. I'll take a look later ...
regards, tom lane
Was there ever a resolution to this? --------------------------------------------------------------------------- Tom Lane wrote: > juanmime@ono.com writes: > > Here is the solution to the problem: > > First: We have to restore (only) the structure (schema) of the database > > pg_restore -Ft -v -s db.tar -d database_name -U postgres -S postgres --disable-triggers > > Second: Restore the datas > > pg_restore -Ft -v -a db.tar -d database_name -U postgres -S postgres --disable-triggers > > [ scratches head... ] Definitely seems that there is a bug in there > somewhere, if you have to do it that way. I'll take a look later ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073