Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D9D869FB3ED for ; Thu, 10 May 2007 20:44:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 06536-09 for ; Thu, 10 May 2007 20:44:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 37A539FB4F1 for ; Thu, 10 May 2007 20:44:27 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id a73so654217pye for ; Thu, 10 May 2007 16:44:26 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=t7KmxnpvdnpGYqkWlctTh16j7/WsZqL17B3STXm/+P8tn2J2MQGZa+uKGyeAO69IwWt2WYVy76DOVQgtIgOLHZYoqjyLWklxdfkA5Mkr4k9eM50PAIe5oivhKWPeURO1d0sM/0U9uvr5kAxikh1iGqaiApQrqvWrEUmlccQ/BBo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=sAUtkbJwWnFItbR2IREB9rHubWYhv05a1MZNn8sNAhyBx9e1Ew0DCROwK6bAXK5a15T8W5G5oa27zOtuU2WA3glsyVEpix72wLDMzdourrxBHKQo0NVpOtYKjMPjMAz5Jd7mvynhonrypacAZ1O1f5oWAwN/I3ubBUixbqokVtc= Received: by 10.35.27.1 with SMTP id e1mr3945980pyj.1178840666094; Thu, 10 May 2007 16:44:26 -0700 (PDT) Received: from ?192.168.0.102? ( [66.168.24.69]) by mx.google.com with ESMTP id p77sm15991009pyb.2007.05.10.16.44.24; Thu, 10 May 2007 16:44:24 -0700 (PDT) Message-ID: <4643AEA2.4050404@gmail.com> Date: Thu, 10 May 2007 18:45:38 -0500 From: Peter Koczan User-Agent: Thunderbird 1.5.0.10 (X11/20070306) MIME-Version: 1.0 To: Peter Koczan CC: Steve Holdoway , pgsql-admin@postgresql.org Subject: Re: upgrade 8.0.3 -> 8.2.4 References: <20070510170751.348e09a6.steve.holdoway@firetrust.com> <4544e0330705100826v1159a7aewedab249e9c77fdbb@mail.gmail.com> In-Reply-To: <4544e0330705100826v1159a7aewedab249e9c77fdbb@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/89 X-Sequence-Number: 25409 I almost forgot one of the biggest gotchas. Remember that users and groups were conflated into "roles" in postgres 8.1. In addition to being a change, there were some other issues, namely that you couldn't manually specify user and group sysids. I had to change our user and group management scripts to properly reflect this. Peter Peter Koczan wrote: > A few months ago, I upgraded postgres from 7.4 to 8.2. There were a > few gotchas, but we don't keep a whole lot of data so even the biggest > problems were, on the whole, minor. > > - The cidr data type became more strict, and a few tables in our > network database would not restore until this was fixed. > - One of the primary keys broke and couldn't get created. This was > more the fault of poor admins (before my time) and internal > fragmentation than postgres 7.4. I had to fix the underlying table > before it would restore. > - There were a few permissions issues (new acls + an inconsistent > previous policy = fun). > - The system databases went from SQL_ASCII encoding to UTF8 encoding. > I had to explicitly create the database during the restore, or else > the database would have the wrong encoding. > > I doubt that you will run into these exact problems, but my point is > that there are inevitably some gotchas. If it's not prohibitively > time-consuming, I'd recommend a full dump/restore of your database(s) > from 8.0 to 8.2 so you can catch many of these gotchas before going > live. Also, you might want to create test versions of your apps and > try them against the 8.2 server. > > On the whole, I've found postgres to be very good at maintaining > backwards compatibility of interfaces and sql, so I estimate that most > queries and db apps should "just work" with 8.2. > > Peter > > On 5/10/07, *Steve Holdoway* > wrote: > > Are there any gotchas? I've got the opportunity to move to another > database server for this application and yould like to take the > opportunity to upgrade at the same time. > > tia, > > Steve > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > >