pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBlock size with pg_dump?
8+ messages / 3 participants
[nested] [flat]
* Block size with pg_dump?
@ 2007-08-26 21:35 Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 8+ messages in thread
From: Jean-David Beyer @ 2007-08-26 21:35 UTC (permalink / raw)
To: pgsql-sql
-----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
iD8DBQFG0fITPtu2XpovyZoRAouwAKCTEour7jbi3uKWmEjerOM3U51xKQCeKYrQ
6jbamlqvTvH04jD7oRbTAKY=
=piNw
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 00:11 Bruce Momjian <bruce@momjian.us>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2007-08-27 00:11 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; +Cc: pgsql-sql
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. +
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 01:09 Jean-David Beyer <jeandavid8@verizon.net>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: Jean-David Beyer @ 2007-08-27 01:09 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-sql
-----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
iD8DBQFG0iRlPtu2XpovyZoRAsXeAKCDuWnpDzTSEhvcBGjKXLO1oS2iAgCgrWB4
6Wj1bz9QoFOXrfL3galipDU=
=pxyE
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 02:41 Erik Jones <erik@myemma.com>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Jones @ 2007-08-27 02:41 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 02:49 Bruce Momjian <bruce@momjian.us>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2007-08-27 02:49 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-sql
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. +
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 12:28 Jean-David Beyer <jeandavid8@verizon.net>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: Jean-David Beyer @ 2007-08-27 12:28 UTC (permalink / raw)
To: pgsql-sql
-----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
iD8DBQFG0sNpPtu2XpovyZoRAvVpAKCD0YPHpZVXwIweDwDfozA/79XJSACg0Jao
qmFsnsJpy8209W8CGwhJ31Y=
=u7p6
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 12:33 Bruce Momjian <bruce@momjian.us>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2007-08-27 12:33 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; +Cc: pgsql-sql
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. +
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Block size with pg_dump?
@ 2007-08-27 15:56 Jean-David Beyer <jeandavid8@verizon.net>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 8+ messages in thread
From: Jean-David Beyer @ 2007-08-27 15:56 UTC (permalink / raw)
To: 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-----
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-08-27 15:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-26 21:35 Block size with pg_dump? Jean-David Beyer <jeandavid8@verizon.net>
2007-08-27 00:11 ` Bruce Momjian <bruce@momjian.us>
2007-08-27 01:09 ` Jean-David Beyer <jeandavid8@verizon.net>
2007-08-27 02:41 ` Erik Jones <erik@myemma.com>
2007-08-27 02:49 ` Bruce Momjian <bruce@momjian.us>
2007-08-27 12:28 ` Jean-David Beyer <jeandavid8@verizon.net>
2007-08-27 12:33 ` Bruce Momjian <bruce@momjian.us>
2007-08-27 15:56 ` Jean-David Beyer <jeandavid8@verizon.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox