# 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](https://www.striim.com/press/webaction-announces-investment-by-summit-partners/) and [Striim leadership background](https://new.striim.com/company/).

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](https://www.striim.com/press/webaction-software-is-now-striim-the-streaming-integration-and-intelligence-platform/).

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?](https://www.striim.com/docs/en/what-is-striim-.html).

### 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](https://www.striim.com/docs/platform/en/pipelines.html).

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](https://www.striim.com/press/microsoft-striim-strategic-collaboration-database-modernization-azure-cloud/) and [Striim's Google Cloud migration partnership](https://www.striim.com/press/striim-deepens-strategic-partnership-with-google-cloud-to-expand-database-migrations-for-google-cloud-customers/).

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:

```text
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](https://www.striim.com/docs/platform/en/system-requirements.html).

## Project files

The reusable scripts are stored under the project directory:

```text
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:

```bash
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:

```bash
brew install postgresql@16
```

Confirm the binaries:

```bash
/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:

```bash
./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:

```text
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:

```bash
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:

```text
FATAL: password authentication failed for user "robmoayedzadeh"
```

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

```bash
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:

| Table | Purpose | Rows |
| --- | --- | --- |
| `customers` | Customer identities and regions | 2,500 |
| `agents` | Support agents and teams | 100 |
| `tickets` | Support requests and lifecycle status | 5,000 |
| `ticket_comments` | Customer and agent conversation | 7,500 |
| `sla_events` | Response, assignment, and resolution timings | 5,000 |
| **Total** |  | **20,100** |

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:

```sql
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:

```sql
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:

```bash
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:

```text
 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:

```bash
./scripts/09_install_wal2json_pg16.sh
```

The script:

1.  Clones the official [`eulerto/wal2json`](https://github.com/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:

```text
/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:

```bash
./scripts/07_create_replication_slot.sh
```

The underlying SQL is:

```sql
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:

```bash
/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:

```text
 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:

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

Striim 5.4 requires JDK 17. Confirm Java before starting:

```bash
java -version
```

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

```bash
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:

```bash
bin/server.sh
```

The successful startup included:

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

The web UI was available at:

```text
http://10.37.129.2:9080
```

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

See [Running Striim as a process](https://www.striim.com/docs/platform/en/running-striim-as-a-process.html) 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:

```bash
./scripts/06_capture_lsn.sh
```

The captured value in this run was:

```text
0/1A7B210
```

It was also saved to:

```text
work/initial-load-start-lsn.txt
```

Display the file with `cat`:

```bash
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](https://www.striim.com/docs/platform/en/switching-from-initial-load-to-continuous-replication-of-postgresql-sources.html).

## 9\. Create the Striim initial-load application

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

```text
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.

```sql
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:

```sql
USE admin;
DEPLOY APPLICATION PostgreSQLInitialLoad;
START APPLICATION PostgreSQLInitialLoad;
```

Both commands returned `SUCCESS`.

Striim Monitor then reported:

| Metric | Result |
| --- | --- |
| Application status | `COMPLETED` |
| Events read by source | 20,100 |
| Events delivered to target | 20,100 |
| Events acknowledged by target | 20,100 |
| Nodes | 1 |

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:

```bash
/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:

```bash
./scripts/10_create_checkpoint_table.sh
```

The script creates and grants access to this table:

```sql
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:

```text
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:

```sql
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:

```text
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:

```sql
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:

```bash
./scripts/01_init_postgres.sh
```

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

```sql
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:

| Application | Status |
| --- | --- |
| `admin.PostgreSQLInitialLoad` | `COMPLETED` |
| `admin.PostgreSQLCDC` | `RUNNING` |

The server reported no errors and no backpressure.

## 13\. Generate and verify live CDC activity

Run the prepared transaction generator:

```bash
./scripts/04_generate_cdc.sh
```

An early version connected as `striim` and failed with:

```text
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:

```bash
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:

```text
 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

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

### Stop the isolated PostgreSQL instance

```bash
/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

```bash
/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

```bash
./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?](https://www.striim.com/docs/platform/en/what-is-striim-.html)
    
*   [Running Striim as a process](https://www.striim.com/docs/platform/en/running-striim-as-a-process.html)
    
*   [Striim system requirements](https://www.striim.com/docs/platform/en/system-requirements.html)
    
*   [PostgreSQL initial load](https://www.striim.com/docs/platform/en/postgresql-initial-load.html)
    
*   [Configuring PostgreSQL for PostgreSQL Reader](https://www.striim.com/docs/platform/en/configuring-postgresql-to-use-postgresql-reader.html)
    
*   [Switching from initial load to PostgreSQL CDC](https://www.striim.com/docs/platform/en/switching-from-initial-load-to-continuous-replication-of-postgresql-sources.html)
    
*   [Database Writer](https://www.striim.com/docs/platform/en/database-writer.html)
    
*   [`wal2json` source and installation instructions](https://github.com/eulerto/wal2json)