Building a Real-Time PostgreSQL Migration Pipeline with Striim

This guide walks through the exact process used to build and verify a complete online database migration demo on an Apple Silicon Mac. The environment uses PostgreSQL as both source and target, Striim Platform as the migration engine, and a generated ticketing dataset with more than 20,000 rows.

A brief history of Striim

Striim grew out of decades of experience in database replication. The company began in Palo Alto in 2012 as WebAction, founded by data-management veterans that included members of the GoldenGate Software leadership and engineering team. GoldenGate had helped establish transaction-log-based replication as a practical foundation for high availability and low-downtime data movement before Oracle acquired it in 2009. That background shaped WebAction's focus: capture data as it changes, process it while it is moving, and deliver it with very low latency rather than waiting for periodic batch jobs. Striim company history and Striim leadership background.

In September 2015, WebAction introduced the Striim name—pronounced “stream”—to better represent a platform that combined streaming data integration with streaming analytics and operational intelligence. The platform was designed not only to capture database changes, but also to filter, transform, enrich, aggregate, analyze, visualize, and route data while it remained in motion. WebAction becomes Striim.

Striim has since expanded from its original self-managed platform into cloud-delivered offerings while retaining Striim Platform for on-premises and self-managed cloud deployments. Its core proposition remains the same: make enterprise data useful as soon as it is created. For database modernization, that heritage appears directly in the two-stage pattern demonstrated here—an initial snapshot followed by transaction-log-based change data capture. What is Striim?.

Migration and partner use cases

On-premises-to-cloud migration is one supported Striim pattern among many. The same initial-load-plus-CDC approach can support cloud-to-cloud moves, database-engine modernization, data-center consolidation, regional expansion, disaster-recovery copies, and ongoing operational synchronization. A migration may end with a controlled cutover and retirement of the original system, or the pipeline may continue running to keep another database, warehouse, analytics platform, or application supplied with current data. Striim pipeline patterns.

This flexibility is useful to cloud providers, systems integrators, consulting firms, and technology partners because they can apply one repeatable migration pattern across multiple customer environments and heterogeneous database combinations. Striim has used this model in partnerships with cloud providers to move databases from on-premises environments or other clouds while applications remain online, reducing downtime and cutover risk. Microsoft and Striim database modernization and Striim's Google Cloud migration partnership.

By the end of the completed steps, we have:

• PostgreSQL 16 running locally on a dedicated port

• Separate databasesource and databasetarget schemas

• Five related source tables containing 20,100 records

• Logical replication enabled with wal2json

• A PostgreSQL replication slot for Striim

• Striim Platform running as a local development process

• A completed PostgreSQLInitialLoad application

• All 20,100 events delivered to and acknowledged by the target

• A running PostgreSQLCDC application reading PostgreSQL WAL

• A repeatable live transaction replicated and verified in the target

Demo architecture

The entire demo runs on one Mac without a cloud database:

PostgreSQL 16
├── databasesource     20,100 ticketing records
└── databasetarget     matching empty tables

        │ JDBC snapshot
        ▼

Striim: PostgreSQLInitialLoad
DatabaseReader → Stream → DatabaseWriter

        │ PostgreSQL WAL
        ▼

Striim: PostgreSQLCDC
PostgreSQLReader → Stream → DatabaseWriter

The source and target live in separate PostgreSQL schemas. This preserves the source-to-target migration pattern while avoiding cloud infrastructure costs.

Hardware and software used

The tested development machine is:

• Apple M3

• 24 GB memory

• macOS

• Eight CPU cores visible to Striim

• PostgreSQL 16.11 from Homebrew

• Striim Platform 5.4.0.6D

• Java 17

• wal2json 2.6

Striim currently supports macOS for evaluation and development, not production. Current production server guidance calls for at least eight CPU cores, 32 GB memory, and 200 GB free disk. A 24 GB Mac is nevertheless sufficient for this compact single-node demo. See the Striim system requirements.

Project files

The reusable scripts are stored under the project directory:

