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