This post shows how to migrate a PostgreSQL 9.1 database that has geospatial data types to a PostgreSQL 9.6.5 database.
An in-place upgrade is possible, but this case required was to move from one physical server to another server. The target server already had PostgreSQL 9.6.5 installed.
Note, the following was done on RHEL 7.4 using PostgreSQL 9.6.5.
On source server, dump the source database as follows:
pg_dump -U postgres -Fc -b -v -f "spatial_db.dump" spatial_db
where:
-F specifies output file format
-c specifies custom-format archive file
-b specifies include large objects
-v specifies verbose mode
-f specifies output file
Copy the dump file over to the target server.
On the 9.6.5 instance, create the new database and appropriate extensions:
psql
create database spatial_db;
\c spatial_db
create extension postgis;
create extension btree_gist;
\q
I re-started the 9.6.5 instance at this point. If you get any errors when creating the btree_gist extension, try again and restart the instance. Check what extensions exist in your database as follows:
psql -U postgres -d spatial_db
spatial_db=# \dx
List of installed extensions
<headers removed from dx command>
btree_gist
plpgsql
postgis
You may require other extensions based on your site.
Run the restore as follows:
perl /usr/pgsql-9.6/share/contrib/postgis-2.3/postgis_restore.pl spatial_db.dump | psql -U postgres spatial_db 2>errors.log
You'll need to find the postgis_restore.pl file on your system as it may be located in a different location.
Check the errors.log for any errors.
One thing to note when going from 9.1 to 9.6.5 is that the public.geometry_columns table in 9.1 gets converted to a view in 9.6.5. There might be other objects as well, but this is one that I noticed when I did table counts from 9.1 to 9.6.5.
