0
Answered

How to import a DB schema from Redshift?

Yuval Shefler 9 years ago updated by shradha 6 years ago 2
I'd like to import our existing DB schema from Amazon Redshift. 
Can you advise how to go about it?

Thanks,
Yuval
GOOD, I'M SATISFIED
Satisfaction mark by Yuval Shefler 9 years ago
Answered
Amazon Redshift is based on PostgreSQL and as its documentation states you can use PostgreSQL JDBC drivers to connect to database. This way you can use our reverse engineering tool that can connect to your database and extract schema information to Vertabelo XML file. Then you should create empty database model in Vertabelo (choose PostgreSQL engine) and import XML file. Here you find detailed instruction how to use our reverse engineering tool:
https://www.vertabelo.com/blog/documentation/reverse-engineering

The second option you can try is dumping your database schema to sql file and import it directly into Vertabelo (we have import from SQL DDL feature). But I'm not sure if pg_dump works well with Amazon Redshift.

We don't test Vertabelo with Amazon Redshift but it should work.

I have been trying to connect to redshift using the rever engineering application (windows). the command is below



java -cp .;reverse-engineering-1_6_12.jar;postgresql-8.0-325.jdbc2.jar com.vertabelo.reverse.Main -url jdbc:postgresql://ge-dr-prd.cdm77nms3a9p.eu-west-1.redshift.amazonaws.com:5439/prod -user shradha_samantray -password VqpWK4hwaZ -o VinstallOpt


The aplication starts with some warnings and then end by displaying an error. Can someone help to resolve this?



C:\Users\samantrays\Downloads>java -cp .;reverse-engineering-1_6_12.jar;postgresql-8.0-325.jdbc2.jar com.vertabelo.reverse.Main -url jdbc:postgresql://ge-dr-prd.cdm77nms3a9p.eu-west-1.redshift.amazonaws.com:5439/prod -user shradha_samantray -password VqpWK4hwaZ -o VinstallOpt
No JDBC driver given. Using JDBC driver: org.postgresql.Driver
Reverse engineering PostgreSQL database
Connecting to the database...
Database: PostgreSQL 8.0.2
Extracting table information
OoOo.oooOOoo.oooooooooooooooooOoOOo..ooooo.oooooooooo.ooooooOOOOOooo.o..ooO
oooOOOOOOooO...oooooOOOooOo....OOOOoo...o..OO.o..o.OooOooooooooooOOoooo....
oo.o.ooooOoooooooooooOoO.ooooooooooOooooooOOO.ooooOOo.oooo..oOoOoooOOoo
Extracting view information

Warning: SQL definition for view 'datarepo.cntct_addr' not found.
Probably the user you're running the application as (shradha_samantray) has no permission to read the view definition.


Try running the application as a different user.


Warning: SQL definition for view 'staging.seq_1_to_1000' not found.
Probably the user you're running the application as (shradha_samantray) has no permission to read the view definition.
Try running the application as a different user.
.
Extracting reference information

Extracting sequence information
Exception in thread "main" java.lang.AbstractMethodError: Method org/postgresql/jdbc2/Jdbc2DatabaseMetaData.getDatabaseMajorVersion()I is abstract
        at org.postgresql.jdbc2.Jdbc2DatabaseMetaData.getDatabaseMajorVersion(Jdbc2DatabaseMetaData.java)
        at com.vertabelo.reverse.oa.a(:290)
        at com.vertabelo.reverse.nt.b(:193)
        at com.vertabelo.reverse.Main.main(:491)