Migrating a PostGIS database to a different server
At some point, user databases need to be migrated to a different server. This need for server migration could be due to new hardware or a database-server software upgrade.
The following are the three methods available for migrating a database:
- Dumping and restoring the database with
pg_dump
andpg_restore
- Performing an in-place upgrade of the database with
pg_upgrade
- Performing streaming replication from one server to another
Getting ready
In this recipe, we will use the dump
and restore
methods to move user data to a new database with a new PostGIS installation. Unlike the other methods, this method is the most foolproof, works in all situations, and stores a backup in case things don't work as expected.
As mentioned before, creating a schema specifically to work with PostGIS may not work properly for Windows users. Working on the public
schema is an option in order to test the results.
How to do it...
On the command line, perform the following...