Examples with PostgresConnection io.debezium.connector.postgresql.connection.PostgresConnection used on opensource projects

Example 6 with PostgresConnection

use of io.debezium.connector.postgresql.connection.PostgresConnection in project debezium by debezium.

the class PostgresSchemaIT method shouldLoadSchemaForBuiltinPostgresTypes.

@Test
public void shouldLoadSchemaForBuiltinPostgresTypes() throws Exception {
    TestHelper.executeDDL("postgres_create_tables.ddl");
    PostgresConnectorConfig config = new PostgresConnectorConfig(TestHelper.defaultConfig().build());
    schema = new PostgresSchema(config, TestHelper.getTypeRegistry(), TopicSelector.create(config));
    try (PostgresConnection connection = TestHelper.create()) {
        schema.refresh(connection, false);
        assertTablesIncluded(TEST_TABLES);
        Arrays.stream(TEST_TABLES).forEach(tableId -> assertKeySchema(tableId, "pk", Schema.INT32_SCHEMA));
        assertTableSchema("public.numeric_table", "si, i, bi, r, db, ss, bs, b", Schema.OPTIONAL_INT16_SCHEMA, Schema.OPTIONAL_INT32_SCHEMA, Schema.OPTIONAL_INT64_SCHEMA, Schema.OPTIONAL_FLOAT32_SCHEMA, Schema.OPTIONAL_FLOAT64_SCHEMA, Schema.INT16_SCHEMA, Schema.INT64_SCHEMA, Schema.OPTIONAL_BOOLEAN_SCHEMA);
        assertTableSchema("public.numeric_decimal_table", "d, dzs, dvs, n, nzs, nvs", Decimal.builder(2).optional().build(), Decimal.builder(0).optional().build(), VariableScaleDecimal.builder().optional().build(), Decimal.builder(4).optional().build(), Decimal.builder(0).optional().build(), VariableScaleDecimal.builder().optional().build());
        assertTableSchema("public.string_table", "vc, vcv, ch, c, t", Schema.OPTIONAL_STRING_SCHEMA, Schema.OPTIONAL_STRING_SCHEMA, Schema.OPTIONAL_STRING_SCHEMA, Schema.OPTIONAL_STRING_SCHEMA, Schema.OPTIONAL_STRING_SCHEMA);
        assertTableSchema("public.cash_table", "csh", Decimal.builder(0).optional().build());
        assertTableSchema("public.bitbin_table", "ba, bol, bs, bv", Schema.OPTIONAL_BYTES_SCHEMA, Schema.OPTIONAL_BOOLEAN_SCHEMA, Bits.builder(2).optional().build(), Bits.builder(2).optional().build());
        assertTableSchema("public.time_table", "ts, tz, date, ti, ttz, it", NanoTimestamp.builder().optional().build(), ZonedTimestamp.builder().optional().build(), Date.builder().optional().build(), NanoTime.builder().optional().build(), ZonedTime.builder().optional().build(), MicroDuration.builder().optional().build());
        assertTableSchema("public.text_table", "j, jb, x, u", Json.builder().optional().build(), Json.builder().optional().build(), Xml.builder().optional().build(), Uuid.builder().optional().build());
        assertTableSchema("public.geom_table", "p", Point.builder().optional().build());
        assertTableSchema("public.tstzrange_table", "unbounded_exclusive_range, bounded_inclusive_range", Schema.OPTIONAL_STRING_SCHEMA, Schema.OPTIONAL_STRING_SCHEMA);
        assertTableSchema("public.array_table", "int_array, bigint_array, text_array", SchemaBuilder.array(Schema.OPTIONAL_INT32_SCHEMA).optional().build(), SchemaBuilder.array(Schema.OPTIONAL_INT64_SCHEMA).optional().build(), SchemaBuilder.array(Schema.OPTIONAL_STRING_SCHEMA).optional().build());
        assertTableSchema("\"Quoted_\"\" . Schema\".\"Quoted_\"\" . Table\"", "\"Quoted_\"\" . Text_Column\"", Schema.OPTIONAL_STRING_SCHEMA);
        TableSchema tableSchema = schemaFor("public.custom_table");
        assertThat(tableSchema.valueSchema().field("lt")).isNull();
    }
}

Example 7 with PostgresConnection

use of io.debezium.connector.postgresql.connection.PostgresConnection in project debezium by debezium.

the class PostgresConnectorTask method start.

@Override
public void start(Configuration config) {
    if (running.get()) {
        // already running
        return;
    }
    PostgresConnectorConfig connectorConfig = new PostgresConnectorConfig(config);
    // Create type registry
    TypeRegistry typeRegistry;
    try (final PostgresConnection connection = new PostgresConnection(connectorConfig.jdbcConfig())) {
        typeRegistry = connection.getTypeRegistry();
    }
    // create the task context and schema...
    TopicSelector topicSelector = TopicSelector.create(connectorConfig);
    PostgresSchema schema = new PostgresSchema(connectorConfig, typeRegistry, topicSelector);
    this.taskContext = new PostgresTaskContext(connectorConfig, schema, topicSelector);
    SourceInfo sourceInfo = new SourceInfo(connectorConfig.serverName());
    Map<String, Object> existingOffset = context.offsetStorageReader().offset(sourceInfo.partition());
    LoggingContext.PreviousContext previousContext = taskContext.configureLoggingContext(CONTEXT_NAME);
    try {
        // Print out the server information
        try (PostgresConnection connection = taskContext.createConnection()) {
            logger.info(connection.serverInfo().toString());
        }
        if (existingOffset == null) {
            logger.info("No previous offset found");
            if (connectorConfig.snapshotNeverAllowed()) {
                logger.info("Snapshots are not allowed as per configuration, starting streaming logical changes only");
                producer = new RecordsStreamProducer(taskContext, sourceInfo);
            } else {
                // otherwise we always want to take a snapshot at startup
                createSnapshotProducer(taskContext, sourceInfo, connectorConfig.initialOnlySnapshot());
            }
        } else {
            sourceInfo.load(existingOffset);
            logger.info("Found previous offset {}", sourceInfo);
            if (sourceInfo.isSnapshotInEffect()) {
                if (connectorConfig.snapshotNeverAllowed()) {
                    // No snapshots are allowed
                    String msg = "The connector previously stopped while taking a snapshot, but now the connector is configured " + "to never allow snapshots. Reconfigure the connector to use snapshots initially or when needed.";
                    throw new ConnectException(msg);
                } else {
                    logger.info("Found previous incomplete snapshot");
                    createSnapshotProducer(taskContext, sourceInfo, connectorConfig.initialOnlySnapshot());
                }
            } else if (connectorConfig.alwaysTakeSnapshot()) {
                logger.info("Taking a new snapshot as per configuration");
                producer = new RecordsSnapshotProducer(taskContext, sourceInfo, true);
            } else {
                logger.info("Previous snapshot has completed successfully, streaming logical changes from last known position");
                producer = new RecordsStreamProducer(taskContext, sourceInfo);
            }
        }
        changeEventQueue = new ChangeEventQueue.Builder<ChangeEvent>().pollInterval(connectorConfig.getPollInterval()).maxBatchSize(connectorConfig.getMaxBatchSize()).maxQueueSize(connectorConfig.getMaxQueueSize()).loggingContextSupplier(() -> taskContext.configureLoggingContext(CONTEXT_NAME)).build();
        producer.start(changeEventQueue::enqueue, changeEventQueue::producerFailure);
        running.compareAndSet(false, true);
    } catch (SQLException e) {
        throw new ConnectException(e);
    } finally {
        previousContext.restore();
    }
}

Example 8 with PostgresConnection

use of io.debezium.connector.postgresql.connection.PostgresConnection in project debezium by debezium.

the class RecordsSnapshotProducer method takeSnapshot.

