You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2022/05/02 07:43:56 UTC

[shardingsphere] branch master updated: Support getting create table SQL in multi schema for PostgreSQL and openGauss (#17264)

This is an automated email from the ASF dual-hosted git repository.

panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git


The following commit(s) were added to refs/heads/master by this push:
     new fa689c316b9 Support getting create table SQL in multi schema for PostgreSQL and openGauss (#17264)
fa689c316b9 is described below

commit fa689c316b9f8c4a38a697a5646f8131db641617
Author: Hongsheng Zhong <sa...@126.com>
AuthorDate: Mon May 2 15:43:40 2022 +0800

    Support getting create table SQL in multi schema for PostgreSQL and openGauss (#17264)
    
    * Add TableNameSchemaNameMapping
    
    * Refactor PrepareTargetTablesParameter
    
    * PostgreSQLDataSourcePreparer support multi schema
    
    * OpenGaussDataSourcePreparer support multi schema
---
 .../core/api/impl/AbstractPipelineJobAPIImpl.java  |  1 +
 .../core/metadata/TableNameSchemaNameMapping.java  | 62 ++++++++++++++++++++++
 .../datasource/PrepareTargetTablesParameter.java   | 20 ++++---
 .../rulealtered/RuleAlteredJobContext.java         | 21 ++++++++
 .../rulealtered/RuleAlteredJobPreparer.java        |  9 ++--
 .../datasource/OpenGaussDataSourcePreparer.java    | 25 +++------
 .../datasource/PostgreSQLDataSourcePreparer.java   | 42 +++++++++------
 7 files changed, 133 insertions(+), 47 deletions(-)

diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java
index 549c3b7d259..406037d25a3 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java
@@ -53,6 +53,7 @@ public abstract class AbstractPipelineJobAPIImpl implements PipelineJobAPI {
         JobConfigurationPOJO jobConfigPOJO = getElasticJobConfigPOJO(jobId);
         jobConfigPOJO.setDisabled(true);
         jobConfigPOJO.getProps().setProperty("stop_time", LocalDateTime.now().format(DATE_TIME_FORMATTER));
+        // TODO updateJobConfiguration might doesn't work
         PipelineAPIFactory.getJobConfigurationAPI().updateJobConfiguration(jobConfigPOJO);
     }
     
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/TableNameSchemaNameMapping.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/TableNameSchemaNameMapping.java
new file mode 100644
index 00000000000..3ae8cab7c05
--- /dev/null
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/TableNameSchemaNameMapping.java
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.data.pipeline.core.metadata;
+
+import org.apache.shardingsphere.data.pipeline.api.metadata.LogicTableName;
+import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
+
+import java.util.HashMap;
+import java.util.Map;
+
+/**
+ * Table name and schema name mapping.
+ */
+public final class TableNameSchemaNameMapping {
+    
+    private final Map<LogicTableName, String> mapping;
+    
+    public TableNameSchemaNameMapping(final ShardingSphereMetaData metaData) {
+        Map<LogicTableName, String> mapping = new HashMap<>();
+        metaData.getSchemas().forEach((schemaName, schema) -> {
+            for (String each : schema.getAllTableNames()) {
+                mapping.put(new LogicTableName(each), schemaName);
+            }
+        });
+        this.mapping = mapping;
+    }
+    
+    /**
+     * Get schema name.
+     *
+     * @param logicTableName logic table name
+     * @return schema name
+     */
+    public String getSchemaName(final String logicTableName) {
+        return mapping.get(new LogicTableName(logicTableName));
+    }
+    
+    /**
+     * Get schema name.
+     *
+     * @param logicTableName logic table name
+     * @return schema name
+     */
+    public String getSchemaName(final LogicTableName logicTableName) {
+        return mapping.get(logicTableName);
+    }
+}
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java
index 41233157c1a..57d019a0590 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java
@@ -19,24 +19,30 @@ package org.apache.shardingsphere.data.pipeline.core.prepare.datasource;
 
 import lombok.Getter;
 import lombok.NonNull;
-import lombok.RequiredArgsConstructor;
 import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.RuleAlteredJobConfiguration;
 import org.apache.shardingsphere.data.pipeline.api.datanode.JobDataNodeLine;
 import org.apache.shardingsphere.data.pipeline.core.datasource.PipelineDataSourceManager;
+import org.apache.shardingsphere.data.pipeline.core.metadata.TableNameSchemaNameMapping;
 
 /**
  * Prepare target tables parameter.
  */
-@RequiredArgsConstructor
 @Getter
 public final class PrepareTargetTablesParameter {
     
-    @NonNull
-    private final JobDataNodeLine tablesFirstDataNodes;
-    
-    @NonNull
     private final RuleAlteredJobConfiguration jobConfig;
     
-    @NonNull
+    private final JobDataNodeLine tablesFirstDataNodes;
+    
     private final PipelineDataSourceManager dataSourceManager;
+    
+    private final TableNameSchemaNameMapping tableNameSchemaNameMapping;
+    
+    public PrepareTargetTablesParameter(@NonNull final RuleAlteredJobConfiguration jobConfig, @NonNull final PipelineDataSourceManager dataSourceManager,
+                                        final TableNameSchemaNameMapping tableNameSchemaNameMapping) {
+        this.jobConfig = jobConfig;
+        tablesFirstDataNodes = JobDataNodeLine.unmarshal(jobConfig.getTablesFirstDataNodes());
+        this.dataSourceManager = dataSourceManager;
+        this.tableNameSchemaNameMapping = tableNameSchemaNameMapping;
+    }
 }
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java
index aa916d6a04a..2f1e7faf1c4 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java
@@ -28,10 +28,13 @@ import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.TaskConfig
 import org.apache.shardingsphere.data.pipeline.api.datasource.PipelineDataSourceWrapper;
 import org.apache.shardingsphere.data.pipeline.api.job.JobStatus;
 import org.apache.shardingsphere.data.pipeline.api.job.progress.JobProgress;
+import org.apache.shardingsphere.data.pipeline.core.context.PipelineContext;
 import org.apache.shardingsphere.data.pipeline.core.datasource.PipelineDataSourceManager;
+import org.apache.shardingsphere.data.pipeline.core.metadata.TableNameSchemaNameMapping;
 import org.apache.shardingsphere.data.pipeline.core.metadata.loader.PipelineTableMetaDataLoader;
 import org.apache.shardingsphere.data.pipeline.core.task.IncrementalTask;
 import org.apache.shardingsphere.data.pipeline.core.task.InventoryTask;
+import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
 
 import java.util.Collection;
 import java.util.LinkedList;
@@ -72,6 +75,14 @@ public final class RuleAlteredJobContext {
         }
     };
     
+    private final LazyInitializer<TableNameSchemaNameMapping> schemaNameMappingLazyInitializer = new LazyInitializer<TableNameSchemaNameMapping>() {
+        @Override
+        protected TableNameSchemaNameMapping initialize() {
+            ShardingSphereMetaData metaData = PipelineContext.getContextManager().getMetaDataContexts().getMetaData(jobConfig.getDatabaseName());
+            return new TableNameSchemaNameMapping(metaData);
+        }
+    };
+    
     private final LazyInitializer<PipelineTableMetaDataLoader> sourceMetaDataLoaderLazyInitializer = new LazyInitializer<PipelineTableMetaDataLoader>() {
         @Override
         protected PipelineTableMetaDataLoader initialize() throws ConcurrentException {
@@ -110,6 +121,16 @@ public final class RuleAlteredJobContext {
         return sourceMetaDataLoaderLazyInitializer.get();
     }
     
+    /**
+     * Get table name and schema name mapping.
+     *
+     * @return mapping
+     */
+    @SneakyThrows(ConcurrentException.class)
+    public TableNameSchemaNameMapping getTableNameSchemaNameMapping() {
+        return schemaNameMappingLazyInitializer.get();
+    }
+    
     /**
      * Release resources.
      */
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java
index 8b54ec70bc2..b1832860489 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java
@@ -20,7 +20,6 @@ package org.apache.shardingsphere.data.pipeline.scenario.rulealtered;
 import lombok.extern.slf4j.Slf4j;
 import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.RuleAlteredJobConfiguration;
 import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.TaskConfiguration;
-import org.apache.shardingsphere.data.pipeline.api.datanode.JobDataNodeLine;
 import org.apache.shardingsphere.data.pipeline.api.datasource.PipelineDataSourceWrapper;
 import org.apache.shardingsphere.data.pipeline.api.datasource.config.PipelineDataSourceConfigurationFactory;
 import org.apache.shardingsphere.data.pipeline.api.datasource.config.impl.ShardingSpherePipelineDataSourceConfiguration;
@@ -115,7 +114,7 @@ public final class RuleAlteredJobPreparer {
     }
     
     private void prepareAndCheckTarget(final RuleAlteredJobContext jobContext) {
-        prepareTarget(jobContext.getJobConfig(), jobContext.getDataSourceManager());
+        prepareTarget(jobContext);
         JobProgress initProgress = jobContext.getInitProgress();
         if (null == initProgress || initProgress.getStatus() == JobStatus.PREPARING_FAILURE) {
             PipelineDataSourceWrapper targetDataSource = jobContext.getDataSourceManager().getDataSource(jobContext.getTaskConfig().getImporterConfig().getDataSourceConfig());
@@ -123,14 +122,14 @@ public final class RuleAlteredJobPreparer {
         }
     }
     
-    private void prepareTarget(final RuleAlteredJobConfiguration jobConfig, final PipelineDataSourceManager dataSourceManager) {
+    private void prepareTarget(final RuleAlteredJobContext jobContext) {
+        RuleAlteredJobConfiguration jobConfig = jobContext.getJobConfig();
         Optional<DataSourcePreparer> dataSourcePreparer = EnvironmentCheckerFactory.getDataSourcePreparer(jobConfig.getTargetDatabaseType());
         if (!dataSourcePreparer.isPresent()) {
             log.info("dataSourcePreparer null, ignore prepare target");
             return;
         }
-        JobDataNodeLine tablesFirstDataNodes = JobDataNodeLine.unmarshal(jobConfig.getTablesFirstDataNodes());
-        PrepareTargetTablesParameter prepareTargetTablesParameter = new PrepareTargetTablesParameter(tablesFirstDataNodes, jobConfig, dataSourceManager);
+        PrepareTargetTablesParameter prepareTargetTablesParameter = new PrepareTargetTablesParameter(jobConfig, jobContext.getDataSourceManager(), jobContext.getTableNameSchemaNameMapping());
         dataSourcePreparer.get().prepareTargetTables(prepareTargetTablesParameter);
     }
     
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java
index 19552851150..b0f90beaca7 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java
@@ -31,7 +31,6 @@ import org.apache.shardingsphere.data.pipeline.core.prepare.datasource.PrepareTa
 import org.apache.shardingsphere.infra.datanode.DataNode;
 
 import java.sql.Connection;
-import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
@@ -84,10 +83,10 @@ public final class OpenGaussDataSourcePreparer extends AbstractDataSourcePrepare
                 // Keep dataSource to reuse
                 PipelineDataSourceWrapper dataSource = dataSourceManager.getDataSource(sourceDataSourceConfig.getActualDataSourceConfig(dataNode.getDataSourceName()));
                 try (Connection sourceConnection = dataSource.getConnection()) {
+                    String schemaName = parameter.getTableNameSchemaNameMapping().getSchemaName(each.getLogicTableName());
                     String actualTableName = dataNode.getTableName();
-                    int oid = queryTableOid(sourceConnection, actualTableName);
-                    String tableDefinition = queryTableDefinition(sourceConnection, oid);
-                    log.info("getActualTableDefinitions, dataNode={}, tableDefinition={}", dataNode, tableDefinition);
+                    String tableDefinition = queryTableDefinition(sourceConnection, schemaName, actualTableName);
+                    log.info("getActualTableDefinitions, schemaName={}, dataNode={}, tableDefinition={}", schemaName, dataNode, tableDefinition);
                     String logicTableName = each.getLogicTableName();
                     result.add(new ActualTableDefinition(logicTableName, actualTableName, tableDefinition));
                 }
@@ -96,21 +95,9 @@ public final class OpenGaussDataSourcePreparer extends AbstractDataSourcePrepare
         return result;
     }
     
-    private int queryTableOid(final Connection sourceConnection, final String actualTableName) throws SQLException {
-        String sql = "SELECT oid FROM pg_class WHERE relname = ?";
-        try (PreparedStatement statement = sourceConnection.prepareStatement(sql)) {
-            statement.setString(1, actualTableName);
-            try (ResultSet resultSet = statement.executeQuery()) {
-                if (!resultSet.next()) {
-                    throw new PipelineJobPrepareFailedException("select oid has no result, sql: " + sql + ", actualTableName: " + actualTableName);
-                }
-                return resultSet.getInt(1);
-            }
-        }
-    }
-    
-    private String queryTableDefinition(final Connection sourceConnection, final int oid) throws SQLException {
-        String sql = String.format("SELECT * FROM pg_get_tabledef(%d)", oid);
+    private String queryTableDefinition(final Connection sourceConnection, final String schemaName, final String actualTableName) throws SQLException {
+        String sql = String.format("SELECT * FROM pg_get_tabledef('%s.%s'::regclass::oid)", schemaName, actualTableName);
+        log.info("queryTableDefinition, sql={}", sql);
         try (Statement statement = sourceConnection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) {
             if (!resultSet.next()) {
                 throw new PipelineJobPrepareFailedException("table definition has no result, sql: " + sql);
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSourcePreparer.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSource [...]
index 638cb4e3c00..2d316002fe6 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSourcePreparer.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSourcePreparer.java
@@ -55,20 +55,20 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
     private static final String FETCH_CREATE_TABLE_TEMPLATE = "SELECT 'CREATE TABLE IF NOT EXISTS '||'%s'||'('||STRING_AGG"
             + "(column_list.column_expr, ',')||',PRIMARY KEY({PK_placeholder}))' FROM (SELECT *,column_name||' '||data_type||COALESCE('('"
             + " || character_maximum_length || ')', '')||CASE WHEN is_nullable = 'YES' then '' ELSE ' NOT NULL' END AS column_expr "
-            + "FROM information_schema.columns WHERE table_name = '%s' AND table_schema='public' ORDER BY ordinal_position) column_list";
+            + "FROM information_schema.columns WHERE table_name = '%s' AND table_schema='%s' ORDER BY ordinal_position) column_list";
     
     private static final String FETCH_NORMAL_INDEXES_TEMPLATE = "SELECT schemaname,indexname,indexdef FROM pg_indexes WHERE tablename = "
-            + "'%s' AND indexname != '%s' AND schemaname='public'";
+            + "'%s' AND indexname != '%s' AND schemaname='%s'";
     
     private static final String DROP_INDEX_TEMPLATE = "DROP INDEX IF EXISTS %s.%s";
     
     private static final String FETCH_PRIMARY_KEY_TEMPLATE = "SELECT constraint_name,column_name FROM information_schema"
-            + ".key_column_usage WHERE table_name = '%s' AND table_schema='public'";
+            + ".key_column_usage WHERE table_name = '%s' AND table_schema='%s'";
     
     private static final String COMMENT_TEMPLATE = "COMMENT ON %s %s IS '%s'";
     
     private static final String FETCH_TABLE_COLUMN_TEMPLATE = "SELECT ordinal_position,column_name FROM information_schema.columns WHERE "
-            + "table_name = '%s' AND table_schema='public' ORDER BY ordinal_position";
+            + "table_name = '%s' AND table_schema='%s' ORDER BY ordinal_position";
     
     private static final String FETCH_COMMENT_TEMPLATE = "SELECT objsubid,description FROM pg_catalog.pg_description WHERE objoid = "
             + "'%s'::regclass";
@@ -130,16 +130,19 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
                 // TODO to remove if config is fix the problem
                 final Map<String, Object> configMap =
                         sourceDataSourceConfig.getRootConfig().getDataSources().get(dataNode.getDataSourceName());
+                // TODO one of jdbcUrl or url might not exist, refactor to getActualDataSourceConfig
                 configMap.put("jdbcUrl", configMap.get("url"));
                 PipelineDataSourceWrapper dataSource =
                         dataSourceManager.getDataSource(sourceDataSourceConfig.getActualDataSourceConfig(dataNode.getDataSourceName()));
                 try (Connection sourceConnection = dataSource.getConnection()) {
                     String actualTableName = dataNode.getTableName();
+                    String schemaName = parameter.getTableNameSchemaNameMapping().getSchemaName(each.getLogicTableName());
                     StringJoiner joiner = new StringJoiner(";");
-                    Pair<String, List<String>> primaryKeyPair = queryTablePrimaryKey(sourceConnection, actualTableName);
-                    joiner.add(queryCreateTableSql(sourceConnection, actualTableName, primaryKeyPair.getRight()));
-                    queryCreateIndexes(sourceConnection, actualTableName, primaryKeyPair.getLeft()).forEach(joiner::add);
-                    queryCommentOnList(sourceConnection, actualTableName).forEach(joiner::add);
+                    Pair<String, List<String>> primaryKeyPair = queryTablePrimaryKey(sourceConnection, schemaName, actualTableName);
+                    joiner.add(queryCreateTableSql(sourceConnection, schemaName, actualTableName, primaryKeyPair.getRight()));
+                    queryCreateIndexes(sourceConnection, schemaName, actualTableName, primaryKeyPair.getLeft()).forEach(joiner::add);
+                    // TODO support query comment for multi schema
+                    //queryCommentOnList(sourceConnection, schemaName, actualTableName).forEach(joiner::add);
                     String tableDefinition = joiner.toString();
                     result.add(new ActualTableDefinition(each.getLogicTableName(), actualTableName, tableDefinition));
                 }
@@ -148,12 +151,14 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
         return result;
     }
     
-    private Pair<String, List<String>> queryTablePrimaryKey(final Connection sourceConnection, final String actualTableName) throws SQLException {
+    private Pair<String, List<String>> queryTablePrimaryKey(final Connection sourceConnection, final String schemaName, final String actualTableName) throws SQLException {
+        String sql = String.format(FETCH_PRIMARY_KEY_TEMPLATE, actualTableName, schemaName);
+        log.info("queryTablePrimaryKey, sql={}", sql);
         String primaryKeyName = null;
         List<String> primaryKeyColumns = new LinkedList<>();
         try (
                 Statement statement = sourceConnection.createStatement();
-                ResultSet resultSet = statement.executeQuery(String.format(FETCH_PRIMARY_KEY_TEMPLATE, actualTableName))) {
+                ResultSet resultSet = statement.executeQuery(sql)) {
             while (resultSet.next()) {
                 primaryKeyName = primaryKeyName == null ? resultSet.getString(1) : primaryKeyName;
                 primaryKeyColumns.add(resultSet.getString(2));
@@ -165,8 +170,9 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
         return Pair.of(primaryKeyName, primaryKeyColumns);
     }
     
-    private String queryCreateTableSql(final Connection sourceConnection, final String actualTableName, final List<String> pkColumns) throws SQLException {
-        final String sql = String.format(FETCH_CREATE_TABLE_TEMPLATE, actualTableName, actualTableName);
+    private String queryCreateTableSql(final Connection sourceConnection, final String schemaName, final String actualTableName, final List<String> pkColumns) throws SQLException {
+        final String sql = String.format(FETCH_CREATE_TABLE_TEMPLATE, actualTableName, actualTableName, schemaName);
+        log.info("queryCreateTableSql, sql={}", sql);
         try (Statement statement = sourceConnection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) {
             if (!resultSet.next()) {
                 throw new PipelineJobPrepareFailedException("table struct has no result, sql: " + sql);
@@ -175,11 +181,13 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
         }
     }
     
-    private List<String> queryCreateIndexes(final Connection sourceConnection, final String actualTableName, final String pkName) throws SQLException {
+    private List<String> queryCreateIndexes(final Connection sourceConnection, final String schemaName, final String actualTableName, final String pkName) throws SQLException {
         List<String> result = new LinkedList<>();
+        String sql = String.format(FETCH_NORMAL_INDEXES_TEMPLATE, actualTableName, pkName, schemaName);
+        log.info("queryCreateIndexes, sql={}", sql);
         try (
                 Statement statement = sourceConnection.createStatement();
-                ResultSet resultSet = statement.executeQuery(String.format(FETCH_NORMAL_INDEXES_TEMPLATE, actualTableName, pkName))) {
+                ResultSet resultSet = statement.executeQuery(sql)) {
             while (resultSet.next()) {
                 // TODO add drop index first, make sure the index is not exist
                 result.add(String.format(DROP_INDEX_TEMPLATE, resultSet.getString(1), resultSet.getString(2)));
@@ -189,8 +197,9 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
         return result;
     }
     
-    private List<String> queryCommentOnList(final Connection sourceConnection, final String actualTableName) throws SQLException {
+    private List<String> queryCommentOnList(final Connection sourceConnection, final String schemaName, final String actualTableName) throws SQLException {
         final String fetchCommentSql = String.format(FETCH_COMMENT_TEMPLATE, actualTableName);
+        log.info("queryCommentOnList, fetchCommentSql={}", fetchCommentSql);
         List<String> result = new LinkedList<>();
         Map<Integer, String> commentMap = Maps.newHashMap();
         try (
@@ -204,7 +213,8 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
                 result.add(String.format(COMMENT_TEMPLATE, "TABLE", actualTableName, tableComment));
             }
         }
-        final String fetchColumnSql = String.format(FETCH_TABLE_COLUMN_TEMPLATE, actualTableName);
+        final String fetchColumnSql = String.format(FETCH_TABLE_COLUMN_TEMPLATE, actualTableName, schemaName);
+        log.info("queryCommentOnList, fetchColumnSql={}", fetchColumnSql);
         try (
                 Statement statement = sourceConnection.createStatement();
                 ResultSet columnsResult = statement.executeQuery(fetchColumnSql)) {