Lost my blobs and can't restore.

[Jerry LeVan]

Hi,

I am just getting into large objects and bytea "stuff".
I created a small db called pictures and loaded some large
objects and then tried to do a restore.

Here is how I got the dump.

pg_dump -Fc -b pictures > /Users/jerry/desktop/db.comp

Here is the archive listing. Note I edited just to restore
the blobs.
;
; Archive created at Fri Aug 6 15:40:56 2004
; dbname: pictures
; TOC Entries: 14
; Compression: -1
; Dump Version: 1.7-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
;
;
; Selected TOC Entries:
;
;11; 21906 FUNC PROCEDURAL LANGUAGE plpgsql_call_handler() postgres
;10; 21907 PROCEDURAL LANGUAGE plpgsql
;4; 2200 ACL public postgres
;5; 21984 TABLE pics levan
;6; 22024 TABLE person levan
;12; 21984 TABLE DATA pics levan
;13; 22024 TABLE DATA person levan
14; 0 BLOBS BLOBS
;9; 21990 CONSTRAINT pics_ident_key levan
;7; 21982 SEQUENCE SET pics_ident_seq levan
;8; 22022 SEQUENCE SET person_id_seq levan
;3; 2200 COMMENT SCHEMA public postgres

toc 14 looks suspicious.

Here is what I did to restore:
pg_restore -L db.lst db.comp

This is the result of the command.
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

--
-- Data for TOC entry 14 (OID 0)
-- Name: BLOBS; Type: BLOBS; Schema: -; Owner:
-- Data Pos: 4843111
--

--
-- SKIPPED
--

pg_restore: [archiver] WARNING: skipping large-object restoration
[macjerry:~/desktop]$ pg_restore -L db.lst db.comp
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

--
-- Data for TOC entry 14 (OID 0)
-- Name: BLOBS; Type: BLOBS; Schema: -; Owner:
-- Data Pos: 4843111
--

--
-- SKIPPED
--

pg_restore: [archiver] WARNING: skipping large-object restoration

I even deleted the large objects via \lo_unlink but get the same
response.

OK, what am I doing wrong?

Jerry
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Jerry LeVan <je*********@eku.edu> writes:

pg_restore: [archiver] WARNING: skipping large-object restoration OK, what am I doing wrong?

You have to run pg_restore with a direct connection to a database. If
we could implement blob restoration with a noninteractive script, then
pg_dump would just emit scripts to do it ...

regards, tom lane

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

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

[Jerry LeVan]

Hmmm, still don't know if pg_restore will add the comments back....

Anyway I was able to recover my comments by sql that looks like

insert into pg_description values(22160 , 16404, 0, 'robasnowman.jpg')

the 22160 is the oid that represented the picture 'robasnowman.jpg'
16404 is the oid that identifies large objects,
0 is a subclass oid currently 0
'robasnowman.jpg' or whatever you want is the 'comment'

You have to be superuser to write the table (I think)

Fortunately I am getting ready to release the next version 1.3.3 of
BiggerSQL , it has the capability to display images stored in oids
and bytea fields so I was able to make a reasonable comment.

Jerry
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org