Postgres Pro
Downloads
Home   >   mailing lists

Thread: Data dumps to files - best methods?

Data dumps to files - best methods?

From
"Machiel Richards"
Date:

Good day all

 

 

       As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files.

 

    Let me give you guys some idea on what I mean by this....

 

    Currently there is a java/perl process that runs and creates datadumps for clients with specific data as per specifications and this creates 4 output files which then are compressed and ftp’d to the client.

 

   The current process takes a very long time to run despite the fact that it does not need to process a lot of data.

 

   Optimisations have been done on the Database side and the process is still running very long.

 

 

   What we are now trying to achieve is to use the Linux scripting and SQL scripting combination to try and rewrite the process in order to compare the two processes.

 

    However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files?

 

 

   Any suggestions and assistance would be greatly appreciated.

 

 

Regards

Machiel

Re: Data dumps to files - best methods?

From
Ralf Schuchardt
Date:
Hi,

Am 23.07.2010 um 10:32 schrieb Machiel Richards:

       As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. 

     However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files?

You can use the "\copy" command in psql to export the result of query into a file. For example:

> psql -c "\\copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header" adb

will copy all rows from "atable" in "adb" to "myfile.csv" in csv format.


Ralf

Re: Data dumps to files - best methods?

From
"Machiel Richards"
Date:

Thank you very much, I think this will help a lot.

 

Will ask for more details once I receive the full specs,etc...

 

 

 

 

Machiel Richards

MySQL DBA

Email: machielr@rdc.co.za

Tel: 0861 732 732

RDC_Logo

 

From: Ralf Schuchardt [mailto:rasc@gmx.de]
Sent: 23 July 2010 01:37 PM
To: Machiel Richards
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Data dumps to files - best methods?

 

Hi,

 

Am 23.07.2010 um 10:32 schrieb Machiel Richards:



       As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. 



     However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files?

 

You can use the "\copy" command in psql to export the result of query into a file. For example:

 

> psql -c "\\copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header" adb

 

will copy all rows from "atable" in "adb" to "myfile.csv" in csv format.

 

 

Ralf

Attachment

Re: Data dumps to files - best methods?

From
"Machiel Richards"
Date:

Hi All

 

       Thank you for the responses so far...

 

    I do however have a more specific question regarding this data dump that I need to create for them.

 

   From what I can see in the specs and current output files, the client needs the data output in .xml format in order to use this on their side, still trying to understand why though...

 

       Is there a method of outputting / dumping the data into .xml format as part of the scripts / crons/ db processes?

 

 

 

 

 

Machiel Richards

MySQL DBA

Email: machielr@rdc.co.za

Tel: 0861 732 732

RDC_Logo

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralf Schuchardt
Sent: 23 July 2010 01:37 PM
To: Machiel Richards
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Data dumps to files - best methods?

 

Hi,

 

Am 23.07.2010 um 10:32 schrieb Machiel Richards:



       As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. 



     However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files?

 

You can use the "\copy" command in psql to export the result of query into a file. For example:

 

> psql -c "\\copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header" adb

 

will copy all rows from "atable" in "adb" to "myfile.csv" in csv format.

 

 

Ralf

Attachment

Re: Data dumps to files - best methods?

From
Ralf Schuchardt
Date:
Hi,

Am 26.07.2010 um 11:46 schrieb Machiel Richards:

       Is there a method of outputting / dumping the data into .xml format as part of the scripts / crons/ db processes?

It depends on your requirements of the xml file format.
psql can output data in html/xhtml format (--html switch), which might or might not be enough for you.

$ psql --html -c "select * from atable" adb > myfile.xhtml.part

"query_to_xml" generates a proper xml version of the query results.


Ralf

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralf Schuchardt
Sent: 23 July 2010 01:37 PM
To: Machiel Richards
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Data dumps to files - best methods?
 
Hi,
 
Am 23.07.2010 um 10:32 schrieb Machiel Richards:


       As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. 


     However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files?
 
You can use the "\copy" command in psql to export the result of query into a file. For example:
 
> psql -c "\\copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header" adb
 
will copy all rows from "atable" in "adb" to "myfile.csv" in csv format.
 
 
Ralf

Re: Data dumps to files - best methods?

From
"Michael A. Peters"
Date:
> Hi All

>
>    From what I can see in the specs and current output files, the client
> needs the data output in .xml format in order to use this on their side,
> still trying to understand why though...

I don't know what they are doing but XML is an excellent data storage
format. I use it for some stuff I don't need in a relational database, I
can just import the xml into DOMDocument and operate on it that way.

You can probably use the libxml2 facilities of your favorite scripting
language (php,perl,python,ruby) to dump the database into whatever kind of
XML they want.

-----
Michael A. Peters

http://www.shastaherps.org/