requirements-1-install-and-configure-an/
├── scripts/
│   ├── 01_init_postgres.sh
│   ├── 02_seed_demo.sql
│   ├── 03_seed_and_verify.sh
│   ├── 04_generate_cdc.sh
│   ├── 05_validate_sync.sql
│   ├── 06_capture_lsn.sh
│   ├── 07_create_replication_slot.sh
│   ├── 08_reset_target.sh
│   ├── 09_install_wal2json_pg16.sh
│   └── 10_create_checkpoint_table.sh
├── striim/
│   ├── PostgreSQLInitialLoad.tql
│   └── PostgreSQLCDC.tql
└── work/
    ├── postgres-data/
    ├── postgres.log
    └── initial-load-start-lsn.txt

All commands below assume this working directory:

cd "/Users/robmoayedzadeh/Documents/Codex/2026-09-02/requirements-1-install-and-configure-an"

1. Install PostgreSQL 16

PostgreSQL 16 was installed through Homebrew. On a new machine, use:

brew install postgresql@16

Confirm the binaries:

/opt/homebrew/opt/postgresql@16/bin/postgres --version
/opt/homebrew/opt/postgresql@16/bin/psql --version

The demo deliberately uses an isolated database cluster inside the project instead of Homebrew's default service. It listens on port 55432, leaving the standard PostgreSQL port 5432 available for other work.

2. Initialize and populate PostgreSQL

Run:

./scripts/03_seed_and_verify.sh

This invokes 01_init_postgres.sh, which initializes the database under work/postgres-data and adds these server settings:

port = 55432
listen_addresses = '127.0.0.1'
wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

The script then starts PostgreSQL and executes 02_seed_demo.sql.

A connection problem and its fix

The first version of the seed script attempted its administrative connection over TCP:

psql -h 127.0.0.1 -p 55432 ...

The cluster was configured with trusted local-socket authentication but password-protected TCP authentication. As a result, psql prompted for the macOS account's PostgreSQL password and failed:

FATAL: password authentication failed for user "robmoayedzadeh"

The fix was to use the trusted local socket for administrative scripts:

psql -p 55432 -d postgres ...

Striim still connects over TCP using its own password-protected PostgreSQL account.

3. Understand the ticketing dataset

The source schema models a customer-support system:

The script creates identical empty tables in databasetarget. Initial load fills those tables; CDC will subsequently keep them synchronized.

Every source table has a primary key and is set to REPLICA IDENTITY FULL, making complete row identity available to logical decoding:

ALTER TABLE databasesource.customers REPLICA IDENTITY FULL;
ALTER TABLE databasesource.agents REPLICA IDENTITY FULL;
ALTER TABLE databasesource.tickets REPLICA IDENTITY FULL;
ALTER TABLE databasesource.ticket_comments REPLICA IDENTITY FULL;
ALTER TABLE databasesource.sla_events REPLICA IDENTITY FULL;

4. Create the Striim PostgreSQL account

The seed script creates a PostgreSQL login with replication privileges:

CREATE ROLE striim
  LOGIN
  REPLICATION
  PASSWORD '<demo-password>';

It grants:

• CONNECT to the postgres database

• USAGE on both schemas

• SELECT on source tables

• SELECT, INSERT, UPDATE, DELETE, and TRUNCATE on target tables

The example project contains a local demonstration password. Replace it before sharing the environment or using it beyond this disposable demo.

Test the same TCP path Striim will use:

PGPASSWORD='<demo-password>' \
/opt/homebrew/opt/postgresql@16/bin/psql \
  -h 127.0.0.1 \
  -p 55432 \
  -U striim \
  -d postgres \
  -c "SELECT count(*) FROM databasesource.tickets;"

Expected result:

 count
-------
  5000

5. Build wal2json for PostgreSQL 16

Striim's PostgreSQL Reader uses logical replication and the wal2json output plugin. The unversioned Homebrew formula may target a newer PostgreSQL major version, so the plugin was compiled specifically against PostgreSQL 16.

Run:

./scripts/09_install_wal2json_pg16.sh

The script:

1. Clones the official eulerto/wal2json repository.

2. Checks out the wal2json_2_6 release tag.

3. Uses /opt/homebrew/opt/postgresql@16/bin/pg_config.

4. Compiles and installs the plugin into PostgreSQL 16's plugin directory.

The detached-HEAD message printed by Git is expected when checking out a release tag. No branch or commit is needed for this build.

macOS library naming

On Linux, the plugin is normally named wal2json.so. On macOS, this build produced:

