Table ordering in pg_dump

From: Jean-Francois Prieur <jfprieur(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Table ordering in pg_dump
Date: 2016-06-16 14:32:37
Message-ID: CAEMWU2_RgZW2t=D-G4h8ZkHVEGSqtG7Omqpj4ep0QbE6z6WYEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-06-16 14:33:15 Re: regarding schema only migration from sqlserver to postgres with runmtk.sh
Previous Message David G. Johnston 2016-06-16 14:25:57 Re: regarding schema only migration from sqlserver to postgres with runmtk.sh