Multiple Tables - staging table error

[AkshayMAnvekar]

Hi,

I'm trying to sync multiple tables in incremental mode. It returns a error in creating and dropping the staging table. I'm working on a test setup where I have 2 PostgreSQL databases running in docker with replicaDB. There are no issues when it runs for single table. The replication fails only for multiple tables.

The configuration file

######################## ReplicadB General Options ########################
mode=incremental
jobs=1
fetch.size=100
verbose=true
############################# Soruce Options ##############################
source.connect=jdbc:postgresql://postgres1:5433/testing
source.user=pguser
source.password=pgpass
source.table=public.orders, public.categories

############################# Sink Options ################################
sink.connect=jdbc:postgresql://postgres2:5434/testing
sink.user=pguser
sink.password=pgpass
sink.table=public.orders, public.categories

Error Log

replicadb_1  | 2020-11-29 19:49:39,280 INFO  ReplicaDB:42 Running ReplicaDB version: 0.8.9
replicadb_1  | 2020-11-29 19:49:39,282 INFO  ReplicaDB:46 Setting verbose mode
replicadb_1  | 2020-11-29 19:49:39,282 DEBUG ReplicaDB:47 ToolOptions{
replicadb_1  |  sourceConnect='jdbc:postgresql://postgres1:5433/testing',
replicadb_1  |  sourceUser='pguser',
replicadb_1  |  sourcePassword='****',
replicadb_1  |  sourceTable='public.orders,public.categories',
replicadb_1  |  sourceColumns='null',
replicadb_1  |  sourceWhere='null',
replicadb_1  |  sourceQuery='null',
replicadb_1  |  sinkConnect='jdbc:postgresql://postgres2:5434/testing',
replicadb_1  |  sinkUser='pguser',
replicadb_1  |  sinkPassword='****',
replicadb_1  |  sinkTable='public.orders,public.categories',
replicadb_1  |  sinkStagingTable='null',
replicadb_1  |  sinkStagingSchema='null',
replicadb_1  |  sinkStagingTableAlias='null',
replicadb_1  |  sinkColumns='null',
replicadb_1  |  sinkDisableEscape=false,
replicadb_1  |  sinkDisableIndex=false,
replicadb_1  |  sinkDisableTruncate=false,
replicadb_1  |  sinkAnalyze=false,
replicadb_1  |  jobs=1,
replicadb_1  |  bandwidthThrottling=0,
replicadb_1  |  quotedIdentifiers=false,
replicadb_1  |  fetchSize=100,
replicadb_1  |  help=false,
replicadb_1  |  version=false,
replicadb_1  |  verbose=true,
replicadb_1  |  optionsFile='/home/replicadb/conf/replicadb.conf',
replicadb_1  |  mode='incremental',
replicadb_1  |  sourceConnectionParams={},
replicadb_1  |  sinkConnectionParams={}}
replicadb_1  | 2020-11-29 19:49:39,288 DEBUG ManagerFactory:41 Trying with scheme: jdbc:postgresql:
replicadb_1  | 2020-11-29 19:49:39,291 DEBUG ManagerFactory:41 Trying with scheme: jdbc:postgresql:
replicadb_1  | 2020-11-29 19:49:39,291 WARN  SqlManager:495 No staging schema is defined, setting it as PUBLIC     
replicadb_1  | 2020-11-29 19:49:39,356 DEBUG SqlManager:269 No connection parameters specified. Using regular API for making connection.
replicadb_1  | 2020-11-29 19:49:39,476 INFO  PostgresqlManager:217 Creating staging table with this command: CREATE UNLOGGED TABLE IF NOT EXISTS public.categoriesrepdb3810 ( LIKE public.orders,public.categories INCLUDING DEFAULTS 
INCLUDING CONSTRAINTS ) WITH (autovacuum_enabled=false)
postgres2_1  | 2020-11-29 19:49:39.478 UTC [224] ERROR:  syntax error at or near "." at character 91
postgres2_1  | 2020-11-29 19:49:39.478 UTC [224] STATEMENT:  CREATE UNLOGGED TABLE IF NOT EXISTS public.categoriesrepdb3810 ( LIKE public.orders,public.categories INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) WITH (autovacuum_enabled=false)
replicadb_1  | 2020-11-29 19:49:39,482 ERROR ReplicaDB:101 Got exception running ReplicaDB:
replicadb_1  | org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
replicadb_1  |   Position: 91
replicadb_1  |  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.1.jar:42.2.1]   
replicadb_1  |  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:244) ~[postgresql-42.2.1.jar:42.2.1]
replicadb_1  |  at org.replicadb.manager.PostgresqlManager.createStagingTable(PostgresqlManager.java:218) ~[ReplicaDB-0.8.9.jar:0.8.9]
replicadb_1  |  at org.replicadb.manager.SqlManager.preSinkTasks(SqlManager.java:498) ~[ReplicaDB-0.8.9.jar:0.8.9] 
replicadb_1  |  at org.replicadb.ReplicaDB.main(ReplicaDB.java:62) [ReplicaDB-0.8.9.jar:0.8.9]
replicadb_1  | 2020-11-29 19:49:39,486 INFO  SqlManager:475 Dropping staging table with this command: DROP TABLE public.categoriesrepdb3810
postgres2_1  | 2020-11-29 19:49:39.486 UTC [224] ERROR:  table "categoriesrepdb3810" does not exist
postgres2_1  | 2020-11-29 19:49:39.486 UTC [224] STATEMENT:  DROP TABLE public.categoriesrepdb3810
replicadb_1  | 2020-11-29 19:49:39,487 ERROR ReplicaDB:119 org.postgresql.util.PSQLException: ERROR: table "categoriesrepdb3810" does not exist
replicadb_1  | 2020-11-29 19:49:39,488 INFO  ReplicaDB:124 Total process time: 220ms

[osalvador]

Hi @AkshayMAnvekar,

Replication of multiple tables at once is not a feature supported by ReplicaDB.

[osalvador]

Hi @AkshayMAnvekar

You can create script for multiple table replication, see this comment: https://github.com/osalvador/ReplicaDB/issues/27#issuecomment-862229297