Repeat after me: I will dump my databases before upgrading PostgreSQL or PostGIS. I will dump my databases before upgrading PostgreSQL or PostGIS. I will dump my databases before upgrading PostgreSQL or PostGIS.
I got fed up with the 2 year old versions of GIS software in Ubuntu 12.04 so I upgraded to versions from the awesome Ubuntu GIS PPAs. However after upgrading PostGIS any spatial query gave me the error
ERROR: could not access file “$libdir/postgis-2.1”: No such file or directory
No idea why that says “2.1” when I installed PostGIS 2.0.1 (2.0.1-2~precise3), but there it is. Googling around I realized this is a symptom of not reinstalling the PostGIS functions in the database. There’s a variety of confusing database upgrade options, and the Debian packages are very clear about not injecting any SQL for you “for safety”.
However, there is a better way! Starting with Postgres 9.1 and PostGIS 2.0, PostGIS can be installed as a simple database extension. No more futzing with template databases on creation or importing lots of .sql files in a new database. All you need to do to spatialize a new database is
create extension postgis create extension postgis_topology
I’m not sure you can use these commands to rescue an old database that didn’t survive an upgrade, but it works great with new databases. IIRC there’s even an upgrade path for extensions. I dropped my old template_postgis2 just so I never get confused again.