Striim Migration Service for Google Cloud Documentation

Create the target tables

Before running the initial load application, you must create tables in the target that are compatible with those in the source. You may use any tool you wish.

The simplest approach is to use the native utilities in PostgreSQL and Cloud SQL for PostgreSQL:

  1. Connect to the source database using psql or another client and run one of the following commands to get the log sequence number (LSN) you will need when starting streaming integration:

    • For PostgreSQL version 10 or higher: SELECT pg_current_xlog_location();

    • For earlier versions: SELECT pg_current_wal_lsn();

  2. Run pg_dump on the source database.

    See Exporting data from an externally-managed database server for instructions on using pg_dump to create a Cloud SQL-compatible export. Also use the --serializable-deferrable option to avoid incomplete transactions in the initial load. Optionally, use the -j <number of cores> option to speed up the operation (see the pg_dump documentation for your version of PostgreSQL).

  3. Import the dump file to the target PostgreSQL instance using one of the following methods:

  4. After importing the dump file, in the same schema, create the checkpoint table required for SMSGC to support recovery of the streaming integration application:

    create table chkpoint (
      id character varying(100) primary key,
      sourceposition bytea,
      pendingddl numeric(1), 
      ddl text);

At this point, you have performed the initial load including all foreign keys and other constraints, so go on to Create and test the streaming integration application.

If you prefer to use SMSGC's schema conversion utility, the command is:

striim/bin/schemaConversionUtility.sh \
-s="postgres" \
-d="jdbc:postgresql://<IP address>:<port>/<database>" \
-u="<user name>" \
-p="<password>" \
-b="<schema>.% or a list of <schema.table> separated by semicolons>" \
-t="postgres"

For example, if converting the example dvdrental database running locally:

striim/bin/schemaConversionUtility.sh \
-s="postgres" \
-d="jdbc:postgresql://localhost:5432/dvdrental" \
-u="postgres" \
-p="mypassword" \
-b="public.%" \
-t="postgres"

For detailed instructions on using the schema conversion utility, see Create target tables using SMSGC's schema conversion utility.