Re: Frustrated...pg_dump/restore

On Oct 6, 2008, at 10:11 AM, Scott Marlowe wrote:

> 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.

yes you are right, stupid me...
I think they guy is looking for the custom format (-F c) to be used
with pg_restore.
but then he should's have to create his schema first... or do a data
dump only...

Ries