[postgis-users] postgis restore using pg_dump and/or postgis_restore.pl

[Birchall, Austen]

If I do a pg_dump of a 8.4.13 postgis enable database for ERSI:

pg_dump -Fc testdb -U postgres >  backup_file

How can I restore it using:


(i)                  Pg_restore - I've tried doing lots of things such as restoring public first but nothing seems to work. The issue seems to be that in the original database if I do a \dt as the dataowner user I created I get a listing that includes:

public | geometry_columns               | table | postgres
public | sde_spatial_references         | table | sde
public | spatial_ref_sys                | table | postgres
sde    | gdb_itemrelationships          | table | sde
sde    | gdb_itemrelationshiptypes      | table | sde
sde    | gdb_items                      | table | sde
sde    | gdb_itemtypes                  | table | sde
sde    | gdb_replicalog                 | table | sde
sde    | gdb_tables_last_modified       | table | sde
sde    | i2                             | table | sde
sde    | i3                             | table | sde
sde    | i4                             | table | sde
sde    | i5                             | table | sde
sde    | i6                             | table | sde
sde    | sde_archives                   | table | sde
sde    | sde_column_registry            | table | sde
sde    | sde_coordinate_systems         | table | sde
sde    | sde_dbtune                     | table | sde
sde    | sde_geometry_columns           | table | sde
sde    | sde_layer_locks                | table | sde
sde    | sde_layer_stats                | table | sde
sde    | sde_layers                     | table | sde
sde    | sde_lineages_modified          | table | sde
sde    | sde_locators                   | table | sde
sde    | sde_logfile_pool               | table | sde
sde    | sde_metadata                   | table | sde
sde    | sde_mvtables_modified          | table | sde
sde    | sde_object_ids                 | table | sde
sde    | sde_object_locks               | table | sde
sde    | sde_process_information        | table | sde
sde    | sde_raster_columns             | table | sde
sde    | sde_server_config              | table | sde
sde    | sde_state_lineages             | table | sde
sde    | sde_state_locks                | table | sde
sde    | sde_states                     | table | sde
sde    | sde_table_locks                | table | sde
sde    | sde_table_registry             | table | sde
sde    | sde_tables_modified            | table | sde
sde    | sde_version                    | table | sde
sde    | sde_versions                   | table | sde
sde    | sde_xml_columns                | table | sde
sde    | sde_xml_index_tags             | table | sde
sde    | sde_xml_indexes                | table | sde

which I assume is a result of doing

grant usage on schema sde to public;

in psql as the sde user?

but on the restored database /dt only gives the public enties
public | geometry_columns               | table | postgres
public | sde_spatial_references         | table | sde
public | spatial_ref_sys                | table | postgres

I not at all  sure how \dt works with permissions



(ii)                Could I use postgis_restore.pl ? and if so how.

Many thanks

Austen Birchall
DBA
Met Office




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130430/8b6f95bc/attachment.html>