/opt/homebrew/opt/postgresql@16/lib/postgresql/wal2json.dylib

An early version of the installer checked only for .so and incorrectly reported failure even though compilation and installation succeeded. The verification now accepts both .dylib and .so.

6. Create the logical replication slot

Run:

./scripts/07_create_replication_slot.sh

The underlying SQL is:

SELECT pg_create_logical_replication_slot('striim_slot', 'wal2json')
WHERE NOT EXISTS (
  SELECT 1
  FROM pg_replication_slots
  WHERE slot_name = 'striim_slot'
);

Verify the slot:

/opt/homebrew/opt/postgresql@16/bin/psql \
  -p 55432 \
  -d postgres \
  -c "SELECT slot_name, plugin, active FROM pg_replication_slots;"

Expected result before the CDC reader starts:

 slot_name   | plugin   | active
-------------+----------+--------
 striim_slot | wal2json | f

active = f is correct. It becomes active when Striim's PostgreSQL Reader connects to the slot.

7. Install and start Striim as a process

For development and testing, Striim can run directly from an extracted distribution. It should not be run this way in production.

Extract the build into a path without spaces. The Striim documentation notes a known issue with spaces in the installation path.

For example:

tar zxvf Striim-<version>.tgz -C /opt

Striim 5.4 requires JDK 17. Confirm Java before starting:

java -version

From the extracted Striim directory on macOS, initialize the keystore and users:

bin/sksConfig.sh

For this single-node demo:

• Use the internal Derby metadata repository.

• Choose a unique cluster name, such as MacDevCluster.

• Use a trial company name or the company associated with supplied keys.

• Leave interfaces blank for automatic selection, or specify the desired local interface.

• Never publish the product key, license key, keystore password, or admin password.

Start Striim:

bin/server.sh

The successful startup included:

Starting Server on cluster : MacDevCluster
Current node started in cluster : MacDevCluster, with Metadata Repository
started.

The web UI was available at:

http://10.37.129.2:9080

Keep this terminal running. Press Ctrl-C to stop the development server.

See Running Striim as a process for the official procedure.

8. Capture the initial-load/CDC handoff position

For a PostgreSQL online migration, CDC must begin from a WAL position recorded before the initial load.

Run immediately before the initial-load application:

./scripts/06_capture_lsn.sh

The captured value in this run was:

0/1A7B210

It was also saved to:

work/initial-load-start-lsn.txt

Display the file with cat:

cat work/initial-load-start-lsn.txt

Typing the filename by itself asks the shell to execute it, which causes zsh: permission denied because it is a data file, not a program.

The LSN must remain unchanged for this migration rehearsal. It will be supplied to the CDC application's PostgreSQL Reader as Start LSN. See Switching from initial load to continuous replication of PostgreSQL sources.

9. Create the Striim initial-load application

The first TQL import created the application shell but no components. Striim's DESCRIBE APPLICATION command showed:

ELEMENTS { }

The empty application was removed and recreated in the Striim console with the following structure. Substitute the actual local demo password securely rather than committing it to source control.

USE admin;

CREATE APPLICATION PostgreSQLInitialLoad;

CREATE SOURCE PostgreSQLInitialLoadSource USING DatabaseReader (
  ConnectionURL:'jdbc:postgresql://127.0.0.1:55432/postgres?reWriteBatchedInserts=true',
  Username:'striim',
  Password:'<demo-password>',
  Tables:'databasesource.%',
  FetchSize:10000,
  QuiesceOnILCompletion:true
)
OUTPUT TO PostgreSQLInitialLoadStream;

CREATE TARGET PostgreSQLInitialLoadTarget USING DatabaseWriter (
  ConnectionURL:'jdbc:postgresql://127.0.0.1:55432/postgres?reWriteBatchedInserts=true',
  Username:'striim',
  Password:'<demo-password>',
  Tables:'databasesource.%,databasetarget.%',
  BatchPolicy:'EventCount:10000,Interval:1',
  CommitPolicy:'EventCount:10000,Interval:1',
  ParallelThreads:4
)
INPUT FROM PostgreSQLInitialLoadStream;

END APPLICATION PostgreSQLInitialLoad;

What each component does

DatabaseReader performs a point-in-time JDBC scan of the source tables. It is designed for initial load, not transaction-log CDC.

