Re: Cross-DB linking? - Mailing list pgsql-admin

Thanks -- I haven't looked at schemas, I guess I will now :-).

As for stability -- I was referring to the hardware breaking down, not
Postgresql!

    Andrew


9/11/03 5:24:50 PM, "scott.marlowe" <scott.marlowe@ihs.com> wrote:

>You might want to consider using schemas to accomplish some of this.
>
>You can backup individual schemas as of 7.4 (maybe 7.3, but I've not used
>it in production, waiting for 7.4 to upgrade from 7.2)
>
>performance will almost certainly suffer if you are doing cross db work,
>so schemas help there.
>
>I've never had any stability issues with Postgresql, and certainly not
>from having everything in one database.
>
>Other than the ability to spread your load across multiple machines,
>7.3/7.4 and schemas should address all your concerns.
>
>And no, you can't fk across databases.  You can get some primitive (but
>quite functional) cross database action with the contrib/dblink package.
>
>On Thu, 11 Sep 2003, Andrew Biagioni wrote:
>
>> I am thinking of separating my data into various DBs (maybe on the same
server,
>> probably not) -- mostly for performance/stability/backup reasons -- but I
have
>> a considerable amount of foreign keys, views, and queries that would need to
>> work across DBs if I were to split things the way I want to.
>>
>> Is it possible to have foreign keys / views / queries work across database
>> boundaries?  On the same server / on separate servers?  If so, how?
>>
>> For example, I have:
>>  - a table, A, with > 200 K rows which never changes;
>>  - another table, B with < 10 K rows which changes frequently;
>>  - and a third table, C, which joins A and B, i.e. has foreign keys into A
and
>> B, and changes rarely
>>
>> I would like to have A in one DB, dbA (possibly its own server);  B in
another
>> DB, dbB (possibly its own server);  and C either with A or with B (this one
is
>> not an issue per se).
>>
>> What I'm looking to gain is:
>>  - dbA would be backed up/replicated religiously, and possibly on a server
>> optimized for frequent writes
>>  - dbB would NEVER be backed up, possibly on a server optimized for cacheing
>>  - each database's schema would be simpler and easier to manage
>>  - as the number of records and users grow, be able to distribute the
>> computing/storage/memory load among various machines rather than have to
>> upgrade the hardware
>>
>> Thanks in advance!
>>
>>         Andrew
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if your
>>       joining column's datatypes do not match
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>