Migrate vega Attic metadata from SQLite to PostgreSQL

nimmo commented

2026-07-01 16:14:35 +01:00

Owner

Goal

Move the production Attic instance on vega from local SQLite metadata to PostgreSQL before introducing dual-node serving.

Scope

This issue covers the work from projects/attic-postgres-lyra-rollout.md Phase 2.

Acceptance Checklist

Stop atticd on vega cleanly.
Back up /var/lib/atticd/server.db before changing anything.
Confirm whether Attic supports a SQLite to PostgreSQL migration path.
If supported, migrate metadata into PostgreSQL.
If unsupported, explicitly decide whether metadata rebuild is acceptable before proceeding.
Switch vega to PostgreSQL-backed Attic and start it successfully.
Confirm known cache paths still serve correctly.

Procedure Outline

Stop atticd on vega.
Back up the SQLite DB.
Determine the supported migration path, if any.
Perform the migration or documented fallback.
Reconfigure vega to use PostgreSQL.
Start atticd and validate both HTTP responses and nix copy reads.

Verification

systemctl status atticd
journalctl -u atticd -n 100 --no-pager
curl -I https://attic.nimmog.uk/nixos/nix-cache-info
curl https://attic.nimmog.uk/nixos/<known-good-hash>.narinfo
nix copy --from https://attic.nimmog.uk/nixos /nix/store/<known-good-path>

Known-good paths called out in the note:

6nli808zpb1k9vk7cz175m2ri78455rl-ollama-0.30.7
4da5p6rwzj366cfsq0xslgqi1q7j5nbr-cuda12.9-libcublas-12.9.1.4-lib
dwpxiy6qmw74y2fchx4jfzvpsh3q7p9a-paseo-desktop-0.1.102-beta.1

Rollback

If migration fails:

Stop atticd.
Restore the SQLite configuration.
Restore the SQLite backup if needed.
Start atticd on vega.
Re-test public cache reads.

## Goal Move the production Attic instance on `vega` from local SQLite metadata to PostgreSQL before introducing dual-node serving. ## Scope This issue covers the work from `projects/attic-postgres-lyra-rollout.md` Phase 2. ## Acceptance Checklist - Stop `atticd` on `vega` cleanly. - Back up `/var/lib/atticd/server.db` before changing anything. - Confirm whether Attic supports a SQLite to PostgreSQL migration path. - If supported, migrate metadata into PostgreSQL. - If unsupported, explicitly decide whether metadata rebuild is acceptable before proceeding. - Switch `vega` to PostgreSQL-backed Attic and start it successfully. - Confirm known cache paths still serve correctly. ## Procedure Outline 1. Stop `atticd` on `vega`. 2. Back up the SQLite DB. 3. Determine the supported migration path, if any. 4. Perform the migration or documented fallback. 5. Reconfigure `vega` to use PostgreSQL. 6. Start `atticd` and validate both HTTP responses and `nix copy` reads. ## Verification ```bash systemctl status atticd journalctl -u atticd -n 100 --no-pager curl -I https://attic.nimmog.uk/nixos/nix-cache-info curl https://attic.nimmog.uk/nixos/<known-good-hash>.narinfo nix copy --from https://attic.nimmog.uk/nixos /nix/store/<known-good-path> ``` Known-good paths called out in the note: - `6nli808zpb1k9vk7cz175m2ri78455rl-ollama-0.30.7` - `4da5p6rwzj366cfsq0xslgqi1q7j5nbr-cuda12.9-libcublas-12.9.1.4-lib` - `dwpxiy6qmw74y2fchx4jfzvpsh3q7p9a-paseo-desktop-0.1.102-beta.1` ## Rollback If migration fails: 1. Stop `atticd`. 2. Restore the SQLite configuration. 3. Restore the SQLite backup if needed. 4. Start `atticd` on `vega`. 5. Re-test public cache reads.

nimmo added this to the Attic PostgreSQL Migration and Lyra Rollout milestone

2026-07-01 16:14:35 +01:00

nimmo added the

host:vega

project:attic-postgres-lyra-rollout

labels

2026-07-01 16:14:35 +01:00

nimmo commented

2026-07-06 15:00:38 +01:00

Author

Owner

Initial migration-path research is done.

Findings so far:

The NixOS module is now wired to read the Attic database URL from secrets/attic.yaml, and a staged vega activation succeeded with the rendered atticd-env carrying ATTIC_SERVER_DATABASE_URL.
Direct PostgreSQL connectivity from vega using the real Attic credentials was tested successfully; the target database is reachable and currently empty (Did not find any relations.).
Upstream Attic clearly supports PostgreSQL as a backend, and atticd db-migrations / the server startup path can apply schema migrations to the configured database.
I have not found evidence yet of a built-in SQLite -> PostgreSQL metadata migration command or documented backend-conversion workflow in the packaged CLI or upstream source.

Next step is to treat the migration itself as an explicit cutover exercise:

inspect the current SQLite database shape/content,
confirm whether a manual export/import is realistic,
if not, decide whether a fresh metadata start is acceptable before switching production over.

Initial migration-path research is done. Findings so far: - The NixOS module is now wired to read the Attic database URL from `secrets/attic.yaml`, and a staged `vega` activation succeeded with the rendered `atticd-env` carrying `ATTIC_SERVER_DATABASE_URL`. - Direct PostgreSQL connectivity from `vega` using the real Attic credentials was tested successfully; the target database is reachable and currently empty (`Did not find any relations.`). - Upstream Attic clearly supports PostgreSQL as a backend, and `atticd db-migrations` / the server startup path can apply schema migrations to the configured database. - I have not found evidence yet of a built-in SQLite -> PostgreSQL metadata migration command or documented backend-conversion workflow in the packaged CLI or upstream source. Next step is to treat the migration itself as an explicit cutover exercise: 1. inspect the current SQLite database shape/content, 2. confirm whether a manual export/import is realistic, 3. if not, decide whether a fresh metadata start is acceptable before switching production over.

nimmo commented

2026-07-06 15:02:17 +01:00

Author

Owner

Live-state check on vega suggests the actual SQLite -> PostgreSQL cutover has not happened yet.

Observed today:

atticd.service has been continuously running since 2026-07-05 19:35:52 BST.
/var/lib/atticd/server.db is 995897344 bytes, owned by atticd:atticd, and its mtime is 2026-07-06 14:10.
The target PostgreSQL database was probed separately from vega and is still empty (Did not find any relations.).

Inference:

Because the SQLite DB file was modified after the current service start time, and the PostgreSQL target is still empty, production Attic on vega is almost certainly still writing to / serving from SQLite.
The secret-backed PostgreSQL wiring is in place, but it has not been cut over in a way that moved live metadata into Postgres.

That means issue #6 remains the first real metadata migration step, not just a post-migration cleanup.

Live-state check on `vega` suggests the actual SQLite -> PostgreSQL cutover has not happened yet. Observed today: - `atticd.service` has been continuously running since `2026-07-05 19:35:52 BST`. - `/var/lib/atticd/server.db` is `995897344` bytes, owned by `atticd:atticd`, and its mtime is `2026-07-06 14:10`. - The target PostgreSQL database was probed separately from `vega` and is still empty (`Did not find any relations.`). Inference: - Because the SQLite DB file was modified after the current service start time, and the PostgreSQL target is still empty, production Attic on `vega` is almost certainly still writing to / serving from SQLite. - The secret-backed PostgreSQL wiring is in place, but it has not been cut over in a way that moved live metadata into Postgres. That means issue #6 remains the first real metadata migration step, not just a post-migration cleanup.

nimmo commented

2026-07-06 15:03:20 +01:00

Author

Owner

Proposed cutover shape for this issue, based on the live-state check:

Recommended migration approach

Use pgloader for the one-off SQLite -> PostgreSQL metadata copy rather than expecting Attic itself to convert between engines.
Keep the existing SQLite file as the rollback anchor until Postgres-backed reads are verified.

Suggested execution order on vega

Stop atticd cleanly.
Back up /var/lib/atticd/server.db.
Inspect the SQLite DB quickly (.tables, row counts) so we know what was copied.
Run pgloader from the SQLite file into the target PostgreSQL DB.
Start atticd with the PostgreSQL-backed configuration and let Attic apply normal schema migrations there if needed.
Verify nix-cache-info, known-good .narinfo lookups, and a nix copy --from read.
Only after successful verification, decide whether to retain or archive the old SQLite file.

Why this is the current recommendation

Upstream Attic supports both SQLite and PostgreSQL and runs schema migrations on whichever backend it is configured to use.
I still have not found any built-in Attic command for cross-backend data migration.
The production SQLite file is nearly 1 GB and is still being actively written, so this should be treated as a real metadata migration rather than a disposable reset.

Proposed cutover shape for this issue, based on the live-state check: Recommended migration approach - Use `pgloader` for the one-off SQLite -> PostgreSQL metadata copy rather than expecting Attic itself to convert between engines. - Keep the existing SQLite file as the rollback anchor until Postgres-backed reads are verified. Suggested execution order on `vega` 1. Stop `atticd` cleanly. 2. Back up `/var/lib/atticd/server.db`. 3. Inspect the SQLite DB quickly (`.tables`, row counts) so we know what was copied. 4. Run `pgloader` from the SQLite file into the target PostgreSQL DB. 5. Start `atticd` with the PostgreSQL-backed configuration and let Attic apply normal schema migrations there if needed. 6. Verify `nix-cache-info`, known-good `.narinfo` lookups, and a `nix copy --from` read. 7. Only after successful verification, decide whether to retain or archive the old SQLite file. Why this is the current recommendation - Upstream Attic supports both SQLite and PostgreSQL and runs schema migrations on whichever backend it is configured to use. - I still have not found any built-in Attic command for cross-backend data migration. - The production SQLite file is nearly 1 GB and is still being actively written, so this should be treated as a real metadata migration rather than a disposable reset.

nimmo commented

2026-07-06 15:11:09 +01:00

Author

Owner

Migration attempt found the next operational blocker:

pgloader failed with CANTOPEN on /var/lib/atticd/server.db.
The SQLite file is mode 0600 and owned by atticd:atticd, so running pgloader as the regular nimmo user cannot read it directly.

Implication:

The actual copy step needs either root privileges, a temporary readable copy, or ACL/ownership adjustment before pgloader can open the SQLite source file.
This is a file-permission issue on the SQLite source, not evidence of a PostgreSQL-side failure.

Migration attempt found the next operational blocker: - `pgloader` failed with `CANTOPEN` on `/var/lib/atticd/server.db`. - The SQLite file is mode `0600` and owned by `atticd:atticd`, so running `pgloader` as the regular `nimmo` user cannot read it directly. Implication: - The actual copy step needs either root privileges, a temporary readable copy, or ACL/ownership adjustment before `pgloader` can open the SQLite source file. - This is a file-permission issue on the SQLite source, not evidence of a PostgreSQL-side failure.

nimmo commented

2026-07-06 15:12:46 +01:00

Author

Owner

Follow-up on the migration attempt:

On vega, user nimmo has primary group users, not a matching nimmo group.
The temporary copy in /tmp stayed root-owned because it was created via sudo, so the subsequent chown nimmo:nimmo failed and chmod as the unprivileged user also failed.

Practical fix:

Create the temporary migration copy with the final owner/mode in one step (for example via install -o nimmo -g users -m 600 ...) before running pgloader.

Follow-up on the migration attempt: - On `vega`, user `nimmo` has primary group `users`, not a matching `nimmo` group. - The temporary copy in `/tmp` stayed root-owned because it was created via `sudo`, so the subsequent `chown nimmo:nimmo` failed and `chmod` as the unprivileged user also failed. Practical fix: - Create the temporary migration copy with the final owner/mode in one step (for example via `install -o nimmo -g users -m 600 ...`) before running `pgloader`.

nimmo commented

2026-07-06 15:14:40 +01:00

Author

Owner

Another concrete migration finding:

pgloader failed when creating PostgreSQL tables from the SQLite schema because it generated invalid types such as timestamp_with_timezone_text.
This means pgloader schema inference is not compatible with Attic's SQLite schema as-is.

Revised migration approach:

Do not let pgloader create the PostgreSQL schema.
Instead, create the target schema using Attic itself (or Attic's normal DB migrations) against an empty PostgreSQL database.
Then rerun pgloader in data-only mode into the already-created Attic schema.

This keeps schema authority with Attic and limits pgloader to row-copying, which should avoid the invalid type generation seen here.

Another concrete migration finding: - `pgloader` failed when creating PostgreSQL tables from the SQLite schema because it generated invalid types such as `timestamp_with_timezone_text`. - This means `pgloader` schema inference is not compatible with Attic's SQLite schema as-is. Revised migration approach: - Do **not** let `pgloader` create the PostgreSQL schema. - Instead, create the target schema using Attic itself (or Attic's normal DB migrations) against an empty PostgreSQL database. - Then rerun `pgloader` in **data-only** mode into the already-created Attic schema. This keeps schema authority with Attic and limits `pgloader` to row-copying, which should avoid the invalid type generation seen here.

nimmo commented

2026-07-06 15:17:06 +01:00

Author

Owner

Latest migration checkpoint:

After recreating the target PostgreSQL DB and starting Attic again, the database still has no tables.
That means the tested atticd instance did not actually connect to the PostgreSQL URL during startup.

Most likely causes now:

the live/staged atticd environment file still contains the old SQLite URL,
the tested generation did not pick up the new secret value,
or the running service is not using the expected environment file at all.

Next step is to inspect the active systemd unit environment and rendered Attic config on vega before attempting any more data-copy work.

Latest migration checkpoint: - After recreating the target PostgreSQL DB and starting Attic again, the database still has no tables. - That means the tested `atticd` instance did not actually connect to the PostgreSQL URL during startup. Most likely causes now: - the live/staged `atticd` environment file still contains the old SQLite URL, - the tested generation did not pick up the new secret value, - or the running service is not using the expected environment file at all. Next step is to inspect the active systemd unit environment and rendered Attic config on `vega` before attempting any more data-copy work.

nimmo commented

2026-07-06 15:19:12 +01:00

Author

Owner

Current diagnosis after inspecting the live vega unit:

atticd.service is definitely reading /run/secrets/rendered/atticd-env.
The rendered env file contains ATTIC_SERVER_DATABASE_URL=postgresql://...@nova.mesh:5432/atticd.
Despite that, starting Attic still does not create any tables in PostgreSQL.

Most likely remaining explanation:

the generated checked-attic-server.toml still contains a database.url entry pointing at SQLite, and Attic is using that explicit config value instead of the env-backed default.

Next diagnostic step:

inspect /nix/store/...-checked-attic-server.toml directly for database, sqlite, postgres, or server.db before attempting further migration work.

Current diagnosis after inspecting the live `vega` unit: - `atticd.service` is definitely reading `/run/secrets/rendered/atticd-env`. - The rendered env file contains `ATTIC_SERVER_DATABASE_URL=postgresql://...@nova.mesh:5432/atticd`. - Despite that, starting Attic still does not create any tables in PostgreSQL. Most likely remaining explanation: - the generated `checked-attic-server.toml` still contains a `database.url` entry pointing at SQLite, and Attic is using that explicit config value instead of the env-backed default. Next diagnostic step: - inspect `/nix/store/...-checked-attic-server.toml` directly for `database`, `sqlite`, `postgres`, or `server.db` before attempting further migration work.

nimmo commented

2026-07-06 15:21:45 +01:00

Author

Owner

Root cause of the failed PostgreSQL cutover is now confirmed and patched in the repo.

What was happening

The live atticd unit correctly loaded /run/secrets/rendered/atticd-env with ATTIC_SERVER_DATABASE_URL=postgresql://....
However, the generated Attic config file still contained:
- [database]
- url = "sqlite:///var/lib/atticd/server.db?mode=rwc"
This comes from the upstream nixpkgs services.atticd module, which sets a default SQLite database.url in settings.
Because Attic only uses the environment variable as a fallback when database.url is absent from config, the explicit SQLite value always won.

Repo fix

Patched modules/services/atticd.nix to force services.atticd.settings.database = {} so the generated TOML no longer bakes in the nixpkgs SQLite default.
nix flake check passes with this change.

Next step

Redeploy this updated generation to vega.
Confirm the checked Attic TOML no longer contains sqlite:///var/lib/atticd/server.db?mode=rwc.
Then retry the PostgreSQL schema creation / data migration sequence.

Root cause of the failed PostgreSQL cutover is now confirmed and patched in the repo. What was happening - The live `atticd` unit correctly loaded `/run/secrets/rendered/atticd-env` with `ATTIC_SERVER_DATABASE_URL=postgresql://...`. - However, the generated Attic config file still contained: - `[database]` - `url = "sqlite:///var/lib/atticd/server.db?mode=rwc"` - This comes from the upstream nixpkgs `services.atticd` module, which sets a default SQLite `database.url` in `settings`. - Because Attic only uses the environment variable as a fallback when `database.url` is absent from config, the explicit SQLite value always won. Repo fix - Patched `modules/services/atticd.nix` to force `services.atticd.settings.database = {}` so the generated TOML no longer bakes in the nixpkgs SQLite default. - `nix flake check` passes with this change. Next step - Redeploy this updated generation to `vega`. - Confirm the checked Attic TOML no longer contains `sqlite:///var/lib/atticd/server.db?mode=rwc`. - Then retry the PostgreSQL schema creation / data migration sequence.

nimmo commented

2026-07-06 15:26:15 +01:00

Author

Owner

Post-fix redeploy result:

After deploying the patched module, Attic now creates tables in the target PostgreSQL database.
The presence of older SQLite-backed checked-attic-server.toml files in /nix/store is expected; they are previous generations and not evidence that the current generation is still on SQLite.
The key change is that PostgreSQL now has Attic tables, which confirms the active service finally connected to Postgres and ran its normal schema migrations.

This moves issue #6 from configuration/debugging into the actual metadata-copy phase: next step is to stop atticd, run a data-only migration from the SQLite copy into the now-initialized PostgreSQL schema, then restart and verify cache reads.

Post-fix redeploy result: - After deploying the patched module, Attic now creates tables in the target PostgreSQL database. - The presence of older SQLite-backed `checked-attic-server.toml` files in `/nix/store` is expected; they are previous generations and not evidence that the current generation is still on SQLite. - The key change is that PostgreSQL now has Attic tables, which confirms the active service finally connected to Postgres and ran its normal schema migrations. This moves issue #6 from configuration/debugging into the actual metadata-copy phase: next step is to stop `atticd`, run a data-only migration from the SQLite copy into the now-initialized PostgreSQL schema, then restart and verify cache reads.

nimmo commented

2026-07-06 15:28:39 +01:00

Author

Owner

The next blocker was only pgloader invocation syntax:

pgloader --with "data only, reset sequences, disable triggers" ... failed at argument parsing before any database work began.
This is a command-line syntax issue, not a SQLite/PostgreSQL compatibility failure.

Practical adjustment:

Use a small pgloader load file with a WITH data only, reset sequences, disable triggers clause instead of relying on the CLI --with parser.

The next blocker was only `pgloader` invocation syntax: - `pgloader --with "data only, reset sequences, disable triggers" ...` failed at argument parsing before any database work began. - This is a command-line syntax issue, not a SQLite/PostgreSQL compatibility failure. Practical adjustment: - Use a small pgloader load file with a `WITH data only, reset sequences, disable triggers` clause instead of relying on the CLI `--with` parser.

nimmo commented

2026-07-06 15:30:37 +01:00

Author

Owner

The data-copy phase is effectively successful.

Observed from the pgloader run:

cache, chunk, object, chunkref, and nar rows copied into PostgreSQL.
The only hard failure was on seaql_migrations with duplicate key value violates unique constraint "seaql_migrations_pkey".

Interpretation:

This is expected after letting Attic initialize the PostgreSQL schema first: seaql_migrations was already populated by Attic's own migration runner.
The seaql_migrations copy is not required for correctness once the schema already exists.
The type mismatch lines were warnings from pgloader's source/target casting logic, not a blocker; the main Attic tables still imported.

Next step:

Treat PostgreSQL as loaded, start atticd, and verify public cache reads plus a nix copy --from read.
If verification passes, this issue can move to cleanup/closeout rather than more migration mechanics.

The data-copy phase is effectively successful. Observed from the `pgloader` run: - `cache`, `chunk`, `object`, `chunkref`, and `nar` rows copied into PostgreSQL. - The only hard failure was on `seaql_migrations` with `duplicate key value violates unique constraint "seaql_migrations_pkey"`. Interpretation: - This is expected after letting Attic initialize the PostgreSQL schema first: `seaql_migrations` was already populated by Attic's own migration runner. - The `seaql_migrations` copy is not required for correctness once the schema already exists. - The type mismatch lines were warnings from pgloader's source/target casting logic, not a blocker; the main Attic tables still imported. Next step: - Treat PostgreSQL as loaded, start `atticd`, and verify public cache reads plus a `nix copy --from` read. - If verification passes, this issue can move to cleanup/closeout rather than more migration mechanics.

nimmo commented

2026-07-06 15:32:56 +01:00

Author

Owner

Verification after the PostgreSQL cutover is mixed, so issue #6 is not ready to close yet.

Observed after starting atticd on PostgreSQL:

At least one expected path still resolves via .narinfo (example: 4da5p6rwzj366cfsq0xslgqi1q7j5nbr-cuda12.9-libcublas-12.9.1.4-lib).
Another previously noted known-good path now returns NoSuchObject / missing nar/... from the cache (example: 6nli808zpb1k9vk7cz175m2ri78455rl-ollama-0.30.7).

Interpretation:

The service is running against PostgreSQL, but verification shows at least one expected object is absent or not queryable after migration.
This shifts the work from transport/configuration into data-integrity diagnosis: compare specific object presence in SQLite vs PostgreSQL and determine whether the object was missing pre-migration, skipped during copy, or is present but not linked/queryable correctly in Postgres.

Next step:

Query both SQLite and PostgreSQL for the missing store path hash / object row directly before making more migration changes.

Verification after the PostgreSQL cutover is mixed, so issue #6 is not ready to close yet. Observed after starting `atticd` on PostgreSQL: - At least one expected path still resolves via `.narinfo` (example: `4da5p6rwzj366cfsq0xslgqi1q7j5nbr-cuda12.9-libcublas-12.9.1.4-lib`). - Another previously noted known-good path now returns `NoSuchObject` / missing `nar/...` from the cache (example: `6nli808zpb1k9vk7cz175m2ri78455rl-ollama-0.30.7`). Interpretation: - The service is running against PostgreSQL, but verification shows at least one expected object is absent or not queryable after migration. - This shifts the work from transport/configuration into data-integrity diagnosis: compare specific object presence in SQLite vs PostgreSQL and determine whether the object was missing pre-migration, skipped during copy, or is present but not linked/queryable correctly in Postgres. Next step: - Query both SQLite and PostgreSQL for the missing store path hash / object row directly before making more migration changes.

nimmo commented

2026-07-06 15:34:33 +01:00

Author

Owner

Follow-up integrity check on the missing 6nli808zpb1k9vk7cz175m2ri78455rl sample:

Querying for that store_path_hash returned no object row and therefore no linked nar row in the checked database.

Implication:

If this was the SQLite source DB, then the previously listed 6nli... sample was stale and its absence is not evidence of migration loss.
If this was PostgreSQL only, we still need the same query against SQLite to tell whether the row was absent before migration or lost during copy.

Next step remains to compare the same store_path_hash lookup in both SQLite and PostgreSQL so the verification set can be corrected or the missing-row path can be debugged precisely.

Follow-up integrity check on the missing `6nli808zpb1k9vk7cz175m2ri78455rl` sample: - Querying for that `store_path_hash` returned no `object` row and therefore no linked `nar` row in the checked database. Implication: - If this was the SQLite source DB, then the previously listed `6nli...` sample was stale and its absence is not evidence of migration loss. - If this was PostgreSQL only, we still need the same query against SQLite to tell whether the row was absent before migration or lost during copy. Next step remains to compare the same `store_path_hash` lookup in both SQLite and PostgreSQL so the verification set can be corrected or the missing-row path can be debugged precisely.

nimmo commented

2026-07-06 15:37:50 +01:00

Author

Owner

Table-count comparison shows the PostgreSQL cutover is still incomplete.

Current counts:

SQLite source:
- object: 6533
- nar: 6427
- chunk: 1772151
- chunkref: 284215
- cache: 1
PostgreSQL target:
- object: 6477
- nar: 6382
- chunk: 1707116
- chunkref: 276611
- cache: 1

Interpretation:

This is not just a stale verification sample problem. The target database is materially missing rows across multiple main Attic tables.
Even though pgloader reported the core tables as copied and Attic starts successfully on PostgreSQL, the resulting dataset is incomplete.

Consequence:

Issue #6 should remain open.
The next step is no longer basic verification; it is diagnosing why pgloader produced lower final row counts in PostgreSQL than exist in the SQLite source before deciding whether to retry with a different import strategy or roll back to SQLite pending a safer migration path.

Table-count comparison shows the PostgreSQL cutover is still incomplete. Current counts: - SQLite source: - `object`: 6533 - `nar`: 6427 - `chunk`: 1772151 - `chunkref`: 284215 - `cache`: 1 - PostgreSQL target: - `object`: 6477 - `nar`: 6382 - `chunk`: 1707116 - `chunkref`: 276611 - `cache`: 1 Interpretation: - This is not just a stale verification sample problem. The target database is materially missing rows across multiple main Attic tables. - Even though pgloader reported the core tables as copied and Attic starts successfully on PostgreSQL, the resulting dataset is incomplete. Consequence: - Issue #6 should remain open. - The next step is no longer basic verification; it is diagnosing why `pgloader` produced lower final row counts in PostgreSQL than exist in the SQLite source before deciding whether to retry with a different import strategy or roll back to SQLite pending a safer migration path.

nimmo commented

2026-07-06 15:38:46 +01:00

Author

Owner

Operational decision for this migration:

PostgreSQL row counts are lower than the old SQLite DB, so the migration is not a perfect metadata copy.
However, the cache signing keys remain the same, and the operator is comfortable allowing missing cache entries to repopulate naturally from future builds/pushes into the cache.

Practical implication:

The cutover can be treated as acceptable for current operations if the priority is to move off SQLite now rather than preserve every historical cache entry immediately.
Follow-up risk remains: some older cache paths may 404 until they are rebuilt/re-uploaded.

Recommended follow-up after the cutover:

keep the SQLite backup intact for now,
avoid deleting the old metadata until enough rebuild/push activity has repopulated the working set,
document that historical cache completeness is temporarily best-effort rather than guaranteed.

Operational decision for this migration: - PostgreSQL row counts are lower than the old SQLite DB, so the migration is not a perfect metadata copy. - However, the cache signing keys remain the same, and the operator is comfortable allowing missing cache entries to repopulate naturally from future builds/pushes into the cache. Practical implication: - The cutover can be treated as acceptable for current operations if the priority is to move off SQLite now rather than preserve every historical cache entry immediately. - Follow-up risk remains: some older cache paths may 404 until they are rebuilt/re-uploaded. Recommended follow-up after the cutover: - keep the SQLite backup intact for now, - avoid deleting the old metadata until enough rebuild/push activity has repopulated the working set, - document that historical cache completeness is temporarily best-effort rather than guaranteed.

nimmo commented

2026-07-06 15:41:41 +01:00

Author

Owner

Resolution path changed after validating the partial PostgreSQL migration:

Rather than trying to preserve incomplete historical metadata or live with stranded storage objects, the operator chose to reset Attic cleanly.
Plan is now to keep the existing signing key, purge both Attic metadata and storage contents, restart with an empty PostgreSQL-backed cache, and let normal future builds/pushes repopulate it.

Why this is preferable now:

avoids mismatched DB/storage state,
avoids indefinite orphaned files on the NAS,
gives predictable GC behavior again,
keeps client trust continuity because the signing key remains unchanged.

Next action is a coordinated wipe of:

the Attic PostgreSQL database,
the NAS-backed storage path,
followed by Attic restart and empty-cache verification.

Resolution path changed after validating the partial PostgreSQL migration: - Rather than trying to preserve incomplete historical metadata or live with stranded storage objects, the operator chose to reset Attic cleanly. - Plan is now to keep the existing signing key, purge both Attic metadata and storage contents, restart with an empty PostgreSQL-backed cache, and let normal future builds/pushes repopulate it. Why this is preferable now: - avoids mismatched DB/storage state, - avoids indefinite orphaned files on the NAS, - gives predictable GC behavior again, - keeps client trust continuity because the signing key remains unchanged. Next action is a coordinated wipe of: 1. the Attic PostgreSQL database, 2. the NAS-backed storage path, 3. followed by Attic restart and empty-cache verification.

nimmo commented

2026-07-06 16:11:44 +01:00

Author

Owner

Fresh-cache bootstrap after the full reset succeeded.

Completed on vega:

generated a root/bootstrap Attic token,
recreated the nixos cache in the empty PostgreSQL-backed Attic instance,
configured the nixos cache as public.

This was the missing post-reset step after wiping metadata and storage: an empty Attic DB has schema but no cache definitions, so /nixos/nix-cache-info cannot work until the nixos cache row is recreated.

At this point the remaining verification is straightforward:

confirm /nixos/nix-cache-info now responds through Attic/proxy,
confirm old historical .narinfo entries miss cleanly,
then repopulate the cache from future pushes/builds.

Fresh-cache bootstrap after the full reset succeeded. Completed on `vega`: - generated a root/bootstrap Attic token, - recreated the `nixos` cache in the empty PostgreSQL-backed Attic instance, - configured the `nixos` cache as public. This was the missing post-reset step after wiping metadata and storage: an empty Attic DB has schema but no cache definitions, so `/nixos/nix-cache-info` cannot work until the `nixos` cache row is recreated. At this point the remaining verification is straightforward: - confirm `/nixos/nix-cache-info` now responds through Attic/proxy, - confirm old historical `.narinfo` entries miss cleanly, - then repopulate the cache from future pushes/builds.

nimmo commented

2026-07-06 16:13:44 +01:00

Author

Owner

Final verification after the reset-based resolution passed.

Current state:

local http://127.0.0.1:8081/nixos/nix-cache-info returns 200 OK,
public https://attic.nimmog.uk/nixos/nix-cache-info returns 200 OK,
historical pre-reset .narinfo requests now miss cleanly with 404 NoSuchObject, which is the expected behavior for an empty fresh cache.

Issue outcome:

The original SQLite -> PostgreSQL metadata migration path was not lossless enough to keep.
The final accepted resolution was a clean reset: keep the signing key, wipe Attic metadata and storage, recreate the nixos cache on PostgreSQL, and let future pushes/builds repopulate it.
This restores a consistent Attic state with PostgreSQL backing and avoids long-term orphaned storage from mismatched metadata.

Follow-up operational note:

historical cache availability is intentionally reset; misses will refill over time as new builds are pushed.

Final verification after the reset-based resolution passed. Current state: - local `http://127.0.0.1:8081/nixos/nix-cache-info` returns `200 OK`, - public `https://attic.nimmog.uk/nixos/nix-cache-info` returns `200 OK`, - historical pre-reset `.narinfo` requests now miss cleanly with `404 NoSuchObject`, which is the expected behavior for an empty fresh cache. Issue outcome: - The original SQLite -> PostgreSQL metadata migration path was not lossless enough to keep. - The final accepted resolution was a clean reset: keep the signing key, wipe Attic metadata and storage, recreate the `nixos` cache on PostgreSQL, and let future pushes/builds repopulate it. - This restores a consistent Attic state with PostgreSQL backing and avoids long-term orphaned storage from mismatched metadata. Follow-up operational note: - historical cache availability is intentionally reset; misses will refill over time as new builds are pushed.

nimmo closed this issue

2026-07-06 16:13:55 +01:00

~~nimmo referenced this issue 2026-07-20 15:34:36 +01:00~~

Decide and implement disk encryption for lena and vega #37

Rows
Columns

Migrate vega Attic metadata from SQLite to PostgreSQL #6

Goal

Scope

Acceptance Checklist

Procedure Outline

Verification

Rollback