PostgreSQLInitialLoadStream carries the resulting events through Striim.

DatabaseWriter maps every table under databasesource to the same table name under databasetarget. Batching and four writer threads improve initial-load throughput.

QuiesceOnILCompletion:true tells the finite initial-load source to finish after it has read the snapshot.

10. Deploy and run the initial load

The application was deployed and started from the Striim console:

USE admin;
DEPLOY APPLICATION PostgreSQLInitialLoad;
START APPLICATION PostgreSQLInitialLoad;

Both commands returned SUCCESS.

Striim Monitor then reported:

This confirms the initial snapshot moved through the entire pipeline and was acknowledged by the PostgreSQL target.

Initial-load verification

You can independently compare source and target counts:

/opt/homebrew/opt/postgresql@16/bin/psql \
  -p 55432 \
  -d postgres \
  -f scripts/05_validate_sync.sql

Every table should show equal source and target counts.

11. Create the recovery checkpoint table

CDC recovery requires DatabaseWriter to persist its source position. Create the checkpoint table before starting the CDC application:

./scripts/10_create_checkpoint_table.sh

The script creates and grants access to this table:

CREATE TABLE IF NOT EXISTS public.chkpoint (
  id character varying(100) PRIMARY KEY,
  sourceposition bytea,
  pendingddl numeric(1),
  ddl text
);

GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLE public.chkpoint TO striim;

12. Create the PostgreSQL CDC application

The completed CDC application uses:

Application:          PostgreSQLCDC
Source adapter:       PostgreSQLReader
Connection URL:       jdbc:postgresql://127.0.0.1:55432/postgres
Tables:               databasesource.%
Replication slot:     striim_slot
Start LSN:            0/1A7B210
Target adapter:       DatabaseWriter
Target mapping:       databasesource.%,databasetarget.%

Unlike DatabaseReader, PostgreSQL Reader does not rescan every table. It consumes decoded WAL and continuously emits inserts, updates, and deletes.

The application was created in the Striim console with this structure:

USE admin;
CREATE APPLICATION PostgreSQLCDC RECOVERY 5 SECOND INTERVAL;

CREATE SOURCE PostgreSQLCDCSource USING PostgreSQLReader (
  ConnectionURL:'jdbc:postgresql://127.0.0.1:55432/postgres',
  Username:'striim',
  Password:'<demo-password>',
  ReplicationSlotName:'striim_slot',
  Tables:'databasesource.%',
  StartLSN:'0/1A7B210',
  FilterTransactionBoundaries:false,
  PostgresConfig:'{"ReplicationPluginConfig":{"Name":"WAL2JSON","Format":"2"}}'
)
OUTPUT TO PostgreSQLCDCStream;

CREATE TARGET PostgreSQLCDCTarget USING DatabaseWriter (
  ConnectionURL:'jdbc:postgresql://127.0.0.1:55432/postgres?reWriteBatchedInserts=true',
  Username:'striim',
  Password:'<demo-password>',
  Tables:'databasesource.%,databasetarget.%',
  BatchPolicy:'EventCount:1,Interval:1',
  CommitPolicy:'EventCount:1,Interval:1',
  CheckPointTable:'public.chkpoint',
  PreserveSourceTransactionBoundary:true,
  IgnorableExceptionCode:'DUPLICATE_ROW_EXISTS,NO_OP_UPDATE,NO_OP_DELETE'
)
INPUT FROM PostgreSQLCDCStream;

END APPLICATION PostgreSQLCDC;

FilterTransactionBoundaries:false on PostgreSQLReader and PreserveSourceTransactionBoundary:true on DatabaseWriter preserve the source transaction as it moves through the pipeline. Recovery checkpoints are written every five seconds.

During the initial-load overlap window, the target writer can temporarily ignore:

DUPLICATE_ROW_EXISTS, NO_OP_UPDATE, NO_OP_DELETE

Once the overlap has drained, stop and undeploy CDC, remove these temporary ignorable exception codes, then redeploy and restart. Recovery allows processing to resume from the checkpoint.

Deploy and start the application:

USE admin;
DEPLOY APPLICATION PostgreSQLCDC;
START APPLICATION PostgreSQLCDC;

Startup issue: PostgreSQL was stopped

