Re: Cross-DB linking?

On Thu, 11 Sep 2003, Andrew Biagioni wrote:

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

Schemas rock.  Like little sandboxes for each user with their own play
areas and what not.

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

Ahhh.  I see. You might want to look into the erserver replication
application on gborg.postgresql.org.  That's a pretty nice little system,
and the .org and .info domains run on top of postgresql using it, so it
has had plenty of production testing.


>
>     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)
> >
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>