Postgresql – Import Postgres 8.3 database in Postgres 10.5

As @a_horse_with_no_name wrote pg_dump and pg_restore cope with PostGIS things without problems. At least since PostGIS 2.0, previously (before PostGIS became an extension) you might had to change paths of the c-library used for geometry-functions.

In PostgreSQL since 9.x ist is also fairly easy:

1. backup at the original databse with pg_dump
2. at the new system:
   1. create a new database
   2. login to that new database and run CREATE EXTENSION postgis;
   3. restore the backuped file with pg_restore to the new database

That works for me as well on windows as on linux, you could even use PgAdmin for it.

Note: the version of pg_dump of the original (backed up) system may not be newer than of the new system - see https://stackoverflow.com/q/12836312/8957103

I would recommend dumping the 9.1 database from your new 9.3 server like this:

pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump

I recommend using the 9.3 pg_dump as pg_dump is always backwards compatible, but not forward compatible. In other words, the newer pg_dump will take care of any syntax changes that the new server requires that the older utility doesn't know about.

Be sure to make sure your pg_hba.conf and listen_addresses in postgresql.conf are set up to allow you to remotely connect and dump appropriately as well.

If you wanted to try a dump and restore in one step, you could try this as well:

pg_dump -h remotehost -U remoteuser remotedbname | psql -U localuser localdbname

Hope that helps. =)