Re: Frustrated...pg_dump/restore

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "ries van Twisk" <pg(at)rvt(dot)dds(dot)nl>
Cc: becauseimjeff(at)yahoo(dot)com, PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Frustrated...pg_dump/restore
Date: 2008-10-06 15:11:14
Message-ID: dcc563d10810060811o1021fc24j4599a0aa59e201d7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Oct 6, 2008 at 8:40 AM, ries van Twisk <pg(at)rvt(dot)dds(dot)nl> wrote:
>
> On Oct 6, 2008, at 9:11 AM, Jeff Amiel wrote:
>
>>
>> I performed a pg_dump on a database and created a new schema-only database
>> to copy that data into.
>>
>> However trying to use psql -f to load the data in, I get a plethora of
>> syntax errors including the dreaded "invalid command \N".
>>
>> I even tried to pipe the pg_dump results directly into the psql
>> command....
>>
>> /usr/local/pgsql/bin/pg_dump -U pgsql --data-only db1 |
>> /usr/local/pgsql/bin/psql -U pgsql db2
>>
>> Same results.
>>
>> Why?
>>
>> using -d (switching to inserts instead of COPY) seems to work just fine
>> but is so slow as to be unusable.
>>
>> Is the COPY pg_dump method useful at all in ANY situation?
>>
>> Do I have to do a pg_dump using a custom archive option and use pg_resore
>> to make this work? (sounds silly to me).
>>
>> Any help would be appreciated.
>
> I think you want top use pg_restore, the default of pg_dump is a binary
> output and you cannot pipe it to psql

Nope, that's exactly reversed. the default of pg_dump is plain text
output, and you have to use the custom format to get a binary backup.
I'm wondering if the OP has some line breaks in his data that are
getting misinterpreted, or maybe his encoding on the two dbs is
different and he's not taking care of that.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alain Roger 2008-10-06 15:12:41 Re: restore a dump db from tar file
Previous Message Gerfried Fuchs 2008-10-06 15:04:43 Re: [Pkg-postgresql-public] Postgres major version support policy on Debian