Thread: Happiness is a cross database link...
I am trying to find out some information on how to set up a cross database links so that I can have queries and foreign keys that cross a physical database. This is not just a technical requirement, but a legal one (The client requires that we do not co-mingle data). I have a new database that has tables and indices named using a new naming schema, and an old database that was implemented based on a customer requirement that is ugly, and I don't really want to put everything into one database (technical reason). I have read that contrib/dblink can do this, but I am unable to find any documentation for this features, or infact any documentation on what is in the 'contrib' pacakge. Can anyone point me to a good place to find contrib docs, or and point me to database cross linking stuff. My boss will be really pissed if I tell him we need to spend $10k to implement Oracle to have a good solution ;) Thanks, Alex Turner NetEconomist
Check out dbi-link ... not sure how far into what you are looking for it is, but from David's description to me today, it sounds like it makes things transparent to the end user (ie. the 'links' look like extra schema's within your database) ... haven't played with it yet, just going by David's '20 sec summary' :) Oh ... it can be found on http://www.pgfoundry.org ... On Wed, 5 Jan 2005, Alex Turner wrote: > I am trying to find out some information on how to set up a cross > database links so that I can have queries and foreign keys that cross > a physical database. This is not just a technical requirement, but a > legal one (The client requires that we do not co-mingle data). I have > a new database that has tables and indices named using a new naming > schema, and an old database that was implemented based on a customer > requirement that is ugly, and I don't really want to put everything > into one database (technical reason). I have read that contrib/dblink > can do this, but I am unable to find any documentation for this > features, or infact any documentation on what is in the 'contrib' > pacakge. Can anyone point me to a good place to find contrib docs, or > and point me to database cross linking stuff. My boss will be really > pissed if I tell him we need to spend $10k to implement Oracle to have > a good solution ;) > > Thanks, > > Alex Turner > NetEconomist > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Looks like a great idea, but alas I cannot actualy find any files.. CVS is empy, FTP is unaccesible, home page goes nowhere, no messages in forums.. Some more clues to the enigmatic whereabouts of this project would be appreciated if you have them. Google even suggests I try dblink postgresql instead... Thanks, Alex Turner NetEconomist On Wed, 5 Jan 2005 21:26:51 -0400 (AST), Marc G. Fournier <scrappy@postgresql.org> wrote: > > Check out dbi-link ... not sure how far into what you are looking for it > is, but from David's description to me today, it sounds like it makes > things transparent to the end user (ie. the 'links' look like extra > schema's within your database) ... haven't played with it yet, just going > by David's '20 sec summary' :) > > Oh ... it can be found on http://www.pgfoundry.org ... > > On Wed, 5 Jan 2005, Alex Turner wrote: > > > I am trying to find out some information on how to set up a cross > > database links so that I can have queries and foreign keys that cross > > a physical database. This is not just a technical requirement, but a > > legal one (The client requires that we do not co-mingle data). I have > > a new database that has tables and indices named using a new naming > > schema, and an old database that was implemented based on a customer > > requirement that is ugly, and I don't really want to put everything > > into one database (technical reason). I have read that contrib/dblink > > can do this, but I am unable to find any documentation for this > > features, or infact any documentation on what is in the 'contrib' > > pacakge. Can anyone point me to a good place to find contrib docs, or > > and point me to database cross linking stuff. My boss will be really > > pissed if I tell him we need to spend $10k to implement Oracle to have > > a good solution ;) > > > > Thanks, > > > > Alex Turner > > NetEconomist > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > ---- > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > >
Hello Alex, DBLink is located in the PostgreSQL source under $POSTGRES_SOURCE/contrib/dblink Just do a make and make install (assuming you installed PostgreSQL from source). The docs are also located in the same directory. You have to execute dblink.sql against your database to implement the functions. I suggest using views named the same as the tables in the external database for transparent queries. Take note that there is a performance hit. Hope this helps, Darren H. On Wed, 5 Jan 2005 21:02:24 -0500, Alex Turner <armtuk@gmail.com> wrote: > Looks like a great idea, but alas I cannot actualy find any files.. > CVS is empy, FTP is unaccesible, home page goes nowhere, no messages > in forums.. Some more clues to the enigmatic whereabouts of this > project would be appreciated if you have them. Google even suggests I > try dblink postgresql instead... > > Thanks, > > Alex Turner > NetEconomist > > On Wed, 5 Jan 2005 21:26:51 -0400 (AST), Marc G. Fournier > <scrappy@postgresql.org> wrote: > > > > Check out dbi-link ... not sure how far into what you are looking for it > > is, but from David's description to me today, it sounds like it makes > > things transparent to the end user (ie. the 'links' look like extra > > schema's within your database) ... haven't played with it yet, just going > > by David's '20 sec summary' :) > > > > Oh ... it can be found on http://www.pgfoundry.org ... > > > > On Wed, 5 Jan 2005, Alex Turner wrote: > > > > > I am trying to find out some information on how to set up a cross > > > database links so that I can have queries and foreign keys that cross > > > a physical database. This is not just a technical requirement, but a > > > legal one (The client requires that we do not co-mingle data). I have > > > a new database that has tables and indices named using a new naming > > > schema, and an old database that was implemented based on a customer > > > requirement that is ugly, and I don't really want to put everything > > > into one database (technical reason). I have read that contrib/dblink > > > can do this, but I am unable to find any documentation for this > > > features, or infact any documentation on what is in the 'contrib' > > > pacakge. Can anyone point me to a good place to find contrib docs, or > > > and point me to database cross linking stuff. My boss will be really > > > pissed if I tell him we need to spend $10k to implement Oracle to have > > > a good solution ;) > > > > > > Thanks, > > > > > > Alex Turner > > > NetEconomist > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > > > > ---- > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Wed, 2005-01-05 at 18:23, Alex Turner wrote: > I am trying to find out some information on how to set up a cross > database links so that I can have queries and foreign keys that cross > a physical database. This is not just a technical requirement, but a > legal one (The client requires that we do not co-mingle data). I have > a new database that has tables and indices named using a new naming > schema, and an old database that was implemented based on a customer > requirement that is ugly, and I don't really want to put everything > into one database (technical reason). I have read that contrib/dblink > can do this, but I am unable to find any documentation for this > features, or infact any documentation on what is in the 'contrib' > pacakge. Can anyone point me to a good place to find contrib docs, or > and point me to database cross linking stuff. My boss will be really > pissed if I tell him we need to spend $10k to implement Oracle to have > a good solution ;) The docs for a contrib package are in their directory. For PostgreSQL 7.4.6, the readme file for the contrib package is here: postgresql-7.4.6/contrib/dblink/README.dblink and the docs are int the directory postgresql-7.4.6/contrib/dblink/doc Assuming you have the .tar.gz version of postgresql. IF you install postgresql via packages then the location of such files are dependent on how your packages are installed by your distribution.