Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 73FB52E007B for ; Fri, 28 Dec 2007 20:27:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 89006-06 for ; Fri, 28 Dec 2007 20:27:31 -0400 (AST) Received: from weatherbeeta.com.au (unknown [218.214.212.154]) by postgresql.org (Postfix) with ESMTP id 87F6A2E0062 for ; Fri, 28 Dec 2007 20:27:30 -0400 (AST) Received: (qmail 9091 invoked from network); 29 Dec 2007 00:27:28 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;f2d91247d2f179556f76db03088ad451; Received: from mail.weatherbeeta.com.au (10.20.0.2) by network-box.weatherbeeta.com.au with SMTP; 29 Dec 2007 00:27:28 -0000 Received: (qmail 9962 invoked by uid 1166); 29 Dec 2007 11:25:42 +1100 Received: from 172.20.0.155 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.0.155):. Processed in 0.18417 secs); 29 Dec 2007 00:25:42 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):. Processed in 0.18417 secs Process 9950) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 29 Dec 2007 11:25:40 +1100 From: "Phillip Smith" To: Subject: Restoring pg_dump Backup with psql Fails Date: Sat, 29 Dec 2007 11:27:25 +1100 Organization: Weatherbeeta Pty Ptd Message-ID: <003501c849b1$95ed1200$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Importance: Normal X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1198888048.97-9084-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.01/0.03 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.691 tagged_above=0 required=5 tests=AWL=-0.806, BAYES_50=0.001, MIME_QP_LONG_LINE=1.396, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200712/236 X-Sequence-Number: 27753 PostgreSQL 8.2.4 on RedHat ES4 64-bit Hi all, Doing some works on a database this morning (changing it to take data = from our new ERP system) so naturally I made a backup of the database first: pg_dump important_database > ~/backup/important-071229.sql chmod 440 ~/backup/important-071229.sql Of course things have screwed up (fortunately no-one is working today) = and I need to restore: psql ALTER DATABASE important_database RENAME TO important_database071229; CREATE DATABASE important_database; \q =09 psql important_database < ~/backup/important-071229.sql But I get a whole bunch of errors, for example: ERROR: syntax error at or near "all" LINE 1: all orders over $400.00 - freight free Order 10 of any produ... ^ ERROR: syntax error at or near "If" LINE 1: If stores spend over $300.00 they will receive a 5% discount... invalid command \. invalid command \. invalid command \N invalid command \N Both psql and pg_dump are the correct versions: [postgres@stirling ~]$ ls -lh `which pg_dump` lrwxrwxrwx 1 root root 31 Jul 25 11:51 /usr/local/bin/pg_dump -> /usr/local/pg-8.2.4/bin/pg_dump [postgres@stirling ~]$ ls -lh `which psql` lrwxrwxrwx 1 root root 28 Jul 25 11:51 /usr/local/bin/psql -> /usr/local/pg-8.2.4/bin/psql (I compile each new version of Postgres to it's own folder and data dir: /usr/local/pg-/ and /home/Postgres/pgData- ) What am I doing wrong? Cheers, ~p Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, VIC, 3131 AUSTRALIA THINK BEFORE YOU PRINT - Save paper if you don't really need to print this *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.