If you installed Postgres using the methodology demonstrated in our earlier article: CentOS Install Postgres 9.3, then at some point you may need to upgrade your Postgresql instance. For this article on upgrading postgresql, I used a 9.2 instance that was installed using the methodology in that earlier article. If your Postgresql instance was installed using a different manner you will likely need to modify these instructions to make it work.
Step 1 – Complete Backup of Postgresql System
Please use your typical backup procedures for backing up your system prior to using these instructions. Use pg_dump, a full filesystem and operating system dump or whatever your typical procedures are so that you can get things back up and running should something not go wrong.
Step 2 – Install New Version of Postgres
For this next step, I will Install Postgresql using the methodology that I explained in my earlier article. The first step in that process is to download the Postgresql repository for the new version that we are installing. You can find the repository for your version at: http://yum.postgresql.org
# wget http://yum.postgresql.org/9.3/redhat/rhel-6.4-x86_64/pgdg-centos93-9.3-1.noarch.rpm
# rpm -ivh ./pgdg-centos91-9.1-4.noarch.rpm
Now that your repository is installed, we need to determine which postgresql packages we need to install. The best way to do this is to determine which packages you have currently installed for the version you are already running. So we will run “rpm -qa” and grep to our current version numbers to find the packages currently installed:
# rpm -qa | grep postgre | grep 92 postgresql92-server-9.2.3-2PGDG.rhel6.x86_64 postgresql92-contrib-9.2.3-2PGDG.rhel6.x86_64 postgresql92-libs-9.2.3-2PGDG.rhel6.x86_64 postgresql92-9.2.3-2PGDG.rhel6.x86_64 postgresql92-devel-9.2.3-2PGDG.rhel6.x86_64
Now, we need to get the list of available packages for the new Postgresql version. Since I am installing 9.3, I will grep on 93:
# yum list postgres* | grep 93 postgresql93.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-contrib.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-debuginfo.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-devel.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-docs.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-jdbc.x86_64 9.3.1100-1PGDG.rhel6 pgdg93 postgresql93-jdbc-debuginfo.x86_64 9.3.1100-1PGDG.rhel6 pgdg93 postgresql93-libs.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-odbc.x86_64 09.02.0100-1PGDG.rhel6 pgdg93 postgresql93-odbc-debuginfo.x86_64 09.02.0100-1PGDG.rhel6 pgdg93 postgresql93-plperl.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-plpython.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-pltcl.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-server.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-test.x86_64 9.3.4-1PGDG.rhel6 pgdg93
There are tons of available packages, but I only need to install the ones that match the packages I have currently installed for the old version.
# yum install postgresql93-server.x86_64 postgresql93-contrib.x86_64 postgresql93-libs.x86_64 postgresql93.x86_64 postgresql93-devel.x86_64
Step 3 – Prepare Postgresql for Upgrade
Now that we have the new Postgresql installed, we need to get finishing touches on the configuration and initialize the new 9.3 database.
I like to install my Postgresql database files in the /data/pgsql directory. So, I need to edit the /etc/init.d/postgresql-9.3 file and set the location.
Change these 2 lines:
PGDATA=/var/lib/pgsql/9.3/data PGLOG=/var/lib/pgsql/9.3/pgstartup.log
to
PGDATA=/data/pgsql/9.3/data PGLOG=/data/pgsql/9.3/pgstartup.log
Change the Postgresql Port on the New Postgresql Instance
Because we have the current postgresql still up and running on the default port of 5432, we need to modify the port that our new postgresql is running on until we get the whole thing complete. I will change it to posrt 5433.
Edit the /etc/init.d/postgresql-9.3 file and change:
PGPORT=5432
to
PGPORT=5433
Now we need to inititalize the new 9.3 Postgresql Instance:
# /etc/init.d/postgresql-9.3 initdb
Verify that it created the new directory wherever you have it listed in your /etc/init.d/postgresql-9.3 file.
# ls /data/pgsql/9.3/data base pg_hba.conf pg_multixact pg_snapshots pg_subtrans PG_VERSION global pg_ident.conf pg_notify pg_stat pg_tblspc pg_xlog pg_clog pg_log pg_serial pg_stat_tmp pg_twophase postgresql.conf
Step 4 – Upgrade Postgresql
Now that we have our new Postgresql packages installed, configured and the instance initialized, we are ready for the upgrade. So, we need to stop our existing Postgresql instance:
# service postgresql-9.2 stop
We will run the pg_upgrade. The options that we need to use for pg_upgrade are:
-b old postgresql binary directory -B new postgresql binary directory -d old postgresql data directory -D new postgresql data directory
So, let’s run the upgrade:
$ /usr/pgsql-9.3/bin/pg_upgrade -v -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.3/bin/ -d /data/pgsql/9.2/data/ -D /data/pgsql/9.3/data/ Running in verbose mode Performing Consistency Checks ----------------------------- Checking cluster versions ok pg_control values: First log file ID after reset: 1483 First log file segment after reset: 58 pg_control version number: 922 Catalog version number: 201204301 Database system identifier: 5862655786611398652 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/20320796 Latest checkpoint's NextOID: 1914241 Latest checkpoint's NextMultiXactId: 10 Latest checkpoint's NextMultiOffset: 22 Latest checkpoint's oldestXID: 1798 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value pg_control values: First log segment after reset: 000000010000000000000002 pg_control version number: 937 Catalog version number: 201306121 Database system identifier: 5992919155432435960 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/1809 Latest checkpoint's NextOID: 12897 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 1799 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: analyze_new_cluster.sh Running this script will delete the old cluster's data files: delete_old_cluster.sh
Step 5 – Finalize Upgrade Cleanup
Our Postgresql is now upgraded, but we still need to work out config file differences. The 2 important files to reconcile are the pg_hba.conf and the postgresql.conf files.
Copy over the old pg_hba.conf file:
cp /data/pgsql/9.2/data/pg_hba.conf ./pg_hba.conf
Then I find the differences in the postgresql.conf file and make the important changes in the new postgresql.conf file. I do not copy it over because there may be new configuration options or defaults.
diff /data/pgsql/9.2/data/postgresql.conf ./postgresql.conf
Change the 9.3 port number back to 5432 by editing the /etc/init.d/postgresql-9.3 file:
PGPORT=5433
to
PGPORT=5432
Startup the new Postgresql Instance
# service postgresql-9.3 start Starting postgresql-9.3 service: [ OK ]
Our Postgresql databases should be up and usable now!
Run analyze_new_cluster.sh
Analyze_new_cluster.sh should be located in the postgres user’s home directory:
$ /data/pgsql/analyze_new_cluster.sh This script will generate minimal optimizer statistics rapidly so your system is usable, and then gather statistics twice more with increasing accuracy. When it is done, your system will have the default level of optimizer statistics. If you have used ALTER TABLE to modify the statistics target for any tables, you might want to remove them and restore them after running this script because they will delay fast statistics generation. If you would like default statistics as quickly as possible, cancel this script and run: "/usr/pgsql-9.3/bin/vacuumdb" --all --analyze-only Generating minimal optimizer statistics (1 target) -------------------------------------------------- vacuumdb: vacuuming database "db1" vacuumdb: vacuuming database "db2" The server is now available with minimal optimizer statistics. Query performance will be optimal once this script completes. Generating medium optimizer statistics (10 targets) --------------------------------------------------- vacuumdb: vacuuming database "db1" vacuumdb: vacuuming database "db2" Generating default (full) optimizer statistics (100 targets?) ------------------------------------------------------------- vacuumdb: vacuuming database "db1" vacuumdb: vacuuming database "db2" Done
Run Delete_old_cluster.sh to Remove the Old Postgresql Directory
delete_old_cluster.sh
Step 6 – Remove Old Postgresql Packages
In step 2 above, we found the list of Postgresql packages that we had installed prior to the upgrade. At this point, we can delete those packages if we think we are ready to. Remember, we only want to delete the old packages and not the new ones that we just installed in this article.
# yum remove postgresql92-server-9.2.3-2PGDG.rhel6.x86_64 postgresql92-contrib-9.2.3-2PGDG.rhel6.x86_64 postgresql92-libs-9.2.3-2PGDG.rhel6.x86_64 postgresql92-9.2.3-2PGDG.rhel6.x86_64 postgresql92-devel-9.2.3-2PGDG.rhel6.x86_64
That’s it. We should be all upgraded and ready to use our newly upgraded Postgresql database. You should probably backup your database again before you go too much further.
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014
Thanks for this post. Has been working perfectly!
Greets from Switzerland
Philipp