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 981313A4A87 for ; Mon, 15 Nov 2004 21:00:34 +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 47089-06 for ; Mon, 15 Nov 2004 21:00:22 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 326123A4A7D for ; Mon, 15 Nov 2004 21:00:22 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iAFL0Mne010657; Mon, 15 Nov 2004 16:00:22 -0500 (EST) To: Christian Fowler Cc: pgsql-admin list Subject: Re: evil characters #bfef cause dump failure In-reply-to: References: <9996.1100547889@sss.pgh.pa.us> <10478.1100550946@sss.pgh.pa.us> Comments: In-reply-to Christian Fowler message dated "Mon, 15 Nov 2004 15:44:20 -0500" Date: Mon, 15 Nov 2004 16:00:22 -0500 Message-ID: <10656.1100552422@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/122 X-Sequence-Number: 15506 Christian Fowler writes: > server_encoding > ----------------- > SQL_ASCII > whoa! yikes, I bet this has a lot to do with it? I really wanted to keep > everything UNICODE end-to-end. I must have forgotten --encoding on my > initdb? Anything I can do at this point? Hmm ... the safe way would be dump-n-reload but that's not working for you. What you can try is to alter the pg_database.encoding value for that database, then start fresh backends (any existing ones won't notice the change). Worst case if that doesn't make life good is to change it back. The real problem is that you've got invalid unicode data in the database (I'm not an expert, but I think that #bf is a 1-byte UTF8 sequence and then #ef starts a 3-byte sequence, so if this comes within 2 characters of end-of-line that would explain your dump problem). You had better fix the data first before trying to lock down the encoding. Once you change the encoding, backend internal operations will start spitting up on any stored bad data, whereas right now it's just passing it through unchanged. The safest way might be a dump-n-reload in any case, since reloading into a fresh UNICODE database will catch bad data. If you try manual repairs you're likely to miss some places :-( regards, tom lane