#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    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
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by jonasf
    I'll check the JDBC SQL, but I think you are right, and only version 7.4 works with cursors.
    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.


    Not sure how to proceed from here without losing a lot of data?
    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.
    Last edited by shammat; September 25th, 2012 at 01:20 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo