Thread: regarding schema only migration from sqlserver to postgres with runmtk.sh

On 06/15/2016 08:27 AM, Durgamahesh Manne wrote:
>
> ---------- Forwarded message ----------
> From: *David G. Johnston* <david.g.johnston@gmail.com
> <mailto:david.g.johnston@gmail.com>>
> Date: Wed, Jun 15, 2016 at 8:40 PM
> Subject: Re: [GENERAL] regarding schema only migration from sqlserver to
> postgres with runmtk.sh
> To: Durgamahesh Manne <maheshpostgres9@gmail.com
> <mailto:maheshpostgres9@gmail.com>>
>
>
> On Wed, Jun 15, 2016 at 11:00 AM, Durgamahesh Manne
> <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>>wrote:
>
>     as per above discussion. i already enabled tcp/ip based access on
>     which the server to listen connections from client applications as
>     well as already set password for required user
>
>
>     may i know the recommended approach for migrate the tables from sql
>     server to postgesql
>
>
>     please let me know
>
>
>
> ​I've helped you all that I can, and I do not respond well to personal
> emails asking for help.  Direct all correspondence to the list: it is ok
> to CC: people who have already replied.  And, as I said, this is not the
> correct forum for your question.  You are using a third-party tool so
> they are the ones you should be asking.  In this case they do monitor
> these lists and may chime in but as a matter of proper etiquette this
> discussion is off-topic for this list.  Specifically it is a request for
> help on how to use a specific non-community-PostgreSQL product.
>
> I suspect that "dnsname" is not actually the host name for your
> server...if the URL is invalid and you've munged it for email it would
> be impossible for us to point out to you how/if that is so.
>
> David J.
>
>
>
> sir as per above discussion
>
> conn =jdbc:postgresql://archive.tf.com <http://archive.tf.com>:5432/
> <http://archive.trustfort.com:5432/edb>edb
>
> i earlier mentioned the host name of the server (archive.tf.com
> <http://archive.tf.com>)

Are you sure?

When I enter archive.tf.com or tf.com in a browser I get:

http://www.cyberfinder.com/

I would:

1) Verify the host name.

2) Then try connecting via a non-JDBC method, say using psql. If that
works go to 3),  otherwise come back here with information on what you
did and how it failed.

3) Then try the migration tool. If that fails contact EDB.

>
> may i know  recommended approach to migrate the tables from sql server
> to postgresql instead of using 3rd party tools


>
> thank you
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/15/2016 01:30 PM, David G. Johnston wrote:
> On Wed, Jun 15, 2016 at 11:27 AM, Durgamahesh Manne
> <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>>wrote:
>
>     may i know  recommended approach to migrate the tables from sql
>     server to postgresql instead of using 3rd party tools
>
>
> ​Answering this specifically.
>
> There are none.  The community PostgreSQL project maintains no tools
> that speak to SQL Server.

That is true, but the external PostgreSQL community does:

https://github.com/tds-fdw/tds_fdw

>
> One possible option to use the PostgreSQL's FDW capabilities with a
> suitable third-party extension that does talk to SQL Server.

