Re: Backup to Tape Incomplete

PostgreSQL 8.2.4
RedHat ES4

I have a nightly cron job that is (supposed) to dump a specific database to
magnetic tape:
/usr/local/bin/pg_dump dbname > /dev/st0

This runs, and doesn't throw any errors, but when I try to restore it fails
because the tape is incomplete:
[postgres(at)stirling ~]$ cat /dev/st0 | tail


--
-- Name: rewards_points; Type: ACL; Schema: public; Owner: dbname
--

REVOKE ALL ON TABLE rewards_points FROM PUBLIC;
REVOKE ALL ON TABLE rewards_points FROM dbname;
GRANT ALL ON TABLE rewards_points TO dbname;
GRANT SELECT,INSERT,UPDATE ON TABLE rewards_points TO
PUBL[postgres(at)stirling ~]$

As you can see, the "end of file" is half-way through a SQL statement, and
doesn't even have a new-line marker.

The database is not too big for the tape - it's a DDS-3 tape drive (12/24gb)
and the database is not even 1gb:
[postgres(at)stirling ~]$ pg_dump dbname > /tmp/dbname080225.sql
[postgres(at)stirling ~]$ ls -lh /tmp/dbname080225.sql
-rw-r--r-- 1 postgres root 957M Feb 25 13:42 /tmp/dbname080225.sql

Is this a PostgreSQL issue or a tape drive issue? I can use tar to read and
write without any problems.

Do I need to change the block size on the tape drive?

[phillips(at)stirling ~]$ mt stat
SCSI 2 tape drive:
File number=0, block number=0, partition=0.
Tape block size 512 bytes. Density code 0x25 (DDS-3).
Soft error count since last status=0
General status bits on (41010000):
BOT ONLINE IM_REP_EN

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

On Sun, Feb 24, 2008 at 9:20 PM, Phillip Smith
<phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
> PostgreSQL 8.2.4
> RedHat ES4
>
> I have a nightly cron job that is (supposed) to dump a specific database to
> magnetic tape:
> /usr/local/bin/pg_dump dbname > /dev/st0
>
> This runs, and doesn't throw any errors, but when I try to restore it fails
> because the tape is incomplete:

A couple of possible things to try; pg_dump to a text file and try
cat'ting that to the tape drive, or pipe it through tar and then to
the tape.

On Sun, Feb 24, 2008 at 9:20 PM, Phillip Smith
<phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
>> PostgreSQL 8.2.4
>> RedHat ES4
>>
>> I have a nightly cron job that is (supposed) to dump a specific
>> database to magnetic tape:
>> /usr/local/bin/pg_dump dbname > /dev/st0
>>
>> This runs, and doesn't throw any errors, but when I try to restore it
>> fails because the tape is incomplete:

> A couple of possible things to try; pg_dump to a text file and try
cat'ting that to the tape drive, or pipe it through tar and then to the
tape.

What would the correct syntax be for that - I can't figure out how to make
tar accept stdin:

