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/
UPDATEWe have instructions for installing PostgreSQL 9.0 via yum.
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.
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
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 data folder.
psql –h localhost –U postgres –p 5432
SELECT setting FROM pg_settings WHERE name='data_directory';
Gives something like:
setting --------------------------------- /var/lib/pgsql/data/
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.
mkdir /pgbak
cp /var/lib/pgsql/data/*.conf /pgbak
pg_dumpall -h localhost -p 5432 -U postgres --globals-only > /pgbak/globals.sql
/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
Uninstall it.
yum erase postgresql
You should get a screen that looks something like this and choose y to uninstall:
Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Removing: 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 Transaction Summary ================================================================================ 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
Figure out which OS you are on. Note the pgdn repository only works with CentOS and Redhat 4 and above, or Fedora.
uname –a
If you see a el5 you are most likely running an enterprise linux. If you see x64, then you have a 64-bit installation.
vi /etc/redhat-release
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.
cd /pgbak
wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-redhat-8.4-1.noarch.rpm
Install the rpm
rpm -ivh pgdg-redhat-8.4-1.noarch.rpm
To get a list of postgresql related stuff:
yum list | grep postgresql
* 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: exclude=postgresql*
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.
vi /etc/rc.d/init.d/postgresql
vi /etc/sysconfig/pgsql/postgresql
PGDATA=/var/lib/pgsql/data
Create data cluster:
mkdir /var/lib/pgsql/data
chown postgres /var/lib/pgsql/data
su postgres
initdb -D /var/lib/pgsql/data
service postgresql initdb
Set postgresql service restart automatically on reboots.
su root
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