Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Restoring 1.5GB SQL dump (Postgres)

    This seemed like the most appropriate place so I decided to post here.

    Fedora Core 4, 3GHz, 2GB RAM
    Postgres 8, Apache 2, PHP 4

    The issue is that I have with me a 1.5GB SQL dump from Postgres 8 which I extracted using pg_dump.

    Restoring it seems to be an issue on this PC. I've tried modifying memory directives in postgresql.conf as well as the kernels SHMAX value but no go.

    I tried a bunch of other things such as gzipping the file so that it was only 100mb and doing:
    gunzip -c dump.sql.gz | psql my_db
    But I recieved many errors saying "invalid command \N" and I also got a syntax error notice and the process terminated.

    I tried to at least try and enter some data manually but vim is incredibly slow since this is a 1.5GB file.

    Is there a way I could perhaps split up my db dump and then import them into the db one by one?
    Any help would be greatly appreciated since this is an urgent matter

  2. #2
    Join Date
    May 2006
    Teh Interweb
    I am not very familiar with PSQL however it seems your issue is with the backup file itself.

    invalid command \N
    This would indicate to me that there are newline codes at the end of each line and PSQL is seeing them when trying to import the DB.

    To combat this, you may want to try running a dos2unix command on the file to ensure it strips the file of all special characters.

    As stated previously, I do not have a lot of experience with PSQL however, this is worth a shot. I would hate to have to import a 1.5g db line by line.

    [[email protected]] ~ $ cat .signature
    cat: .signature: No such file or directory

  3. #3
    Join Date
    Jun 2004
    thanks for the reply CaroTech04

    it turns out i had ommited the required parameters for creating an SQL dump with complete inserts when extracting the dump. I had created a dump that used the copy command so any NULL values were replaced with \N

    what i did was i recreated the dump on the remote host, gzip'd it and downloaded it. thank god for gzip or i'd be downloading a 4.5 GB (thats the size at the moment) SQL instead of a 200MB .gz file and that's not too much fun either on the internet speeds that we get in Sri Lanka

    thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts