Migrate from pg_search to Elasticsearch

This guide moves an existing LobeHub deployment from PostgreSQL pg_search to Elasticsearch while the application remains available. The 3.x migration tool copies LobeHub's 14 searchable entity types into a new Elasticsearch project. It atomically records cursors, counters, and item-level failures in a local checkpoint file, so running the same command with the same state directory resumes the existing attempt.

This workflow is for a database that has already applied the historical pg_search migrations and currently serves search with FTS_SEARCH_PROVIDER=pg_search. For backend selection and the ongoing Elasticsearch data flow, read Full-Text Search.

Environment variables

ES_URL and ES_API_KEY are not needed for the read-only --status command. Operators that keep multiple Elasticsearch targets in one environment can select an explicit pair with --elasticsearch-url-env=<KEY> and --elasticsearch-api-key-env=<KEY>. Both flags are required together. --expected-elasticsearch-host-prefix=<PREFIX> can additionally refuse a mistargeted endpoint before any backfill mutation. The command prints and logs the selected hostname and variable names, never credential values.

Remote Elasticsearch targets must use HTTPS. Plain HTTP is accepted only for loopback addresses such as localhost during local development, so an API key cannot be sent unencrypted to a remote host. The single explicit exception is ES_ALLOW_INSECURE_HTTP=true, which permits plaintext HTTP to a private container hostname without an API key; even then the runtime, reindex, and sync clients all refuse to send an API key over plaintext HTTP.

Before you start

1. Create a new empty Elasticsearch project in the intended production region. LobeHub's search analyzers require the official analysis-icu plugin. Elastic Cloud Serverless includes core analysis plugins; on Elastic Cloud Hosted, enable the provided plugin for the deployment; on self-managed Elasticsearch, install it on every node and restart every node before the first --apply. Docker Compose deployments can instead enable the optional elasticsearch service of the official Compose file, whose image is built with the plugin included; see Run Elasticsearch with Docker Compose.

2. Create and verify a PostgreSQL recovery point before enabling Elasticsearch capture, then rehearse the migration against an isolated database copy and an isolated empty Elasticsearch target. The rehearsal should exercise at least one batch for every non-empty entity, a resume using the same checkpoint, incremental catch-up, and the application smoke tests you will use at cutover.

   On Neon, create a child branch for the rehearsal and use a manual snapshot or the root branch's point-in-time restore window for production recovery. A child branch is an isolated test copy; it is not itself a point-in-time restore point. See Neon's branching and backup and restore documentation.

3. Apply the database migrations for the same LobeHub release:

   bashCopy

   bun run db:migrate
   

   This creates the Outbox sequence, table, ordinary Outbox indexes, and the schema-managed Memory fanout GIN index. It does not create the PostgreSQL change-capture functions or triggers. A PostgreSQL-only deployment can stop here: it still maintains the schema-managed GIN index, but does not pay the trigger or Outbox capture-write overhead.

4. Install the optional PostgreSQL change capture before the first backfill:

   bashCopy

   bun run db:install-fts-search-capture
   

   The installer first strictly validates the schema-managed GIN index (it does not create it), then atomically installs and verifies the capture functions and 16 triggers in one transaction. It is safe to rerun when the complete expected infrastructure already exists; partial, disabled, or unexpected definitions fail closed instead of being silently repaired. Once it succeeds, source changes are recorded in the Outbox immediately. The bun run fts-search:reindex -- --apply command also runs this installer automatically; using the explicit command first is useful when you want capture active while preparing the Elasticsearch target. If you do not intend to enable Elasticsearch, do not run it.

5. Keep the application's search backend on PostgreSQL and keep incremental Elasticsearch draining disabled.

The first production mapping is version v1. The tool creates physical indexes such as lobehub-messages-v1. After all 14 indexes finish and their counts match the durable checkpoints, it creates stable aliases such as lobehub-messages. An alias is only a stable Elasticsearch name; creating it does not change which search backend serves users.

If a stable alias already points to another physical index, the command fails instead of moving it. Online schema-version upgrades require a separately coordinated dual-write migration and are not supported by this initial migration tool.

Run from source

The package command bundles the worker and executes it on Node.js. Use this documented command instead of invoking the TypeScript entrypoint directly so runtime preparation and temporary-file cleanup remain consistent.

Inspect the current state without writing to Elasticsearch:

bun run fts-search:reindex -- --status

Choose a durable local state directory and start a new backfill into an empty Elasticsearch target:

ES_REINDEX_STATE_DIR=.elasticsearch-reindex \
  bun run fts-search:reindex -- --apply --fresh-run --yes

Only the first invocation uses --fresh-run. Resume the same run with the same state directory:

ES_REINDEX_STATE_DIR=.elasticsearch-reindex bun run fts-search:reindex -- --apply --yes

--apply verifies every physical Elasticsearch index and its ICU analysis settings before reading source rows. It also validates the schema-managed Memory fanout GIN index (it never creates this index) and idempotently installs the optional PostgreSQL capture infrastructure (unless it was installed already). The read-only --status command never installs or changes this infrastructure. Before reading source rows, --apply briefly fences writes to all trigger source tables so transactions holding older Outbox revisions finish before the snapshot. If the three-second lock timeout expires, let the long transaction finish and rerun the command. Keep the incremental consumer paused during the initial backfill if needed; PostgreSQL continues to append changes to the Outbox, and the consumer can drain them after the full snapshot is ready. The required --yes acknowledges the mutating operation; it is not an interactive prompt.

The run ID is also stored in each physical index's mapping metadata. A different local checkpoint cannot silently continue writing the same physical indexes. Never run copies of one checkpoint concurrently from multiple machines.

The checkpoint file contains only migration control state and sanitized failure metadata. It never stores database or Elasticsearch credentials. Keep the entire state directory until validation and incremental catch-up finish; moving to another machine requires copying that directory.

For a bounded rehearsal that exercises every entity without completing the full database, run one batch per non-empty entity with bounded concurrency:

ES_REINDEX_STATE_DIR=.elasticsearch-reindex \
  bun run fts-search:reindex -- --apply --yes \
  --expected-elasticsearch-host-prefix=search-dev- \
  --entity-concurrency=4 --bulk-concurrency=2 \
  --batch-size=5000 --entity-batch-size=documents:1000 --bulk-max-bytes=10485760 \
  --max-batches-per-entity=1

This command creates all 14 entity indexes and writes source rows into each non-empty one. If at least one entity has more than the configured batch limit, the run remains in backfilling without creating aliases. A small deployment whose 14 entities all fit within the limit can complete and create aliases in this rehearsal, so always use an isolated empty Elasticsearch target. Re-run the same command to exercise resume behavior, or remove --max-batches-per-entity to intentionally continue until every entity completes.

--entity-concurrency controls independent PostgreSQL entity scans. --bulk-concurrency controls parallel byte-bounded Elasticsearch requests inside each entity batch. Start conservatively and increase them only while PostgreSQL and Elasticsearch remain healthy. --batch-size limits rows per keyset page. --bulk-max-bytes limits each encoded _bulk request; an individual document larger than this limit is stored as an explicit failure. Repeat --entity-batch-size=<entity>:<rows> to use a smaller PostgreSQL page only for source types with materially wider rows, without slowing narrower entities.

Large installations can repeat --entity-range-concurrency=<documents|messages>:<workers> to scan either high-volume entity in parallel ID ranges. Range mode requires the database collation to be bytewise (C, C.UTF-8, or C.utf8); the command checks this before writing. Completed ranges advance the durable cursor in ID order, so an interrupted run may safely replay a later range that Elasticsearch already received. Start with a small worker count and increase it only after measuring sustained database and Elasticsearch behavior. Range concurrency cannot be combined with --max-batches-per-entity.

Repeat --entity=<entity> to limit one invocation to selected entities, for example while tuning documents and messages. A selected invocation never creates aliases early and leaves the run in backfilling while any unselected entity is incomplete. Resume once without --entity after all selected work finishes so the command can perform the complete 14-entity reconciliation and create the stable aliases.

Transient request failures are retried with exponential backoff before the batch cursor advances. The defaults are four retries, a 500 ms base delay, and a 30-second request timeout. Override them with --max-request-retries, --retry-base-delay-ms, and --request-timeout-ms when required.

Every apply or resume session appends sanitized operational events to <state-dir>/runs/<run-id>/events.jsonl and atomically replaces <state-dir>/runs/<run-id>/summary.json. The log records entity and bulk timings, byte and document counts, retries, cursors, failures, and final state. It never records database or Elasticsearch URLs, credentials, or document source text. Keep these private files with the checkpoint when moving or archiving a run.

The backfill command exports OpenTelemetry only when ENABLE_TELEMETRY is set. In that case, pass --telemetry-environment=<development|preview|production> and configure either OTEL_EXPORTER_OTLP_ENDPOINT or both signal-specific metrics and traces endpoints. Otherwise leave telemetry disabled and use the local event and summary files. Always set the same explicit ES_INDEX_NAMESPACE for the migration command, incremental consumer, and application runtime; the command has no development fallback for a missing namespace.

Run from the Docker image

The official image includes /app/fts-search-elasticsearch-reindex.cjs:

mkdir -m 700 -p .elasticsearch-reindex
docker run --rm --user "$(id -u):$(id -g)" --env-file .env \
  -e ES_REINDEX_STATE_DIR=/data/elasticsearch-reindex \
  -v "$PWD/.elasticsearch-reindex:/data/elasticsearch-reindex" \
  lobehub/lobehub:latest /app/fts-search-elasticsearch-reindex.cjs --status
docker run --rm --user "$(id -u):$(id -g)" --env-file .env \
  -e ES_REINDEX_STATE_DIR=/data/elasticsearch-reindex \
  -v "$PWD/.elasticsearch-reindex:/data/elasticsearch-reindex" \
  lobehub/lobehub:latest /app/fts-search-elasticsearch-reindex.cjs --apply --fresh-run --yes

The bind mount is required for resume after a --rm container exits. Running with the host user's ID lets the process write the bind-mounted directory without changing its ownership.

Run from Docker Compose

The official docker-compose/deploy/docker-compose.yml already defines the same command as the fts-search-reindex service, wired to the Compose PostgreSQL, the optional in-network elasticsearch service, and a named fts-search-reindex-state volume for the checkpoint:

docker compose run --rm fts-search-reindex --status
docker compose run --rm fts-search-reindex --apply --fresh-run --yes

Extra arguments are passed through, so the rehearsal, --entity, --skip-failure, and resume commands above work unchanged. The service uses the ES_URL, ES_ALLOW_INSECURE_HTTP, and ES_INDEX_NAMESPACE values from .env; with an external Elastic Cloud target, set ES_URL and ES_API_KEY there instead, leave ES_ALLOW_INSECURE_HTTP unset, and keep the elasticsearch profile out of COMPOSE_PROFILES; the service depends only on PostgreSQL, so the bundled node is neither built nor started. The complete Compose sequence, including enabling the node and the sync worker, is in Run Elasticsearch with Docker Compose.

Start incremental synchronization

After the reindex status is ready_for_incremental_sync, set FTS_SEARCH_SYNC_ENABLED=true and run the bounded consumer:

bun run fts-search:sync -- --max-steps=8 --yes

One invocation processes at most eight drain steps and then exits. hasMore: true in the final JSON means the bound was reached safely; run it again. Schedule the same command with cron, a container job, or another process supervisor so it continues to run while Elasticsearch search is enabled. Omitting --max-steps processes one step; accepted values are from 1 through 100.

For a long-running worker instead of an external scheduler, add --interval-seconds=<1-3600>: the command repeats the same bounded drain, continues immediately while hasMore is true, sleeps for the interval otherwise, and stops cleanly on SIGINT / SIGTERM after finishing the drain step in progress (not the whole bound), so a supervisor's stop timeout only needs to cover one step. A run that leaves failed or dead-letter work still exits non-zero, so the supervisor's restart policy and logs surface it. The Compose fts-search-sync service (profile elasticsearch-sync) uses this mode. The consumer validates the PostgreSQL capture definitions and every Elasticsearch write alias before claiming work. Missing or stale capture infrastructure, an invalid alias, retryable write failure, or dead-letter work produces a non-zero exit code. It never installs triggers and never deletes failed work automatically.

The official image includes /app/fts-search-elasticsearch-sync.cjs for the same bounded operation:

docker run --rm --env-file .env \
  -e FTS_SEARCH_SYNC_ENABLED=true \
  -e MIGRATION_DB=1 \
  lobehub/lobehub:latest /app/fts-search-elasticsearch-sync.cjs --max-steps=8 --yes

Keep scheduling the consumer and run bun run fts-search:reindex -- --status until pending, ready, retrying, inFlight, dead, and revisionLag are all zero. Recheck that state immediately before switching queries. Then set FTS_SEARCH_PROVIDER=elasticsearch and redeploy the application. Keep FTS_SEARCH_PROVIDER=pg_search until catch-up is complete. Elasticsearch failures remain visible to callers; the search path does not silently fall back to PostgreSQL or ilike.

After deployment, keep the old BM25 indexes and pg_search extension during an agreed observation window. Confirm that successful backend operations are attributed to elasticsearch, no new pg_search operations appear, the Outbox repeatedly returns to zero lag with no dead work, and the product search surfaces your deployment uses pass smoke tests. While the old database objects still exist, rollback is limited to restoring FTS_SEARCH_PROVIDER=pg_search and redeploying; the Outbox consumer may continue running while you diagnose Elasticsearch. If you stop that consumer after capture has been installed, source changes continue accumulating in the Outbox until it resumes.

Resume and verify

• After a process crash or temporary Elasticsearch failure, re-run the --apply --yes command with the same state directory but without --fresh-run. Completed entities are skipped and the next batch starts after the local cursor. This is safe while the optional capture infrastructure remains installed, because changes made during the pause are retained in the Outbox.
• Request-level failures do not advance the cursor. If one concurrent bulk succeeds and another fails, the entire database batch is replayed safely on resume. Item-level failures are recorded in the local checkpoint and replayed before an entity can complete.
• The tool refuses to create aliases while any entity or item-level failure is incomplete.
• The final status is ready_for_incremental_sync. At this point the full snapshot, aliases, and the Outbox high-water boundary exist, but user queries must still remain on PostgreSQL.

The high-water revision is an observation point for catch-up monitoring, not a committed database snapshot. An incremental consumer must process every queued row and must never discard rows only because their revision is at or below that value.

Run --status and confirm all 14 entities show completed, every failedCount is 0, and the Outbox dead count is 0 before starting the incremental consumer documented above. A paused incremental consumer may leave queued Outbox work; that is expected while backfill is running and does not mean source-change capture is paused. The status output lists unresolved document IDs, retryability, and attempt counts without persisting or printing Elasticsearch error reasons that may contain source text.

After correcting a rejected source document or mapping, run --apply --yes again to retry it. If an operator deliberately accepts that one document with a non-retryable failure will be absent from the initial Elasticsearch snapshot, resolve the blocker explicitly and then resume:

ES_REINDEX_STATE_DIR=.elasticsearch-reindex \
  bun run fts-search:reindex -- --skip-failure=agents:document-id --yes
ES_REINDEX_STATE_DIR=.elasticsearch-reindex bun run fts-search:reindex -- --apply --yes

Only non-retryable failures can be skipped, and skipping does not count the document as indexed. Use it only after reviewing the source record and accepting that it will not be searchable until a later source change or incremental repair writes it.

To pause incremental Elasticsearch writes during backfill, pause the deployment's incremental consumer using its normal worker setting. This does not stop the installed PostgreSQL capture triggers: source changes continue to accumulate in the Outbox and can be drained when the consumer is enabled. Do not disable or remove the triggers, or truncate the Outbox, while a reindex is in progress.

Remove pg_search before the Neon deadline

After Elasticsearch has served search reliably through your observation window and a current PostgreSQL recovery point is available, inspect the remaining LobeHub-managed objects:

bun run scripts/pgSearchCleanup/index.ts --status

Then remove them without copying SQL from this guide:

bun run scripts/pgSearchCleanup/index.ts --apply --yes

Use a direct DATABASE_URL, not a transaction-pool endpoint. The command refuses to apply before FTS_SEARCH_PROVIDER=elasticsearch, refuses unrecognized BM25 indexes, removes the known indexes concurrently, and then removes the extension without CASCADE. It is safe to rerun after an interrupted cleanup. It does not remove the Elasticsearch Outbox, capture triggers, or incremental consumer.

The official image includes /app/fts-search-pg-search-cleanup.cjs:

docker run --rm --env-file .env \
  lobehub/lobehub:latest /app/fts-search-pg-search-cleanup.cjs --status
docker run --rm --env-file .env \
  lobehub/lobehub:latest /app/fts-search-pg-search-cleanup.cjs --apply --yes

Affected Neon deployments must complete this cleanup before September 21, 2026. Other PostgreSQL providers can keep pg_search installed, but removing the retired objects avoids continuing to maintain unused BM25 indexes.