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
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.
