Обсуждение: Lost my blobs and can't restore.
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
Jerry LeVan <jerry.levan@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
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