Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5953C6500B4 for ; Wed, 27 Aug 2008 12:54:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20344-08 for ; Wed, 27 Aug 2008 12:54:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id 9E9D064FEDD for ; Wed, 27 Aug 2008 12:54:29 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id c7so1176637nfi.23 for ; Wed, 27 Aug 2008 08:54:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=Kdq43T1nZZco0No1jzBuz5l4u4Gt6ct1GGoYwB8bMkQ=; b=JTMfjO5867kEmLKyFH3GittS0iv4uTZVkxnG4RzMMo0clSe6QtFmmp69vMm4iREQZZ pU5EedM9sMhRlXrkj//cxtp1Ye4FwF0mZp47iWTyBV98XG1ZPJCe/1WNK/pa7Y44NXGp friDu5JgKe4tGkEhdKjVtiT+bofqhGFbhXsg0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=efUNcxWcpMLmlQnQT6gf8o5PAkvt8XY+LfPGiPl7o5iv0SWJUEApqJwSX8Kaw+jIon n7TlHqtLdo979qunFLZ5Ot4fRzJ/t30yQ47BxlwlIJapJX3f+nyGu0tNU2gETTOvXBfW FR0PFPd1LcMTWdyzdtKXwZL8SJrBRj+liRKHw= Received: by 10.210.22.16 with SMTP id 16mr142307ebv.27.1219852467568; Wed, 27 Aug 2008 08:54:27 -0700 (PDT) Received: by 10.210.130.1 with HTTP; Wed, 27 Aug 2008 08:54:27 -0700 (PDT) Message-ID: Date: Wed, 27 Aug 2008 23:54:27 +0800 From: "Phoenix Kiula" To: "Andrew Sullivan" , pgsql-general@postgresql.org Subject: Re: Dumping/Restoring with constraints? In-Reply-To: <20080827154109.GA18946@commandprompt.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <22708.1219845242@sss.pgh.pa.us> <23571.1219848176@sss.pgh.pa.us> <23743.1219848802@sss.pgh.pa.us> <20080827154109.GA18946@commandprompt.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/961 X-Sequence-Number: 136791 Ok. Here is what it is. OLD DB: Since 2002. May contain non-UTF8 data. But I thought I had modified it all when I changed it to UTF-8 pgsql database (it was originally Mysql). The database works very well on a very busy website. Everything on that website is now UTF-8. I wish to mirror this database locally on my home machine. Server is linux with 6GB ram and pgsql 8.2.3, home machine is osx with imac of 4GM ram and 8.3.3 (new pg). NEW DB: Just installed fresh. There's nothing in it. I can basically wipe all data out. WHAT I AM DOING: 1. On the server, I am executing "pg_dumpall > mydb.out". Simple. 2. FTP that mydb.out file to local home machine. 3. Here, locally, I do this: "psql -h localhost -d postgres -U postgres -f mydb.out". This is what gives me the error. MY QUESTION: What can I do to: (a) Make sure all data on pgsql on server (OLD DB) is utf-8. Is there a command I can execute to convert all data? I thought I had converted it all to utf-8 using PHP sometime ago, which went through each and every row and column! (b) Once that data is utf8-ed, how can I bring it home and have a mirror of the db. Thanks.