[postgis-users] cross-database queries and choice of database configuration

[Tim Pigden]

Hi
Postgres doesn't properly support cross-database queries but there seems to
something called dblink

I'm looking at constructing a database which will have a point data
(customer locations, delivery records and other logistics info) but all the
other data is open-source feature data - boundaries, roads, etc.

I had hoped  to implement a structure witgh the feature data in one
database and put my application-specific data in another - not least
because it simplifies backups and so on. The feature data is very large and
slow-to-change whereas the logistics data is much smaller and more dynamic.

Is this a practical approach for postgis type queries or do I have to put
it all in one database to get good querying flexibility?

Thanks
Tim
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] cross-database queries and choice of database configuration

[Nicolas Ribot]

> Hi
> Postgres doesn't properly support cross-database queries but there seems to
> something called dblink
>
> I'm looking at constructing a database which will have a point data
> (customer locations, delivery records and other logistics info) but all the
> other data is open-source feature data - boundaries, roads, etc.
>
> I had hoped  to implement a structure witgh the feature data in one database
> and put my application-specific data in another - not least because it
> simplifies backups and so on. The feature data is very large and
> slow-to-change whereas the logistics data is much smaller and more dynamic.
>
> Is this a practical approach for postgis type queries or do I have to put it
> all in one database to get good querying flexibility?
>

I would definitely use one database, eventually separating data into
schemas. (but its only my opinion ;) )
For the backup, pg_dump allows you to backup only tables or schemas.
I think it would be easier to manage than 2 databases.

Nicolas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] cross-database queries and choice of database configuration

[Paul Ramsey]

On Thu, Mar 22, 2012 at 4:53 AM, Tim Pigden  wrote:
> Is this a practical approach for postgis type queries or do I have to put it
> all in one database to get good querying flexibility?

No, bad approach. Put it all in one database, with the application
data in one schema and the geo data in another. Then for regular
backups you can just backup the application schema separately.

P.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] cross-database queries and choice of database configuration

[shirabez]

Following up on this thread from a few months ago, can you give a rationale
for this perspective? Why is it better to go with one database? Performance?
Simplicity? Both? Thanks,

Shira



Paul Ramsey-4 wrote
> 
> On Thu, Mar 22, 2012 at 4:53 AM, Tim Pigden  wrote:
>> Is this a practical approach for postgis type queries or do I have to put
>> it
>> all in one database to get good querying flexibility?
> 
> No, bad approach. Put it all in one database, with the application
> data in one schema and the geo data in another. Then for regular
> backups you can just backup the application schema separately.
> 
> P.
> ___
> postgis-users mailing list
> postgis-users@.refractions
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 


--
View this message in context: 
http://postgis.17.n6.nabble.com/cross-database-queries-and-choice-of-database-configuration-tp4644336p4998684.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] cross-database queries and choice of database configuration

[Paul Ramsey]

Because the feature he wants, separately backing up spatial and
attributes, can be easily achieved inside one database by putting the
different parts in different schemas. At the same time, it would be
common to expect to run queries that would going the spatial to the
attributes, and that would be very inefficient indeed over a
cross-database dblink or fdw connection.

Just do not store data you want to join together in different
databases, full stop.

P.

On Fri, Jun 29, 2012 at 9:10 PM, shirabez  wrote:
> Following up on this thread from a few months ago, can you give a rationale
> for this perspective? Why is it better to go with one database? Performance?
> Simplicity? Both? Thanks,
>
> Shira
>
>
>
> Paul Ramsey-4 wrote
>>
>> On Thu, Mar 22, 2012 at 4:53 AM, Tim Pigden  wrote:
>>> Is this a practical approach for postgis type queries or do I have to put
>>> it
>>> all in one database to get good querying flexibility?
>>
>> No, bad approach. Put it all in one database, with the application
>> data in one schema and the geo data in another. Then for regular
>> backups you can just backup the application schema separately.
>>
>> P.
>> ___
>> postgis-users mailing list
>> postgis-users@.refractions
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
> --
> View this message in context: 
> http://postgis.17.n6.nabble.com/cross-database-queries-and-choice-of-database-configuration-tp4644336p4998684.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users