Options which are to be set ----- The following parameters can be set on a JDBC foreign server: drivername: The name of the JDBC driver e.g.(org.postgresql.Driver). Note that drivername has to be specified for jdbc_fdw to work i.e. it is compulsory. url: The JDBC URL that shall be used to connect to the foreign database. Note that URL has to be specified for jdbc_fdw to work i.e. it is compulsory. querytimeout: The time after which a query shall automatically be terminated. The option can be used for terminating hung queries. jarfile: The path and name(e.g. folder1/folder2/abc.jar) of the JAR file of the JDBC driver to be used of the foreign database. maxheapsize: The value of the maximum heap size of the JVM being used in jdbc_fdw. Please read the notes about maxheapsize option in the installation instructions carefully before setting a value for the option. The following parameter can be set on a JDBC foreign table: query: An SQL query to define the data set on the JDBC server. table: The name of a table (quoted and qualified as required) on the foreign database table. The following parameter can be set on a user mapping for a JDBC foreign server: username: The username to use when connecting to foreign database. Default password: The password to authenticate to the foreign database with. Default: Installing ---------- **Important** Please ensure that the URL you pass in the url option when creating the server is correct and is according to the JDBC URL that is accepted by the foreign database. Any fault in the passed JDBC URL can cause hard-to-understand errors.Please recheck and validate the JDBC URL before passing it in server options. ************************************************************************************************************************************************ ** Installation instructions for source installation of PostgreSQL Documentation for installing PostgreSQL from source can be found [here](http://www.postgresql.org/docs/current/static/installation.html). The steps to follow to install JDBC_FDW on source installation of PostgreSQL are: 1) Enter the /contrib directory in PostgreSQL folder. gitc@ubuntu:~$ cd Downloads/postgresql-9.2beta2/contrib 2) Get JDBC_FDW source. gitc@ubuntu:~$ git clone git://github.com/atris/JDBC_FDW.git 3) Enter the JDBC_FDW folder. gitc@ubuntu:~$ cd JDBC_FDW 4) Execute Make Clean gitc@ubuntu:~$ Make Clean 5) Execute Make Install (You may have to change to root/installation privileges before executing Make Install) **Important** : Before running Make Install,please execute the following command: gitc@ubuntu:~$ sudo ln -s /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so Please replace the path in the command with the path to libjvm.so(it should be in Java JRE folder). For locating libjvm.so,you can use the following command: gitc@ubuntu:~$ locate libjvm.so Please give the path to libjvm.so in the 'server' folder if multiple libjvm.so files are being shown. If the above command does not work,please execute the following command gitc@ubuntu:~$ sudo ln -s /usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so /usr/lib/libjvm.so After running one of the above commands,please execute the following command: gitc@ubuntu:~$ sudo Make Install 6) Ensure Make Install executes successfully without any warning or errors. 7) Enter psql. gitc@ubuntu:~$ psql 6) Set up jdbc_fdw extension. CREATE EXTENSION jdbc_fdw; 7) Create a server that uses jdbc_fdw. **Command to set up server that uses jdbc_fdw as the foreign data wrapper:** CREATE SERVER jdbc_serv4 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS( drivername 'org.sqlite.JDBC', url 'jdbc:sqlite:/home/atri/atri1.sdb', querytimeout '15', jarfile '/home/atri/Downloads/sqlite-jdbc-3.7.2.jar', maxheapsize '600' ); ** Explanation of setting up options for the server drivername : The drivername has to be given the value of the exact class name which has to be loaded for the JDBC driver e.g. org.postgresql.Driver url : The url has to be given the value of the JDBC URL of the database from which the data has to fetched by jdbc_fdw into PostgreSQL. jarfile : The jarfile has to be given the value of the path and name of the JAR file of the JDBC driver of the database from which the data has to fetched. querytimeout : The time after which a query will be terminated automatically. This can be used for terminating hung queries. maxheapsize : The value of the option shall be set to the maximum heap size of the JVM which is being used in jdbc fdw.It can be set from 1 Mb onwards. This option is used for setting the maximum heap size of the JVM manually. **Important** : Please note that setting the maximum heap size of the JVM manually can lead to decrease in performance of jdbc_fdw.It is recommended that you double check the value you are setting in maxheapsize.It is not a compulsory option i.e. If you do net set any value for maxheapsize,the default value for the maximum heap size of the JVM being used in jdbc_fdw shall be used. Please use it only if you want to set the maximum heap size of the JVM manually. Setting it to a very low value can lead to drastic performance hit. Also,since the JVM being used in jdbc fdw is created only once for the entire psql session,therefore,the first query issued that uses jdbc fdw shall set the value of maximum heap size of the JVM(if the first query specifies a maximum heap value). 8) Create a user mapping for the server. gitc=# CREATE USER MAPPING FOR gitc SERVER jdbc_serv3 OPTIONS(username 'test',password 'test'); 9) Create a foreign table on the server. gitc=# CREATE FOREIGN TABLE test16 (a int) SERVER jdbc_serv3 OPTIONS (query 'select generate_series(1,1000000)'); 10) Query the foreign table. gitc=# SELECT * FROM test16; The output should be : Connection to PostgreSQL 9.2beta2 successful. a --------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Time: 6080.603 ms gitc=# ** Installation instructions for packaged installation of PostgreSQL on Debian versions Documentation for installing PostgreSQL from packages can be found [here](http://wiki.debian.org/PostgreSql). 1) Ensure you have the PostgreSQL dev package installed.If not,please install the latest version. gitc@ubuntu:~$ sudo apt-get install postgresql-server-dev-9.1 Please replace '9.1' with the appropriate version of your installation of PostgreSQL. 2) Get JDBC_FDW source. gitc@ubuntu:~$ git clone git://github.com/atris/JDBC_FDW.git 3) Enter the JDBC_FDW folder. gitc@ubuntu:~$ cd JDBC_FDW 4) Execute Make Clean gitc@ubuntu:~$ sudo PATH=/usr/lib/postgresql/9.1/bin/:$PATH make USE_PGXS=1 clean Assuming you have PostgreSQL installed in /usr/lib/postgresql.If not,please make appropriate changes to the $PATH value set in the command. 5)Execute Make Install (You may have to change to root/installation privileges before executing Make Install) **Important** : Before running Make Install,please execute the following command: gitc@ubuntu:~$ sudo ln -s /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so Please replace the path in the command with the path to libjvm.so (it should be in Java JRE folder). For locating libjvm.so,you can use the following command: gitc@ubuntu:~$ locate libjvm.so Please give the path to libjvm.so in the 'server' folder if multiple libjvm.so files are being shown. If the above command does not work,please execute the following command gitc@ubuntu:~$ sudo ln -s /usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so /usr/lib/libjvm.so After running one of the above commands,please execute the following command: gitc@ubuntu:~$ sudo PATH=/usr/lib/postgresql/9.1/bin/:$PATH make USE_PGXS=1 install Assuming you have PostgreSQL installed in /usr/lib/postgresql.If not,please make appropriate changes to the $PATH value set in the command. 6) Ensure Make Install executes successfully without any warning or errors. 7) Enter psql. gitc@ubuntu:~$ psql 8) Set up jdbc_fdw extension. CREATE EXTENSION jdbc_fdw; 9) Create a server that uses jdbc_fdw. **Command to set up server that uses jdbc_fdw as the foreign data wrapper:** CREATE SERVER jdbc_serv4 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS( drivername 'org.sqlite.JDBC', url 'jdbc:sqlite:/home/atri/atri1.sdb', querytimeout '15', jarfile '/home/atri/Downloads/sqlite-jdbc-3.7.2.jar', maxheapsize '600' ); ** Explanation of setting up options for the server drivername : The drivername has to be given the value of the exact class name which has to be loaded for the JDBC driver e.g. org.postgresql.Driver url : The url has to be given the value of the JDBC URL of the database from which the data has to fetched by jdbc_fdw into PostgreSQL. jarfile : The jarfile has to be given the value of the path and name of the JAR file of the JDBC driver of the database from which the data has to fetched. querytimeout : The time after which a query will be terminated automatically. This can be used for terminating hung queries. maxheapsize : The value of the option shall be set to the maximum heap size of the JVM which is being used in jdbc fdw.It can be set from 1 Mb onwards. This option is used for setting the maximum heap size of the JVM manually. **Important** : Please note that setting the maximum heap size of the JVM manually can lead to decrease in performance of jdbc_fdw.It is recommended that you double check the value you are setting in maxheapsize.It is not a compulsory option i.e. If you do net set any value for maxheapsize,the default value for the maximum heap size of the JVM being used in jdbc_fdw shall be used. Please use it only if you want to set the maximum heap size of the JVM manually. Setting it to a very low value can lead to drastic performance hit. Also,since the JVM being used in jdbc fdw is created only once for the entire psql session,therefore,the first query issued that uses jdbc fdw shall set the value of maximum heap size of the JVM(if the first query specifies a maximum heap value). 10) Create a user mapping for the server. CREATE USER MAPPING FOR postgres SERVER jdbc_serv4; 11) Create a foreign table on the server. CREATE FOREIGN TABLE test13 (a int,b int,c int) SERVER jdbc_serv4 OPTIONS (table 'stest1'); 12) Query the foreign table. gitc=# SELECT * FROM test13; The output should be : Connection to SQLite 3.7.2 successful. a | b | c ---+---+------ 4 | 5 | 6 7 | 8 | 9 1 | 2 | 1009 (3 rows) ** Installation Instruction for packaged installation of PostgreSQL on Red Hat versions Documentation for installing PostgreSQL from packages can be found [here](http://wiki.postgresql.org/wiki/Detailed_installation_guides#RedHat.2FFedora.2FCentOS). 1) Ensure you have the PostgreSQL dev package installed.If not,please install the latest version. [root@AtriServer atri]# yum install postgresql-devel 2) Get JDBC_FDW source. gitc@ubuntu:~$ git clone git://github.com/atris/JDBC_FDW.git 3) Enter the JDBC_FDW folder. gitc@ubuntu:~$ cd JDBC_FDW 4) Execute Make Clean [root@AtriServer atri]# sudo PATH=/usr/local/pgsql/bin/:$PATH make USE_PGXS=1 clean Assuming you have PostgreSQL installed in /usr/local/pgsql.If not,please make appropriate changes to the $PATH value set in the command. 5)Execute Make Install (You may have to change to root/installation privileges before executing Make Install) **Important** : Before running Make Install,please execute the following command: [root@AtriServer atri]# ln -s /usr/lib/jvm/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so Please replace the path in the command with the path to libjvm.so (it should be in Java JRE folder). For locating libjvm.so,you can use the following command: [root@AtriServer atri]# locate libjvm.so Please give the path to libjvm.so in the 'server' folder if multiple libjvm.so files are being shown. If the above command does not work,please execute the following command [root@AtriServer atri]# ln -s /usr/lib/jvm/jre/lib/i386/server/libjvm.so /usr/lib/libjvm.so After running one of the above commands,please execute the following command: [root@AtriServer atri]# sudo PATH=/usr/local/pgsql/bin/:$PATH make USE_PGXS=1 install Assuming you have PostgreSQL installed in /usr/local/pgsql.If not,please make appropriate changes to the $PATH value set in the command. 6) Ensure Make Install executes successfully without any warning or errors. 7) Enter psql. [atri@AtriServer ~]$ psql 8) Set up jdbc_fdw extension. CREATE EXTENSION jdbc_fdw; 9) Create a server that uses jdbc_fdw. **Command to set up server that uses jdbc_fdw as the foreign data wrapper:** CREATE SERVER jdbc_serv4 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS( drivername 'org.sqlite.JDBC', url 'jdbc:sqlite:/home/atri/atri1.sdb', querytimeout '15', jarfile '/home/atri/Downloads/sqlite-jdbc-3.7.2.jar', maxheapsize '600' ); ** Explanation of setting up options for the server drivername : The drivername has to be given the value of the exact class name which has to be loaded for the JDBC driver e.g. org.postgresql.Driver url : The url has to be given the value of the JDBC URL of the database from which the data has to fetched by jdbc_fdw into PostgreSQL. jarfile : The jarfile has to be given the value of the path and name of the JAR file of the JDBC driver of the database from which the data has to fetched. querytimeout : The time after which a query will be terminated automatically. This can be used for terminating hung queries. maxheapsize : The value of the option shall be set to the maximum heap size of the JVM which is being used in jdbc fdw.It can be set from 1 Mb onwards. This option is used for setting the maximum heap size of the JVM manually. **Important** : Please note that setting the maximum heap size of the JVM manually can lead to decrease in performance of jdbc_fdw.It is recommended that you double check the value you are setting in maxheapsize.It is not a compulsory option i.e. If you do net set any value for maxheapsize,the default value for the maximum heap size of the JVM being used in jdbc_fdw shall be used. Please use it only if you want to set the maximum heap size of the JVM manually. Setting it to a very low value can lead to drastic performance hit. Also,since the JVM being used in jdbc fdw is created only once for the entire psql session,therefore,the first query issued that uses jdbc fdw shall set the value of maximum heap size of the JVM(if the first query specifies a maximum heap value). 10) Create a user mapping for the server. CREATE USER MAPPING FOR postgres SERVER jdbc_serv4; 11) Create a foreign table on the server. CREATE FOREIGN TABLE test13 (a int,b int,c int) SERVER jdbc_serv4 OPTIONS (table 'stest1'); 12) Query the foreign table. atri=# SELECT * FROM test13; The output should be : Connection to SQLite 3.7.2 successful. a | b | c ---+---+------ 4 | 5 | 6 7 | 8 | 9 1 | 2 | 1009 (3 rows) Example ------- -- Install the extension CREATE EXTENSION jdbc_fdw; -- Create the foreign server, a pointer to the JDBC server. CREATE SERVER jdbc_serv3 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(drivername 'org.postgresql.Driver',url 'jdbc:postgresql:gitc'); -- Create one or more foreign tables on the JDBC server. CREATE FOREIGN TABLE test12(a text,b text,c text) SERVER jdbc_serv3 OPTIONS(query 'SELECT * FROM test11'); -- Create a user mapping to tell the FDW the username/password to -- use to connect to foreign database. This could be done on a per- -- role basis. CREATE USER MAPPING FOR gitc SERVER jdbc_serv3 OPTIONS(username 'gitc',password ''); Features -------- 1)jdbc_fdw can connect and fetch data to PostgreSQL from any data source that supports JDBC. 2)Basic query cancellation is supported. -- Atri Sharma atri.jiit@gmail.com