I have been needing to upgrade the PostgreSQL servers at my work for awhile now and was finally forced to do so after needing some of the features available with PostgreSQL 8.4.X. Currently we are running PostgreSQL 8.3.10 which is super old but it is definitely not the latest stable release available. One thing I am looking forward to testing is the improvements to the vacuum process. Over the past two years I have become a lot more familiar with Postgres and one thing that has been disappointing is the downtime involved in reclaiming disk space via data removed from PSQL database tables. The full vacuum process, which is required to reclaim disk after deleting data from a table, in 8.3 requires a lock on tables which essentially causes downtime. When you are dealing with large tables over 10GB in size this can take days to complete. Follow the directions below to first backup all of your PostgreSQL data, remove the current PostgreSQL installation, and then install PostgreSQL 8.4 on a CentOS Linux server.
Upgrade Postgres 8.3 To Postgres 8.4:
- Backup Working Directory: First create a directory we will use as the main working directory during the Postgres upgrade process. In the following example we will call the backup directory postgres-upgrade which will be used to store the database data, PostgreSQL configuration files, and other files associated to the PSQL upgrade.
Create Directory To Be Used During The Postgres Upgrade:code
- [root@dev ~] mkdir /usr/local/src/postgres-upgrade
- Backup Database Data: Now use the pg_dumpall command to backup all of the database data. The below command will dump every database and all of the data in each database to a file.
Use pg_dumpall To Backup Databases:code
- [root@dev postgres-upgrade] pg_dumpall -U postgres -f /usr/local/src/postgres-upgrade/full-backup.sql
- Backup Configuration Files: After the databases have been backed up you should now copy your configuration files from the current psql data directory to the working upgrade directory we created in step one as shown in the below example.
Copy PostgreSQL Configuration Files To Postgres Upgrade Directory:code
- [root@dev ~] cp -p /var/lib/pgsql/data/*.conf /usr/local/src/pgupgrade/
- Shutdown PostgreSQL: This is where the downtime will begin so make sure you set downtime in your monitoring software or if you have multiple database servers this is the time to make the secondary active. Use the below command to shutdown PostgreSQL.
Stop The PostgreSQL Server:code
- [root@dev ~] /etc/init.d/postgresql stop
- Uninstall PostgreSQL Packages: After PostgreSQL has been shutdown you want to remove the current postgresql* packages. First you might list all of the packages that are currently installed using the command below.
List Installed PostgreSQL Packages:code
- [root@dev ~]# yum list *POSTGRESQL* | grep installed
- compat-postgresql-libs.i686 4-1PGDG.rhel5 installed
- postgresql.i386 8.3.10-1PGDG.el5 installed
- postgresql-devel.i386 8.3.10-1PGDG.el5 installed
- postgresql-libs.i386 8.3.10-1PGDG.el5 installed
- postgresql-server.i386 8.3.10-1PGDG.el5 installed
As you can see above there are 5 packages installed with postgresql in the name. We will be removing all of them except for compat-postgresql-libs.i686.
Remove PostgreSQL Packages Using Yum:bash
- [root@dev ~]# yum remove postgresql postgresql-server postgresql-libs postgresql-devel
- Backup Old PostgreSQL Data: After the postgresql packages have been removed you should move the current psql data directory as another form of backup using the syntax below.
Move PSQL Data Directory:code
- [root@dev ~] mv /var/lib/pgsql /var/lib/pgsql.BAK
- Download 8.4 PGDG RPM: Visit the CentOS pgdg repo listings by clicking here. In the current example the pgdg RPM we are going to download is pgdg-centos-8.4-2.noarch. First make sure you are in the working directory we created in step one and then download the new PGDG RPM using the syntax below.
Use wget To Download New PGDG RPM:code
- [root@dev ~] wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-2.noarch.rpm
- Uninstall PGDG 8.3 RPM: If you previously had a pgdg repo installed you should remove the pgdg RPM that was initially installed. First locate the exact RPM that was installed as shown in the below example.
Locate Installed PGDG RPM:code
- [root@sdev pg-upgrade]# rpm -qa | grep pgdg
Remove Old PGDG RPM:code
- [root@sdev pg-upgrade]# rpm -e pgdg-centos-8.3-7
- Install 8.4 PGDG Yum Repository: From the working postgres upgrade directory issue the following command to install the new pgdg 8.4 yum repository.
Install PGDG 8.4 RPM:code
- [root@dev ~] rpm -Uhv /usr/local/src/pgdg-centos-8.4-2.noarch.rpm
- Install Postgres 8.4 Packages: Now install the new postgresql 8.4 packages. Make sure to verify you are installing all of the packages that were removed in step five.
Install PostgreSQL 8.4 Via Yum:code
- [root@dev ~] yum install postgresql postgresql-server postgresql-libs postgresql-devel
- Initialize PostgreSQL : You must first initialize the PostgreSQL database using the syntax below.
Use initdb To Initialize Postgres Server:code
- [root@dev ~] /etc/init.d/postgresql initdb
- Auto Start PostgreSQL: After initializing the database use chkconfig to make sure Postgres will restart in various run levels when the server restarts.
Use chkconfig To Set PostgreSQL To Start Automatically:code
- [root@dev ~] chkconfig postgresql on
Verify PostgreSQL Will Auto Restart:code
- [root@dev ~]# chkconfig --list | grep postgres
- postgresql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
- Start PostgreSQL: It is now time to start the PostgreSQL server for the first. We will make sure that the configuration is correct in the steps below.
- [root@dev ~] /etc/init.d/postgresql start
- Restore Configuration Files: Now we will want to restore the configuration files we backed up in step three. In this example there were only modifications to pg_hba.conf and postgresql.conf. Make sure that before you modify any configuration file you make a backup of the new 8.4 default configuration file in case you need to reference something in that file. The backup pg_hba.conf can be copied directly over the new 8.4 pg_hba.conf. I suggest manually merging the postgresql.conf files because some of the variables within the postgresql.conf file may be different between PostgreSQL versions.
- Restore Postgres Data: Now use the below syntax to import the data that was backed up in step two. Depending on the size of your databases this could take a long time.
Restore PostgreSQL Database Data:code
- [root@dev ~] psql -U postgres < /usr/local/src/postgres-upgrade/full-backup.sql
Your PostgreSQL database server will now be running the latest 8.4 release. Make sure to test your custom built applications that reference the database to verify there are no issues.