The following bug has been logged online: Bug reference: 4777 Logged by: Kasia Tuszynska Email address: ktuszyn...@esri.com PostgreSQL version: 8.3.7 Operating system: Windows 2003 server Description: pg_restore is done in alphabetical order by schema Details:
Summary:Postgres utility pg_restore.exe restores in alphabetical order by schema name, thus if a user table has a dependency on data yet to be restored (because it resides in a schema that will be restored further down in the restore) it will be blank. The Environment:Postgresql 8.3.0 (but the issue was retested in 8.3.7)is used as a database to store spatial data maintained by an application called ArcSDE (Spatial Database Engine). The application maintains 87 system tables, one of which resides in the "public" schema, the rest reside in a schema called "sde". The data it's self is stored in a user defined data type. All user data has a dependancy on a table in the public schema, the dependency is not maintained with constraints. If user data resides in schema called avtest and a schema called vtest, doing a restore will result in the data in the vtest schema to restore correctly ( meaning that the table was populated with data), and the data stored in the avtest schema will have no records in the table. example of unsuccessfull backup and restore: C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h aisak -p 5432 -U postgres -F c -b -v -f "C:\aisak.dump.backup" postgis C:\Program Files\PostgreSQL\8.3\bin\pg_restore.exe -h localhost -p 5432 -U postgres -d postgis -v "C:\aisak.dump.backup" If however I were to restore the public schema first, and than the remaineder of the database the data in both schemas restores correctly, because the public.sde_spatial_reference table is present for the data restore in both avtest and vtest schema. Example of a successful restore: C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h aisak -p 5432 -U postgres -F c -b -v -f "C:\testbackup.dump.backup" testbackup C:\Program Files\PostgreSQL\8.3\bin>pg_restore.exe -n public -p 5432 -U postgres -d testbackup -v "C:\testbackup.dump.backup" C:\Program Files\PostgreSQL\8.3\bin>pg_restore.exe -p 5432 -U postgres -d testbackup ckup -v "C:\testbackup.dump.backup" Identifying the problem:The best way to see the issue without setting up the whole environment of ArcSDE is to look at the text backup file: C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h aisak -p 5432 -U postgres -F p -v -f "C:\testbackup_text.dump.backup" testbackup reading the file illustrates that, the order in which the restore is structured is the following: create all schemas (in alphabetical order) create all tables, object ( in alphabetical order) execute copy command to populate tables ( in alphabetical schema order), the data I was testing with was restored in the following order: ... -- TOC entry 2891 (class 0 OID 889207) -- Dependencies: 2239 -- Data for Name: state; Type: TABLE DATA; Schema: avtest; Owner: avtest COPY state (objectid, state_name, state_fips... ... -- TOC entry 2806 (class 0 OID 888299) -- Dependencies: 2141 -- Data for Name: sde_spatial_references; Type: TABLE DATA; Schema: public; Owner: sde COPY sde_spatial_references (srid, sr_name, ... ... -- TOC entry 2893 (class 0 OID 889278) -- Dependencies: 2241 -- Data for Name: poles; Type: TABLE DATA; Schema: vtest; Owner: vtest COPY poles (objectid, dscktlabel,... Please notice that the sde_spatial_references table is restored as the second table in the list. It should be restored as the first table since all of the user data (states and poles) in this case relies on it. Please let me know if there is any other information I can provide. Sincerely, Kasia Tuszynska ktuszyn...@esri.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