The first start placed the application in HALT because both the source and target connections to 127.0.0.1:55432 were refused. Restart the isolated cluster:

./scripts/01_init_postgres.sh

Then resume the already-deployed application instead of recreating it:

USE admin;
RESUME APPLICATION PostgreSQLCDC;

Startup issue: missing checkpoint table

The next resume attempt connected successfully but reported that chkpoint did not exist. Running 10_create_checkpoint_table.sh fixed the recovery prerequisite. A second RESUME APPLICATION PostgreSQLCDC returned SUCCESS.

Striim Monitor then showed:

The server reported no errors and no backpressure.

13. Generate and verify live CDC activity

Run the prepared transaction generator:

./scripts/04_generate_cdc.sh

An early version connected as striim and failed with:

ERROR: permission denied for table tickets

That was intentional least-privilege behavior: the Striim replication account can read source tables and write target tables, but it should not act as the source application's writer. The generator was corrected to use the trusted local administrative socket. It also deletes any previous demo rows before inserting them, so the demonstration can be repeated safely.

The transaction performs:

1. Cleanup DELETE operations for ticket and comment 900001.

2. An INSERT for an urgent login-failure ticket.

3. An UPDATE from open to pending.

4. An INSERT for an agent comment.

5. A final UPDATE to resolved.

All changes are committed as one transaction. During the successful test, Striim Monitor reported PostgreSQLCDC as RUNNING, approximately 2.13 events per second, and 102 events processed in the preceding minute.

Verify the replicated business row over the same TCP connection used by Striim:

PGPASSWORD='<demo-password>' \
/opt/homebrew/opt/postgresql@16/bin/psql \
  -h 127.0.0.1 -p 55432 -U striim -d postgres \
  -c "SELECT ticket_id, subject, status FROM databasetarget.tickets WHERE ticket_id = 900001;"

The verified result was:

 ticket_id |          subject         |  status
-----------+--------------------------+----------
    900001 | Live demo: login failure | resolved

This proves the full path: a committed source transaction was decoded from PostgreSQL WAL, processed by Striim, written into the target schema, and independently queried from PostgreSQL.

Operational commands

Confirm PostgreSQL is ready

/opt/homebrew/opt/postgresql@16/bin/pg_isready \
  -h 127.0.0.1 \
  -p 55432

Stop the isolated PostgreSQL instance

/opt/homebrew/opt/postgresql@16/bin/pg_ctl \
  -D "/Users/robmoayedzadeh/Documents/Codex/2026-09-02/requirements-1-install-and-configure-an/work/postgres-data" \
  stop

Start it again

/opt/homebrew/opt/postgresql@16/bin/pg_ctl \
  -D "/Users/robmoayedzadeh/Documents/Codex/2026-09-02/requirements-1-install-and-configure-an/work/postgres-data" \
  -l "/Users/robmoayedzadeh/Documents/Codex/2026-09-02/requirements-1-install-and-configure-an/work/postgres.log" \
  start

Reset the target for another rehearsal

./scripts/08_reset_target.sh

This is intentionally destructive to the demo target schema. It does not change the source data.

Key lessons

1. Use DatabaseReader for the initial point-in-time snapshot and PostgreSQLReader for continuous WAL-based CDC.

2. Capture the PostgreSQL LSN before initial load so CDC can safely cover changes made during the snapshot.

3. Compile wal2json against the exact PostgreSQL major version being used.

4. Expect .dylib, not .so, for a PostgreSQL plugin built on macOS.

5. Use local socket authentication for trusted administration and password-protected TCP for Striim.

6. Keep source and target logically separate even when they share one physical PostgreSQL server.

7. Verify migration success at both levels: Striim acknowledgements and database row counts.

8. Create and grant access to DatabaseWriter's checkpoint table before starting a recovery-enabled CDC application.

9. Keep the replication account least-privileged; generate source transactions through an application or administrative identity instead.

10. A halted deployed application can be resumed after fixing its external prerequisite; it does not need to be rebuilt.

References

• What is Striim?

• Running Striim as a process

• Striim system requirements

• PostgreSQL initial load

• Configuring PostgreSQL for PostgreSQL Reader

• Switching from initial load to PostgreSQL CDC

• Database Writer

• wal2json source and installation instructions