Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Upgrade Postgresql 9.2 to 9.3

PostgreSQL Upgrading Type:
  1. pg_upgrade utility
  2. pg_dumpall -taking full cluster backup and restoring new version of software
  3. slony  -upgrading postgres with Zero Down Time
pg_upgrade utility
In this article on upgrading postgresql using pg_upgrade utility, 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
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.2/data
PGLOG=/var/lib/pgsql/9.2/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 initdbVerify 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.

Comments

Popular posts from this blog

PostgreSQL pgBadger

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart