How can I create a copy of postgis db from one windows machine to another

Question

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database.

In mssql it would be simple , just backup -> restore.

In postgres when I try to backup and restore using pgAdmin i keep getting different problems.

1. The PostGIS tables (with the geometries columns) are not being backedup
2. I get errors when resoring that sequence does not exits

Is there a simple way / tool on windows to backup / restore postgres db with gis extension ?

Answer 1

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