pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedselect * and save into a text file failed
11+ messages / 5 participants
[nested] [flat]
* select * and save into a text file failed
@ 2005-06-10 15:24 Lee Wu <Lwu@mxlogic.com>
2005-06-10 15:35 ` Re: select * and save into a text file failed Bricklen Anderson <BAnderson@PresiNET.com>
2005-06-10 15:39 ` Re: select * and save into a text file failed James Herbers <james@herbers.ca>
0 siblings, 2 replies; 11+ messages in thread
From: Lee Wu @ 2005-06-10 15:24 UTC (permalink / raw)
To: pgsql-admin
Hi,
When I try to save a query result into a text file from "select * from
a_table", once I got:
out of memory for query result
under psql
Once got killed:
My_db=# select * from a_table;
Killed
bash-2.05b$
And once it caused PG crashed.
My PG version is:
select version();
version
------------------------------------------------------------------------
----------------------------------
PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
The table has 6287585 rows, and takes 2805000 bytes.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
2005-06-10 15:24 select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
@ 2005-06-10 15:35 ` Bricklen Anderson <BAnderson@PresiNET.com>
1 sibling, 0 replies; 11+ messages in thread
From: Bricklen Anderson @ 2005-06-10 15:35 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin
Lee Wu wrote:
>
> How can I save PG data into text file without using pg_dump?
>
did you try
select * from table \o '/var/tmp/textfile.txt'
;
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
2005-06-10 15:24 select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
@ 2005-06-10 15:39 ` James Herbers <james@herbers.ca>
1 sibling, 0 replies; 11+ messages in thread
From: James Herbers @ 2005-06-10 15:39 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin
From a client machine try the program Aqua Data Studio
(http://aquafold.com/) it has a nice export feature to export to text files.
James Herbers
Lee Wu wrote:
> Hi,
>
> When I try to save a query result into a text file from “select * from
> a_table”, once I got:
>
> out of memory for query result
>
> under psql
>
> Once got killed:
>
> My_db=# select * from a_table;
>
> Killed
>
> bash-2.05b$
>
> And once it caused PG crashed.
>
> My PG version is:
>
> select version();
>
> version
>
> ----------------------------------------------------------------------------------------------------------
>
> PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
> 20030502 (Red Hat Linux 3.2.3-49)
>
> (1 row)
>
> The table has 6287585 rows, and takes 2805000 bytes.
>
> From the PG log, I can only find:
>
> could not send data to client: Connection reset by peer
>
> could not send data to client: Broken pipe
>
> unexpected EOF on client connection
>
> How can I save PG data into text file without using pg_dump?
>
> Thanks,
>
> PS: I know pg_dump, but it is not my option.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
@ 2005-06-10 16:22 Lee Wu <Lwu@mxlogic.com>
0 siblings, 0 replies; 11+ messages in thread
From: Lee Wu @ 2005-06-10 16:22 UTC (permalink / raw)
To: Bricklen Anderson <BAnderson@PresiNET.com>; +Cc: pgsql-admin
Same:
My_db=# select * from a_table \o 'test2.dat'
My_db-# ;
Killed
-----Original Message-----
From: Bricklen Anderson [mailto:BAnderson@PresiNET.com]
Sent: Friday, June 10, 2005 9:35 AM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] select * and save into a text file failed
Lee Wu wrote:
>
> How can I save PG data into text file without using pg_dump?
>
did you try
select * from table \o '/var/tmp/textfile.txt'
;
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
@ 2005-06-10 16:33 Lee Wu <Lwu@mxlogic.com>
2005-06-10 16:55 ` Re: select * and save into a text file failed Tom Lane <tgl@sss.pgh.pa.us>
2005-06-10 16:59 ` Re: select * and save into a text file failed Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 2 replies; 11+ messages in thread
From: Lee Wu @ 2005-06-10 16:33 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; pgsql-admin
Even without saving to file, it is still killed:
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
My_db=# select * from a_table;
Killed
bash-2.05b$
PG log shows:
could not send data to client: Connection reset by peer
could not send data to client: Broken pipe
unexpected EOF on client connection
Can anyone help?
Thanks,
PS: psql launched from the same PG server box.
________________________________
From: Lee Wu
Sent: Friday, June 10, 2005 9:25 AM
To: pgsql-admin@postgresql.org
Subject: select * and save into a text file failed
Hi,
When I try to save a query result into a text file from "select * from
a_table", once I got:
out of memory for query result
under psql
Once got killed:
My_db=# select * from a_table;
Killed
bash-2.05b$
And once it caused PG crashed.
My PG version is:
select version();
version
------------------------------------------------------------------------
----------------------------------
PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
The table has 6287585 rows, and takes 2805000 bytes.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
2005-06-10 16:33 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
@ 2005-06-10 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-06-10 16:55 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin
"Lee Wu" <Lwu@mxlogic.com> writes:
> My_db=3D# select * from a_table;
> Killed
Apparently the table is bigger than psql can hold in memory?
The simplest way to do this is certainly to use pg_dump in one-table
mode. If you're intent on using psql, you could use a cursor or
OFFSET/LIMIT to fetch part of the table at a time.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
2005-06-10 16:33 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
@ 2005-06-10 16:59 ` Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 11+ messages in thread
From: Steve Crawford @ 2005-06-10 16:59 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; pgsql-admin
On Friday 10 June 2005 9:33 am, Lee Wu wrote:
> Even without saving to file, it is still killed:
>...
> My_db=# select * from a_table;
> Killed
>...
The previous examples don't work for me. In psql try this:
--First set the output to a file
\o 'my_output.txt'
--Now run the query
select * from myfile;
--Quit and check your results
\q
If you have a very large table you can exhaust memory on the client
side unless you are writing the data directly to a file.
Cheers,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
@ 2005-06-10 17:03 Lee Wu <Lwu@mxlogic.com>
2005-06-10 17:15 ` Re: select * and save into a text file failed Tom Lane <tgl@sss.pgh.pa.us>
2005-06-10 17:36 ` Re: select * and save into a text file failed Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 2 replies; 11+ messages in thread
From: Lee Wu @ 2005-06-10 17:03 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-admin
That is exactly what I did:
\o a_lot_room_to_hold_my_result
select * from a_table
either
1. out of memory for query result
2. killed
3. crash PG
"If you have a very large table you can exhaust memory on the client
side unless you are writing the data directly to a file."
How besides "\o" and pg_dump?
We have 4G RAM, and shared_buffers= 32768, it is a dedicate test box,
while the table is about 2G.
Thanks,
-----Original Message-----
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Friday, June 10, 2005 11:00 AM
To: Lee Wu; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] select * and save into a text file failed
On Friday 10 June 2005 9:33 am, Lee Wu wrote:
> Even without saving to file, it is still killed:
>...
> My_db=# select * from a_table;
> Killed
>...
The previous examples don't work for me. In psql try this:
--First set the output to a file
\o 'my_output.txt'
--Now run the query
select * from myfile;
--Quit and check your results
\q
If you have a very large table you can exhaust memory on the client
side unless you are writing the data directly to a file.
Cheers,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
2005-06-10 17:03 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
@ 2005-06-10 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-06-10 17:15 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-admin
"Lee Wu" <Lwu@mxlogic.com> writes:
> either
> 1. out of memory for query result
> 2. killed
> 3. crash PG
Cases 2 and 3 represent the kernel out-of-memory code going nuts.
Google for "OOM kill" and "memory overcommit". IMHO you never
want Linux's default memory overcommit behavior on a production
server.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
2005-06-10 17:03 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
@ 2005-06-10 17:36 ` Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 11+ messages in thread
From: Steve Crawford @ 2005-06-10 17:36 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; pgsql-admin
On Friday 10 June 2005 10:03 am, Lee Wu wrote:
> That is exactly what I did:
>
> \o a_lot_room_to_hold_my_result
> select * from a_table
>
> either
> 1. out of memory for query result
> 2. killed
> 3. crash PG
>
> "If you have a very large table you can exhaust memory on the
> client side unless you are writing the data directly to a file."
> How besides "\o" and pg_dump?
>
> We have 4G RAM, and shared_buffers= 32768, it is a dedicate test
> box, while the table is about 2G.
Something to try (I don't know if it will work because I don't know
the exact internals of the PG libraries but it's something I've
observed): set the output formatting to unaligned (\pset format
unaligned).
I had a situation once where selecting a few thousand rows exhausted
my RAM. The problem was that one item in a text column was ~8k long
which meant that every other row had 8k of padding. And this was the
case for more than one column so the pager was having to swallow gobs
of data most of which was blank space. I ran the output to a file and
ran some tests. Switching to unaligned output dropped the size by
orders of magnitude. Depending on the nature of your data this may
help or do nothing at all.
Of course you are going to hit RAM or disk limitations on any given
machine. Cursors are there for your use and your project may require
them.
Cheers,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: select * and save into a text file failed
@ 2005-06-10 17:05 Lee Wu <Lwu@mxlogic.com>
0 siblings, 0 replies; 11+ messages in thread
From: Lee Wu @ 2005-06-10 17:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Which memory are you refer Tom?
shared_buffers= 32768
and RAM is 4G while the table is about 2G.
Why straight "select * from" need so big memory?
From top, I did not see the process eating a lot of memory.
From vmstat, I did not see any swaping.
It is a dedicate test box, with only PG running.
Thanks Tom,
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, June 10, 2005 10:55 AM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] select * and save into a text file failed
"Lee Wu" <Lwu@mxlogic.com> writes:
> My_db=3D# select * from a_table;
> Killed
Apparently the table is bigger than psql can hold in memory?
The simplest way to do this is certainly to use pg_dump in one-table
mode. If you're intent on using psql, you could use a cursor or
OFFSET/LIMIT to fetch part of the table at a time.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-06-10 17:36 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-10 15:24 select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
2005-06-10 15:35 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-06-10 15:39 ` James Herbers <james@herbers.ca>
2005-06-10 16:22 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
2005-06-10 16:33 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
2005-06-10 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-10 16:59 ` Steve Crawford <scrawford@pinpointresearch.com>
2005-06-10 17:03 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
2005-06-10 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-10 17:36 ` Steve Crawford <scrawford@pinpointresearch.com>
2005-06-10 17:05 Re: select * and save into a text file failed Lee Wu <Lwu@mxlogic.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox