restoring a database from a file copy

[Chris Fjell]

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?

[Bruno Wolff III]

On Fri, Jun 18, 2004 at 16:42:18 -0700,

Post by Chris Fjell
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[j***@ono.com]

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.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Post by j***@ono.com
pg_dump -Ft -b -U dbuser -h saturn dbsample > db.tar
pg_restore -Ft -v db.tar -d dbsample -U dbuser
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[j***@ono.com]

Post by Tom Lane

Post by j***@ono.com
pg_dump -Ft -b -U dbuser -h saturn dbsample > db.tar
pg_restore -Ft -v db.tar -d dbsample -U dbuser
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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[j***@ono.com]

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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by j***@ono.com
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]

Was there ever a resolution to this?

---------------------------------------------------------------------------

Post by Tom Lane

Post by j***@ono.com
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