FAQ
Hi there,
I am a newbie, trying to move a pg db from PostgreSQL 7.13 on
RedHat 7.3 to PostgreSQL 7.42 on FreeBSD 4.9. The db has one table with
32,000 rows, each containing a blob. The blobs range in size from 2K to
about 300K. The entire backup .tar file is just over 3GB.

I first tried to restore using the folloing command:
pg_restore -d athena backup.tar

It ran for a while (creating all the table defs) and the I got the
following messages:
pg_restore: ERROR: literal carriage return found in data.

So, I had some bad carriage returns. I found an answer at
(http://forums.devshed.com/archive/t-99865), which suggested using sed
to fix the carriage returns. I did that, creating backup2.tar. The
problem is, when I now run the following command (after recreating the
db athena):
pg_restore -d athena backup2.tar

I get this reply _immediately_:
pg_restore: [archiver] out of memory

This happens so fast that I'm pretty sure the restore hasn't even
*started*. So, I'm not sure how it has run out of memory. This is on a
P4 2.8 GHz system with a 2GB swap and 1GB RAM.

I can restore all the other tables individually with backups to sql
and imports, but the blob table is the most important table in the db.
I've spent about 8 hours trolling through Google and mailing lists. Any
suggestions as to how I can find out more information on my problem?

Thanks in advance for your help,
Chris McCormick

PS - If I can't get this working myself, I'd be willing to pay someone
to figure it out. What would be a reasonable amount to expect to pay
for this kind of troubleshooting?

Search Discussions

  • Chris McCormick at Jun 4, 2004 at 1:33 pm
    Hi there,
    I am a newbie, trying to move a pg db from PostgreSQL 7.13 on RedHat
    7.3 to PostgreSQL 7.42 on FreeBSD 4.9. The db has one table with 32,000
    rows, each containing a blob. The blobs range in size from 2K to about
    300K. The entire backup .tar file is just over 3GB.

    I first tried to restore using the folloing command:
    pg_restore -d athena backup.tar

    It ran for a while (creating all the table defs) and the I got the
    following messages:
    pg_restore: ERROR: literal carriage return found in data.

    So, I had some bad carriage returns. I found an answer at
    (http://forums.devshed.com/archive/t-99865), which suggested using sed
    to fix the carriage returns. I did that, creating backup2.tar. The
    problem is, when I now run the following command (after recreating the
    db athena):
    pg_restore -d athena backup2.tar

    I get this reply _immediately_:
    pg_restore: [archiver] out of memory

    This happens so fast that I'm pretty sure the restore hasn't even
    *started*. So, I'm not sure how it has run out of memory. This is on a
    P4 2.8 GHz system with a 2GB swap and 1GB RAM.

    I can restore all the other tables individually with backups to sql
    and imports, but the blob table is the most important table in the db.
    I've spent about 8 hours trolling through Google and mailing lists. Any
    suggestions as to how I can find out more information on my problem?

    Thanks in advance for your help,
    Chris McCormick

    PS - If I can't get this working myself, I'd be willing to pay someone
    to figure it out. What would be a reasonable amount to expect to pay
    for this kind of troubleshooting?
  • Tom Lane at Jun 4, 2004 at 3:00 pm

    Chris McCormick writes:
    It ran for a while (creating all the table defs) and the I got the
    following messages:
    pg_restore: ERROR: literal carriage return found in data.
    So, I had some bad carriage returns. I found an answer at
    (http://forums.devshed.com/archive/t-99865), which suggested using sed
    to fix the carriage returns. I did that, creating backup2.tar.
    You can't do that to a tar (or custom) format dump file, because you'll
    clobber the tar metadata, which is full of raw-binary numbers, not to
    mention absolute file lengths that will be wrong after sed hacks the data.

    The basic problem here is that COPY's handling of embedded CRs changed
    somewhere around 7.2, and the backwards compatibility kluge that was in
    place for awhile isn't there anymore in 7.4.

    Probably the least painful solution is to repeat the dump, adding the -d
    switch (or whichever one it is that selects dump-as-INSERTs instead of
    dump-as-COPY). This will take much longer to restore :-( but a manual
    solution that allows COPY will probably take even longer in total.

    If you can't repeat the dump because you don't have the original 7.1
    installation anymore, you might be forced into a serial upgrade: install
    7.2, reload, dump, install 7.4, reload. I think you can skip 7.3,
    or skip 7.2 and use 7.3 as the intermediate step --- IIRC both 7.2 and
    7.3 contain the backwards-compatibility kluge for the old COPY data
    format.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 3, '04 at 1:14p
activeJun 4, '04 at 3:00p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Chris McCormick: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase