Thread: Database backup

Database backup

From
Alexandros Perdikomatis
Date:
Hello everybody!

I use postgresql for long time now, all through libpq (wich I think is the
most exciting interface) and everything is fine.

When I wanted to backup the base, I was using: "pg_dump mybase > mybase.pgd"and "cat mybase.pgd|psql mybase" (don't
careabout OIDs) 

I faced some small problems to finalise the conversion settings between
different versions, but at the end everything was fine.

Question: Am I too stupid to use tar?

Some time I had no space on the filesystem to copy all the data in command
mode (pg_dump) so I thought to pipe pg_dump to tar using a scsi tape archiver
as output (/dev/rmt0).
Why doesn't work?

Using just pg_dump we have the output to stdout.
I thought that I missender a parameter or something.
If this is the case, sorry everybody for bothering - I'm just too stupid to
use the pipe to tar. In this case, PLEASE someone drop a line correcting the
syndax for me.

If is not, then we have to thing about piping pg_dump to tape archive.

//a command line view:
# pg_dump mybase|tar cvf /dev/rmt0 - // (and some other versions)
tar: -: Cannot stat: No such file or directory
tar: Error exit delayed from previous errors
# tar xvf /dev/rmt0|psql mybase
EOF    // (!!!)
#

after the "EOF" message from psql the database is, of cource, empty.

*****************

thanks,

Alexandros



Re: Database backup

From
"D'Arcy J.M. Cain"
Date:
Redirecting to help list as it seems more appropriate.

On Saturday 18 January 2003 09:17, Alexandros Perdikomatis wrote:
> Question: Am I too stupid to use tar?

I'm sure not but you may misunderstand what tar does.  Tar is for storing a 
collection of files into an archive.  It expects to find the list of files on 
the command line in one form or another.  If you don't write to a file first 
then all you have is a stream of data, not a file.

> Some time I had no space on the filesystem to copy all the data in command
> mode (pg_dump) so I thought to pipe pg_dump to tar using a scsi tape
> archiver as output (/dev/rmt0).
> Why doesn't work?

What exactly was the problem?  You may just have a device selection issue.  
What system is this on?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: Database backup

From
Alexandros Perdikomatis
Date:
Στις Κυρ 19 Ιαν 2003 04:59, ο/η D'Arcy J.M. Cain έγραψε:
> Redirecting to help list as it seems more appropriate.
>
> On Saturday 18 January 2003 09:17, Alexandros Perdikomatis wrote:
> > Question: Am I too stupid to use tar?
>
> I'm sure not but you may misunderstand what tar does.  Tar is for storing a
> collection of files into an archive.  It expects to find the list of files
> on the command line in one form or another.  If you don't write to a file
> first then all you have is a stream of data, not a file.
>
> > Some time I had no space on the filesystem to copy all the data in
> > command mode (pg_dump) so I thought to pipe pg_dump to tar using a scsi
> > tape archiver as output (/dev/rmt0).
> > Why doesn't work?
>
> What exactly was the problem?  You may just have a device selection issue.
> What system is this on?

