[postgis-users] problem with restoring database postgis 2.0

[John Callahan]

Thanks Steve.  Great idea.  Unfortunately, I don't think it helps me now
(unless I want to reload 70 or so datasets.)   I'll keep it in mind for
other databases.

I'm still learning postgis but how do you keep the functions in the data
schema if the database is created (with functions) before that schema?  Or
should all the functions stay in the public schema and datasets in the
"data" schema?  Thanks for any help.

- John





On Thu, Mar 22, 2012 at 8:11 PM, Stephen Woodbridge <woodbri at swoodbridge.com
> wrote:

> This is why I ALWAYS build my databases and create a data schema than set
> the search path to "data, public". Then all my functions and data go into
> "data" and I can pg_dump -N public to leave behind all the postgis stuff.
>
> createdb -T template_postgis mydb
> psql mydb -c "create schema data; alter database mydb set search_path to
> data, public"
> ...
> pg_dump ... -f mydb.dump -N public mydb
>
> Never have to worry about that again.
>
> -Steve
>
>
> On 3/22/2012 8:02 PM, John Callahan wrote:
>
>> Well, that makes sense.  I was using the --data-only option because I
>> did not want to bring over the functions (since the functions already
>> exist in the new database through the postgis creation script.)
>>
>> Is there a way to dump/restore only the data tables (including create
>> statements, indexes,  sequences, privileges) and not the functions or
>> function comments?  I ran into a problem recently while upgrading when I
>> had too many similar functions and postgis couldn't determine a unique
>> function at times.
>>
>> - John
>>
>> *************************************************
>> John Callahan, Research Scientist
>> Delaware Geological Survey, University of Delaware
>> URL: http://www.dgs.udel.edu
>> ***************************************************
>>
>>
>>
>> On Thu, Mar 22, 2012 at 7:17 PM, Bborie Park <bkpark at ucdavis.edu
>> <mailto:bkpark at ucdavis.edu>> wrote:
>>
>>    The pg_dump call is called with "--data-only" so you're not getting
>>    any table creation information.  And since the new DB doesn't have
>>    any tables, restoring the data will result in an error.
>>
>>    -bborie
>>
>>
>>    On 03/22/2012 04:14 PM, John Callahan wrote:
>>
>>        I having a problem with something that is usually straight
>>        forward. I am
>>        moving a database from a Windows 2003 Server box to Windows 2008
>>        Server.
>>          Both are x64 but running 32bit Postgres 9.0.7.  The
>>        new/destination
>>        database runs the latest postgis 2.0 binaries (from March 19 or
>>        so) and the
>>        source/older database runs postgis 2.0 libraries from a few
>>        months ago.
>>
>>        On the new db, I installed postgres 9.0.7, then postgis 2.0
>>        latest build,
>>        which created my database.  Of course, the database does not
>>        have any
>>        tables at this point (just the functions, a few views.)
>>
>>        I run pg_dump on the source db as so:
>>        pg_dump.exe --host localhost --port 5432 --username "username"
>>        --role
>>        "myrole"  --format plain --data-only --verbose --file
>>        "C:\temp\webdata_20120315.sql" "mydb"
>>
>>        When I try to run the resulting sql on the new server, I get the
>>        following
>>        error:
>>        ERROR: relation<table_name>  does not exist
>>
>>        If I use --inserts options in pg_dump, I still get the "relation
>>        does not
>>        exist error"   If I use pgAdmin to backup and restore (using the
>> tar
>>        format), the error is the same.
>>
>>        What am I missing?   Thanks.
>>
>>        - John
>>
>>        ****************************** *****************
>>        John Callahan, Research Scientist
>>        Delaware Geological Survey, University of Delaware
>>        URL: http://www.dgs.udel.edu
>>        ****************************** *******************
>>
>>
>>
>>
>>        ______________________________ _________________
>>        postgis-users mailing list
>>        postgis-users at postgis. refractions.net
>>        <mailto:postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
>> >
>>        http://postgis.refractions. net/mailman/listinfo/postgis- users
>>
>>        <http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>> >
>>
>>
>>    --
>>    Bborie Park
>>    Programmer
>>    Center for Vectorborne Diseases
>>    UC Davis
>>    530-752-8380 <tel:530-752-8380>
>>    bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
>>    ______________________________ _________________
>>    postgis-users mailing list
>>    postgis-users at postgis. refractions.net
>>    <mailto:postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
>> >
>>    http://postgis.refractions. net/mailman/listinfo/postgis- users
>>    <http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>> >
>>
>>
>>
>>
>>
>> ______________________________**_________________
>> postgis-users mailing list
>> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>>
>
> ______________________________**_________________
> postgis-users mailing list
> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120323/ff6f710f/attachment.html>