How to Backup and Restore an entire postgreSQL database that contains one or more PostGIS databases in two easy steps

How to Backup and Restore an entire postgreSQL database that contains one or more PostGIS databases in two easy steps


Problem: We have a PostgreSQL server on Windows Server 2008 that has 5 databases. One of which is a PostGIS spatial database. If you just do a complete server backup and restore, the PostGIS database doesn't restore correctly.


We wanted to upgrade to 64 bits, the latest V9 version of Postgresql from V8, so needed to do a complete backup and restore to the new server.


After a couple of days mucking around in the documentation, I found that the instructions here did not work, at least not for me.

PostGIS Hard Upgrade for Windows

http://trac.osgeo.org/postgis/wiki/UsersWikiWinUpgrade

Instead, I found a simple work around, just make sure the PostGIS database is created before the restore.

First. Do a Server backup from the old server using pgAdmin III

On the old server, the easiest way is to use PGAdmin III, right click on the server and choose “Backup Server...”

Easy.

Next, 

Transfer this file to the new server.

We will restore this file on the new machine. It includes all roles and databases. The restore is pretty ugly, but works.

However, before doing this restore, you have to create any PostGIS databases, and turn them into PostGIS databases. Otherwise, the restore won't work correctly for these.

Create the PostGIS database:

Right click on “Databases” in PGAdmin and select ” New Database”.

Create a new PostGIS database on the new database server in PgAdmin.

When this is done, highlight the OGI database and open an SQL Query window from pgAdmin. (the magnifying glass in the toolbar)

Run these commands:

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

Also, open and execute the “legacy.sql” script in the SQL window. This is located here:

PostgreSQL913\share\contrib\postgis-2.0

From the start menu, run the PostGreSQL 9.1 | SQL Shell as Administrator

Log into the server, with Database OGI. Run the following commands:

\cd f:/ogi/backups This makes your current directory where the backup SQL file is

\i OGI_5-23-2012.sql This loads the backup SQL file into the OGI database.

You will get many errors and warnings, you can ignore them. For sanity, it is best to just minimize the SQL Shell window and wait for the process to complete.

When this has completed, you will have all the roles, tables etc that existed in the old server. This process will work for upgrading from any version of PostgreSQL/PostGIS to this one.