private void takeSnapshot(BlockingConsumer<ChangeEvent> consumer) {
    long snapshotStart = clock().currentTimeInMillis();
    Connection jdbcConnection = null;
    try (PostgresConnection connection = taskContext.createConnection()) {
        jdbcConnection = connection.connection();
        String lineSeparator = System.lineSeparator();
        logger.info("Step 0: disabling autocommit");
        connection.setAutoCommit(false);
        long lockTimeoutMillis = taskContext.config().snapshotLockTimeoutMillis();
        logger.info("Step 1: starting transaction and refreshing the DB schemas for database '{}' and user '{}'", connection.database(), connection.username());
        // we're using the same isolation level that pg_backup uses
        StringBuilder statements = new StringBuilder("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;");
        connection.executeWithoutCommitting(statements.toString());
        statements.delete(0, statements.length());
        // next refresh the schema which will load all the tables taking the filters into account
        PostgresSchema schema = schema();
        schema.refresh(connection, false);
        logger.info("Step 2: locking each of the database tables, waiting a maximum of '{}' seconds for each lock", lockTimeoutMillis / 1000d);
        statements.append("SET lock_timeout = ").append(lockTimeoutMillis).append(";").append(lineSeparator);
        // we're locking in SHARE UPDATE EXCLUSIVE MODE to avoid concurrent schema changes while we're taking the snapshot
        // this does not prevent writes to the table, but prevents changes to the table's schema....
        // DBZ-298 Quoting name in case it has been quoted originally; it doesn't do harm if it hasn't been quoted
        schema.tables().forEach(tableId -> statements.append("LOCK TABLE ").append(tableId.toDoubleQuotedString()).append(" IN SHARE UPDATE EXCLUSIVE MODE;").append(lineSeparator));
        connection.executeWithoutCommitting(statements.toString());
        // now that we have the locks, refresh the schema
        schema.refresh(connection, false);
        // get the current position in the log, from which we'll continue streaming once the snapshot it finished
        // If rows are being inserted while we're doing the snapshot, the xlog pos should increase and so when
        // we start streaming, we should get back those changes
        long xlogStart = connection.currentXLogLocation();
        long txId = connection.currentTransactionId().longValue();
        logger.info("\t read xlogStart at '{}' from transaction '{}'", ReplicationConnection.format(xlogStart), txId);
        // and mark the start of the snapshot
        sourceInfo.startSnapshot();
        sourceInfo.update(xlogStart, clock().currentTimeInMicros(), txId);
        logger.info("Step 3: reading and exporting the contents of each table");
        AtomicInteger rowsCounter = new AtomicInteger(0);
        final Map<TableId, String> selectOverrides = getSnapshotSelectOverridesByTable();
        for (TableId tableId : schema.tables()) {
            if (schema.isFilteredOut(tableId)) {
                logger.info("\t table '{}' is filtered out, ignoring", tableId);
                continue;
            }
            long exportStart = clock().currentTimeInMillis();
            logger.info("\t exporting data from table '{}'", tableId);
            try {
                // DBZ-298 Quoting name in case it has been quoted originally; it doesn't do harm if it hasn't been quoted
                final String selectStatement = selectOverrides.getOrDefault(tableId, "SELECT * FROM " + tableId.toDoubleQuotedString());
                logger.info("For table '{}' using select statement: '{}'", tableId, selectStatement);
                connection.queryWithBlockingConsumer(selectStatement, this::readTableStatement, rs -> readTable(tableId, rs, consumer, rowsCounter));
                logger.info("\t finished exporting '{}' records for '{}'; total duration '{}'", rowsCounter.get(), tableId, Strings.duration(clock().currentTimeInMillis() - exportStart));
                rowsCounter.set(0);
            } catch (SQLException e) {
                throw new ConnectException(e);
            }
        }
        // finally commit the transaction to release all the locks...
        logger.info("Step 4: committing transaction '{}'", txId);
        jdbcConnection.commit();
        SourceRecord currentRecord = this.currentRecord.get();
        if (currentRecord != null) {
            // process and send the last record after marking it as such
            logger.info("Step 5: sending the last snapshot record");
            sourceInfo.markLastSnapshotRecord();
            this.currentRecord.set(new SourceRecord(currentRecord.sourcePartition(), sourceInfo.offset(), currentRecord.topic(), currentRecord.kafkaPartition(), currentRecord.keySchema(), currentRecord.key(), currentRecord.valueSchema(), currentRecord.value()));
            sendCurrentRecord(consumer);
        }
        // and complete the snapshot
        sourceInfo.completeSnapshot();
        logger.info("Snapshot completed in '{}'", Strings.duration(clock().currentTimeInMillis() - snapshotStart));
    } catch (SQLException e) {
        rollbackTransaction(jdbcConnection);
        throw new ConnectException(e);
    } catch (InterruptedException e) {
        Thread.interrupted();
        rollbackTransaction(jdbcConnection);
        logger.warn("Snapshot aborted after '{}'", Strings.duration(clock().currentTimeInMillis() - snapshotStart));
    }
}

