Обсуждение: Restore postgres database problem
Hello,
I am using postgres 7.3.4. I am trying to restore database dump using psql command but getting following error.
Taken Dump : /usr/bin/pg_dumpall > /home/inpreet/postgres.sql
Restoring Dump : su - postgres
/usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql
but getting error
ERROR: copy: line 32, Bad date external representation '\N
lost synchronization with server, resetting connection
lost synchronization with server, resetting connection
Please tell me which way to go.
Thanks and Regards
Inpreet Singh
On Tue, Jan 25, 2005 at 10:26:25AM +0530, Inpreet Singh wrote: > > I am using postgres 7.3.4. I am trying to restore database dump using > psql command but getting following error. > > Taken Dump : /usr/bin/pg_dumpall > /home/inpreet/postgres.sql > Restoring Dump : su - postgres > /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql I see that pg_dumpall and psql are in different directories. Are they from different versions of PostgreSQL? Are they connecting to backends running different versions? Which commands and which backends are running which versions? > but getting error > ERROR: copy: line 32, Bad date external representation '\N > lost synchronization with server, resetting connection What do the offending line and the line or two preceding it look like? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Which options did you use to dump data? And which options did you use to restore it? Please submit the command lines used. Luis Sousa Inpreet Singh wrote: > Hello, > I am using postgres 7.3.4. I am trying to restore database dump using > psql command but getting following error. > > *Taken Dump :* /usr/bin/pg_dumpall > /home/inpreet/postgres.sql > *Restoring Dump :* su - postgres > /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql > > but getting error > ERROR: copy: line 32, Bad date external representation '\N > lost synchronization with server, resetting connection > > Please tell me which way to go. > > Thanks a*n*d Regards > *Inpreet Singh* > >
Вложения
The command I use for dump is: pg_dump -Fc -h <host name> <database name> > <dump file> For restore, I create the database first on pgsql, and then (on shell) I give the command: pg_restore -d <database name> <dump file> Luis Sousa Inpreet Singh wrote: >*Taken Dump :* /usr/bin/pg_dumpall > /home/inpreet/postgres.sql /*which >was taken at client site*/ > >*Restoring Dump :* su - postgres /*This process I am doing*/ > /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql > >Sir I have some table values which are of type timestamp and those columns >have values like >""2004-10-09 10:03:36.689993"". So is it possible that when I am restoring >values this columns are creating all sort of trouble??? as timestamp field >means date time at the time of insertion of record and I am trying to insert >earlier date time. > >Regards >Inpreet Singh > >----- Original Message ----- >From: "Luis Sousa" <llsousa@ualg.pt> >To: "Inpreet Singh" <Singh.Inpreet@netsity.com> >Sent: Wednesday, January 26, 2005 3:38 PM >Subject: Re: [ADMIN] Restore postgres database problem > > > > >>Which options did you use to dump data? >>And which options did you use to restore it? >>Please submit the command lines used. >> >>Luis Sousa >> >>Inpreet Singh wrote: >> >> >> >>>Hello, >>>I am using postgres 7.3.4. I am trying to restore database dump using >>>psql command but getting following error. >>> >>>*Taken Dump :* /usr/bin/pg_dumpall > /home/inpreet/postgres.sql >>>*Restoring Dump :* su - postgres >>>/usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql >>> >>>but getting error >>>ERROR: copy: line 32, Bad date external representation '\N >>>lost synchronization with server, resetting connection >>> >>>Please tell me which way to go. >>> >>>Thanks a*n*d Regards >>>*Inpreet Singh* >>> >>> >>> >>> >> >> > > > >
On Thu, Jan 27, 2005 at 11:03:43AM +0530, Inpreet Singh wrote: > *Taken Dump :* /usr/bin/pg_dumpall > /home/inpreet/postgres.sql /*which > was taken at client site*/ > > *Restoring Dump :* su - postgres /*This process I am doing at local site > so thats why psql and dumpall are in diff directories*/ > /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql Is the local site running the same version of PostgreSQL or a later version than the client site? If you try to restore into an earlier version then you could have problems. How are you transferring the file from the client site to the local site? Is it possible that the file was modified in transit? I wonder if carriage returns have been added -- do you see ^M at the ends of lines when you run the following command? head postgres.sql | cat -v > Sir I have some table values which are of type timestamp and those columns > have values like > ""2004-10-09 10:03:36.689993"". So is it possible that when I am restoring > values this columns are creating all sort of trouble??? as timestamp field > means date time at the time of insertion of record and I am trying to insert > earlier date time. The "timestamp" type holds a date and time; PostgreSQL shouldn't care about the value as long as it's syntactically correct (although it's possible that a trigger or constraint might care). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Jan 28, 2005 at 11:00:44AM +0530, Inpreet Singh wrote: > Yes it returning carriage returns. I am pasting below the output to the > command u send to me > > *******Linux Prompt********************** > [root@localhost inpreet]# head postgres.sql | cat -v > --^M > -- PostgreSQL database cluster dump^M > --^M You could strip the carriage returns with tr, sed, perl, dos2unix, etc. Here's an example: tr -d '\r' < postgres.sql > fixed.sql # or pipe into psql What kind of system did you create the dump on? How did you transfer the file? If you can figure out how the carriage returns got there then you might be able to prevent it from happening in the future. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Yes it returning carriage returns. I am pasting below the output to the command u send to me *******Linux Prompt********************** [root@localhost inpreet]# head postgres.sql | cat -v --^M -- PostgreSQL database cluster dump^M --^M ^M \connect "template1"^M ^M --^M -- Users^M --^M ^M *************************************** Now what should I do? Regards Inpreet Singh ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Inpreet Singh" <Singh.Inpreet@netsity.com> Cc: <pgsql-admin@postgresql.org> Sent: Friday, January 28, 2005 10:34 AM Subject: Re: [ADMIN] Restore postgres database problem > On Thu, Jan 27, 2005 at 11:03:43AM +0530, Inpreet Singh wrote: > > > *Taken Dump :* /usr/bin/pg_dumpall > /home/inpreet/postgres.sql /*which > > was taken at client site*/ > > > > *Restoring Dump :* su - postgres /*This process I am doing at local site > > so thats why psql and dumpall are in diff directories*/ > > /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql > > Is the local site running the same version of PostgreSQL or a later > version than the client site? If you try to restore into an earlier > version then you could have problems. > > How are you transferring the file from the client site to the local > site? Is it possible that the file was modified in transit? I > wonder if carriage returns have been added -- do you see ^M at the > ends of lines when you run the following command? > > head postgres.sql | cat -v > > > Sir I have some table values which are of type timestamp and those columns > > have values like > > ""2004-10-09 10:03:36.689993"". So is it possible that when I am restoring > > values this columns are creating all sort of trouble??? as timestamp field > > means date time at the time of insertion of record and I am trying to insert > > earlier date time. > > The "timestamp" type holds a date and time; PostgreSQL shouldn't > care about the value as long as it's syntactically correct (although > it's possible that a trigger or constraint might care). > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >
Yes dos2unix <postgres.sql> worked absolutely fine. I got the solution of my problem. Thanks alot for your co-operation. I am using Linux Red hat 9 and my client is also using the same platform but when he emailed me this sql file he first saved file on windows and then I saved it on windows and saved n run on linux So I think this procedure leads to carriage return. but dos2unix command sorted out the problem. ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Inpreet Singh" <Singh.Inpreet@netsity.com> Cc: <pgsql-admin@postgresql.org> Sent: Friday, January 28, 2005 11:18 AM Subject: Re: [ADMIN] Restore postgres database problem > On Fri, Jan 28, 2005 at 11:00:44AM +0530, Inpreet Singh wrote: > > > Yes it returning carriage returns. I am pasting below the output to the > > command u send to me > > > > *******Linux Prompt********************** > > [root@localhost inpreet]# head postgres.sql | cat -v > > --^M > > -- PostgreSQL database cluster dump^M > > --^M > > You could strip the carriage returns with tr, sed, perl, dos2unix, > etc. Here's an example: > > tr -d '\r' < postgres.sql > fixed.sql # or pipe into psql > > What kind of system did you create the dump on? How did you transfer > the file? If you can figure out how the carriage returns got there > then you might be able to prevent it from happening in the future. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >
*Taken Dump :* /usr/bin/pg_dumpall > /home/inpreet/postgres.sql /*which was taken at client site*/ *Restoring Dump :* su - postgres /*This process I am doing at local site so thats why psql and dumpall are in diff directories*/ /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql Sir I have some table values which are of type timestamp and those columns have values like ""2004-10-09 10:03:36.689993"". So is it possible that when I am restoring values this columns are creating all sort of trouble??? as timestamp field means date time at the time of insertion of record and I am trying to insert earlier date time. Regards Inpreet Singh ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Inpreet Singh" <Singh.Inpreet@netsity.com> Cc: <pgsql-admin@postgresql.org> Sent: Wednesday, January 26, 2005 3:08 AM Subject: Re: [ADMIN] Restore postgres database problem > On Tue, Jan 25, 2005 at 10:26:25AM +0530, Inpreet Singh wrote: > > > > I am using postgres 7.3.4. I am trying to restore database dump using > > psql command but getting following error. > > > > Taken Dump : /usr/bin/pg_dumpall > /home/inpreet/postgres.sql > > Restoring Dump : su - postgres > > /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql > > I see that pg_dumpall and psql are in different directories. Are > they from different versions of PostgreSQL? Are they connecting > to backends running different versions? Which commands and which > backends are running which versions? > > > but getting error > > ERROR: copy: line 32, Bad date external representation '\N > > lost synchronization with server, resetting connection > > What do the offending line and the line or two preceding it look > like? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >