[postgis-users] Install postgis on a different schema

[P Kishor]

On Tue, Nov 24, 2009 at 4:03 PM, MarkW <mark.wimer at gmail.com> wrote:
>
>
> On Tue, Nov 24, 2009 at 5:01 PM, P Kishor <punk.kish at gmail.com> wrote:
>>
>> On Tue, Nov 24, 2009 at 3:55 PM, P Kishor <punk.kish at gmail.com> wrote:
>> > On Tue, Nov 24, 2009 at 2:56 PM, MarkW <mark.wimer at gmail.com> wrote:
>> >>
>> >> On Tue, Nov 24, 2009 at 3:13 PM, P Kishor <punk.kish at gmail.com> wrote:
>> >>>
>> >>> Leo,
>> >>>
>> >>> Many thanks for your clear replies. One follow-up question --
>> >>>
>> >>> On Tue, Nov 24, 2009 at 1:29 PM, Paragon Corporation <lr at pcorp.us>
>> >>> wrote:
>> >>> ..
>> >>> > --
>> >>> > d. The notes above refer to setting the search_path and the
>> >>> > session_path.
>> >>> > Where is that done?
>> >>> >
>> >>> > You can do it in postgresql.conf or at the database level.  The
>> >>> > database
>> >>> > level is better I think
>> >>> >
>> >>> > ALTER DATABASE mydb SET search_path = public, shared
>> >>> >
>> >>> >
>> >>> > The above will have it set for good, except if you restore a db, you
>> >>> > need to
>> >>> > remember to rerun the above command.
>> >>> >
>> >>>
>> >>>
>> >>> In the above note, should I be setting the search_path to the
>> >>> different project schemas as well, and not just 'public' and 'shared'?
>> >>> So, if I have the following schemas in mydb, 'public', 'shared',
>> >>> 'project1', 'project2' then the above command really should be
>> >>>
>> >>> ALTER DATABASE mydb SET search_path = public, shared, project1,
>> >>> project2;
>> >>>
>> >>> And, if I add another schema, 'project3', a few months later, then I
>> >>> run
>> >>>
>> >>> ALTER DATABASE mydb SET search_path = public, shared, project1,
>> >>> project2, project3;
>> >>>
>> >>> Correct?
>> >>>
>> >>> By the way, what is that search_path doing? What is it for? And, why
>> >>> don't I have to set it if I have only a 'public' schema? Is that
>> >>> because by default, a public schema doesn't need an explicit search
>> >>> path?
>> >>>
>> >>> Puneet.
>> >>>
>> >>
>> >> If I could add another question to this thread since it's related to
>> >> the
>> >> PostGIS schema. I am moving to this "Project1"  schema approach in prep
>> >> for
>> >> an upgrade and to keep the data in separate schema in the future. If
>> >> my
>> >> Project1 schema is dumped/backed up, then put back/restored after an
>> >> upgrade
>> >> of PostgreSQL/PostGIS, what's the best way to restore information in
>> >> the
>> >> Geometry_Columns table; should that be saved, or should I be using a
>> >> function to re-populate the Geometry_Columns table?
>> >>
>> >
>> > I am not sure what you are asking above. If you backup Project1
>> > schema, then restore it in another db, the_geom columns will all come
>> > back. After all, backup will back up all the tables in that schema,
>> > and restore will restore them. A table is a table, doesn't matter what
>> > kind of columns it holds.
>> >
>> > Maybe I misunderstood your concern.
>> >
>> >
>> >
>>
>>
>> Yeah, I am pretty sure I misunderstood your problem. Disregard my
>> response, and let's wait for a more educated response. I am curious as
>> well now.
>>
>> > Puneet Kishor http://www.punkish.org
>> > Carbon Model http://carbonmodel.org
>> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> > -----------------------------------------------------------------------
>> > Assertions are politics; backing up assertions with evidence is science
>> > =======================================================================
>> > Sent from Madison, Wisconsin, United States
>
> Thanks Puneet - sorry for the confusion. I was referring to the
> "geometry_columns" table - I believe it's always in the public schema along
> with postGIS functions and not in each schema; I wonder if I need to worry
> about backing that up, or is this what one of the newer functions is for:
> http://postgis.refractions.net/documentation/manual-1.4/Populate_Geometry_Columns.html
>


And, which brings me to another related question -- diddling around
with my database, I moved a spatial table from one schema to another
with

ALTER TABLE <table> SET SCHEMA <new_schema>

But, the geometry_columns.f_table_schema doesn't get updated with the
above command. This leads to a mismatch between the two, and smoke
starts coming out of the hard disk.

Not nice.