Thread: Table ordering in pg_dump
Hello,
I am trying to copy a PostGIS database from one server to another using pg_dump. This database uses pointcloud objects to store lidar points.
When I try to import the dump on the new machine, I get the following errors:
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 am assuming that since pcid is a key in a a table (pointcloud_formats) that has not been loaded yet, it throws the error.
1) Is there any way to tell pg_dump the order in which the tables should be dumped?
2) Am I correct to assume that if I use the --disable-trigger option in pg_restore it should mitigate the problem? This only works on a data-only dump per the documentation, so if I am doing the initial copy of the database to the new server, will data-only give me everything I need or is it only good for incremental updates to an already running copy of a database?
3) Alternatively, is there a psql command that I could run on the new server to disable all triggers/constraint checking, run the restore and then re-enable them?
4) Since we are in a lab environment, I can shut down the server and copy the folder containing the database to the new server. Is this kosher? I could then try the data-only dump and restore in 2) to keep them updated.
Sorry for the noobish questions, thank you for your time. Coming from mysql so the concepts are familiar but the execution different!
JF Prieur
PhD candidate in Remote Sensing
Université de Sherbrooke
Sherbrooke, QC, Canada
On Thu, Jun 16, 2016 at 10:32 AM, Jean-Francois Prieur <jfprieur@gmail.com> wrote:
1) Is there any way to tell pg_dump the order in which the tables should be dumped?2) Am I correct to assume that if I use the --disable-trigger option in pg_restore it should mitigate the problem? This only works on a data-only dump per the documentation, so if I am doing the initial copy of the database to the new server, will data-only give me everything I need or is it only good for incremental updates to an already running copy of a database?3) Alternatively, is there a psql command that I could run on the new server to disable all triggers/constraint checking, run the restore and then re-enable them?4) Since we are in a lab environment, I can shut down the server and copy the folder containing the database to the new server. Is this kosher? I could then try the data-only dump and restore in 2) to keep them updated.Sorry for the noobish questions, thank you for your time. Coming from mysql so the concepts are familiar but the execution different!
Firstly, what version of postgres are you using? What format are you dumping the file to?
When you dump using the compressed format, the pg_restore process will not have an FK's until such time that the data is entirely loaded.