Posts Tagged postgres
copy a table across databases via dblink
Posted by jens in Uncategorized on February 6, 2009
Recently I ran into the situation that I needed to copy a large subset of data from one database to another. Normally I would say, make a dump and then re-import the data into the new schema. But this solution has some serious drawbacks. First you have to copy the complete database. Second you have to maintain the structure of the data. A third problem could be that you have to copy the complete dump to the target location (in case it is not the same machine and your database is a bit larger e.g. some gigabyte). Having these drawbacks in mind I started searching for an alternative solution for my problem.
Here some facts to render my situation more precisely.
- database containing multiple tables
- only one has relevant data
- only the subset of 1 month is needed
ddl for the original table:
CREATE TABLE realtime ( name varchar(10), date timestamp, bid numeric, ask numeric )
ddl for the target table:
CREATE TABLE realtime ( symbol varchar(10), date timestamp, price numeric, "day" char(5), max numeric, avg numeric, atr numeric )
here the mapping:
realtime.name -> realtime.symbol
realtime.date -> realtime.date
(realtime.bid + realtime.ask) /2 -> realtime.price
-> other columns filled by trigger
To get this task done I decided to use a dblink between those two database instances (how-to here).
So here is the select I used to transfer the month January to the new db:
insert into realtime (symbol,date,price)
select * from dblink('dbname=stocks',
'select name,date,(bid+ask)/2 as price
from realtime
where date > to_date(''20081231'',''yyyyMMDD'') and date < to_date(''20090201'',''yyyyMMDD'')')
as t1 (name character varying,date timestamp,price numeric);
As you can see this approach is pretty straight forward. You basically write an insert statement for the new table and use a dblink as source. In the dblink definition you can apply any given sql criteria.
One real drawback has this solution, because of the mode of operation of the dblink approach it is pretty slow. Here is what the postgres documentation has to say about this:
dblink fetches the entire remote query result before returning any of it to the local system. If the query is expected to return a large number of rows, it’s better to open it as a cursor with dblink_open and then fetch a manageable number of rows at a time.For me the performance was ok because I just copied several hundred megabytes.
Tibco EMS with database backend (postgresql)
I recently tried to build a JMS Server with database backend. The chosen product was the TIBCO EMS Server. The Server brings its own database support over hibernate.
Unfortunately TIBCO supports only Oracle,Mysql and DB2 by default. Lucky me, I needed an installation for Postgres but this shouldn’t be a big deal, because hibernate supports postgres as well. You just have to modify the hibernate config.
First you should install the EMS server and hibernate (version provided by Tibco). After that let’s start configuring.
To have everthing in the database you need 3 databases (3 separate EMS stores). For me I used the following 3 stores:
- emsmeta -> for metadata content
- emsnf -> for nonfailsafe data
- emsf -> for failsafe data
I used the same account for all 3 databases (just easier for testing purposes).
Setup the stores in the stores.conf in the EMS folder:
[$sys.meta] type=dbstore dbstore_driver_url=jdbc:postgresql://localhost/emsmeta dbstore_driver_username=ems dbstore_driver_password=ems [$sys.nonfailsafe] type=dbstore dbstore_driver_url=jdbc:postgresql://localhost/emsnf dbstore_driver_username=ems dbstore_driver_password=ems [$sys.failsafe] type=dbstore dbstore_driver_url=jdbc:postgresql://localhost/emsf dbstore_driver_username=ems dbstore_driver_password=ems
After that you have to change you tibemsd.conf file.The following line is a sample what you should add. Additionally you should add tha path to your jdbc driver (in this sample the last parameter – change it to your config). You also have to add the path to your JVM (here it is the debian lenny default).
dbstore_classpath = ../../../components/eclipse/plugins/com.tibco.tpcl.org.hibernate_3.2.5.001/hibernate3.jar:../../../components/eclipse/plugins/com.tibco.tpcl.org.com.mchange.c3p0_0.9.1.001/c3p0-0.9.1.jar:antlr-2.7.6.jar:asm-attrs.jar:asm.jar:cglib-2.1.3.jar:commons-collections-2.1.1.jar:commons-logging-1.0.4.jar:dom4j-1.6.1.jar:ehcache-1.2.3.jar:jta.jar:/usr/local/bin/oracledrivers/lib/ojdbc5.jar:/home/jens/tibco/tpcl/5.6/jdbc/postgresql-8.3-603.jdbc3.jar ## db section #dbstore_driver_name = oracle.jdbc.driver.OracleDriver #dbstore_driver_dialect = org.hibernate.dialect.Oracle10gDialect dbstore_driver_name = org.postgresql.Driver dbstore_driver_dialect = org.hibernate.dialect.PostgreSQLDialect jre_library = /usr/lib/jvm/java-6-sun/jre/lib/i386/server/libjvm.so
As you can see changing databases is easy. You just have to change the driver_name and driver_dialect of hibernate.
After that you have the basic configuration ready. Next thing to do is to initialize the dabases. For that task Tibco provides a tool which generates the proper sql-statements.
You can run the following command in your shell and get the sql as output.
java -jar tibemsd_util.jar -tibemsdconf tibemsd.conf -createall
For some reason the create sql-statements had no ‘;’ at the end of every command. So you can’t just pipe the output to psql. You have to do it old school.
After that you can start the EMS Server and it stores all data to the selected database.