From Singh.Inpreet@netsity.com Tue Jan 25 04:56:25 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 973DB3A56D3 for ; Tue, 25 Jan 2005 04:56:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72743-07 for ; Tue, 25 Jan 2005 04:56:23 +0000 (GMT) Received: from mail.netsity.com (unknown [61.246.180.169]) by svr1.postgresql.org (Postfix) with ESMTP id E3C793A56C8 for ; Tue, 25 Jan 2005 04:56:22 +0000 (GMT) Received: by mail.netsity.com with Internet Mail Service (5.5.2653.19) id ; Tue, 25 Jan 2005 10:26:33 +0530 Received: from ISINGH ([192.168.103.60]) by mail.netsity.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id D3CHC12M; Tue, 25 Jan 2005 10:26:25 +0530 From: Inpreet Singh To: pgsql-admin@postgresql.org Message-ID: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> Subject: Restore postgres database problem Date: Tue, 25 Jan 2005 10:26:25 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_006A_01C502C8.52AA1480" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.028 tagged_above=0 required=5 tests=HTML_60_70, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200501/262 X-Sequence-Number: 16121 This is a multi-part message in MIME format. ------=_NextPart_000_006A_01C502C8.52AA1480 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I am using postgres 7.3.4. I am trying to restore database dump using psql command but getting following error. =20 Taken Dump : /usr/bin/pg_dumpall > /home/inpreet/postgres.sql=20 Restoring Dump : su - postgres /usr/local/pgsql/bin/psql -d template1 < /home/inpreet/postgres.sql =20 but getting error ERROR: copy: line 32, Bad date external representation '\N lost synchronization with server, resetting connection =20 Please tell me which way to go. =20 Thanks and Regards Inpreet Singh =20 ------=_NextPart_000_006A_01C502C8.52AA1480 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello,
I am using postgres 7.3.4. I am trying = to restore=20 database dump using psql command but getting following = error.
 
Taken Dump = :  =20 /usr/bin/pg_dumpall > /home/inpreet/postgres.sql
Restoring Dump = :   su -=20 postgres
/usr/local/pgsql/bin/psql -d template1 = <=20 /home/inpreet/postgres.sql
 
but getting error
ERROR:  copy: line 32, Bad date = external=20 representation '\N
lost synchronization with server, resetting=20 connection
 
Please tell me which way to = go.
 
Thanks and = Regards
Inpreet = Singh
 

