[SQL] Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)

[Chris Preston]

Hello all,
How far can I get to a higher version of PostgreSQL by just entering a
command line instruction to upgrade without any major effort?

Regards

Chris Preston


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)

[Steve Crawford]

On 07/10/2012 08:28 AM, Chris Preston wrote:

Hello all,
How far can I get to a higher version of PostgreSQL by just entering a
command line instruction to upgrade without any major effort?

Regards

Chris Preston


At the simplest you just do a dump (using the dump tools from the *new* 
version of PostgreSQL) and restore to the new version. If you aren't 
worried about down-time and have the absolute simplest use-case then it 
isn't too difficult.


Most people have to deal with reviewing the release notes to see how 
they affect the overall system, setting up a test of the new version to 
shake out issues and planning the upgrade to minimize down-time. Among 
the things that have changed over the past few releases are tightening 
up of casting rules and changing the default for 
standard_conforming_strings to on either of which could cause serious 
breakage.


Once you get to a newer version (anything past 8.3? 8.4) you can use the 
new upgrade tools moving forward to minimize downtime during the upgrade 
process but you will *always* need to test and evaluate before deploying.


Cheers,
Steve



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)

[Craig Ringer]

On 07/10/2012 11:28 PM, Chris Preston wrote:

Hello all,
How far can I get to a higher version of PostgreSQL by just entering a
command line instruction to upgrade without any major effort?


Not very, unless your database and queries are very simple.

At minimum you'll have to deal with the removal of implicit casts to 
text ( 
http://www.postgresql.org/docs/current/static/release-8-3.html#AEN116407) and, 
if you use bytea, the change of default bytea format ( 
 
http://www.postgresql.org/docs/current/static/release-9-0.html#AEN109764).


In general, you will want to read the "Migration to" notes for each version:

http://www.postgresql.org/docs/current/static/release-8-2.html 

http://www.postgresql.org/docs/current/static/release-8-3.html 

http://www.postgresql.org/docs/current/static/release-8-4.html 


http://www.postgresql.org/docs/current/static/release-9-0.html
http://www.postgresql.org/docs/current/static/release-9-1.html 



Alternately, you could try using PostgreSQL 9.1's pg_dump on your 8.1 
database, load the dump into a 9.1 instance, run your client against it 
and see what breaks. For simpler apps that can be a reasonable choice.


--
Craig Ringer