Upgrading from postgreSQL 7.3 - fails on large tables

Dear all,

I am in the process of upgrading postgreSQL DB from version 7.3,
and I have been experiencing some problems.

I started with pg_dump, and it finished without error messages, but
the data in some of the tables in the DB was not copied (only the table
and index definition).

The tables which were not copied were a mix of small and large
tables. Trying pg_dump -t with individual tables didn't make a
difference. I tried COPY or \copy from the psql prompt but the
command generates an empty file, although the table is not
empty. There is no error message either. The server just echoes COPY.

To save the data in these tables, I tried instead:
psql -qc 'select * from table' dbname > filename

It worked for some of the tables which were not copied during dump, but
it failed on the very large tables.

For these tables, the process either ended with segmentation fault,
or with an error message:

server sent data ("D" message) without prior row description ("T" message)

I found a posting in one of the forums saying that such error messages
could be the result of insufficient memory:

"Before 7.4, the client-side libpq library tended to go nuts like this
if the received query result overran available memory."

I installed version 7.4.30, but I cannot start the server, because the data is
not compatible. So I am stuck with version 7.3.

How do I get around this problem and save the data in the large tables?

The host operating system is linux RHEL 3

Thank you

-Jona

Well 7.x has been out of support for ages. 8.3 is the oldest version to be still supported (for one more year only). The problems you have are somewhat a result of your laziness to keep your software up-to-date.

Did you try "COPY" to spool the data into textfiles?

Other than that I could only think of trying e.g. a JDBC SQL clients to export the data to plain text (or SQL statements), e.g. http://www.sql-workbench.net (although I don't know if they will work with such an outdated Postgres version).

When configured properly the JDBC driver will not buffer the results in memory and therefor that should even work for large tables (although it seems only the 7.4 JDBC driver does support this: http://www.postgresql.org/docs/7.4/static/jdbc-query.html#JDBC-QUERY-WITH-CURSOR

If you finally manage to export the data, you should really upgrade to a modern version of Postgres. 9.2 has just been released and ynything before 8.4 should already be considered legacy. Don't even think about 7.4

Thanks for your reply, shammat.

True, I should have upgraded a long time ago. But I would have probably run into the same problems even if I started it while 7.3 was still supported.

I did try COPY and (\copy) to a text file, but it fails too on these large tables. An empty file is created (although the table is not empty),
There is no error message either. The server just echoes COPY after I type the copy command from the psql prompt.

I do intend to upgrade to the most recent version once I manage to backup the data.
I only tried 7.4 because it supposes to fix the issue with large tables, and I was hopping the data format will be compatabile. Alas, it is not.

I'll check the JDBC SQL, but I think you are right, and only version 7.4 works with cursors.

Not sure how to proceed from here without losing a lot of data?

-Jona

I don't know if this is a feature implemented only in the driver (which would mean you could use the 7.4 driver against your 7.3 database) or if that feature also needs something in the backend to work (which would mean it wouldn't work for you). But it is probably worth trying.


You should definitely take this to the PostgreSQL mailing list. Someone there might have an idea. Or maybe even ask a commercial PostgreSQL support company (http://www.postgresql.org/support/professional_support), for an offer.