[postgres(at)stirling ~]$ pg_dump dbname | tar cvf /dev/st0
tar: Cowardly refusing to create an empty archive
Try `tar --help' for more information.
[postgres(at)stirling ~]$ pg_dump dbname | tar cvf /dev/st0 -
tar: -: Cannot stat: No such file or directory
[postgres(at)stirling ~]$

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

"Phillip Smith" <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> writes:
> On Sun, Feb 24, 2008 at 9:20 PM, Phillip Smith
> <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
>> A couple of possible things to try; pg_dump to a text file and try
> cat'ting that to the tape drive, or pipe it through tar and then to the
> tape.

> What would the correct syntax be for that - I can't figure out how to make
> tar accept stdin:

I don't think it can. Instead, maybe dd with blocksize set equal to the
tape drive's required blocksize would do? You'd have to check what
options your dd version has for padding out the last partial block.
Padding with spaces should work fine, not totally sure if nulls would
be OK.

regards, tom lane

Tom Lane wrote:
> "Phillip Smith" <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> writes:
>> On Sun, Feb 24, 2008 at 9:20 PM, Phillip Smith
>> <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
>>> A couple of possible things to try; pg_dump to a text file and try
>> cat'ting that to the tape drive, or pipe it through tar and then to the
>> tape.
>
>> What would the correct syntax be for that - I can't figure out how to make
>> tar accept stdin:
>
> I don't think it can.

Coming in the middle of this thread, so slap me if I'm off base here.
tar will accept standard in as:

tar -cf -

the '-f -' says take input.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

> Coming in the middle of this thread, so slap me if I'm off base here.
> tar will accept standard in as:
>
> tar -cf -
>
> the '-f -' says take input.

That would be to write to stdout :) I can't figure out how to accept from
stdin :(

-f is where the send the output, either a file, a device (such as tape) or
stdout (aka '-')

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

>> What would the correct syntax be for that - I can't figure out how to
>> make tar accept stdin:

> I don't think it can. Instead, maybe dd with blocksize set equal to the
tape drive's required blocksize would do? You'd have to check what options
your
> dd version has for padding out the last partial block. Padding with spaces
should work fine, not totally sure if nulls would be OK.

I don't think it can either, which kind of makes sense. Tar is an archiving
utility to create an archive of files; not to create an archive of the
contents of files. Subtle difference, but makes sense. If I created a tar
archive from the stream coming out of tar, what would be listed when I did
'tar tvf /dev/st0'?

I think I'll hack the backup to output it to a temp file, then tar that file
to tape.

At the very least:

#!/bin/bash

$DBNAME='dbname'
$TMPFILE="/tmp/$DBNAME.date.sql"
$TAPE_DRIVE='/dev/st0'

/usr/local/bin/pg_dump $DBNAME > $TMPFILE
/bin/tar cvf $TAPE_DRIVE --label="$TMPFILE" $TMPFILE
/bin/rm -f $TMPFILE

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

On Wed, 27 Feb 2008 13:48:38 +1100
"Phillip Smith" <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:

> > Coming in the middle of this thread, so slap me if I'm off base here.
> > tar will accept standard in as:
> >
> > tar -cf -
> >
> > the '-f -' says take input.
>
> That would be to write to stdout :) I can't figure out how to accept from
> stdin :(
>
> -f is where the send the output, either a file, a device (such as tape) or
> stdout (aka '-')

Not quite. tar cf - will pipe to stdout, but tar xf - will pipe from stdin.

For the OP's problem, I'd try piping through dd as a file buffer, and run sync on completion. Is there a /dev/rst0, like on some of the old unixes ( doesn't look like it after checking my CentOS 4 server)? The difference was that rst0 was unbuffered, and st0 was buffered. Either way, the sync may help.

Worth a try (:

Steve.

Sorry Steve, I missed the "reply all" by 3 pixels :)

> > > tar -cf -
> > >
> > > the '-f -' says take input.
> >
> > That would be to write to stdout :) I can't figure out how to accept
> > from stdin :(
> >
> > -f is where the send the output, either a file, a device (such as
> > tape) or stdout (aka '-')
>
> Not quite. tar cf - will pipe to stdout, but tar xf - will pipe from
> stdin.

Yes, true; my head was in "tar c" mode :)

> For the OP's problem, I'd try piping through dd as a file buffer, and
> run sync on completion. Is there a /dev/rst0, like on some of the old
> unixes (doesn't look like it after checking my CentOS 4 server)? The
> difference was that rst0 was unbuffered, and st0 was buffered. Either
> way, the sync may help.

There's no /dev/rst0 block device, but I'm more familiar with tar than dd,
so I think I'll just rewrite the script to tar to a temp file first :)

Do we think this is a Postgres problem, a Linux problem or a problem
specific to my hardware setup? Was I wrong to think that I should be able to
stream directly from pg_dump to /dev/st0? I would have thought it *should*
work, but maybe I was wrong in the first place with that?

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

On Tue, Feb 26, 2008 at 9:38 PM, Phillip Smith
<phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
>
> Do we think this is a Postgres problem, a Linux problem or a problem
> specific to my hardware setup? Was I wrong to think that I should be able to
> stream directly from pg_dump to /dev/st0? I would have thought it *should*
> work, but maybe I was wrong in the first place with that?

If you can dd the file onto your tape drive, then it's some weird
interaction between pg_dump and your system I'd think. Could the be
some maximum size that you can buffer through pipes / redirects on
your machine?

I'd test to see if cat pgdumpfile.sql > /dev/st0 works or not. If it
fails at the same approximate size, then it's something to do with
redirection. If tar works but redirects fail, then the problem isn't
with postgresql. i.e. do something similar to what you're doing with
pgsql and see which things fail and which ones don't.

> > Do we think this is a Postgres problem, a Linux problem or a problem
> > specific to my hardware setup? Was I wrong to think that I should be
> > able to stream directly from pg_dump to /dev/st0? I would have
> > thought it *should* work, but maybe I was wrong in the first place
> > with that?
>
> If you can dd the file onto your tape drive, then it's some weird
interaction between
> pg_dump and your system I'd think. Could the be some maximum size that
you can buffer
> through pipes / redirects on your machine?
>
> I'd test to see if cat pgdumpfile.sql > /dev/st0 works or not. If it
fails at the
> same approximate size, then it's something to do with redirection. If >
tar works but
> redirects fail, then the problem isn't with postgresql. i.e. do something
similar to
> what you're doing with pgsql and see which things fail and which ones
don't.

It appears to be me :(

[postgres(at)stirling ~]$ cat /tmp/dbname080225.sql > /dev/st0
cat: write error: Invalid argument

It ran for a good 30 minutes, then died with that.

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

On Tue, Feb 26, 2008 at 10:20 PM, Phillip Smith
<phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
>
> > > Do we think this is a Postgres problem, a Linux problem or a problem
> > > specific to my hardware setup? Was I wrong to think that I should be
> > > able to stream directly from pg_dump to /dev/st0? I would have
> > > thought it *should* work, but maybe I was wrong in the first place
> > > with that?
> >
> > If you can dd the file onto your tape drive, then it's some weird
> interaction between
> > pg_dump and your system I'd think. Could the be some maximum size that
> you can buffer
> > through pipes / redirects on your machine?
> >
> > I'd test to see if cat pgdumpfile.sql > /dev/st0 works or not. If it
> fails at the
> > same approximate size, then it's something to do with redirection. If >
> tar works but
> > redirects fail, then the problem isn't with postgresql. i.e. do something
> similar to
> > what you're doing with pgsql and see which things fail and which ones
> don't.
>
> It appears to be me :(
>
> [postgres(at)stirling ~]$ cat /tmp/dbname080225.sql > /dev/st0
> cat: write error: Invalid argument
>
> It ran for a good 30 minutes, then died with that.

I wonder what it's meaning by invalid arg? Is something in the .sql
file somehow coming across as an argument? Can you cat the sql file
to /dev/null successfully?

> > > > Do we think this is a Postgres problem, a Linux problem or a
> > > > problem specific to my hardware setup? Was I wrong to think
> > > > that I should be able to stream directly from pg_dump to
> > > > /dev/st0? I would have thought it *should* work, but maybe
> > > > I was wrong in the first place with that?

> > > If you can dd the file onto your tape drive, then it's some
> > > weird interaction between pg_dump and your system I'd think.
> > > Could the be some maximum size that you can buffer through
> > > pipes / redirects on your machine?
> > >
> > > I'd test to see if cat pgdumpfile.sql > /dev/st0 works or not.
> > > If it fails at the same approximate size, then it's something
> > > to do with redirection. If tar works but redirects fail, then
> > > the problem isn't with postgresql. i.e. do something similar
> > > to what you're doing with pgsql and see which things fail and
> > > which ones don't.

> > It appears to be me :(
> >
> > [postgres(at)stirling ~]$ cat /tmp/dbname080225.sql > /dev/st0
> > cat: write error: Invalid argument
> >
> > It ran for a good 30 minutes, then died with that.

> I wonder what it's meaning by invalid arg? Is something in the
> .sql file somehow coming across as an argument? Can you cat the
> sql file to /dev/null successfully?

Yes.
[postgres(at)stirling ~]$ cat /tmp/dbname080225.sql > /dev/null
[postgres(at)stirling ~]$

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> I wonder what it's meaning by invalid arg?

On my Fedora machine, "man write" explains EINVAL thusly:

EINVAL fd is attached to an object which is unsuitable for writing; or
the file was opened with the O_DIRECT flag, and either the
address specified in buf, the value specified in count, or the
current file offset is not suitably aligned.

I'm not sure that writing to a tape is quite like O_DIRECT, but the
mention of an un-aligned count seems pretty relevant. If you grant
the assumption that the underlying problem is that the tape drive
has to be written to in multiples of its blocksize, then this supports
the idea that a violation of that rule would be reported as EINVAL.

regards, tom lane

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> > I wonder what it's meaning by invalid arg?
>
> On my Fedora machine, "man write" explains EINVAL thusly:
>
> EINVAL fd is attached to an object which is unsuitable for
writing; or
> the file was opened with the O_DIRECT flag, and either
the
> address specified in buf, the value specified in count, or
the
> current file offset is not suitably aligned.
>
> I'm not sure that writing to a tape is quite like O_DIRECT, but the
mention of
> an un-aligned count seems pretty relevant. If you grant the assumption
that
> the underlying problem is that the tape drive has to be written to in
> multiples of its blocksize, then this supports the idea that a violation
of
> that rule would be reported as EINVAL.

Interesting... If I set the block-size to 32, everything goes without any
error,
But not ending with "PostgreSQL database dump complete":

[postgres(at)stirling ~]$ mt setblk 32
[postgres(at)stirling ~]$ mt stat
SCSI 2 tape drive:
File number=0, block number=0, partition=0.
Tape block size 32 bytes. Density code 0x25 (DDS-3).
Soft error count since last status=0
General status bits on (41010000):
BOT ONLINE IM_REP_EN
[postgres(at)stirling ~]$ pg_dump dbname > /dev/st0
[postgres(at)stirling ~]$ tail - < /dev/st0
REVOKE ALL ON TABLE stock_test FROM dbuser;
GRANT ALL ON TABLE stock_test TO dbuser;
GRANT ALL ON TABLE stock_test TO PUBLIC;

--
-- Name: stores; Type: ACL; Schema: public; Owner: dbuser
--

[postgres(at)stirling ~]$

If I set the block-size to 0... Well it's really not happy:

[postgres(at)stirling ~]$ mt setblk 0
[postgres(at)stirling ~]$ mt stat
SCSI 2 tape drive:
File number=0, block number=0, partition=0.
Tape block size 0 bytes. Density code 0x25 (DDS-3).
Soft error count since last status=0
General status bits on (41010000):
BOT ONLINE IM_REP_EN
[postgres(at)stirling ~]$ pg_dump dbname > /dev/st0
[postgres(at)stirling ~]$ tail - < /dev/st0
tail: error reading `standard input': Cannot allocate memory
[postgres(at)stirling ~]$ cat /dev/st0 > /tmp/test-restore.sql
cat: /dev/st0: Cannot allocate memory
[postgres(at)stirling ~]$

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.