How to upgrade your database to PostGIS 2.0 let me count the ways

Running PostGIS 2.0.0 micro upgrade - no extensions

You should be running PostGIS 2.0.0 beta2 or above to use this approach (or at least an alpah1 or above). If you installed without using extensions (which you would have to do if you are on PostgreSQL 9.0 or below then run the following scripts in your database which should be located in your contrib/postgis-2.0 folder of your PostgreSQL install:

postgis_upgrade_20_minor.sql
-- this one only if you had installed with raster support before
rtpostgis_upgrade_20_minor.sql
topology_upgrade_20_minor.sql (if you had topology support this may be in a topology sub folder)

If you had installed postgis with plain vanilla postgis support and now want to add on raster support as well (and of course you compiled (or your distro comes with raster support) , then you run these:

postgis_upgrade_20_minor.sql
-- this one only if you had installed without raster support before
rtpostgis.sql

-- this one only if you had installed without topology support before
topology.sql

Running PostGIS 2.0.0 micro upgrade with extensions

If you are using PostgreSQL 9.1 and had installed using the CREATE EXTENSION approach, good for you. Your life will be easier from now on assuming Regina didn't screw up when packaging the extensions.

First you know you have raster, because postgis extension ALWAYS comes packaged with raster and PSC folks had long extensive conversations about it and settled on that. So to upgrade, just run the following sql statements in your database:

ALTER EXTENSION postgis UPDATE TO '2.0.0';
ALTER EXTENSION postgis_topology UPDATE TO '2.0.0';

The cool thing about extensions is:

1. You don't have to figure out where those damn sql scripts are anymore. You know what I mean? How many minutes/hours of your precious life did you waste on that exercise in the past trying to figure out where your distro decided to put the files this time.

2. You can update your database half-drunk or half asleep or a combination of both, because your PostGIS extension remembers where you installed it last time, because you can have only one PostGIS in a database if you install with extensions.

   Remember the old days, Yah you thought you were being clever to put your data in a separate schema and to make it easier on your users, you set your data schema to be the first in search_path so all tables get created there and whoops you went to upgrade and forgot that safety net for users and got tangled in your own safety trap. Low and behold you have two sorta now screwed up PostGIS installs in your database fighting each other flying around (one in public with a geometry and one in your user data schema with all the operators and some other stuff tied to the one in your public) and your data is not happy in the least bit at this turn of events and your queries, once an impressive cast of formula-one racing machines, are now inching along at turtle speed because your indexes don't work anymore because they are tied to the one in public but the operators in your user schema are the ones the queries are attracted to. Fear no more, this will not happen ever again if you use extensions.

3. One day (perhaps again half drunk and half asleep) you might also decide you don't want PostGIS in public anymore so you can do something like:
   ALTER EXTENSION postgis SET SCHEMA postgis; and if things don't work out you can move it back again with a simple short sweet command.
4. Oh and upgrade. None of that messy removing PostGIS functions from your backup cause they don't get backed up. Your postgis will upgrade to whatever the latest is on your server when you restore. This I consider a Gotcha feature. It's a feature if you know about it and a possible WTF Gotcha if you were planning to have a nice 2.0.0 and ended up with a 2.1.0 or something.
5. Last but not least, you decide PostGIS is not for you, DROP EXTENSION postgis, but you'll be warned this time if you have data using the extension and will have to do a DROP EXTENSION postgis CASCADE if you decided you are willing to lose your data to be rid of PostGIS. No more suprises of oops where did all my geometry columns go

Running on PostGIS 2.0.0 PostgreSQL 9.0 or lower and moving to 9.1 with extensions

Yes, This also applys to folks who were running 32-bit PostgreSQL on Windows and decided what the hey lets see what this PostGIS on 64-bit PostgreSQL windows is all about.

1. Backup your old database
2. Make sure the PostGIS 2.0.0 binaries are installed on your new PostgreSQL service
3. Restore your old db on new server service
4. run:

   postgis_upgrade_20_minor.sql
   -- this one only if you had installed with raster support before
   rtpostgis_upgrade_20_minor.sql
   
   -- otherwise you'll need to install
   rtpostgis.sql
   -- run this 00
   topology_upgrade_20_minor.sql (if you had topology support this may be in a topology sub folder)
   

5. Run sql statement

   CREATE EXTENSION postgis FROM unpackaged VERSION '2.0.0';
   -- only if you have topology support
   CREATE EXTENSION postgis_topology FROM unpackaged VERSION '2.0.0';
   
   -- (if you don't have topology and want it)
   CREATE EXTENSION postgis_topology;
   

1.x uprade The long path with postgis_restore.pl

This is outlined in PostGIS 2.0.0 hard upgrade, but I'll go over the highlights.

1. Backup your database using pg_dump:
   pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
   
2. Create a new database and install the following scripts
   If you are on PostgreSQL 9.1 or above (even if you are migrating from PostgreSQL 9.1)

   CREATE EXTENSION postgis;
   run the legacy.sql (and pray this is the last time you need to run an sql script)
   CREATE EXTENSION postgis_topology;

   if you are on PostgreSQL 9.0 or below

   postgis.sql
   legacy.sql
   rtpostgis.sql
   topology.sql
   spatial_ref_sys.sql
   

3. Now use postgis_restore.pl to restore your db into the new db. As outlined in PostGIS 2.0.0 hard upgrade.

   I have to say that this does not work out of the box for a lot of folks especially if you are on windows. You may need to set some command line variables, and you may need to install Perl. Of course we don't want to clutter the manual by telling you how to use PostgreSQL or install perl. After all you should know how to ride a bicycle before you jump on a motorcycle, but of course who has time to learn to ride a bicycle while they are jumping on a motorcycle. If you are on windows, your environment settings in batch script will look something like: Upgrade on Windows. On Unix/Linux/MacOSX your shell script will look much the same except you might use export or nothing at all and you would use ${PATH} instead.

   Bonus topic -- uninstall legacy

   If your database uses old legacy calls, the uninstall_legacy.sql will fail. However, you can safely remove the BEGIN COMMIT lines from the uninstall_legacy.sql and run it from psql. The functions still bound to views, tables etc. will remain, but the others will be removed.