Migrating Postgres SQL from v9.0 to latest version...

Hello everyone,

Having legacy application and want to move to a new platform..well that a good idea of upgrading to the new release than relying on older version.

This article will help those who want to migrate PostgreSQL data from pre-postgres 9.1 to post-postgres 9.1 version.

What will be the problem? It must be a simple upgrade of database?

Well yeah! It is a simple database upgrade. Just keep a backup and upgrade the database and restore the data. Done!

Wait, when you will have a column in postgres having "citext" extension, then bang!! It will not let you in and even if it will, then your application will throws exceptions related to collation mismatch while comparing with values having "citext" data type. Believe me that will make you scream "What the hell is happening...??"

 ERROR: could not determine which collation to use for string comparison Hint: Use the COLLATE clause to set the collation explicitly.

Reason for this problem:

Existing citext columns and indexes aren't correctly marked as being of a collatable data type during pg_upgrade from a pre-9.1 server, or when a pre-9.1 dump containing the citext type is loaded into a 9.1 server. That leads to operations on these columns failing with errors such as "could not determine which collation to use for string comparison". This change allows them to be fixed by the same script that upgrades the citext module into a proper 9.1 extension during CREATE EXTENSION citext FROM unpackaged. (Source)

Solution/Procedure to fix this issue (I am doing this by migrating the data from postgres 9.0 to postgres 9.2):

• Open pgAdmin UI tool installed with postgres 9.0 . This can be found in path/to/Program Files/PostgresSQL/9.0/bin/pgAdmin3.exe(version 1.14).
• Take the backup of all the database you have in 9.0. You can  use the pg_dump.
• Now open pgAdmin tool form the bin folder of 9.2 (version 1.16) and create the database you want and restore from the dump taken from 9.0. You can use the pg_restore. 
• Make sure you are using the correct pg_restore.To verify this, Go to file -> Options ->Binary paths   -> PG bin path -> /path/to/Program Files\PostgreSQL\9.2\bin.
• Now the most important thing to avoid the errors, We have to create the "citext" extensions in the new database tables. To do so we have to run the following query in each database.
  
  CREATE EXTENSION citext FROM unpackaged;

Once we run the query successfully, we are D O N E.

Now we can upgrade to any higher version without any trouble...unless anything thing else do not want you to upgrade.. :P

Hope this will help someone and save some time fighting with the collation errors while doing something good...

Enjoy coding.. :)