[HACKERS] pg_dump and blobs
Why doesn't pg_dump include blob by default? I understand that older pg_dump didn't deal with blobs, and blobs are now kind of obsolete in favor of BYTEA/TEXT, but blobs are every bit a part of a database. Perhaps only exclude blobs when -t is specified? Then -b is required to include blob. Otherwise, -b is implied. Also, it would be really nice if there were a way that pg_dumpall could include blobs. This is my biggest Postgres annoyance nowadays, as I've recently been bitten by this. I though we are already able to escape all octet range from '\\000' to '\\377'? -- dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump, formats blobs
At 04:10 14/04/01 +0200, Mathijs Brands wrote: Sorry about the false alarm. I was convinced restoring blobs didn't work correctly. The tar problem is now fixed in CVS. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_dump, formats blobs
Hi
I've been experimenting with 7.1rc4 for a couple of hours. I was messing with
blobs, and the new toast setup worked quite nicely. One thing I especially
liked was the fact that by having pg_dump create a dumpfile in the custom or
tar format, I could also backup all blobs in one go.
Unfortunately, practice was a bit different. Which is why I would like to know
if these functions are intended for general use.
A small log:
sol2:~$ uname -srm
SunOS 5.8 sun4u
sol2:~$ createdb blaat
CREATE DATABASE
sol2:~$ psql -c 'create table test(a oid)' blaat
CREATE
sol2:~$ psql -c "insert into test values(lo_import('/etc/hosts'))" blaat
INSERT 18761 1
sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
sol2:~$ pg_restore -l blaat.bk
;
; Archive created at Sat Apr 14 01:03:02 2001
; dbname: blaat
; TOC Entries: 4
; Compression: -1
; Dump Version: 1.5-2
; Format: CUSTOM
;
;
; Selected TOC Entries:
;
2; 18749 TABLE test mathijs
3; 18749 TABLE DATA test mathijs
4; 0 BLOBS BLOBS
sol2:~$ grep serv /etc/hosts
10.1.8.12 serv2.ilse.nl
10.1.8.10 serv0.ilse.nl
sol2:~$ grep serv blaat.bk
sol2:~$ pg_dump -b -Ft -f blaat.tar blaat
zsh: segmentation fault (core dumped) pg_dump -b -Ft -f blaat.tar blaat
sol2:~$ psql -c 'select version()' blaat
version
---
PostgreSQL 7.1rc4 on sparc-sun-solaris2.8, compiled by GCC 2.95.3
(1 row)
A backtrace reveals the following:
#0 0xff132e5c in strlen () from /usr/lib/libc.so.1
#1 0xff181890 in _doprnt () from /usr/lib/libc.so.1
#2 0xff183a04 in vsnprintf () from /usr/lib/libc.so.1
#3 0x2710c in ahprintf (AH=0x56cd0, fmt=0x430a8 "-- File: %s\n")
at pg_backup_archiver.c:1116
#4 0x2ee90 in _PrintExtraToc (AH=0x56cd0, te=0x5e838) at pg_backup_tar.c:305
#5 0x290e0 in _printTocEntry (AH=0x56cd0, te=0x5e838, ropt=0x681b0)
at pg_backup_archiver.c:1877
#6 0x25470 in RestoreArchive (AHX=0x56cd0, ropt=0x681b0)
at pg_backup_archiver.c:269
#7 0x2ffb8 in _CloseArchive (AH=0x56cd0) at pg_backup_tar.c:840
#8 0x24f68 in CloseArchive (AHX=0x56cd0) at pg_backup_archiver.c:136
#9 0x15128 in main (argc=6, argv=0xffbefcac) at pg_dump.c:1114
What happens is that in line 305 of pg_backup_tar.c, ahprintf is handed a NULL
pointer.
300 static void
301 _PrintExtraToc(ArchiveHandle *AH, TocEntry *te)
302 {
303 lclTocEntry *ctx = (lclTocEntry *) te-formatData;
304
305 ahprintf(AH, "-- File: %s\n", ctx-filename);
306 }
Could this be caused by the fact that IMHO blobs aren't dumped correctly?
Regards,
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_dump, formats blobs
At 01:14 14/04/01 +0200, Mathijs Brands wrote: ... sol2:~$ pg_dump -b -Fc -f blaat.bk blaat sol2:~$ pg_restore -l blaat.bk ... ; ; Archive created at Sat Apr 14 01:03:02 2001 ... This all looks fine. sol2:~$ pg_dump -b -Ft -f blaat.tar blaat zsh: segmentation fault (core dumped) pg_dump -b -Ft -f blaat.tar blaat This is less good. It's caused by the final part of TAR output, which also dumps a plain SQL script for reference (not actually ever used by pg_restore). I will fix this in CVS; ctx-filename is set to null for this script, and my compiler outputs '(null)', which is very forgiving of it. Could this be caused by the fact that IMHO blobs aren't dumped correctly? Is there some other problem with BLOBs that you did not mention? AFAICT, this is only a problem with TAR output (an will be fixed ASAP). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump, formats blobs
On Sat, Apr 14, 2001 at 11:44:18AM +1000, Philip Warner allegedly wrote:
At 01:14 14/04/01 +0200, Mathijs Brands wrote:
...
sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
sol2:~$ pg_restore -l blaat.bk
...
;
; Archive created at Sat Apr 14 01:03:02 2001
...
This all looks fine.
Hmm, I can only agree.
sol2:~$ cksum postgresql-7.1rc4.tar.gz
615403298088934 postgresql-7.1rc4.tar.gz
sol2:~$ dropdb blaat
DROP DATABASE
sol2:~$ createdb blaat
CREATE DATABASE
sol2:~$ psql -c 'create table test(a oid)' blaat
CREATE
sol2:~$ psql -c "insert into test
values(lo_import('/export/home/mathijs/postgresql-7.1rc4.tar.gz'))" blaat
INSERT 22753 1
sol2:~$ pg_dump -b -Fc -f blaat.bk blaat
sol2:~$ psql -c 'drop table test ; vacuum' blaat
VACUUM
sol2:~$ pg_restore -d blaat blaat.bk
sol2:~$ psql -c "select lo_export(test.a, '/export/home/mathijs/testfile') from test"
blaat
lo_export
---
1
(1 row)
sol2:~$ cksum testfile
61540329 8088934 testfile
sol2:~$ pg_restore -l blaat.bk
;
; Archive created at Sat Apr 14 03:59:02 2001
; dbname: blaat
; TOC Entries: 4
; Compression: -1
; Dump Version: 1.5-2
; Format: CUSTOM
;
;
; Selected TOC Entries:
;
2; 18792 TABLE test mathijs
3; 18792 TABLE DATA test mathijs
4; 0 BLOBS BLOBS
I couldn't get blobs to be restored correctly (must've been doing
something wrong). When something doesn't work, never question your
own methods ;)
sol2:~$ pg_dump -b -Ft -f blaat.tar blaat
zsh: segmentation fault (core dumped) pg_dump -b -Ft -f blaat.tar blaat
This is less good. It's caused by the final part of TAR output, which also
dumps a plain SQL script for reference (not actually ever used by
pg_restore). I will fix this in CVS; ctx-filename is set to null for this
script, and my compiler outputs '(null)', which is very forgiving of it.
It's more likely that your C library is more forgiving (ie. Open Source OS?).
Could this be caused by the fact that IMHO blobs aren't dumped correctly?
Is there some other problem with BLOBs that you did not mention? AFAICT,
this is only a problem with TAR output (an will be fixed ASAP).
Yeah, they're not fool proof ;)
Sorry about the false alarm. I was convinced restoring blobs
didn't work correctly.
Regards,
Mathijs
--
$_='while(read+STDIN,$_,2048){$a=29;$c=142;if((@a=unx"C*",$_)[20]48){$h=5;
$_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$/;$d=
unxV,xb25,$_;$b=73;$e=256|(ord$b[4])9|ord$b[3];$d=$d8^($f=($t=255)($d
12^$d4^$d^$d/8))17,$e=$e8^($t($g=($q=$e147^$e)^$q*8^$q6))9
,$_=(map{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])110;$t
^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271))
[$_]^(($h=8)+=$f+(~$g$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pack+/g;eval
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