If you have access to MS Access, you can create linked tables between
MSSQL and PostgreSQL. You can then create queries to move the data
between the tables. Libreoffice may have similar functionality as well
(I don't know).


Sincerely,

JD


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

One possible option to use the PostgreSQL's FDW capabilities with a suitable third-party extension that does talk to SQL Server.

The other is to dump plain SQL, hopefully standard-conforming, INSERT statements from the SQL Server and load them into PostgreSQL via psql.

last time I had to convert some MS SQL to another database (it was MySQL but thats immaterial), I wrote a perl script to do it.    there were only 6 or 8 tables, so I manually created the schema, then my perl script stepped through each table, for each row, fetching from the source database and inserting into the destination.

-- 
john r pierce, recycling bits in santa cruz

On 06/15/2016 08:50 PM, Durgamahesh Manne wrote:
> sir
>
> as per above discussion .the URL which i mentioned in toolkit properties
> was 100% correct .i have checked db connectivity from another cluster
> with psql .connection esatblished to below mentioned database
> conn =jdbc:postgresql://archive.trustfort.com:5432
> <http://archive.trustfort.com:5432>/edb
>
>
>
> thank you for the valuable information

So does this mean the migration is working now?




--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/16/2016 07:19 AM, Durgamahesh Manne wrote:
>
> sir
>
> PostgreSQL always no1 in world
> as per above discussion
>
> migration not working from sql to postgreSQL  with runmtk.sh
>
>
> Connecting with source SQL Server database server...
> Connected to Microsoft SQL Server, version '10.50.1600'
> Connecting with target EnterpriseDB database server...
> MTK-10045: The URL specified for the "target" database is invalid.
> Check the connectivity credentials.
> Stack Trace:
> com.edb.MTKException: MTK-10045: The URL specified for the "target"
> database is invalid.
> Check the connectivity credentials.
>
>
>
> same error repeated even there mentioned correct credentials as here i
> checked multiple times

Are you sure you are looking at the right file?

Also:

Have you looked here?:

https://www.enterprisedb.com/docs/en/9.5/migrate/Postgres_Plus_Migration_Guide.1.16.html#

In particular do you have a user and associated password set for the
Postgres database?

>
> thank you
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Please don't top-post.

On Thursday, June 16, 2016 8:30:53 PM EDT Durgamahesh Manne wrote:
> hi
> sir
> as per above discussion same error repeated even mentioned ip address of
> hostname
>
> ./runMTK.sh -sourcedbtype sqlserver -targetSchema public -schemaOnly
> -allTables dbo
>
>
> TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
> TARGET_DB_USER=postgres
> TARGET_DB_PASSWORD=*****
>
>
>
> Connecting with source SQL Server database server...
> Connected to Microsoft SQL Server, version '10.50.1600'
> Connecting with target EnterpriseDB database server...
> MTK-10045: The URL specified for the "target" database is invalid.
> Check the connectivity credentials.
> Stack Trace:
> com.edb.MTKException: MTK-10045: The URL specified for the "target"
> database is invalid.
> Check the connectivity credentials.
>

So have you tried the connectivity details? What does

$ psql --host 192.168.168.201 --user postgres -d raghu

give you?

I have a sneaking suspicion you don't have TCP/IP access to the database
configured in pg_hba.conf.

On 2016-06-16 11:13 AM, Durgamahesh Manne wrote:
> yes sir
>
> as per above discussion..i already looked pdf postgres plus guide you
> mentioned
>
> as well as i already set password for postgres user associated with
> postgres db
>
> On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
> <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>> wrote:
>
>     hi
>     sir
>     as per above discussion same error repeated even mentioned ip
>     address of hostname
>
>     ./runMTK.sh -sourcedbtype sqlserver -targetSchema public -schemaOnly
>     -allTables dbo
>
>
>     TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
>     <http://192.168.168.201:5432/raghu>
>     TARGET_DB_USER=postgres
>     TARGET_DB_PASSWORD=*****
>
>
>
>     Connecting with source SQL Server database server...
>     Connected to Microsoft SQL Server, version '10.50.1600'
>     Connecting with target EnterpriseDB database server...
>     MTK-10045: The URL specified for the "target" database is invalid.
>     Check the connectivity credentials.
>     Stack Trace:
>     com.edb.MTKException: MTK-10045: The URL specified for the "target"
>     database is invalid.
>     Check the connectivity credentials.
>
>     On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
>     <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>         On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne
>         <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>>wrote:
>
>
>             sir
>
>             PostgreSQL always no1 in world
>             as per above discussion
>
>             migration not working from sql to postgreSQL  with runmtk.sh
>
>
>             Connecting with source SQL Server database server...
>             Connected to Microsoft SQL Server, version '10.50.1600'
>             Connecting with target EnterpriseDB database server...
>             MTK-10045: The URL specified for the "target" database is
>             invalid.
>             Check the connectivity credentials.
>             Stack Trace:
>             com.edb.MTKException: MTK-10045: The URL specified for the
>             "target" database is invalid.
>             Check the connectivity credentials.
>
>
>
>             same error repeated even there mentioned correct credentials
>             as here i checked multiple times
>
>
>         ​Maybe you should try using an IP address instead of a host name.
>
>         David J.​
>
>
>
>

I've never used this tool so like everyone else I am guessing here but
having quickly looked at the documentation have you tried specifying the
'-targetdbtype postgres' in addition to the '-sourcedbtype sqlserver'
that you have already specified?

Neil A

On 06/16/2016 08:13 AM, Durgamahesh Manne wrote:
> yes sir
>
> as per above discussion..i already looked pdf postgres plus guide you
> mentioned
>
> as well as i already set password for postgres user associated with
> postgres db

Is the Postgres server you are trying to connect to the Postgres
Advanced Server from EDB?

If so then you need to look here:

https://www.enterprisedb.com/docs/en/9.5/migrate/Postgres_Plus_Migration_Guide.1.15.html#

Where the important part is:

TARGET_DB_URL=jdbc:edb://192.168.168.201:5432/raghu

instead of:

TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
>
> On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
> <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>> wrote:
>
>     hi
>     sir
>     as per above discussion same error repeated even mentioned ip
>     address of hostname
>
>     ./runMTK.sh -sourcedbtype sqlserver -targetSchema public -schemaOnly
>     -allTables dbo
>
>
>     TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
>     <http://192.168.168.201:5432/raghu>
>     TARGET_DB_USER=postgres
>     TARGET_DB_PASSWORD=*****
>
>
>
>     Connecting with source SQL Server database server...
>     Connected to Microsoft SQL Server, version '10.50.1600'
>     Connecting with target EnterpriseDB database server...
>     MTK-10045: The URL specified for the "target" database is invalid.
>     Check the connectivity credentials.
>     Stack Trace:
>     com.edb.MTKException: MTK-10045: The URL specified for the "target"
>     database is invalid.
>     Check the connectivity credentials.
>
>     On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
>     <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>         On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne
>         <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>>wrote:
>
>
>             sir
>
>             PostgreSQL always no1 in world
>             as per above discussion
>
>             migration not working from sql to postgreSQL  with runmtk.sh
>
>
>             Connecting with source SQL Server database server...
>             Connected to Microsoft SQL Server, version '10.50.1600'
>             Connecting with target EnterpriseDB database server...
>             MTK-10045: The URL specified for the "target" database is
>             invalid.
>             Check the connectivity credentials.
>             Stack Trace:
>             com.edb.MTKException: MTK-10045: The URL specified for the
>             "target" database is invalid.
>             Check the connectivity credentials.
>
>
>
>             same error repeated even there mentioned correct credentials
>             as here i checked multiple times
>
>
>         ​Maybe you should try using an IP address instead of a host name.
>
>         David J.​
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

****************************************
P L E A S E   D O N ' T   T O P  P O S T
****************************************


On Thursday, June 16, 2016 9:00:48 PM EDT Durgamahesh Manne wrote:
> as per above conversation. i checked connectivity details as it is
> connected to database  (even hostname i mentioned connected to database)
>   psql --host 192.168.168.201 --user postgres -d raghu
> Password for user postgres:
> psql.bin (9.4.8, server 9.4.4)
> Type "help" for help.
>
> No entry for terminal type "xterm";
> using dumb terminal settings.
> raghu=#
>
> On Thu, Jun 16, 2016 at 8:51 PM, Jan de Visser <jan@de-visser.net> wrote:
> > Please don't top-post.
> >
> > On Thursday, June 16, 2016 8:30:53 PM EDT Durgamahesh Manne wrote:
> > > hi
> > > sir
> > > as per above discussion same error repeated even mentioned ip address of
> > > hostname
> > >
> > > ./runMTK.sh -sourcedbtype sqlserver -targetSchema public -schemaOnly
> > > -allTables dbo
> > >
> > >
> > > TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
> > > TARGET_DB_USER=postgres
> > > TARGET_DB_PASSWORD=*****
> > >
> > >

- Just to make sure: TARGET_DB_PASSWORD is not actually set to '*****', right?
- Are you sure that you need 'jdbc:' in the URL?


Also, your tone and communication style are not really helping your cause.
Your quoting style is chaotic and you're very adversarial. I understand you're
probably under pressure to get this fixed, but yelling at the volunteers that
help you here is not helping.

On 2016-06-16 11:42 AM, Durgamahesh Manne wrote:
> i got another error even i specified -targetdbtype
> like
> ./runMTK.sh -sourcedbtype sqlserver -targetdbtype postgresql
> -targetSchema public -schemaOnly -allTables dbo
> Running EnterpriseDB Migration Toolkit (Build 49.0.4) ...
> Source database connectivity info...
> conn =jdbc:jtds:sqlserver://fxserver.trustfort.com:49883/DataFeedHandler
> <http://fxserver.trustfort.com:49883/DataFeedHandler>
> user =trustfort
> password=******
> Target database connectivity info...
> conn =jdbc:postgresql://192.168.168.201:5432/raghu
> <http://192.168.168.201:5432/raghu>
> user =postgres
> password=******
> Connecting with source SQL Server database server...
> Connected to Microsoft SQL Server, version '10.50.1600'
> Connecting with target Postgres database server...
> Exception in thread "main" java.lang.NoClassDefFoundError:
> org/postgresql/Driver
>     at
> com.edb.dbhandler.postgresql.PGConnection.<init>(PGConnection.java:32)
>     at com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:228)
>     at
> com.edb.MigrationToolkit.createNewTargetConnection(MigrationToolkit.java:5987)
>     at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:3376)
>     at com.edb.MigrationToolkit.main(MigrationToolkit.java:1700)
> Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver
>
>
>
>
>
> On Thu, Jun 16, 2016 at 8:59 PM, Neil Anderson
> <neil.t.anderson@gmail.com <mailto:neil.t.anderson@gmail.com>> wrote:
>
>     On 2016-06-16 11:13 AM, Durgamahesh Manne wrote:
>
>         yes sir
>
>         as per above discussion..i already looked pdf postgres plus
>         guide you
>         mentioned
>
>         as well as i already set password for postgres user associated with
>         postgres db
>
>         On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
>         <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>
>         <mailto:maheshpostgres9@gmail.com
>         <mailto:maheshpostgres9@gmail.com>>> wrote:
>
>             hi
>             sir
>             as per above discussion same error repeated even mentioned ip
>             address of hostname
>
>             ./runMTK.sh -sourcedbtype sqlserver -targetSchema public
>         -schemaOnly
>             -allTables dbo
>
>
>             TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
>         <http://192.168.168.201:5432/raghu>
>             <http://192.168.168.201:5432/raghu>
>             TARGET_DB_USER=postgres
>             TARGET_DB_PASSWORD=*****
>
>
>
>             Connecting with source SQL Server database server...
>             Connected to Microsoft SQL Server, version '10.50.1600'
>             Connecting with target EnterpriseDB database server...
>             MTK-10045: The URL specified for the "target" database is
>         invalid.
>             Check the connectivity credentials.
>             Stack Trace:
>             com.edb.MTKException: MTK-10045: The URL specified for the
>         "target"
>             database is invalid.
>             Check the connectivity credentials.
>
>             On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
>             <david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>
>         <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>>> wrote:
>
>                 On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne
>                 <maheshpostgres9@gmail.com
>         <mailto:maheshpostgres9@gmail.com>
>         <mailto:maheshpostgres9@gmail.com
>         <mailto:maheshpostgres9@gmail.com>>>wrote:
>
>
>                     sir
>
>                     PostgreSQL always no1 in world
>                     as per above discussion
>
>                     migration not working from sql to postgreSQL  with
>         runmtk.sh
>
>
>                     Connecting with source SQL Server database server...
>                     Connected to Microsoft SQL Server, version '10.50.1600'
>                     Connecting with target EnterpriseDB database server...
>                     MTK-10045: The URL specified for the "target"
>         database is
>                     invalid.
>                     Check the connectivity credentials.
>                     Stack Trace:
>                     com.edb.MTKException: MTK-10045: The URL specified
>         for the
>                     "target" database is invalid.
>                     Check the connectivity credentials.
>
>
>
>                     same error repeated even there mentioned correct
>         credentials
>                     as here i checked multiple times
>
>
>                 ​Maybe you should try using an IP address instead of a
>         host name.
>
>                 David J.​
>
>
>
>
>
>     I've never used this tool so like everyone else I am guessing here
>     but having quickly looked at the documentation have you tried
>     specifying the '-targetdbtype postgres' in addition to the
>     '-sourcedbtype sqlserver' that you have already specified?
>
>     Neil A
>
>
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>

That appears to be an issue finding the right Java library to connect to
Postgres. The documentation here outlines where to download and install
the JDBC drivers
https://www.enterprisedb.com/docs/en/9.5/migrate/Postgres_Plus_Migration_Guide.1.13.html#

thank you for the information related to postgres

On 06/16/2016 11:28 AM, Durgamahesh Manne wrote:
> i can able to resolve the issue on migration from sql to postgres with
> runmtk.sh  without taking any support from edb
>
> even i frustated with runmtk.sh as i wanted to use postgresql life long
> in my company

For completeness and to help anyone else who comes across this thread
when having the same problem, what did you do to get it to work?

>
> thank you for information
>
>
>
>
> On Thu, Jun 16, 2016 at 11:43 PM, Durgamahesh Manne
> <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>> wrote:
>
>     thank you for the information related to postgres
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com