Example 9 with PostgresConnection

use of io.debezium.connector.postgresql.connection.PostgresConnection in project debezium by debezium.

the class RecordsStreamProducer method columnValues.

private Object[] columnValues(List<ReplicationMessage.Column> columns, TableId tableId, boolean refreshSchemaIfChanged, boolean metadataInMessage) throws SQLException {
    if (columns == null || columns.isEmpty()) {
        return null;
    }
    Table table = schema().tableFor(tableId);
    assert table != null;
    // check if we need to refresh our local schema due to DB schema changes for this table
    if (refreshSchemaIfChanged && schemaChanged(columns, table, metadataInMessage)) {
        try (final PostgresConnection connection = taskContext.createConnection()) {
            // Refresh the schema so we get information about primary keys
            schema().refresh(connection, tableId);
            // Update the schema with metadata coming from decoder message
            if (metadataInMessage) {
                schema().refresh(tableFromFromMessage(columns, schema().tableFor(tableId)));
            }
            table = schema().tableFor(tableId);
        }
    }
    // based on the schema columns, create the values on the same position as the columns
    List<String> columnNames = table.columnNames();
    // JSON does not deliver a list of all columns for REPLICA IDENTITY DEFAULT
    Object[] values = new Object[columns.size() < columnNames.size() ? columnNames.size() : columns.size()];
    columns.forEach(message -> {
        // DBZ-298 Quoted column names will be sent like that in messages, but stored unquoted in the column names
        String columnName = Strings.unquoteIdentifierPart(message.getName());
        int position = columnNames.indexOf(columnName);
        assert position >= 0;
        values[position] = message.getValue(this::typeResolverConnection, taskContext.config().includeUnknownDatatypes());
    });
    return values;
}

Example 10 with PostgresConnection

use of io.debezium.connector.postgresql.connection.PostgresConnection in project debezium by debezium.

the class RecordsStreamProducer method tableSchemaFor.

private TableSchema tableSchemaFor(TableId tableId) throws SQLException {
    PostgresSchema schema = schema();
    if (schema.isFilteredOut(tableId)) {
        logger.debug("table '{}' is filtered out, ignoring", tableId);
        return null;
    }
    TableSchema tableSchema = schema.schemaFor(tableId);
    if (tableSchema != null) {
        return tableSchema;
    }
    // which means that is a newly created table; so refresh our schema to get the definition for this table
    try (final PostgresConnection connection = taskContext.createConnection()) {
        schema.refresh(connection, tableId);
    }
    tableSchema = schema.schemaFor(tableId);
    if (tableSchema == null) {
        logger.warn("cannot load schema for table '{}'", tableId);
        return null;
    } else {
        logger.debug("refreshed DB schema to include table '{}'", tableId);
        return tableSchema;
    }
}