[pdal] PointCloud: Transferring database to different server

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[pdal] PointCloud: Transferring database to different server

jfprieur
Hello,

Was not sure whether to post this here or PostGIS list, apologies if this is the wrong list!

We have a PostGIS database using pointcloud objects to store our lidar data.

I am trying to move the database to another server using

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

The connections work fine remotely and it starts creating the database (including the extensions), when it starts to copy the actual pointcloud entries, we get an error:


2016-06-15 16:33:59 EDT LOG Pointcloud (1.1.0) module loaded at character 53
2016-06-15 16:33:59 EDT STATEMENT CREATE TABLE crpt (
   id integer NOT NULL,
   pa pcpatch(1),
   tree_id character varying
);
2016-06-15 16:34:24 EDT ERROR no entry in "pointcloud_formats" for pcid = 1
2016-06-15 16:34:24 EDT CONTEXT COPY crpt, line 1, column pa: "0101000000020000007403000002E8030000090000006588634786E6FDF6237D42E0B460602D36A0D8B9D80E9DCB5D580D83..."


I have the feeling something is not being done in the correct order, a missing schema or something.

What is the proper procedure for copying a poincloud enabled database from one server to another?

Thanks,
JF

_______________________________________________
pdal mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/pdal
Reply | Threaded
Open this post in threaded view
|

Re: [pdal] PointCloud: Transferring database to different server

Rémi Cura
Hey,
in this case the fix seems obvious :
you should first copy the content of "pointcloud_formats" before copying the points.
I don't remember if there are options in pgdump to prioritize the tables to be dumped and copied.

Indeed,
pointcloud_formats is like the spatial_ref_sys postgis table, that is it defines the referential, therefore you need it to create the type of points you want.

Cheers,
Rémi-C

2016-06-15 22:57 GMT+02:00 Jean-Francois Prieur <[hidden email]>:
Hello,

Was not sure whether to post this here or PostGIS list, apologies if this is the wrong list!

We have a PostGIS database using pointcloud objects to store our lidar data.

I am trying to move the database to another server using

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

The connections work fine remotely and it starts creating the database (including the extensions), when it starts to copy the actual pointcloud entries, we get an error:


2016-06-15 16:33:59 EDT LOG Pointcloud (1.1.0) module loaded at character 53
2016-06-15 16:33:59 EDT STATEMENT CREATE TABLE crpt (
   id integer NOT NULL,
   pa pcpatch(1),
   tree_id character varying
);
2016-06-15 16:34:24 EDT ERROR no entry in "pointcloud_formats" for pcid = 1
2016-06-15 16:34:24 EDT CONTEXT COPY crpt, line 1, column pa: "0101000000020000007403000002E8030000090000006588634786E6FDF6237D42E0B460602D36A0D8B9D80E9DCB5D580D83..."


I have the feeling something is not being done in the correct order, a missing schema or something.

What is the proper procedure for copying a poincloud enabled database from one server to another?

Thanks,
JF

_______________________________________________
pdal mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/pdal


_______________________________________________
pdal mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/pdal
Reply | Threaded
Open this post in threaded view
|

Re: [pdal] PointCloud: Transferring database to different server

Rémi Cura
Maybe a simple mail to postgres list,
I guess it must be very common to want to change order of pgdump.

Another simple way would be to write a script that calls pgdump on each table (excluding extension tables),
this way you have control.

I'm not an expert, but I think I remember that copying postgres folder is a very bad idea.

Cheers,
Rémi-C

2016-06-16 13:32 GMT+02:00 Jean-Francois Prieur <[hidden email]>:
Thanks again Rémi, no such option in pgdump unfortunately! I finally did it the old school way of shutting down the server and copying the folder containing the database to the new machine.

I will keep looking to see if there is a way to prioritize table order and get back to the list if I find something.

JF


On Thu, Jun 16, 2016, 04:55 Rémi Cura <[hidden email]> wrote:
Hey,
in this case the fix seems obvious :
you should first copy the content of "pointcloud_formats" before copying the points.
I don't remember if there are options in pgdump to prioritize the tables to be dumped and copied.

Indeed,
pointcloud_formats is like the spatial_ref_sys postgis table, that is it defines the referential, therefore you need it to create the type of points you want.

Cheers,
Rémi-C

2016-06-15 22:57 GMT+02:00 Jean-Francois Prieur <[hidden email]>:
Hello,

Was not sure whether to post this here or PostGIS list, apologies if this is the wrong list!

We have a PostGIS database using pointcloud objects to store our lidar data.

I am trying to move the database to another server using

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

The connections work fine remotely and it starts creating the database (including the extensions), when it starts to copy the actual pointcloud entries, we get an error:


2016-06-15 16:33:59 EDT LOG Pointcloud (1.1.0) module loaded at character 53
2016-06-15 16:33:59 EDT STATEMENT CREATE TABLE crpt (
   id integer NOT NULL,
   pa pcpatch(1),
   tree_id character varying
);
2016-06-15 16:34:24 EDT ERROR no entry in "pointcloud_formats" for pcid = 1
2016-06-15 16:34:24 EDT CONTEXT COPY crpt, line 1, column pa: "0101000000020000007403000002E8030000090000006588634786E6FDF6237D42E0B460602D36A0D8B9D80E9DCB5D580D83..."


I have the feeling something is not being done in the correct order, a missing schema or something.

What is the proper procedure for copying a poincloud enabled database from one server to another?

Thanks,
JF

_______________________________________________
pdal mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/pdal



_______________________________________________
pdal mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/pdal