Thread: BLOBS (ODBC lo object) and pg_restore
Hi all, I've run into a snag with using pg_restore. My database contains two tables with the lo datatype they use with Visual Basic client side and the ODBC driver. I have a cron job running every night to backup my database thus: DB="somedb" SCHEMA="/var/db/backup/schema.sql" DATA="/var/db/backup/data.tar" PGDUMP="/usr/local/pgsql/bin/pg_dump" $PGDUMP -f $SCHEMA -F p -C -o -s -h localhost -p 5432 -U postgres $DB chmod 700 $SCHEMA $PGDUMP -f $DATA -Ft -C -o -a -b -h localhost -p 5432 -U postgres $DB chmod 700 $DATA This is then copied onto another file server for offsite backups. I can't remember why but I decided to dump the schema seperately to the data. I've never had to do a restore so far. My current production DB is 7.3.0 and I have been playing with 7.3.2 with the view to upgrading however I want to test in a dev environment before slapping the 7.3.2 binary on my production data. When I create the new database in a new cluster, reload the schema and then reload the data it always dies at the BLOB recreation. The commands I issue to load the data (using port 5431 for the test DB) are: ./createdb -p5431 somedb ./psql -p5431 -dsomedb < /var/db/backup/schema.sql ./pg_restore -p5431 -dsomedb -Ft -N -X disable-triggers /var/db/backup/data.tar The error I get is: ERROR: Relation "BLOBs" does not exist I'm presuming that pg_dump writes the blob table of contents incorrectly however I'm not sure... All my BLOB (lo) objects are defined in a non public schema however postgres stores lo objects in pg_largeobjects anyway so maybe its something to do with the OID/lo linking between my two BLOB tables and pg_largeobjects... I'm probably way out here but does anyone have any pointers on getting BLOB/lo loading to work? Thanks in advance, Jason
Jason Godden <jasongodden@optushome.com.au> writes:
> ./pg_restore -p5431 -dsomedb -Ft -N -X disable-triggers
> /var/db/backup/data.tar
> ERROR: Relation "BLOBs" does not exist
Looks like a bug in pg_restore. For the moment I'd suggest not using
-X disable-triggers while restoring blobs ...
regards, tom lane
Jason Godden <jasongodden@optushome.com.au> writes:
> ./pg_restore -p5431 -dsomedb -Ft -N -X disable-triggers
> /var/db/backup/data.tar
> ERROR: Relation "BLOBs" does not exist
Here's the patch if you need it.
regards, tom lane
*** src/bin/pg_dump/pg_backup_archiver.c~ Sat Feb 1 17:07:14 2003
--- src/bin/pg_dump/pg_backup_archiver.c Sun Mar 9 14:36:40 2003
***************
*** 293,299 ****
* Maybe we can't do BLOBS, so check if this node is
* for BLOBS
*/
! if ((strcmp(te->desc, "BLOBS") == 0) && !_canRestoreBlobs(AH))
{
ahprintf(AH, "--\n-- SKIPPED \n--\n\n");
--- 293,300 ----
* Maybe we can't do BLOBS, so check if this node is
* for BLOBS
*/
! if ((strcmp(te->desc, "BLOBS") == 0) &&
! !_canRestoreBlobs(AH))
{
ahprintf(AH, "--\n-- SKIPPED \n--\n\n");
***************
*** 445,450 ****
--- 446,455 ----
if (!ropt->dataOnly || !ropt->disable_triggers)
return;
+ /* Don't do it for the BLOBS TocEntry, either */
+ if (te && strcmp(te->desc, "BLOBS") == 0)
+ return;
+
oldUser = strdup(AH->currUser);
oldSchema = strdup(AH->currSchema);
***************
*** 506,511 ****
--- 511,520 ----
/* This hack is only needed in a data-only restore */
if (!ropt->dataOnly || !ropt->disable_triggers)
+ return;
+
+ /* Don't do it for the BLOBS TocEntry, either */
+ if (te && strcmp(te->desc, "BLOBS") == 0)
return;
oldUser = strdup(AH->currUser);
I'd like to select a random record from a table, but I'd like to weight it.
For example, let's say I have a table like:
data1 data2 weight
----------------------------------------
1 2 3.44
3 4 0.94
5 6 1.00
7 8 2.00
I would like to select (data1,data2) from a random record, but I'd like (1,2)
to be 3.44 times as likely as (5,6). I would also like (7,8) to be 2 times as
likely as (5,6), and (1,2) to be 1.72 times as likely as (7,8).
Does that make mathematical sense? I think I can do it if weight is an integer
(but it would be a really bad hack), but I can't think of a nice way with
fractions.
Thanks,
Jeff Davis
On Sun, 9 Mar 2003 13:40:30 -0800, Jeff Davis
<jdavis-pgsql@empires.org> wrote:
>I would like to select (data1,data2) from a random record, but I'd like (1,2)
>to be 3.44 times as likely as (5,6). I would also like (7,8) to be 2 times as
>likely as (5,6), and (1,2) to be 1.72 times as likely as (7,8).
If you had
data1 data2 weight minw maxw
--------------------------------------
1 2 3.44 0.00 3.44
3 4 0.94 3.44 4.38
5 6 1.00 4.38 5.38
7 8 2.00 5.38 7.38
and an immutable wrapper function around random(), you could
SELECT data1, data2
FROM t
WHERE minw < myrandom(7.38) AND myrandom(7.38) <= maxw;
Make sure myrandom() never returns 0.00 or set minw to something less
than 0.00 in the first row.
Servus
Manfred
Hi Tom,
Thanks for this - I'm really impressed with the response. In the end I used
lo_export and PL/PGSQL procedure to dump all the files (60000 of them!) to
disk and I bz2 that up to backup each night. I'll revisit pg_restore/pg_dump
when I get a chance.
I realise that you guys probably have plenty of developers but I'm wanting to
get my C/C++ back up to scratch again and I love Postgres - anything that you
guys need someone to sink their teeth into that I could have a shot at?
Cheers,
Jason
On Monday 10 March 2003 06:41, Tom Lane wrote:
> Jason Godden <jasongodden@optushome.com.au> writes:
> > ./pg_restore -p5431 -dsomedb -Ft -N -X disable-triggers
> > /var/db/backup/data.tar
> > ERROR: Relation "BLOBs" does not exist
>
> Here's the patch if you need it.
>
> regards, tom lane
>
> *** src/bin/pg_dump/pg_backup_archiver.c~ Sat Feb 1 17:07:14 2003
> --- src/bin/pg_dump/pg_backup_archiver.c Sun Mar 9 14:36:40 2003
> ***************
> *** 293,299 ****
> * Maybe we can't do BLOBS, so check if this node is
> * for BLOBS
> */
> ! if ((strcmp(te->desc, "BLOBS") == 0) && !_canRestoreBlobs(AH))
> {
> ahprintf(AH, "--\n-- SKIPPED \n--\n\n");
>
> --- 293,300 ----
> * Maybe we can't do BLOBS, so check if this node is
> * for BLOBS
> */
> ! if ((strcmp(te->desc, "BLOBS") == 0) &&
> ! !_canRestoreBlobs(AH))
> {
> ahprintf(AH, "--\n-- SKIPPED \n--\n\n");
>
> ***************
> *** 445,450 ****
> --- 446,455 ----
> if (!ropt->dataOnly || !ropt->disable_triggers)
> return;
>
> + /* Don't do it for the BLOBS TocEntry, either */
> + if (te && strcmp(te->desc, "BLOBS") == 0)
> + return;
> +
> oldUser = strdup(AH->currUser);
> oldSchema = strdup(AH->currSchema);
>
> ***************
> *** 506,511 ****
> --- 511,520 ----
>
> /* This hack is only needed in a data-only restore */
> if (!ropt->dataOnly || !ropt->disable_triggers)
> + return;
> +
> + /* Don't do it for the BLOBS TocEntry, either */
> + if (te && strcmp(te->desc, "BLOBS") == 0)
> return;
>
> oldUser = strdup(AH->currUser);
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html