Thread: 8.3.0 upgrade
I just finished upgrading my production DB to 8.3.0. Everything went smoothly, but I thought of a few questions. After the upgrade, while restoring my backup to the new version, I got this error message: ERROR: role "postgres" already exists I assume this is nothing to be concerned about. But is there something I could have done to avoid this error? (I think I followed the upgrade instructions to the letter). Is there any scenario where the "postgres" role wouldn't exist? (should pg_dumpall exclude it?) Moving on... In step 6 of the upgrade instructions, it says: "Restore your previous pg_hba.conf and any postgresql.conf modifications." Perhaps this should also mention pg_ident.conf since I restored the two mentioned files, but still couldn't connect. The third completely escaped my mind until I ran a "diff" on the old & new data directories. Next, one of my apps failed because of a dependency on libpq.so.4. During previous upgrades, I remedied that by installing this package: compat-postgresql-libs-4-2PGDG.rhel4 But it seems under the 8.3.0 binary downloads, this package is no longer available. The only compat package is "compat-postgresql-libs-3" which of course includes only libpq.so.3 so I had to browse older releases to find the missing version which I thought seemed a little odd. Am I missing something? Finally, regarding the new "HOT" feature. The release notes say that benefits are realized "if no changes are made to indexed columns". If my updates include *all columns* (the SQL is generated dynamically) but the new value matches the old value for all *indexed* columns, do I still reap the benefits of HOT? Thanks! Adam
On Mon, Mar 17, 2008 at 12:43 PM, Adam Rich <adam.r@sbcglobal.net> wrote: > > Finally, regarding the new "HOT" feature. The release notes say that > benefits are realized "if no changes are made to indexed columns". > If my updates include *all columns* (the SQL is generated dynamically) > but the new value matches the old value for all *indexed* columns, > do I still reap the benefits of HOT? > Yes. At the execution time, a binary comparison of old and new index column values is performed and if the old and new value is same for all index columns, HOT update is feasible. So even if the UPDATE statement sets value to one of the index columns, HOT update is possible as long as the old and the new value is same. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Thanks to Pavan for the answer regarding HOT. Does anybody have an answer regarding the postgres role or compat lib ? > > * From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> > * To: <pgsql-general(at)postgresql(dot)org> > * Subject: 8.3.0 upgrade > * Date: Mon, 17 Mar 2008 02:13:55 -0500 > > > I just finished upgrading my production DB to 8.3.0. Everything went > smoothly, but I thought of a few questions. > > After the upgrade, while restoring my backup to the new version, > I got this error message: > > ERROR: role "postgres" already exists > > I assume this is nothing to be concerned about. But is there something > I could have done to avoid this error? (I think I followed the upgrade > instructions to the letter). Is there any scenario where the "postgres" > role wouldn't exist? (should pg_dumpall exclude it?) > > Moving on... In step 6 of the upgrade instructions, it says: > > "Restore your previous pg_hba.conf and any postgresql.conf modifications." > > Perhaps this should also mention pg_ident.conf since I restored the > two mentioned files, but still couldn't connect. The third completely > escaped my mind until I ran a "diff" on the old & new data directories. > > Next, one of my apps failed because of a dependency on libpq.so.4. > During previous upgrades, I remedied that by installing this package: > > compat-postgresql-libs-4-2PGDG.rhel4 > > But it seems under the 8.3.0 binary downloads, this package is no longer > available. The only compat package is "compat-postgresql-libs-3" > which of course includes only libpq.so.3 so I had to browse older > releases to find the missing version which I thought seemed a little odd. > Am I missing something? > > Finally, regarding the new "HOT" feature. The release notes say that > benefits are realized "if no changes are made to indexed columns". > If my updates include *all columns* (the SQL is generated dynamically) > but the new value matches the old value for all *indexed* columns, > do I still reap the benefits of HOT? > > Thanks! > > Adam > >
Hi, On Mon, 2008-03-17 at 21:26 -0500, Adam Rich wrote: > Next, one of my apps failed because of a dependency on libpq.so.4. > During previous upgrades, I remedied that by installing this package: > > compat-postgresql-libs-4-2PGDG.rhel4 > > But it seems under the 8.3.0 binary downloads, this package is no > longer available. The only compat package is > "compat-postgresql-libs-3" which of course includes only libpq.so.3 > so I had to browse older releases to find the missing version which I > thought seemed a little odd. Pick your OS/Arch from this list, and click to it: http://yum.pgsqlrpms.org/rpmchart.php Then click to "C" at the top, and download the compat package. Also, you can use that repository, so that it will take care of the dependencies: http://yum.pgsqlrpms.org/howtoyum.php Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment
> Pick your OS/Arch from this list, and click to it: > > http://yum.pgsqlrpms.org/rpmchart.php > > Then click to "C" at the top, and download the compat package. Devrim, I clicked on my OS (RHEL/CentOS 4 - x86) Then on "C" as you said, But the RPM list still only contains the compat-postgresql-libs-3-2 package, when I'm looking for compat-postgresql-libs-4-2 Any other ideas?
Hi, On Fri, 2008-03-21 at 14:13 -0500, Adam Rich wrote: > I clicked on my OS (RHEL/CentOS 4 - x86) Then on "C" as you said, > But the RPM list still only contains the compat-postgresql-libs-3-2 > package, when I'm looking for compat-postgresql-libs-4-2 No, you need compat-3, not compat-4. For example: [root@koji-centos4-i386-pg83 ~]# yum install php-pgsql <snip> --> Processing Dependency: libpq.so.3 for package: php-pgsql Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment
> No, you need compat-3, not compat-4. For example: > > [root@koji-centos4-i386-pg83 ~]# yum install php-pgsql <snip> > --> Processing Dependency: libpq.so.3 for package: php-pgsql I have applications that depend on libpq.so.4 Where do I get that, if not compat-postgresql-libs-4-2 ??
Hi, On Fri, 2008-03-21 at 14:37 -0500, Adam Rich wrote: > I have applications that depend on libpq.so.4 > > Where do I get that, if not compat-postgresql-libs-4-2 ?? I'll send you instructions for how to build custom compat package. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/