Thread: Dump database more than 1 flat file
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/
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?
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/
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.
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
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/
> 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.
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.
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/ ----
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/
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.
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.
> 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.
"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
> >> 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.