Thread: Restore postgres database problem

Restore postgres database problem

From
Inpreet Singh
Date:
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 and Regards
Inpreet Singh
 

Re: Restore postgres database problem

From
Michael Fuhr
Date:
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/

Re: Restore postgres database problem

From
Luis Sousa
Date:
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*
>
>



Attachment

Re: Restore postgres database problem

From
Luis Sousa
Date:
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*
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>

Re: Restore postgres database problem

From
Michael Fuhr
Date:
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/

Re: Restore postgres database problem

From
Michael Fuhr
Date:
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/

Re: Restore postgres database problem

From
Inpreet Singh
Date:
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/
>

Re: Restore postgres database problem

From
Inpreet Singh
Date:
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/
>

Re: Restore postgres database problem

From
Inpreet Singh
Date:
*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/
>