Data dumps to files - best methods?
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
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
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 <smb://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
Attachments:
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 <smb://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
Attachments:
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
Another way is to use the function described here: http://www.postgresql.org/docs/8.4/interactive/functions-xml.html#FUNCTIONS-XML-MAPPING
"query_to_xml" generates a proper xml version of the query results.
Ralf
Show quoted text
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
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