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

When I make a backup of a database, I put the output file directly on
magnetic tape; i.e., my command looks like this:

pg_dump --file=/dev/st0 ....

This way I do not have to worry if the total backup exceeds the size of a
file system, and it saves me the trouble of copying it to the tape as a
separate step. My current tapes will hold 20 GBytes raw or 40GBytes if I
enable hardware compression (assuming 2:1 compression happens). Now it says
in the documentation that if I use format c it will compress the data in
software, so I doubt the hardware compression will do much.

I do not know what blocksize pg_dump uses, or if it insists on a particular
blocksize on input.

Now my tape drive will work with any blocksize, but prefers 65536-byte
blocks. I do not see any options for this in pg_dump, but I could pipe the
output of pg_dump through dd I suppose to make any blocksize I want.

On the way back, likewise I could pipe the tape through dd before giving it
to pg_restore.

Does pg_dump care what blocksize it gets? If so, what is it?

- --
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 17:20:01 up 17 days, 20:42, 5 users, load average: 5.12, 5.26, 5.21
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFG0fITPtu2XpovyZoRAouwAKCTEour7jbi3uKWmEjerO M3U51xKQCeKYrQ
6jbamlqvTvH04jD7oRbTAKY=
=piNw
-----END PGP SIGNATURE-----

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

http://archives.postgresql.org

Jean-David Beyer wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> When I make a backup of a database, I put the output file directly on
> magnetic tape; i.e., my command looks like this:
>
> pg_dump --file=/dev/st0 ....
>
> This way I do not have to worry if the total backup exceeds the size of a
> file system, and it saves me the trouble of copying it to the tape as a
> separate step. My current tapes will hold 20 GBytes raw or 40GBytes if I
> enable hardware compression (assuming 2:1 compression happens). Now it says
> in the documentation that if I use format c it will compress the data in
> software, so I doubt the hardware compression will do much.
>
> I do not know what blocksize pg_dump uses, or if it insists on a particular
> blocksize on input.
>
> Now my tape drive will work with any blocksize, but prefers 65536-byte
> blocks. I do not see any options for this in pg_dump, but I could pipe the
> output of pg_dump through dd I suppose to make any blocksize I want.
>
> On the way back, likewise I could pipe the tape through dd before giving it
> to pg_restore.
>
> Does pg_dump care what blocksize it gets? If so, what is it?

I assume you could pipe pg_dump into dd and specify the block size in
dd.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

http://www.postgresql.org/docs/faq

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

Bruce Momjian wrote:
> Jean-David Beyer wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> When I make a backup of a database, I put the output file directly on
>> magnetic tape; i.e., my command looks like this:
>>
>> pg_dump --file=/dev/st0 ....
>>
>> This way I do not have to worry if the total backup exceeds the size of a
>> file system, and it saves me the trouble of copying it to the tape as a
>> separate step. My current tapes will hold 20 GBytes raw or 40GBytes if I
>> enable hardware compression (assuming 2:1 compression happens). Now it says
>> in the documentation that if I use format c it will compress the data in
>> software, so I doubt the hardware compression will do much.
>>
>> I do not know what blocksize pg_dump uses, or if it insists on a particular
>> blocksize on input.
>>
>> Now my tape drive will work with any blocksize, but prefers 65536-byte
>> blocks. I do not see any options for this in pg_dump, but I could pipe the
>> output of pg_dump through dd I suppose to make any blocksize I want.
>>
>> On the way back, likewise I could pipe the tape through dd before giving it
>> to pg_restore.
>>
>> Does pg_dump care what blocksize it gets? If so, what is it?
>
> I assume you could pipe pg_dump into dd and specify the block size in
> dd.
>
Of course on the way out I can do that.

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?

- --
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 21:05:01 up 18 days, 27 min, 0 users, load average: 4.32, 4.12, 4.09
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFG0iRlPtu2XpovyZoRAsXeAKCDuWnpDzTSEhvcBGjKXL O1oS2iAgCgrWB4
6Wj1bz9QoFOXrfL3galipDU=
=pxyE
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Aug 26, 2007, at 8:09 PM, Jean-David Beyer wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Bruce Momjian wrote:
>> Jean-David Beyer wrote:
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA1
>>>
>>> When I make a backup of a database, I put the output file
>>> directly on
>>> magnetic tape; i.e., my command looks like this:
>>>
>>> pg_dump --file=/dev/st0 ....
>>>
>>> This way I do not have to worry if the total backup exceeds the
>>> size of a
>>> file system, and it saves me the trouble of copying it to the
>>> tape as a
>>> separate step. My current tapes will hold 20 GBytes raw or
>>> 40GBytes if I
>>> enable hardware compression (assuming 2:1 compression happens).
>>> Now it says
>>> in the documentation that if I use format c it will compress the
>>> data in
>>> software, so I doubt the hardware compression will do much.
>>>
>>> I do not know what blocksize pg_dump uses, or if it insists on a
>>> particular
>>> blocksize on input.
>>>
>>> Now my tape drive will work with any blocksize, but prefers 65536-
>>> byte
>>> blocks. I do not see any options for this in pg_dump, but I could
>>> pipe the
>>> output of pg_dump through dd I suppose to make any blocksize I want.
>>>
>>> On the way back, likewise I could pipe the tape through dd before
>>> giving it
>>> to pg_restore.
>>>
>>> Does pg_dump care what blocksize it gets? If so, what is it?
>>
>> I assume you could pipe pg_dump into dd and specify the block size in
>> dd.
>>
> Of course on the way out I can do that.
>
> 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.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Erik Jones wrote:
> >>> On the way back, likewise I could pipe the tape through dd before
> >>> giving it
> >>> to pg_restore.
> >>>
> >>> Does pg_dump care what blocksize it gets? If so, what is it?
> >>
> >> I assume you could pipe pg_dump into dd and specify the block size in
> >> dd.
> >>
> > Of course on the way out I can do that.
> >
> > 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.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

Bruce Momjian wrote:
> Erik Jones wrote:
>>>>> On the way back, likewise I could pipe the tape through dd before
>>>>> giving it
>>>>> to pg_restore.
>>>>>
>>>>> Does pg_dump care what blocksize it gets? If so, what is it?
>>>> I assume you could pipe pg_dump into dd and specify the block size in
>>>> dd.
>>>>
>>> Of course on the way out I can do that.
>>>
>>> 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?

- --
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 08:25:01 up 18 days, 11:47, 2 users, load average: 4.34, 4.31, 4.27
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFG0sNpPtu2XpovyZoRAvVpAKCD0YPHpZVXwIweDwDfoz A/79XJSACg0Jao
qmFsnsJpy8209W8CGwhJ31Y=
=u7p6
-----END PGP SIGNATURE-----

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

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.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

-----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

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

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

iD8DBQFG0vQuPtu2XpovyZoRAlwcAKC5ApaGOoZrnHDUa5vgg9 tx4jrqjwCeLfLV
oPLB1xCbJ0/WLYrg5/qVs2g=
=BkQ6
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate