Thread: Dump database more than 1 flat file

Dump database more than 1 flat file

From
"A.Burbello"
Date:
Hi people,

How can I export (dump) database more than 1 file?
I have great table and can't split.
Like Oracle, just set the parameter filesize and the
files name, and dump in several files.

Could I do it the same way in PG?

Thank you in advance.


      Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/

Re: Dump database more than 1 flat file

From
"Scott Marlowe"
Date:
On Dec 15, 2007 11:42 AM, A.Burbello <burbello3000@yahoo.com.br> wrote:
> Hi people,
>
> How can I export (dump) database more than 1 file?
> I have great table and can't split.
> Like Oracle, just set the parameter filesize and the
> files name, and dump in several files.

You could pipe the output of pg_dump to split, or split it after it's
been created.

But is there a reason for doing this or is it just some practice you're into?

Re: Dump database more than 1 flat file

From
"A.Burbello"
Date:
I consider this way a good practice to transport the
files. This is because I have table that has more than
30GB.
But if the OS was windows, couldn't split the files
because postgres doesn't has this feature!
Could be a good option if postgres had native.

Weel, I will try this:
eg: $ pg_dump postgres -U postgres -f split.txt |
split --bytes=10m

Thank you.






--- Scott Marlowe <scott.marlowe@gmail.com> escreveu:

> On Dec 15, 2007 11:42 AM, A.Burbello
> <burbello3000@yahoo.com.br> wrote:
> > Hi people,
> >
> > How can I export (dump) database more than 1 file?
> > I have great table and can't split.
> > Like Oracle, just set the parameter filesize and
> the
> > files name, and dump in several files.
>
> You could pipe the output of pg_dump to split, or
> split it after it's
> been created.
>
> But is there a reason for doing this or is it just
> some practice you're into?
>



      Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/

Re: Dump database more than 1 flat file

From
"Scott Marlowe"
Date:
On Dec 17, 2007 2:06 PM, A.Burbello <burbello3000@yahoo.com.br> wrote:
> I consider this way a good practice to transport the
> files. This is because I have table that has more than
> 30GB.
> But if the OS was windows, couldn't split the files
> because postgres doesn't has this feature!
> Could be a good option if postgres had native.
>
> Weel, I will try this:
> eg: $ pg_dump postgres -U postgres -f split.txt |
> split --bytes=10m

http://gnuwin32.sourceforge.net/packages/coreutils.htm

for now.  I used these back in the day (NT4.0 SP4 or so) and they
worked a charm back then.  Heck, even ln worked ( in a manner of
speaking ) back then.

Re: Dump database more than 1 flat file

From
Geoffrey
Date:
Scott Marlowe wrote:
> On Dec 17, 2007 2:06 PM, A.Burbello <burbello3000@yahoo.com.br> wrote:
>> I consider this way a good practice to transport the
>> files. This is because I have table that has more than
>> 30GB.
>> But if the OS was windows, couldn't split the files
>> because postgres doesn't has this feature!
>> Could be a good option if postgres had native.
>>
>> Weel, I will try this:
>> eg: $ pg_dump postgres -U postgres -f split.txt |
>> split --bytes=10m
>
> http://gnuwin32.sourceforge.net/packages/coreutils.htm
>
> for now.  I used these back in the day (NT4.0 SP4 or so) and they
> worked a charm back then.  Heck, even ln worked ( in a manner of
> speaking ) back then.

There's also Uwin from AT&T:

http://www.research.att.com/sw/tools/uwin

list of available commands and man pages:

http://www.research.att.com/~gsf/man/man1/


--
Until later, Geoffrey

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

Re: Dump database more than 1 flat file

From
"A.Burbello"
Date:
Thank you for your response!

I tested the split command and I got another problem
because for each file doesn't have the "COPY" header.

As well I said early, one table has more than 30G and
I need to import to another server (linux), but by dvd
media because doens't have network connection.

Whats the best way to do this.
Any suggestion?

If doesn't have another way, how can I put the
"header" in the begin of file without open?
With "cat >>" command I put in the end.

Could you help me?

Once again, thank you!








--- Scott Marlowe <scott.marlowe@gmail.com> escreveu:

> On Dec 17, 2007 2:06 PM, A.Burbello
> <burbello3000@yahoo.com.br> wrote:
> > I consider this way a good practice to transport
> the
> > files. This is because I have table that has more
> than
> > 30GB.
> > But if the OS was windows, couldn't split the
> files
> > because postgres doesn't has this feature!
> > Could be a good option if postgres had native.
> >
> > Weel, I will try this:
> > eg: $ pg_dump postgres -U postgres -f split.txt |
> > split --bytes=10m
>
>
http://gnuwin32.sourceforge.net/packages/coreutils.htm
>
> for now.  I used these back in the day (NT4.0 SP4 or
> so) and they
> worked a charm back then.  Heck, even ln worked ( in
> a manner of
> speaking ) back then.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will
> ignore your desire to
>        choose an index scan if your joining column's
> datatypes do not
>        match
>



      Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/

Re: Dump database more than 1 flat file

From
"Phillip Smith"
Date:
> If doesn't have another way, how can I put the
> "header" in the begin of file without open?
> With "cat >>" command I put in the end.

After the split files are loaded on the new server file system:
    cat splitfile*.txt > bigfile.txt
    psql < bigfile.txt


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.

Re: Dump database more than 1 flat file

From
Alvaro Herrera
Date:
A.Burbello wrote:

> If doesn't have another way, how can I put the
> "header" in the begin of file without open?
> With "cat >>" command I put in the end.

Don't -- you can put the header in a separate file and do something like

(cat header-file ; cat split-1 ; cat tail-file ) | psql

lather, rinse, repeat.  (Each split-N file would be an output file from
split).  I'm not sure if you also need the tail-file containing the \.
The parens tell the shell to execute all those commands and send the
output of all of them into the single psql process.

Of course you can automate it further, once you've copied all the DVDs
into a directory in the destination server:

for file in split-*; do
  ( cat header-file ; cat $file ; cat tail-file ) | psql
done

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Dump database more than 1 flat file

From
Thomas Pundt
Date:
Hi,

On Dienstag, 18. Dezember 2007, Alvaro Herrera wrote:
| A.Burbello wrote:
| > If doesn't have another way, how can I put the
| > "header" in the begin of file without open?
| > With "cat >>" command I put in the end.
|
| Don't -- you can put the header in a separate file and do something like
|
| (cat header-file ; cat split-1 ; cat tail-file ) | psql

even simpler: "cat" usually takes any number of file name arguments,
so usually you simply can do

  cat header-file split-1 tail-file | psql

No need for parentheses and starting a new process for each file.

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

Re: Dump database more than 1 flat file

From
"A.Burbello"
Date:
Sorry to insist in this question, but now I was doing
some test and I notice that is a good idea to use
split utility.

This is because the output file got X bytes specified,
cut the rest of the line and will not import after.

In my example, I generated more than 2 files equal to
1MB. But already first file cut the last line. Even
join with the second file, the last line from first
file is broken and it's not possible to import this
way.

How can I transport more than 35G (just one table) in
one DVD?
Could you help?

Thank you









      Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/

Re: Dump database more than 1 flat file

From
"Phillip Smith"
Date:
Try splitting by line count instead of size:
  -l, --lines=NUMBER
    put NUMBER lines per output file

pg_dump | split -l 32000

You'll have to experiment to find how many lines will give you a roughly
appropriate size for your purposes.

Alternatively, another option is to get an USB external HDD (I picked up a
Seagate 120gb 2.5" one recently for AU$135) and dump your data to that, then
take that to the new server. Any recent 2.6 kernel shouldn't have any issues
mounting it (especially formatted as ext2/3 or jfs/xfs etc)

Cheers,
~p


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.

Re: Dump database more than 1 flat file

From
Chris Browne
Date:
phillip.smith@weatherbeeta.com.au ("Phillip Smith") writes:
>> If doesn't have another way, how can I put the
>> "header" in the begin of file without open?
>> With "cat >>" command I put in the end.
>
> After the split files are loaded on the new server file system:
>     cat splitfile*.txt > bigfile.txt
>     psql < bigfile.txt
>
>
> 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.

You should think before you attach these sorts of "confidentiality" notices.

If you put them on messages like this that are *OBVIOUSLY* being sent
to publicly-available mailing lists, this may undermine future claims
of material being supposedly-confidential.

In other words, by WRONGLY attaching confidentiality notices, courts
might decide to ignore them even in cases where you imagined they
ought to be legitimate...
--
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/x.html
The cigars in Los Angeles that were Duchamp-signed and then smoked.

Re: Dump database more than 1 flat file

From
"Phillip Smith"
Date:
> You should think before you attach these sorts of "confidentiality"
> notices.
>
> If you put them on messages like this that are *OBVIOUSLY* being sent
> to publicly-available mailing lists, this may undermine future claims
> of material being supposedly-confidential.
>
> In other words, by WRONGLY attaching confidentiality notices, courts
> might decide to ignore them even in cases where you imagined they
> ought to be legitimate...

I've thought about that before and agree completely. Unfortunately this
disclaimer is added by our perimeter UTM device, which has no way to know
what is 'public' and what is 'confidential'


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.

Re: Dump database more than 1 flat file

From
Tom Lane
Date:
"Phillip Smith" <phillip.smith@weatherbeeta.com.au> writes:
>> In other words, by WRONGLY attaching confidentiality notices, courts
>> might decide to ignore them even in cases where you imagined they
>> ought to be legitimate...

> I've thought about that before and agree completely. Unfortunately this
> disclaimer is added by our perimeter UTM device, which has no way to know
> what is 'public' and what is 'confidential'

Which, indeed, is exactly the sort of fact that would invalidate any
subsequent claims of confidentiality based on the notice.  You should
point out to your organization's lawyers that this type of requirement
accomplishes nothing except making them look like fools.

            regards, tom lane

Re: Dump database more than 1 flat file

From
"Phillip Smith"
Date:
> >> In other words, by WRONGLY attaching confidentiality notices, courts
> >> might decide to ignore them even in cases where you imagined they
> >> ought to be legitimate...
>
> > I've thought about that before and agree completely. Unfortunately this
> > disclaimer is added by our perimeter UTM device, which has no way to
> know
> > what is 'public' and what is 'confidential'
>
> Which, indeed, is exactly the sort of fact that would invalidate any
> subsequent claims of confidentiality based on the notice.  You should
> point out to your organization's lawyers that this type of requirement
> accomplishes nothing except making them look like fools.
>

I've forwarded it to the powers that be and they can decide what they want
to do. I'm just the IT Manager to make the magic happen when they decide
they want it ;D


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.