[postgis-users] Migrating part of database (At schema or table level) between two version of postgis.

[Regina Obe]

Actually with create extension it wouldn't even matter if your tables were in public schema as well.

 

The only thing to make sure is whatever schema you installed postgis in in the older database is the same schema you install in new database.

 

Sounds like you installed postgis in public.

 

That said using pg_dump  to backup the whole data

 

Pg_restore to restore the whole database

 

Should be sufficient.  You don't even need to install postgis in the database  before hand, since pg_restore includes CREATE EXTENSION postgis (but no version) so always uses the postgis defined in the /share/extension/postgis.control file.

 

 

The only reason to run 

 

CREATE EXTENSION postgis;  

 

First is if you are doing a partial restore

 

Or you have multiple versions of PostGIS installed in your cluster, and don't want it to use the version specified in the postgis.control file.

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Hemant Bist
Sent: Thursday, October 19, 2017 3:25 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Migrating part of database (At schema or table level) between two version of postgis.

 

Thanks, I will give it a try.

All the new data schemas were created long after installation of postgis  so postgis cannot be installed there.

Most probably the postgis functions/objects are in public schema.

1) the posgtis was installed via create extension postgis ; -- no schema was specified.

2) There is only one table with name  spatial_ref_sys and its in public schema.

 

select table_schema from information_schema.tables where table_name ='spatial_ref_sys';

 table_schema 

--------------

 public

 

 

 

On Thu, Oct 19, 2017 at 10:43 AM, Sandro Santilli <strk at kbt.io <mailto:strk at kbt.io> > wrote:

On Thu, Oct 19, 2017 at 10:21:46AM -0700, Hemant Bist wrote:
> We  have an old database (postgis 2.1.2/postgresql-9.3/ubuntu 14.04), and a
> new database (postgis 2.4.0,postgresql-9.5,ubuntu 16.04). Old database was
> created via Hard Upgrade
> <https://postgis.net/docs/postgis_installation.html#hard_upgrade> Although
> both dbs have spatial tables in public schema, the data I want to copy over
> is in their own schemas.
>
> Can I do the following?
> 1) Copy data for a schema/tables  from old db to new db?
> 2) Copy data for a schema /tables from new db to old db? NOT sure what the
> caveats here would be.

If your data is in a *different* schema from the postgis
functions/objects it should be straight-forward to move them around
(pg_dump/pg_restore). If postgis is in the same schema as the data
you'll need postgis_restore.pl <http://postgis_restore.pl>  instead of pg_restore. In both cases
you can restrict the pg_dump to the schema of interest.

--strk;
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20171019/6221493c/attachment.html>