Oracle to PostgreSQL datasync in incremental mode issue

[sthirumani]

Hi, I am trying to migrate a table from oracle to Postgres in incremental mode but it keeps erroring. I am missing something. Can you please share a sample example on how this can be achieved.

I have attached the error I am getting here.

[osalvador]

Hi @sthirumani

As explained in the docs, in the incremental mode it's required that the sink table has at least one primary key, since a MERGE is performed between the staging table and the sink table. I think this will be the problem.

Anyway, the error thrown by ReplicaDB should be more explicit.

I am working on integration tests where a replica file is written for each type of integration and will serve as an example for documentation. You can also use the Configuration Wizard if you have any doubt in the configuration.

PS: In next issues don't attach an image, copy and paste the text (use the <> insert code button)

[sthirumani]

The sink table has a primary key similar to the one in the source table on oracle.

I am attaching the table schema used also for your reference here -

Please note - I am unable to copy paste the actual contents due to some restrictions in my organisation. I am also attaching the Conf generated using the configuration wizard here -

[osalvador]

Hi @sthirumani

Execute the replica in verbose mode -v. I just executed it myself and everything is fine.

 osalvador@GalaxyOSM  ~/Downloads/ReplicaDB-0.8.7  ./bin/replicadb -v --mode=incremental -j=1 \
--source-connect=jdbc:oracle:thin:@127.0.0.1:1521:XE \
--source-user=${REPLICADB_ORACLE_USER} \
--source-password=${REPLICADB_ORACLE_PASSWROD} \
--source-table=HR.JOBS \
--sink-connect=jdbc:postgresql://127.0.0.1/replicadb \
--sink-user=${REPLICADB_USER} \
--sink-password=${REPLICADB_PASSWROD} \
--sink-table=jobs
2020-10-06 17:49:12,292 INFO  ReplicaDB:42 Running ReplicaDB version: 0.8.7
2020-10-06 17:49:12,295 INFO  ReplicaDB:46 Setting verbose mode
2020-10-06 17:49:12,295 DEBUG ReplicaDB:47 ToolOptions{
	sourceConnect='jdbc:oracle:thin:@127.0.0.1:1521:XE',
	sourceUser='system',
	sourcePassword='****',
	sourceTable='HR.JOBS',
	sourceColumns='null',
	sourceWhere='null',
	sourceQuery='null',
	sinkConnect='jdbc:postgresql://127.0.0.1/replicadb',
	sinkUser='replicadb',
	sinkPassword='****',
	sinkTable='jobs',
	sinkStagingTable='null',
	sinkStagingSchema='null',
	sinkStagingTableAlias='null',
	sinkColumns='null',
	sinkDisableEscape=false,
	sinkDisableIndex=false,
	sinkDisableTruncate=false,
	sinkAnalyze=false,
	jobs=1,
	bandwidthThrottling=0,
	quotedIdentifiers=false,
	fetchSize=5000,
	help=false,
	version=false,
	verbose=true,
	optionsFile='null',
	mode='incremental',
	sourceConnectionParams=null,
	sinkConnectionParams=null}
2020-10-06 17:49:12,304 DEBUG ManagerFactory:41 Trying with scheme: jdbc:oracle:thin:@127.0.0.1:1521
2020-10-06 17:49:12,305 DEBUG ManagerFactory:41 Trying with scheme: jdbc:postgresql:
2020-10-06 17:49:12,308 WARN  SqlManager:495 No staging schema is defined, setting it as PUBLIC
2020-10-06 17:49:12,416 DEBUG SqlManager:269 No connection parameters specified. Using regular API for making connection.
2020-10-06 17:49:12,540 INFO  PostgresqlManager:217 Creating staging table with this command: CREATE UNLOGGED TABLE IF NOT EXISTS public.jobsrepdb7810 ( LIKE jobs INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) WITH (autovacuum_enabled=false)
2020-10-06 17:49:12,552 INFO  SqlManager:386 Truncating sink table with this command: TRUNCATE TABLE public.jobsrepdb7810
2020-10-06 17:49:12,563 INFO  ReplicaTask:36 Starting TaskId-0
2020-10-06 17:49:12,563 DEBUG ManagerFactory:41 Trying with scheme: jdbc:oracle:thin:@127.0.0.1:1521
2020-10-06 17:49:12,564 DEBUG ManagerFactory:41 Trying with scheme: jdbc:postgresql:
2020-10-06 17:49:12,564 DEBUG SqlManager:212 No connection parameters specified. Using regular API for making connection.
2020-10-06 17:49:13,381 DEBUG SqlManager:269 No connection parameters specified. Using regular API for making connection.
2020-10-06 17:49:13,566 DEBUG SqlManager:131 TaskId-0: Using fetchSize for next query: 5000
2020-10-06 17:49:13,569 INFO  SqlManager:141 TaskId-0: Executing SQL statement: SELECT /*+ NO_INDEX(HR.JOBS)*/ * FROM HR.JOBS where 0 = ?
2020-10-06 17:49:13,569 INFO  SqlManager:148 TaskId-0: With args: 0,
2020-10-06 17:49:13,683 WARN  ConnManager:187 Options source-columns and sink-columns are null, getting from Source ResultSetMetaData: JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY
2020-10-06 17:49:13,684 INFO  PostgresqlManager:158 Copying data with this command: COPY public.jobsrepdb7810 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) FROM STDIN WITH DELIMITER e'\x1f'  NULL '' ENCODING 'UTF-8'
2020-10-06 17:49:13,744 DEBUG SqlManager:331 Getting PKs for schema: null and table: jobs. Found.
2020-10-06 17:49:13,744 INFO  PostgresqlManager:266 Merging staging table and sink table with this command: INSERT INTO jobs (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY )  SELECT JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY FROM jobsrepdb7810 ON CONFLICT  (job_id ) DO UPDATE SET  JOB_ID = excluded.JOB_ID , JOB_TITLE = excluded.JOB_TITLE , MIN_SALARY = excluded.MIN_SALARY , MAX_SALARY = excluded.MAX_SALARY
2020-10-06 17:49:13,750 INFO  SqlManager:475 Dropping staging table with this command: DROP TABLE public.jobsrepdb7810
2020-10-06 17:49:13,755 INFO  ReplicaDB:124 Total process time: 1478ms

[sthirumani]

Thanks for your help with this. With the sample shared I am able to replicate the db in incremental mode now. The miss was not adding the schema name for the tables, this worked fine for the other two modes though. But this solved the problem. Thanks again.

[osalvador]

Hi @sthirumani

I have created a new release the v0.8.8 that handles the null pointer exception. It returns an understandable error to the user: "could not find the PKs...".

It's more secure that the name of the sink table is always preceded by its schema, but perhaps in your case and with v0.8.8 you do not need it.

Thanks for reporting.