Re: Block size with pg_dump? - Mailing list pgsql-sql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruce Momjian wrote:
> Jean-David Beyer wrote:
>>>>> The main question is, If I present pg_restore with a 65536-byte  
>>>>> blocksize
>>>>> and it is expecting, e.g., 1024-bytes, will the rest of each block get
>>>>> skipped? I.e., do I have to use dd on the way back too? And if so,  
>>>>> what
>>>>> should the blocksize be?
>>>> Postgres (by default) uses 8K blocks.
>>> That is true of the internal storage, but not of pg_dump's output
>>> because it is using libpq to pull rows and output them in a stream,
>>> meaning there is no blocking in pg_dumps output itself.
>>>
>> Is that true for both input and output (i.e., pg_restore and pg_dump)?
>> I.e., can I use dd to write 65536-byte blocks to tape, and then do nothing
>> on running pg_restore? I.e., that pg_restore will accept any block size I
>> choose to offer it?
> 
> Yes.
> 
Did not work at first:

...
pg_dump: dumping contents of table vl_ranks
51448+2 records in
401+1 records out
26341760 bytes (26 MB) copied, 122.931 seconds, 214 kB/s

So I suppose that worked. (This database just has some small initial tables
loaded. The biggest one is still empty.) But then

trillian:postgres[~]$ ./restore.db
pg_restore: [archiver] did not find magic string in file header
trillian:postgres[~]$

I fixed it by changing my backup script as follows:

$ cat backup.db
#!/bin/bash
#
#       This is to backup the postgreSQL database, stock.
#
DD=/bin/dd
DD_OPTIONS="obs=65536 of=/dev/st0"
MT=/bin_mt
MT_OPTIONS="-f /dev/st0 setblk 0"
PG_OPTIONS="--format=c --username=postgres --verbose"
PG_DUMP=/usr/bin/pg_dump

$PG_DUMP $PG_OPTIONS stock | $DD $DD_OPTIONS

and it still would not restore until I changed the restore script to this:

$ cat restore.db
#!/bin/bash

#       This is to restore database stock.
FILENAME=/dev/st0

DD=/bin/dd
DD_OPTIONS="ibs=65536 if=$FILENAME"
MT=/bin/mt
MT_OPTIONS="-f $FILENAME setblk 0"
PG_OPTIONS="--clean --dbname=stock --format=c --username=postgres --verbose"
PG_RESTORE=/usr/bin/pg_restore

$MT $MT_OPTIONS
$DD $DD_OPTIONS | $PG_RESTORE $PG_OPTIONS

It appears that I must read in the same blocksize as I wrote. My normal
backup program (BRU) can infer the blocksize from the first record, but
apparently pg_restore does not. But dd will read it if I tell it the size.
Hence the above.

The MT stuff is to tell the tape driver to accept variable block size so the
program that opens it can set it. DD can do that, but I infer that
pg_restore does not.


- -- .~.  Jean-David Beyer          Registered Linux User 85642. /V\  PGP-Key: 9A2FC99A         Registered Machine
241939./()\ Shrewsbury, New Jersey    http://counter.li.org^^-^^ 11:00:01 up 18 days, 14:22, 3 users, load average:
5.54,4.84, 4.45
 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFG0vQuPtu2XpovyZoRAlwcAKC5ApaGOoZrnHDUa5vgg9tx4jrqjwCeLfLV
oPLB1xCbJ0/WLYrg5/qVs2g=
=BkQ6
-----END PGP SIGNATURE-----