Thanks for the reply.
I think I understand what you mean, I just thought that instead of a file
input, we could have a pipe input.
It runs on a SuSE linux 6.4 (you may ask why this old version, I updated what
was necesary, I like it becouse I have full greek support) and the archiver
is an old conner DDS-2 scsi tape streamer which is known on the operating
system as /dev/st0 by the kernel whith a symbolic link to /dev/rmt0.
When I pg_dump on a file, of cource there's no problem. I restore also
correctly.
But I would like to pipe this procces. For example: if we type the command:
# tar cvf /dev/rmt0 *
we're going to have all the files in the tree below to the tape.
How can I pipe the output of the pg_dump to it?
Thank you one more time for your reply, I just feel that it must be a way with
it. I face no problems in my C programs using postgresql, (I like it so much)
so I also feel that this problem sould not be in this mailing list becouse
maybe is a system-side question. If this is the case, sorry everybody.
(one question: where (which mailing list, book or something) is going to help
me installing postgresql to an IBM AiX 4.3 system (RS6000 architecture)?
(please)
thanks again.

Alexandros




Re: Database backup

From
"Nigel J. Andrews"
Date:
On Sun, 19 Jan 2003, Alexandros Perdikomatis wrote:

> Óôéò Êõñ 19 Éáí 2003 04:59, ï/ç D'Arcy J.M. Cain Ýãñáøå:
> > Redirecting to help list as it seems more appropriate.
> >
> > On Saturday 18 January 2003 09:17, Alexandros Perdikomatis wrote:
> > > Question: Am I too stupid to use tar?
> >
> > I'm sure not but you may misunderstand what tar does.  Tar is for storing a
> > collection of files into an archive.  It expects to find the list of files
> > on the command line in one form or another.  If you don't write to a file
> > first then all you have is a stream of data, not a file.
> >
> > > Some time I had no space on the filesystem to copy all the data in
> > > command mode (pg_dump) so I thought to pipe pg_dump to tar using a scsi
> > > tape archiver as output (/dev/rmt0).
> > > Why doesn't work?
> >
> > What exactly was the problem?  You may just have a device selection issue.
> > What system is this on?
>
> Thanks for the reply.
> I think I understand what you mean, I just thought that instead of a file
> input, we could have a pipe input.
> It runs on a SuSE linux 6.4 (you may ask why this old version, I updated what
> was necesary, I like it becouse I have full greek support) and the archiver
> is an old conner DDS-2 scsi tape streamer which is known on the operating
> system as /dev/st0 by the kernel whith a symbolic link to /dev/rmt0.
> When I pg_dump on a file, of cource there's no problem. I restore also
> correctly.
> But I would like to pipe this procces. For example: if we type the command:
> # tar cvf /dev/rmt0 *
> we're going to have all the files in the tree below to the tape.
> How can I pipe the output of the pg_dump to it?

Well, you could do:

pg_dump ... > /dev/rmt0

or if you want to set block size:

pg_dump ... | dd of=/dev/rmt0 obs=1k

Basically, you're trying to make it too complicated, tar will archive one file
but would you really use something like:

tar -cf archived-file.tar unarchived-file

unless the unarchived-file name was generated automatically and it just so
happened that only one file existed at that time?

If you really do want something tar try cpio instead.


--
Nigel J. Andrews



Re: Database backup

From
Alexandros Perdikomatis
Date:
Στις Κυρ 19 Ιαν 2003 20:23, ο/η Nigel J. Andrews έγραψε:
> On Sun, 19 Jan 2003, Alexandros Perdikomatis wrote:
> > Óôéò Êõñ 19 Éáí 2003 04:59, ï/ç D'Arcy J.M. Cain Ýãñáøå:
> > > Redirecting to help list as it seems more appropriate.
> > >
> > > On Saturday 18 January 2003 09:17, Alexandros Perdikomatis wrote:
> > > > Question: Am I too stupid to use tar?
> > >
> > > I'm sure not but you may misunderstand what tar does.  Tar is for
> > > storing a collection of files into an archive.  It expects to find the
> > > list of files on the command line in one form or another.  If you don't
> > > write to a file first then all you have is a stream of data, not a
> > > file.
> > >
> > > > Some time I had no space on the filesystem to copy all the data in
> > > > command mode (pg_dump) so I thought to pipe pg_dump to tar using a
> > > > scsi tape archiver as output (/dev/rmt0).
> > > > Why doesn't work?
> > >
> > > What exactly was the problem?  You may just have a device selection
> > > issue. What system is this on?
> >
> > Thanks for the reply.
> > I think I understand what you mean, I just thought that instead of a file
> > input, we could have a pipe input.
> > It runs on a SuSE linux 6.4 (you may ask why this old version, I updated
> > what was necesary, I like it becouse I have full greek support) and the
> > archiver is an old conner DDS-2 scsi tape streamer which is known on the
> > operating system as /dev/st0 by the kernel whith a symbolic link to
> > /dev/rmt0. When I pg_dump on a file, of cource there's no problem. I
> > restore also correctly.
> > But I would like to pipe this procces. For example: if we type the
> > command: # tar cvf /dev/rmt0 *
> > we're going to have all the files in the tree below to the tape.
> > How can I pipe the output of the pg_dump to it?
>
> Well, you could do:
>
> pg_dump ... > /dev/rmt0
>
> or if you want to set block size:
>
> pg_dump ... | dd of=/dev/rmt0 obs=1k
>
> Basically, you're trying to make it too complicated, tar will archive one
> file but would you really use something like:
>
> tar -cf archived-file.tar unarchived-file
>
> unless the unarchived-file name was generated automatically and it just so
> happened that only one file existed at that time?
>
> If you really do want something tar try cpio instead.

Thanks for the information, I should think about piping to dd... thanks again.
The idea of cpio looks more interesting becouse we could have a multivolume
archive I suppose.

one more time thanks,

Alexandros