Thread: Frustrated...pg_dump/restore
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.
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 Ries
On Mon, Oct 6, 2008 at 8:40 AM, ries van Twisk <pg@rvt.dds.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.
On Oct 6, 2008, at 10:11 AM, Scott Marlowe wrote: > On Mon, Oct 6, 2008 at 8:40 AM, ries van Twisk <pg@rvt.dds.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
--- On Mon, 10/6/08, Scott Marlowe <scott.marlowe@gmail.com> wrote: > 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. Ahhh.... *looks at encoding* Well..they are both the same...BUT...they are set to ENCODING = 'SQL_ASCII'; That explains a lot....they should probably be set to Unicode UTF8.... Duh!!!! Any way to change encoding without dumping/restoring database?
Jeff Amiel wrote: > Ahhh.... > *looks at encoding* > > Well..they are both the same...BUT...they are set to > ENCODING = 'SQL_ASCII'; > > That explains a lot....they should probably be set to Unicode UTF8.... > Duh!!!! > > Any way to change encoding without dumping/restoring database? You can change client encoding any time with the PGCLIENTENCODING environment variable. AFAIK, there's no way to change the encoding of a database, it's set at creation time. But I think SQL_ASCII makes it less picky about the input, so that might not be the source of your problem. You should look at the errors you see _before_ the "invalid command \N". I suspect a slight schema mismatch... that could cause a COPY to fail, while an INSERT might still work. How did you create the 'schema-only database'? With a pg_dump --schema-only or with a different SQL script? You may also try and pg_dump --schema-only both databases and diff the output. .TM.