In this exercise, we'll go thru our steps for upgrading a Redhat Enterprise Linux 64-bit PostgreSQL
box from PostgreSQL 8.3 to PostgreSQL 8.4. If you don't have any kind of PostgreSQL installed on your box,
you can skip the Upgrade step.
UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use
this one instead since it seems more up to date than the other. http://yum.pgrpms.org/
We have instructions for installing PostgreSQL 9.0 via yum.
Updgrading from PostgreSQL 8.* to PostgreSQL 8.4
If you are starting from scratch -- just skip this section.
If you are upgrading from 8.4.0 to 8.4.1 you can get away with a simple
yum update postgresql
and skip the rest of this article.
If you are upgrading from PostgreSQL 8.3 to 8.4, in theory you can use PgMigrator, but in practice,
particularly with a Yum install, you are bound to run into obstacles. If you are running an older version, you must dump and restore.
For starters we did try upgrading our database using the PgMigrator but were unsuccessful. Keep in mind these
comments are not flames -- just things we see that are obstacles to upgrade and perhaps can be made less painful
for future migration work and we consider important to increase PostgreSQL adoption.
This process turns out to be a bit more difficult if you are using Yum for a couple of reasons.
- Yum and I think most distros always install in the same location -- the bin folder and bin/pgsql
so its hard to tell which files you need and pgMigrator requires both the new binaries and the old binaries
to complete. To work around that problem -- we did compile from scratch a PostgreSQL 8.3 that matched our 8.3 version by downloading the source and compile and setting --prefix== another location
just to make the migrator happy.
- pg_migrator was not happy with the location of pg_migrator.so -- this is probably a 64-bit issue and one we run into a lot. No biggies just
ln -s /usr/lib64/pg_migrator.so /usr/local/pgsql84/lib/pg_migrator.so
The third obstacle we ran into was an insurmountable one. Or rather one we didn't really care to tackle because we figured trying to
work around it would bite us in the future and also prevent us from using Yum which we really like using by the way. This is that the default date time storage between 8.3 and 8.4 has changed. So it seems
to be able to migrate or at least on our 64-bit Linux box, we would need to recompile the PostgreSQL 8.4 to ignore which sounded like a future management nightmare waiting to happen.
The error you get is this Old and new pg_controldata date/time storage types do not match. You will need to rebuild the new server with configure
--disable-integer-datetimes or get server binaries built with those options.
I suspect this will become a non-issue in PostgreSQL 8.4 to 8.5
Please see Greg's notes about suggestions for this issue
Using the tried and true dump/restore but slower more space needed
We were upgrading 800 gb database which reduces down to a 80gb backup file. Space was a concern, but this is a dev box we wanted
to blow out and have clean anyway. We had a good last night backup from production so we were in good shape to uninstall and just remove our
- Note location of your old data cluster:
Gives something like:
psql –h localhost –U postgres –p 5432
SELECT setting FROM pg_settings WHERE name='data_directory';
Make sure to backup at least the .conf files to different location which are located in same folder as data
and also make backup of users and other global settings.
cp /var/lib/pgsql/data/*.conf /pgbak
pg_dumpall -h localhost -p 5432 -U postgres --globals-only > /pgbak/globals.sql
- Make backup of each database or whole server. We liked compressed backups of each db since we don't always have space for
a non-compressed and sometimes want to only selectively restore part of a db. Repeat the below for each db.
/usr/bin/pg_dump -i -h localhost -U postgres -F c -b -v -f "/pgbak/mydb_beforeupgrade.backup" mydb
Shut down the old service.
service postgresql stop
yum erase postgresql
You should get a screen that looks something like this and choose y to uninstall:
Package Arch Version Repository Size
postgresql x86_64 8.3.7-1PGDG.rhel5 installed 4.7 M
Removing for dependencies:
postgresql-contrib x86_64 8.3.7-1PGDG.rhel5 installed 1.3 M
postgresql-devel x86_64 8.3.7-1PGDG.rhel5 installed 4.9 M
postgresql-plperl x86_64 8.3.7-1PGDG.rhel5 installed 68 k
postgresql-server x86_64 8.3.7-1PGDG.rhel5 installed 12 M
Install 0 Package(s)
Update 0 Package(s)
Remove 5 Package(s)
Is this ok [y/N]: y
If you have space you can move your old cluster.
mv /var/lib/pgsql/data to /var/lib/pgsql/dataold
but if not and you know your backup is sound or this is a dev box just destroy it. You are destroying
data so proceed with caution.
rm -rf /var/lib/pgsql/data
Installing PostgreSQL 8.4.1 from Yum repository
Figure out which OS you are on. Note the pgdn repository only works with CentOS and Redhat 4 and above, or Fedora.
If you see a el5 you are most likely running an enterprise linux. If you see x64, then you have a 64-bit installation.
May help narrow it down.
Download the right file - from http://yum.pgsqlrpms.org/reporpms/8.4/. In our case we were running a redhat EL distro so we downloaded.
Install the rpm
rpm -ivh pgdg-redhat-8.4-1.noarch.rpm
To get a list of postgresql related stuff:
yum list | grep postgresql
If you see postgresql from other repositories besides pgdg84, then you need to exclude postgresql from coming from other repositories by following the below instructions excerpted from PostgreSQL How to Yum
* As root, cd /etc/yum.repos.d
* Edit distro's .repo file:
o On Fedora, edit fedora.repo and fedora-updates.repo, [fedora] sections
o On CentOS, edit CentOS-Base.repo, [base] and [updates] sections.
o On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section.
+ Add to the bottom of the section:
To get a listing of what is available in the PostgreSQL 8.4.1 yum
yum list | grep pgdg84
Install what you want: The developer package is needed to compile things like PostGIS if you are going to compile your own. the name would be
different if you are on a 32-bit box.
yum install postgresql-devel.x86_64
yum install postgresql-server
yum install postgresql-contrib
yum install postgresql-plperl
If you decide to use non-default location for data, you need to edit the postgresql
service sysconfig file and change the PGDATA argument.
(For vi you do a I to insert a line -- ESC then :w to save and exit)
NOTE: postgresql in sysconfig/pgsql may not exist so you may need to create it and put in a line denoting where you want the data -- something like below
Create data cluster:
chown postgres /var/lib/pgsql/data
initdb -D /var/lib/pgsql/data
service postgresql initdb
Set postgresql service restart automatically on reboots.
chkconfig --list (to see list of services)
chkconfig postgresql on
Start the service.
service postgresql start
Copy config, Restore accounts -- this is needed only if you were upgrading.
cp /pgbak/pg_hba.conf /yourdatacluster/
(NOTE: for postgresql.conf -- you are best manually editing that since its changed considerably from 8.3 to 8.4)
vi /var/lib/pgsql/data/postgresql.conf (-- use your old file as reference)
psql -U postgres -d postgres -f /pgbak/globals.sql
Install pgadmin pack if you want to be able to change config settings directly from pgAdmin III
psql -U postgres -d postgres -f /usr/share/pgsql/contrib/adminpack.sql
Restart for whatever config changes you made to take effect
service postgresql restart
- If you have database backups, first create a blank db for each and then restore with pg_restore.
Tracked: Dec 04, 13:05
Hello, and welcome to the 171st edition of Log Buffer, the weekly review of database blogs. Let’s get it going this week with . . . Oracle Uwe Hesse, the Oracle Instructor look at result cache, another brilliant 11g new feat...
Tracked: Dec 04, 15:29
Tracked: Mar 23, 02:49
Tracked: Jul 15, 04:34
Tracked: Sep 30, 03:24
Tracked: May 05, 06:39
Tracked: May 05, 09:40
Tracked: May 27, 08:41
Tracked: May 27, 08:41
Tracked: Jun 21, 04:16
Tracked: Sep 13, 04:49