#1
  1. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    Restoring data from PG 7.1.3 -> 7.4 Release


    Having trouble with some data from a pg_dumpall from 7.1.3.

    I've done pg_dumpall -c > alldump.sql

    I'm restoring with psql -e -d template1 -f alldump.sql

    and everything looks like it imports fine until I find out that some of the biggest tables don't have their data. I traced this down to the following error in the logs:

    COPY "emp_skill" FROM stdin;
    psql:alldump.sql:12724: ERROR: literal carriage return found in data
    HINT: Use "\r" to represent carriage return.
    CONTEXT: COPY emp_skill, line 10145: "1149 V933280 NOTREQ G <strong>FirstName LastName</strong>, 03/21/2003 - 03:41PM<br>\
    NOT IN LVN SKIL..."

    groups.google.com doesn't yield many results, and this is all on a linux 7.2 box, so the carriage returns are within the actual data and not caused by transport between Windows to *nix. As you can see some of the data is also in high ascii as my predecessor when encrypting some info did not base64encode it.

    Any help would be greatly appreciated.
    -b
    PostgreSQL, it's what's for dinner...
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    For anyone that's interested here's what worked for me (Thanks to Tom Lane for his suggestion of using sed from the postgres mailing lists):

    sed 's/^M/\\r/' alldump.sql > alldump2.sql
    where ^M is entered by pressing Ctrl+V then Enter.

    If this doesn't work for anyone else here's Tom's full reply:

    Hm, that's a bit of a large version jump :-(. You might be able to make
    it work by using the 7.4 pg_dump (which I'd recommend anyway) and
    selecting -d or -D mode ... but that would be a lot slower to restore
    than the COPY-based dump.

    The most useful approach is probably just to run a quick sed script over
    the dump file to fix the embedded carriage returns.

    regards, tom lane
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo