Archiving PostgreSQL data with Coldctl v1

A Boolescale walkthrough with ten million rows

A growing PostgreSQL database eventually raises a practical question: which historical records can move out of the application's everyday working set, and how do we preserve them in a form we can verify and recover?

For our first Coldctl workflow, we used Boolescale, a fictional company with two familiar kinds of operational data: user events and network activity. We provisioned ten million synthetic rows, connected a locally installed agent, defined retention policies, exported eligible records to Parquet, verified the archives, and synchronized lifecycle metadata to Coldctl Cloud.

The result was 4,771,532 archived rows across two completed, verified jobs. Database contents and archive files stayed on the client infrastructure. The Cloud console received metadata describing the sources, policies, and archive jobs.

This walkthrough also exposed the work still ahead. A restore reached 847,000 committed rows before we stopped it because throughput was too slow. Built-in scheduling and incremental selection for recurring exports are not implemented in this release. Source records were retained throughout the workflow.

“V1” here describes the first product workflow. The executable used for this demonstration reports coldctl 0.1.1; this is not a claim that a 1.0.0 package has shipped. The commands below use that installed executable directly in Windows PowerShell. Selected terminal excerpts come from the October 6, 2026 demonstration, with credentials and personal machine identifiers omitted.

The environment and dataset

Boolescale's source database was boolescale_main, hosted in a local PostgreSQL 15 Docker container. A dedicated Docker volume preserved its data across container restarts. The client connected through 127.0.0.1:15441.

The rows span roughly two years. Both tables use a bigint primary key named id, an index on created_at, and an index on (tenant_id, created_at). UUIDs, decimal values, JSONB, booleans, and nullable columns make the dataset more representative than a narrow table containing only an ID and a timestamp.

The source database occupied approximately 4,141 MB after loading, index creation, and statistics collection. These are synthetic demo records, not customer data. The fixture was generated in PostgreSQL in batches of 500,000 rows, followed by index creation and statistics collection.

We separated the permissions needed for different tasks. The source reader could connect, inspect metadata, and select rows; it could not delete records or create source tables. Restoration used a different role and database, with permission to create dedicated target tables.

That separation matters because export and recovery have different responsibilities. Reading historical data should not require granting the archive agent write access to the source database.

Connect the agent to PostgreSQL

We began with fresh local Coldctl state, preserving the previous state separately. A new installation starts with:

coldctl --version
coldctl init
coldctl status

The version output was:

coldctl 0.1.1

Next, we registered the source using a password-free connection URL and an environment reference for its password:

$credential = Get-Credential -UserName boolescale_reader -Message 'Enter the PostgreSQL reader password'
$env:BOOLESCALE_PG_PASSWORD = $credential.GetNetworkCredential().Password

coldctl source add postgres --name boolescale --url 'postgres://boolescale_reader@127.0.0.1:15441/boolescale_main?sslmode=disable' --password-env BOOLESCALE_PG_PASSWORD
coldctl source test boolescale

The explicit sslmode=disable belongs to this local Docker fixture. A remote deployment should use its required TLS configuration.

The connection test confirmed the endpoint and role:

✓ Connected to source 'boolescale'.
Database  boolescale_main
User      boolescale_reader
PostgreSQL 15.14 (Debian 15.14-1.pgdg13+1)

Coldctl stores the password environment variable's name in its configuration. The variable's value is resolved when connecting. It needs to be available in each process that runs database operations; setting it in one interactive shell does not configure credentials for a future scheduled task.

Discover the tables before choosing a policy

Schema discovery and archival analysis answer different questions:

coldctl source discover boolescale
coldctl analyze boolescale

Discovery describes columns, nullability, primary keys, indexes, and catalog row estimates. Analysis helps an operator identify large tables and possible retention columns.

The analysis reported:

public.user_events
  Rows: ~5000006 | Total: 2.1 GiB | Table/TOAST: 1.7 GiB | Indexes: 364.7 MiB
  Primary key: id
  Time candidate: created_at (timestamp with time zone)
    Leading B-tree/BRIN indexes: user_events_created_at_idx

public.network_activity
  Rows: ~4999978 | Total: 1.9 GiB | Table/TOAST: 1.6 GiB | Indexes: 364.7 MiB
  Primary key: id
  Time candidate: created_at (timestamp with time zone)
    Leading B-tree/BRIN indexes: network_activity_created_at_idx

This excerpt omits other timestamp candidates and their warnings. For example, processed_at was nullable and lacked a leading time-range index. That does not prevent a policy based on the indexed created_at column, but it is useful information if a developer chooses a different retention rule.

The ~ is significant. These row counts came from PostgreSQL statistics; they were not exact counts obtained by scanning five million records. We had independently verified the fixture's exact counts during provisioning.

Likewise, total table storage includes indexes and PostgreSQL's auxiliary storage for large values. It does not predict the eventual archive size. Analysis did not measure timestamp ranges or count eligible rows, and it did not invent a business retention rule.

At this point, the developer still has a decision to make: what does the application consider historical, and can those records change?

Define the retention rules

For Boolescale's demonstration, we selected two rules:

The user-event status filter excludes records still marked pending. The network table contains completed historical observations, so its demo policy uses the timestamp alone.

We first registered a local archive destination and checked that Coldctl could use it:

coldctl destination add local --name boolescale-archive --path 'C:\ColdctlArchives\Boolescale'
coldctl destination test boolescale-archive

The destination test exercised the directory rather than merely accepting a path:

✓ Destination 'boolescale-archive' passed write, sync, read-back, and cleanup checks.
  Path: C:\ColdctlArchives\Boolescale

We then saved the two policies:

coldctl policy create --name boolescale-user-events --source boolescale --destination boolescale-archive --schema public --table user_events --time-column created_at --older-than-days 365 --equals-column status --equals-value completed --batch-size 1000

coldctl policy create --name boolescale-network-activity --source boolescale --destination boolescale-archive --schema public --table network_activity --time-column created_at --older-than-days 365 --batch-size 1000

The release accepts batch sizes from 1 through 1,000. An earlier attempt used 5,000 and failed with batch-size must be 1–1000; consequently, those policies were not saved. The commands here use the corrected value.

Policy creation saves configuration. It does not export records, delete rows, or decide whether the retention rule is appropriate for the application.

Validate and inspect the archive plan

Before moving data, we validated the policies and inspected the plans:

coldctl policy validate boolescale-user-events
coldctl policy validate boolescale-network-activity
coldctl archive plan boolescale-user-events
coldctl archive plan boolescale-network-activity

The user-event plan included:

Policy       boolescale-user-events
Source       boolescale
Table        public.user_events
Time column  created_at
Cutoff (UTC) 2025-10-06 08:06:34.13606+00
Primary key  id
Destination  C:\ColdctlArchives\Boolescale
Batch rows   1000
Estimated rows ~2242939 (planner estimate)
Delete       NO
Equality     status = completed

The cutoff is relative to the time of planning, so a later command can show a slightly different timestamp. An archive job captures its own plan for execution and subsequent resume.

The plan also warned that PostgreSQL selected a sequential scan. Having a time index does not guarantee the planner will use it: a policy selecting a large fraction of the table can still make a sequential scan attractive. The warning gives the operator a reason to review source load and query planning before a larger run.

Two other conditions deserve attention. Exports use snapshots taken per batch, rather than one shared snapshot for the entire job. Eligible rows and their membership must remain stable, including the absence of backdated inserts. Also, supported numeric, date, timestamp, UUID, and JSON values are preserved as text with PostgreSQL type metadata; this release rejects rows exceeding its 64 KiB text-array limit instead of truncating them.

For our frozen synthetic source, the stability condition was straightforward. For a live application, the developer must establish it before exporting.

Start with a bounded export

We began with 10,000 eligible rows from each table:

coldctl archive run boolescale-user-events --source-stability immutable-rows --max-rows 10000

coldctl archive run boolescale-network-activity --source-stability immutable-rows --max-rows 10000
coldctl jobs list

The immutable-rows option is an operator assertion that the rows and their eligibility will remain stable. It does not freeze the database on the operator's behalf.

For user events, the first invocation ended with:

✓ Archive job 4813a1db-4a62-4b5f-b28f-9dea21a88f02: Paused.
Rows: 10000 | Parquet files: 10 | Bytes: 1485244
Directory: C:\ColdctlArchives\Boolescale\4813a1db-4a62-4b5f-b28f-9dea21a88f02
Source rows were not deleted.

The network job likewise paused after 10,000 rows, producing ten Parquet files totaling 1,820,907 bytes.

“Paused” was expected. We had imposed an execution limit while more eligible records remained. The bounded run let us inspect actual files and progress before committing to the full export. Each job kept its own directory and checkpoint.

Resume the same jobs to completion

We kept the existing job IDs rather than starting new exports:

$EventsJob = '4813a1db-4a62-4b5f-b28f-9dea21a88f02'
$NetworkJob = 'fafe12dc-c81e-49ad-a01b-f75c681bfb73'

coldctl archive resume $EventsJob --confirm-original-source --max-rows 0
coldctl archive resume $NetworkJob --confirm-original-source --max-rows 0

Use the IDs returned by your own runs when reproducing the workflow. The IDs above identify this demonstration.

Here, --max-rows 0 removes the saved row limit. --confirm-original-source acknowledges that the operator has checked the original database and the continued stability of the eligible data.

The completed jobs recorded:

The total is approximately 757.4 MiB of Parquet output. It represents eligible subsets of the source tables, so comparing it with the entire 4.1 GB database would not establish a compression ratio or a storage-saving percentage. The source database also retained all its rows.

The resume progress output reported 568.6 seconds for user events and 761.5 seconds for network activity. These are elapsed times reported by those invocations in one local demo, not a controlled benchmark or a throughput promise. Machine load, row width, batch size, disk behavior, and source query plans all affect execution time.

Verify the completed archives

The first 10,000-row invocations were paused, so they were not yet ready for completed-archive verification. After resuming to completion, we ran:

coldctl archive verify $EventsJob
coldctl archive verify $NetworkJob

Both passed:

✓ Verified 4813a1db-4a62-4b5f-b28f-9dea21a88f02: 2262482 rows in 2263 files.
✓ Verified fafe12dc-c81e-49ad-a01b-f75c681bfb73: 2509050 rows in 2510 files.

Verification operates on the archive offline, without source database credentials. It provides an integrity check for the exported artifact. It does not establish application-level retention correctness, and it does not replace a recovery exercise against PostgreSQL.

Exercise restoration in a separate database

We provisioned boolescale_restore and a dedicated demo_restore schema. The restore role could create its own target tables there, keeping recovery activity separate from boolescale_main.

The target connection was configured as another source entry:

$restoreCredential = Get-Credential -UserName boolescale_restorer -Message 'Enter the PostgreSQL restore password'
$env:BOOLESCALE_RESTORE_PASSWORD = $restoreCredential.GetNetworkCredential().Password

coldctl source add postgres --name boolescale-restore --url 'postgres://boolescale_restorer@127.0.0.1:15441/boolescale_restore?sslmode=disable' --password-env BOOLESCALE_RESTORE_PASSWORD
coldctl source test boolescale-restore

The user-event restore command was:

coldctl archive restore $EventsJob --target boolescale-restore --schema demo_restore --table user_events --confirm-separate-target

Coldctl verifies the archive before restoring, creates a dedicated target table, and commits checkpoints in a journal in the target database. Repeating the same restore command can resume committed work. Existing target tables require their matching restore journal; the agent does not silently take over an unrelated table.

Our first mistake was invoking validate-restore before restoring anything. It failed because the target tables did not exist. Validation compares restored data with its archive; it does not create the target or perform restoration. The error message combined several possible causes and could have been clearer about the missing table.

The subsequent user-event restore made progress, but it was slow. After requesting shutdown with Ctrl+C, the terminal reported:

Shutdown requested; stopping at the next safe batch boundary.
Restore committed: 847000 rows, 847 batches | 2665.1s elapsed
Restore paused: 847000 rows, 847 batches | 2665.6s elapsed
Restore paused: 847000 rows, 847 batches; values validated: false. Integrity basis: existing_local_journal

That is approximately 44.4 minutes for the recorded invocation. The restore remained partial, and the final output explicitly reported values validated: false. This demonstration does not establish a successful full restore or completed value validation for either table.

Code inspection identified an immediate throughput limitation: the current implementation prepares an INSERT statement for a batch, then sends and awaits each row individually. Millions of rows therefore require millions of database round trips. Bulk loading each batch while preserving validation and transactional checkpoints is a priority for the next iteration.

The following commands describe how to continue the recovery exercise; they are not completed outcomes from this run:

# Resume the partial user-event restore using the same target.
coldctl archive restore $EventsJob --target boolescale-restore --schema demo_restore --table user_events --confirm-separate-target

# Restore the network archive into its own target table.
coldctl archive restore $NetworkJob --target boolescale-restore --schema demo_restore --table network_activity --confirm-separate-target

# After both restores complete, compare their values with the archives.
coldctl archive validate-restore $EventsJob --target boolescale-restore --schema demo_restore --table user_events
coldctl archive validate-restore $NetworkJob --target boolescale-restore --schema demo_restore --table network_activity

Preserving the target tables and journals preserves the checkpoints needed to resume. Archive completion and restore completion are separate outcomes.

Synchronize metadata to Coldctl Cloud

With both archive jobs completed and verified, we previewed the metadata payload:

coldctl agent sync --dry-run

The preview included the agent version, registered sources and destination, retention policy definitions, archive row and byte counts, file counts, job status, and verification timestamps. The job records showed completed with a verified stage.

It is important to read that status in context. The archive jobs were completed and verified; the interrupted restore was a different operation. Seeing a completed archive in Cloud did not mean its restore had completed.

After supplying a lifecycle-scoped workspace token, we synchronized to the Cloud service:

$cloudToken = Read-Host 'Enter the Cloud workspace token' -AsSecureString
$env:COLDCTL_CLOUD_TOKEN = [System.Net.NetworkCredential]::new('', $cloudToken).Password
coldctl agent sync --cloud-url https://www.coldctl.com

The terminal confirmed:

Lifecycle metadata synchronized. Database contents and credentials stayed local.

The operator then confirmed that the metadata was visible in the production Cloud UI. This demonstrates local execution with Cloud visibility: archive data and database credentials stay on the client, while the workspace receives allowlisted lifecycle metadata. Metadata still includes operational names and identifiers, which is why the dry-run preview is useful before transmission.

Cloud sync does not upload the Parquet archives or enable the Cloud service to execute agent commands. In this version, it is an explicit CLI operation.

What happens when the operator asks for scheduling

After the manual workflow, the obvious question was how to make it recurring.

Coldctl 0.1.1 does not include a built-in scheduler. Windows Task Scheduler or cron could invoke commands externally, but that alone would not provide a complete recurring archival workflow.

The current retention policies select old rows that remain in PostgreSQL. A fresh archive run can therefore select records already exported by an earlier job. The CLI requires acknowledgement for repeat runs, but acknowledgement does not provide incremental selection or deduplication.

Before enabling recurring archival, we need to define how the next run selects new work: for example, through durable progress tracking and clearly specified behavior for late-arriving records. Credential handling, retry behavior, execution history, and coordination between workers also belong in that design. Simply running the same command every night would not solve those problems.

Cloud metadata sync can be scheduled separately, provided the scheduled process has the correct state path and credential access. For this demonstration, synchronization remained manual.

What this first workflow establishes

The Boolescale exercise demonstrated a concrete local archival path on a ten-million-row PostgreSQL fixture: discover schema, inspect statistics, choose retention rules, test a destination, export in bounded batches, resume, verify, and report the lifecycle metadata to Cloud.

The current agent supports PostgreSQL and local Parquet storage. Support for additional databases and storage destinations is part of the product direction, rather than a capability exercised here.

For a team evaluating the first version, the practical outcome is a verifiable archive with local control and Cloud visibility. The next development priorities are equally concrete: improve restore throughput, make recovery errors clearer, and establish incremental execution before adding a dependable scheduler.