------=_NextPart_000_006A_01C502C8.52AA1480-- From mike@fuhr.org Tue Jan 25 21:39:13 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C5C3D3A568F for ; Tue, 25 Jan 2005 21:39:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23268-01 for ; Tue, 25 Jan 2005 21:39:01 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 43D343A46A0 for ; Tue, 25 Jan 2005 21:39:02 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0PLct56014022 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 25 Jan 2005 14:38:58 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0PLctVq095941; Tue, 25 Jan 2005 14:38:55 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0PLcloB095940; Tue, 25 Jan 2005 14:38:47 -0700 (MST) (envelope-from mfuhr) Date: Tue, 25 Jan 2005 14:38:47 -0700 From: Michael Fuhr To: Inpreet Singh Cc: pgsql-admin@postgresql.org Subject: Re: Restore postgres database problem Message-ID: <20050125213847.GA95908@winnie.fuhr.org> References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/264 X-Sequence-Number: 16123 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/ From llsousa@ualg.pt Wed Jan 26 13:13:41 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ACE6F3A573C for ; Wed, 26 Jan 2005 13:13:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15787-04 for ; Wed, 26 Jan 2005 13:13:34 +0000 (GMT) Received: from smtp.ualg.pt (unknown [193.136.224.8]) by svr1.postgresql.org (Postfix) with ESMTP id 21B443A5736 for ; Wed, 26 Jan 2005 13:13:35 +0000 (GMT) Received: from localhost (smtp.ualg.pt [127.0.0.1]) by smtp.ualg.pt (Postfix) with ESMTP id 9920D4BC3A for ; Wed, 26 Jan 2005 14:13:11 +0000 (WET) Received: from smtp.ualg.pt ([127.0.0.1]) by localhost (smtp.ualg.pt [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 14362-10 for ; Wed, 26 Jan 2005 14:13:09 +0000 (WET) Received: from [10.10.20.85] (raio.ualg.pt [10.10.20.85]) by smtp.ualg.pt (Postfix) with ESMTP id CE0C74BC9C for ; Wed, 26 Jan 2005 14:13:08 +0000 (WET) Message-ID: <41F79758.2030802@ualg.pt> Date: Wed, 26 Jan 2005 13:12:56 +0000 From: Luis Sousa User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.5) Gecko/20050105 Debian/1.7.5-1 X-Accept-Language: en-pt MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Restore postgres database problem References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> In-Reply-To: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms070505070608010004020905" X-Virus-Scanned: by amavisd-new X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/268 X-Sequence-Number: 16127 This is a cryptographically signed message in MIME format. --------------ms070505070608010004020905 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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* > > --------------ms070505070608010004020905 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIJ1jCC BOcwggSRoAMCAQICChKPTqUAAgAAAMUwDQYJKoZIhvcNAQEFBQAwgaExHDAaBgkqhkiG9w0B CQEWDWRyb3NhQHVhbGcucHQxCzAJBgNVBAYTAlBUMRAwDgYDVQQIEwdBbGdhcnZlMQ0wCwYD VQQHEwRGYXJvMSAwHgYDVQQKExdVbml2ZXJzaWRhZGUgZG8gQWxnYXJ2ZTEgMB4GA1UECxMX U2Vydmljb3MgZGUgSW5mb3JtYXRpY2ExDzANBgNVBAMTBlVhbE5ldDAeFw0wNDA2MzAxMzQ4 MDZaFw0wNTA2MzAxMzQ4MDZaMEgxHjAcBgkqhkiG9w0BCQEWD2xsc291c2FAdWFsZy5wdDEm MCQGA1UEAxMdTFVJUyBNSUdVRUwgTElNQSBTT1VTQSAoMTY3NCkwggEiMA0GCSqGSIb3DQEB AQUAA4IBDwAwggEKAoIBAQDcUYAS5FaaqQWOwhPWXpgjxgxhCz1ENCm7dENtTr5mJytdhVXB z7XY9eoRgGcKlNUQC9q56jww2n08gt9em7S2nEcj8BV7TWIf4wkU3YyF6BPAhRL4Km7+P7s7 szVGPjcs2mp7by7a5T51CI2cFkSqw2bbr+QGdEytfTedPXgC751EbaRNpO/sSjtzNz+s1jGK fUHVJrZbtQdB2k0UNfh3s7mAYTGEnNAXSj+xI/5mr/lw9k93M+TcRwZrLMHFJGT7hytmUs7w 7w7ubIo4Rs9kl2KEnjY3JwIdldWg9EfZKH/ehI6XbwKKLHGqvo+F8k3MWF9GGhVo2leJKul1 A67pAgMBAAGjggI5MIICNTAdBgNVHQ4EFgQU36rA18vDYUwKmiFfzElm5cJAXBkwgd0GA1Ud IwSB1TCB0oAUDlemmBHtWUTtGV2FEEod0l87V/qhgaekgaQwgaExHDAaBgkqhkiG9w0BCQEW DWRyb3NhQHVhbGcucHQxCzAJBgNVBAYTAlBUMRAwDgYDVQQIEwdBbGdhcnZlMQ0wCwYDVQQH EwRGYXJvMSAwHgYDVQQKExdVbml2ZXJzaWRhZGUgZG8gQWxnYXJ2ZTEgMB4GA1UECxMXU2Vy dmljb3MgZGUgSW5mb3JtYXRpY2ExDzANBgNVBAMTBlVhbE5ldIIQS1dGfnTvEatHwdnJY6a3 STA8BgNVHR8ENTAzMDGgL6AthitodHRwOi8vd3d3LnNpLnVhbGcucHQvQ2VydEVucm9sbC9V YWxOZXQuY3JsMFoGCCsGAQUFBwEBBE4wTDBKBggrBgEFBQcwAoY+aHR0cDovL3d3dy5zaS51 YWxnLnB0L0NlcnRFbnJvbGwvcHVjY2luaS51YWxnLnB0X1VhbE5ldCgyKS5jcnQwDAYDVR0T AQH/BAIwADALBgNVHQ8EBAMCBaAwKQYDVR0lBCIwIAYKKwYBBAGCNwoDBAYIKwYBBQUHAwQG CCsGAQUFBwMCMBcGCSsGAQQBgjcUAgQKHggAVQBzAGUAcjA7BgNVHREENDAyoB8GCisGAQQB gjcUAgOgEQwPbGxzb3VzYUB1YWxnLnB0gQ9sbHNvdXNhQHVhbGcucHQwDQYJKoZIhvcNAQEF BQADQQBb4RvnTkyDA+1CU+1x8/nAjpulQ8m63FWWi8FIkPPiU1xXkg5R6+wIut9+6A1HoWh+ 5CkP1pVZtQUi+4/F5Zc4MIIE5zCCBJGgAwIBAgIKEo9OpQACAAAAxTANBgkqhkiG9w0BAQUF ADCBoTEcMBoGCSqGSIb3DQEJARYNZHJvc2FAdWFsZy5wdDELMAkGA1UEBhMCUFQxEDAOBgNV BAgTB0FsZ2FydmUxDTALBgNVBAcTBEZhcm8xIDAeBgNVBAoTF1VuaXZlcnNpZGFkZSBkbyBB bGdhcnZlMSAwHgYDVQQLExdTZXJ2aWNvcyBkZSBJbmZvcm1hdGljYTEPMA0GA1UEAxMGVWFs TmV0MB4XDTA0MDYzMDEzNDgwNloXDTA1MDYzMDEzNDgwNlowSDEeMBwGCSqGSIb3DQEJARYP bGxzb3VzYUB1YWxnLnB0MSYwJAYDVQQDEx1MVUlTIE1JR1VFTCBMSU1BIFNPVVNBICgxNjc0 KTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBANxRgBLkVpqpBY7CE9ZemCPGDGEL PUQ0Kbt0Q21OvmYnK12FVcHPtdj16hGAZwqU1RAL2rnqPDDafTyC316btLacRyPwFXtNYh/j CRTdjIXoE8CFEvgqbv4/uzuzNUY+NyzaantvLtrlPnUIjZwWRKrDZtuv5AZ0TK19N509eALv nURtpE2k7+xKO3M3P6zWMYp9QdUmtlu1B0HaTRQ1+HezuYBhMYSc0BdKP7Ej/mav+XD2T3cz 5NxHBmsswcUkZPuHK2ZSzvDvDu5sijhGz2SXYoSeNjcnAh2V1aD0R9kof96EjpdvAooscaq+ j4XyTcxYX0YaFWjaV4kq6XUDrukCAwEAAaOCAjkwggI1MB0GA1UdDgQWBBTfqsDXy8NhTAqa IV/MSWblwkBcGTCB3QYDVR0jBIHVMIHSgBQOV6aYEe1ZRO0ZXYUQSh3SXztX+qGBp6SBpDCB oTEcMBoGCSqGSIb3DQEJARYNZHJvc2FAdWFsZy5wdDELMAkGA1UEBhMCUFQxEDAOBgNVBAgT B0FsZ2FydmUxDTALBgNVBAcTBEZhcm8xIDAeBgNVBAoTF1VuaXZlcnNpZGFkZSBkbyBBbGdh cnZlMSAwHgYDVQQLExdTZXJ2aWNvcyBkZSBJbmZvcm1hdGljYTEPMA0GA1UEAxMGVWFsTmV0 ghBLV0Z+dO8Rq0fB2cljprdJMDwGA1UdHwQ1MDMwMaAvoC2GK2h0dHA6Ly93d3cuc2kudWFs Zy5wdC9DZXJ0RW5yb2xsL1VhbE5ldC5jcmwwWgYIKwYBBQUHAQEETjBMMEoGCCsGAQUFBzAC hj5odHRwOi8vd3d3LnNpLnVhbGcucHQvQ2VydEVucm9sbC9wdWNjaW5pLnVhbGcucHRfVWFs TmV0KDIpLmNydDAMBgNVHRMBAf8EAjAAMAsGA1UdDwQEAwIFoDApBgNVHSUEIjAgBgorBgEE AYI3CgMEBggrBgEFBQcDBAYIKwYBBQUHAwIwFwYJKwYBBAGCNxQCBAoeCABVAHMAZQByMDsG A1UdEQQ0MDKgHwYKKwYBBAGCNxQCA6ARDA9sbHNvdXNhQHVhbGcucHSBD2xsc291c2FAdWFs Zy5wdDANBgkqhkiG9w0BAQUFAANBAFvhG+dOTIMD7UJT7XHz+cCOm6VDybrcVZaLwUiQ8+JT XFeSDlHr7Ai6337oDUehaH7kKQ/WlVm1BSL7j8XllzgxggQXMIIEEwIBATCBsDCBoTEcMBoG CSqGSIb3DQEJARYNZHJvc2FAdWFsZy5wdDELMAkGA1UEBhMCUFQxEDAOBgNVBAgTB0FsZ2Fy dmUxDTALBgNVBAcTBEZhcm8xIDAeBgNVBAoTF1VuaXZlcnNpZGFkZSBkbyBBbGdhcnZlMSAw HgYDVQQLExdTZXJ2aWNvcyBkZSBJbmZvcm1hdGljYTEPMA0GA1UEAxMGVWFsTmV0AgoSj06l AAIAAADFMAkGBSsOAwIaBQCgggI7MBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZI hvcNAQkFMQ8XDTA1MDEyNjEzMTI1NlowIwYJKoZIhvcNAQkEMRYEFEpWfoIpk9cup+2zMGzl e3D9HK3RMFIGCSqGSIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0G CCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIHBBgkrBgEEAYI3EAQxgbMw gbAwgaExHDAaBgkqhkiG9w0BCQEWDWRyb3NhQHVhbGcucHQxCzAJBgNVBAYTAlBUMRAwDgYD VQQIEwdBbGdhcnZlMQ0wCwYDVQQHEwRGYXJvMSAwHgYDVQQKExdVbml2ZXJzaWRhZGUgZG8g QWxnYXJ2ZTEgMB4GA1UECxMXU2Vydmljb3MgZGUgSW5mb3JtYXRpY2ExDzANBgNVBAMTBlVh bE5ldAIKEo9OpQACAAAAxTCBwwYLKoZIhvcNAQkQAgsxgbOggbAwgaExHDAaBgkqhkiG9w0B CQEWDWRyb3NhQHVhbGcucHQxCzAJBgNVBAYTAlBUMRAwDgYDVQQIEwdBbGdhcnZlMQ0wCwYD VQQHEwRGYXJvMSAwHgYDVQQKExdVbml2ZXJzaWRhZGUgZG8gQWxnYXJ2ZTEgMB4GA1UECxMX U2Vydmljb3MgZGUgSW5mb3JtYXRpY2ExDzANBgNVBAMTBlVhbE5ldAIKEo9OpQACAAAAxTAN BgkqhkiG9w0BAQEFAASCAQAY5iVsT6TI6Xe0HOnwbok7TwPv9Z5wEeOMGqwXGb1QvhOPEW9h Sf5QYAJR5OXMWLUYUEO7NB2wbVzeeSBOAWOV7OJj4nPy2XGNGBfa7cleVxdKT4iKfm7nAsT3 qT7yvQivqwOkCgLUVk0HnSbJCKd7AcOnPo3GOxWEuZSOcmXnp0CBdpJa6BOXgyEas7YvvSvd G8Il+vQsJwcRlT2V6vaHKPgCkzLvhIAGza4/805pTV+6jutl6MGiYM2gHU9IxQGcGHltPTxX IFA2mU6kinoPzeptbQm0Rwc+bT7iIsx35gcV9u24DPQsgHH8AVadbPTsEVw0oK1WSxDI0dYB QRyhAAAAAAAA --------------ms070505070608010004020905-- From Singh.Inpreet@netsity.com Thu Jan 27 05:33:39 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9570A3A5DE3 for ; Thu, 27 Jan 2005 05:33:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93189-06 for ; Thu, 27 Jan 2005 05:33:36 +0000 (GMT) Received: from mail.netsity.com (unknown [61.246.180.169]) by svr1.postgresql.org (Postfix) with ESMTP id DBA843A5DD3 for ; Thu, 27 Jan 2005 05:33:34 +0000 (GMT) Received: by mail.netsity.com with Internet Mail Service (5.5.2653.19) id ; Thu, 27 Jan 2005 11:03:46 +0530 Received: from ISINGH ([192.168.103.60]) by mail.netsity.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id D3CHCH5S; Thu, 27 Jan 2005 11:03:43 +0530 From: Inpreet Singh To: Michael Fuhr Cc: pgsql-admin@postgresql.org Message-ID: <00bc01c50431$c3997590$3c67a8c0@netsity.com> References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> <20050125213847.GA95908@winnie.fuhr.org> Subject: Re: Restore postgres database problem Date: Thu, 27 Jan 2005 11:03:43 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.329 tagged_above=0 required=5 tests=AWL, URIBL_SBL X-Spam-Level: X-Archive-Number: 200501/297 X-Sequence-Number: 16156 *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" To: "Inpreet Singh" Cc: 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/ > From llsousa@ualg.pt Thu Jan 27 10:02:19 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 70CF43A5DA3 for ; Thu, 27 Jan 2005 10:02:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32135-09 for ; Thu, 27 Jan 2005 10:02:11 +0000 (GMT) Received: from smtp.ualg.pt (smtp1.ualg.pt [193.136.224.8]) by svr1.postgresql.org (Postfix) with ESMTP id B91503A5DA5 for ; Thu, 27 Jan 2005 10:02:11 +0000 (GMT) Received: from localhost (smtp.ualg.pt [127.0.0.1]) by smtp.ualg.pt (Postfix) with ESMTP id 06DFE4BB83; Thu, 27 Jan 2005 11:01:44 +0000 (WET) Received: from smtp.ualg.pt ([127.0.0.1]) by localhost (smtp.ualg.pt [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 02455-01; Thu, 27 Jan 2005 11:01:41 +0000 (WET) Received: from [10.10.20.85] (raio.ualg.pt [10.10.20.85]) by smtp.ualg.pt (Postfix) with ESMTP id C5C614BB53; Thu, 27 Jan 2005 11:01:36 +0000 (WET) Message-ID: <41F8BBF7.6070600@ualg.pt> Date: Thu, 27 Jan 2005 10:01:27 +0000 From: Luis Sousa User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.5) Gecko/20050105 Debian/1.7.5-1 X-Accept-Language: en-pt MIME-Version: 1.0 To: Inpreet Singh Cc: pgsql-admin Subject: Re: Restore postgres database problem References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> <41F76C2C.8070007@ualg.pt> <00ad01c50431$7c5f5be0$3c67a8c0@netsity.com> In-Reply-To: <00ad01c50431$7c5f5be0$3c67a8c0@netsity.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.34 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, URIBL_SBL X-Spam-Level: X-Archive-Number: 200501/275 X-Sequence-Number: 16134 The command I use for dump is: pg_dump -Fc -h > For restore, I create the database first on pgsql, and then (on shell) I give the command: pg_restore -d 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" >To: "Inpreet Singh" >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* >>> >>> >>> >>> >> >> > > > > From mike@fuhr.org Fri Jan 28 05:04:47 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D028A3A4280 for ; Fri, 28 Jan 2005 05:04:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00117-03 for ; Fri, 28 Jan 2005 05:04:32 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id AE0D13A61E0 for ; Fri, 28 Jan 2005 05:04:31 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0S54QeL002048 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 27 Jan 2005 22:04:28 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0S54P77083765; Thu, 27 Jan 2005 22:04:26 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0S54FX3083696; Thu, 27 Jan 2005 22:04:15 -0700 (MST) (envelope-from mfuhr) Date: Thu, 27 Jan 2005 22:04:15 -0700 From: Michael Fuhr To: Inpreet Singh Cc: pgsql-admin@postgresql.org Subject: Re: Restore postgres database problem Message-ID: <20050128050415.GA70681@winnie.fuhr.org> References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> <20050125213847.GA95908@winnie.fuhr.org> <00bc01c50431$c3997590$3c67a8c0@netsity.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <00bc01c50431$c3997590$3c67a8c0@netsity.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/284 X-Sequence-Number: 16143 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/ From Singh.Inpreet@netsity.com Fri Jan 28 05:30:56 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0D0F33A61CF for ; Fri, 28 Jan 2005 05:30:56 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02104-08 for ; Fri, 28 Jan 2005 05:30:48 +0000 (GMT) Received: from mail.netsity.com (unknown [61.246.180.169]) by svr1.postgresql.org (Postfix) with ESMTP id A77DD3A61C0 for ; Fri, 28 Jan 2005 05:30:44 +0000 (GMT) Received: by mail.netsity.com with Internet Mail Service (5.5.2653.19) id ; Fri, 28 Jan 2005 11:00:54 +0530 Received: from ISINGH ([192.168.103.60]) by mail.netsity.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id D3CHCKA8; Fri, 28 Jan 2005 11:00:44 +0530 From: Inpreet Singh To: Michael Fuhr Cc: pgsql-admin@postgresql.org Message-ID: <004a01c504fa$8388e890$3c67a8c0@netsity.com> References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> <20050125213847.GA95908@winnie.fuhr.org> <00bc01c50431$c3997590$3c67a8c0@netsity.com> <20050128050415.GA70681@winnie.fuhr.org> Subject: Re: Restore postgres database problem Date: Fri, 28 Jan 2005 11:00:44 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.382 tagged_above=0 required=5 tests=AWL, URIBL_SBL X-Spam-Level: X-Archive-Number: 200501/295 X-Sequence-Number: 16154 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" To: "Inpreet Singh" Cc: 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/ > From mike@fuhr.org Fri Jan 28 05:48:25 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CA6BB3A61C8 for ; Fri, 28 Jan 2005 05:48:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03390-10 for ; Fri, 28 Jan 2005 05:48:23 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id C275C3A61F6 for ; Fri, 28 Jan 2005 05:48:23 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0S5mJHd002083 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 27 Jan 2005 22:48:21 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0S5mIR5099217; Thu, 27 Jan 2005 22:48:18 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0S5mIxi099216; Thu, 27 Jan 2005 22:48:18 -0700 (MST) (envelope-from mfuhr) Date: Thu, 27 Jan 2005 22:48:18 -0700 From: Michael Fuhr To: Inpreet Singh Cc: pgsql-admin@postgresql.org Subject: Re: Restore postgres database problem Message-ID: <20050128054818.GA95253@winnie.fuhr.org> References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> <20050125213847.GA95908@winnie.fuhr.org> <00bc01c50431$c3997590$3c67a8c0@netsity.com> <20050128050415.GA70681@winnie.fuhr.org> <004a01c504fa$8388e890$3c67a8c0@netsity.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <004a01c504fa$8388e890$3c67a8c0@netsity.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/285 X-Sequence-Number: 16144 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/ From Singh.Inpreet@netsity.com Fri Jan 28 06:35:14 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CA05D3A6205 for ; Fri, 28 Jan 2005 06:35:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08392-08 for ; Fri, 28 Jan 2005 06:35:06 +0000 (GMT) Received: from mail.netsity.com (unknown [61.246.180.169]) by svr1.postgresql.org (Postfix) with ESMTP id 37C023A61E1 for ; Fri, 28 Jan 2005 06:35:05 +0000 (GMT) Received: by mail.netsity.com with Internet Mail Service (5.5.2653.19) id ; Fri, 28 Jan 2005 12:05:18 +0530 Received: from ISINGH ([192.168.103.60]) by mail.netsity.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id D3CHCKH4; Fri, 28 Jan 2005 12:05:15 +0530 From: Inpreet Singh To: Michael Fuhr Cc: pgsql-admin@postgresql.org Message-ID: <008c01c50503$868326b0$3c67a8c0@netsity.com> References: <006d01c5029a$38fb4e60$3c67a8c0@netsity.com> <20050125213847.GA95908@winnie.fuhr.org> <00bc01c50431$c3997590$3c67a8c0@netsity.com> <20050128050415.GA70681@winnie.fuhr.org> <004a01c504fa$8388e890$3c67a8c0@netsity.com> <20050128054818.GA95253@winnie.fuhr.org> Subject: Re: Restore postgres database problem Date: Fri, 28 Jan 2005 12:05:14 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.088 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/296 X-Sequence-Number: 16155 Yes dos2unix 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" To: "Inpreet Singh" Cc: 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/ >