Database Reference
In-Depth Information
Migrating
a
PostGIS
database
to
a
different server
At some point, user databases need to be migrated to a different server. This need
forservermigrationcouldbeduetoanewhardwareordatabase-serversoftwareup-
grade.
The following are thethree methods available for migrating a database:
• Dump and restore the database with
pg_dump
and
pg_restore
• Perform an in-place upgrade of the database with
pg_upgrade
• Perform 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
newdatabasewithanewPostGISinstallation.Unliketheothermethods,thismethod
isthemostfoolproof,worksinallsituations,andstoresabackupincasethingsdon't
work as expected.
How to do it...
On the command line,perform the following steps:
1. Thoughabackupfilewascreatedinthischapter'sthirdrecipe,createanew
backup file by executing the following command:
> pg_dump -f chapter10.backup -F custom
chapter10
2. Createanewdatabasetowhichthebackupfilewillberestoredbyexecuting
the following commands:
> psql -d postgres
postgres=# CREATE DATABASE new10;
3. Connect to the
new10
database and create a schema for PostGIS as follows:
