How to migrate an APDB from v24 to v29 while preserving source IDs?

Support
#1

Hi,

Since early 2024, we have been using LSST Science Pipelines v24 to process WFST data, and have accumulated over 1.5 years of science results in an Alert Production Database (APDB) created with make_apdb.py.

We have now successfully tested the v29.2.0 pipeline on WFST exposures—from raw images through to alert generation—and are preparing to transition fully to this version.

However, we would like to preserve continuity with our historical data, ideally by reusing or migrating the existing APDB so that:

  • Past DIA sources and objects remain accessible
  • Source/object IDs stay consistent across versions

We compared the database schemas between v24 and v29 and found significant differences (e.g., renamed columns). This suggests the databases are not directly compatible.

Could you please advise:

  1. Is there an official or recommended way to migrate an APDB from v24 to v29?
  2. If direct migration is not supported, what is the best practice for linking historical alerts (v24) with new ones (v29)?
  3. Are there any tools or scripts available to assist with schema conversion or data transfer?

Thank you very much for your guidance!

1 Like
#2

The official way to migrate APDB database to a newer schema version is to use tools in dax_apdb_migrate package. It has documentation, you can build it locally with package-docs after checking out the package.

Unfortunately, before APDB versioning and dax_apdb_migrate were introduced, there were few changes in the schema that had no associated versions. Depending on when your APDB instance was created, its schema may be in a state that does not match the one expected by dax_apdb_migrate. Would it be possible for me to look at the current existing schema of your APDB instance to verify that our tools can work with it.

#3

Thank you very much for the prompt and helpful response!

We greatly appreciate your offer to check our existing APDB schema. Below is the output of \dt and the CREATE statements for the main tables in our v24-created database:

Schema | Name | Type | Owner
--------±--------------------------±------±------
public | DiaForcedSource | table | wfst
public | DiaObject | table | wfst
public | DiaObject_To_Object_Match | table | wfst
public | DiaSource | table | wfst
public | SSObject | table | wfst
(5 rows)

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.17 (Ubuntu 14.17-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.17 (Ubuntu 14.17-0ubuntu0.22.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: DiaForcedSource; Type: TABLE; Schema: public; Owner: wfst
--

CREATE TABLE public."DiaForcedSource" (
    "diaObjectId" bigint NOT NULL,
    "ccdVisitId" bigint NOT NULL,
    "psFlux" double precision,
    "psFluxErr" double precision,
    x double precision,
    y double precision,
    flags bigint DEFAULT '0'::bigint NOT NULL,
    "midPointTai" double precision NOT NULL,
    "diaForcedSourceId" bigint NOT NULL,
    "totFlux" double precision,
    "totFluxErr" double precision,
    "filterName" character(1)
);


ALTER TABLE public."DiaForcedSource" OWNER TO wfst;

--
-- Name: DiaObject; Type: TABLE; Schema: public; Owner: wfst
--

CREATE TABLE public."DiaObject" (
    "diaObjectId" bigint NOT NULL,
    "validityStart" timestamp without time zone NOT NULL,
    "validityEnd" timestamp without time zone,
    ra double precision NOT NULL,
    "raErr" double precision,
    decl double precision NOT NULL,
    "declErr" double precision,
    "ra_decl_Cov" double precision,
    "radecTai" double precision,
    "pmRa" double precision,
    "pmRaErr" double precision,
    "pmDecl" double precision,
    "pmDeclErr" double precision,
    parallax double precision,
    "parallaxErr" double precision,
    "pmRa_pmDecl_Cov" double precision,
    "pmRa_parallax_Cov" double precision,
    "pmDecl_parallax_Cov" double precision,
    "pmParallaxLnL" double precision,
    "pmParallaxChi2" double precision,
    "pmParallaxNdata" integer NOT NULL,
    "uPSFluxMean" double precision,
    "uPSFluxMeanErr" double precision,
    "uPSFluxSigma" double precision,
    "uPSFluxChi2" double precision,
    "uPSFluxNdata" integer NOT NULL,
    "uFPFluxMean" double precision,
    "uFPFluxMeanErr" double precision,
    "uFPFluxSigma" double precision,
    "gPSFluxMean" double precision,
    "gPSFluxMeanErr" double precision,
    "gPSFluxSigma" double precision,
    "gPSFluxChi2" double precision,
    "gPSFluxNdata" integer NOT NULL,
    "gFPFluxMean" double precision,
    "gFPFluxMeanErr" double precision,
    "gFPFluxSigma" double precision,
    "rPSFluxMean" double precision,
    "rPSFluxMeanErr" double precision,
    "rPSFluxSigma" double precision,
    "rPSFluxChi2" double precision,
    "rPSFluxNdata" integer NOT NULL,
    "rFPFluxMean" double precision,
    "rFPFluxMeanErr" double precision,
    "rFPFluxSigma" double precision,
    "iPSFluxMean" double precision,
    "iPSFluxMeanErr" double precision,
    "iPSFluxSigma" double precision,
    "iPSFluxChi2" double precision,
    "iPSFluxNdata" integer NOT NULL,
    "iFPFluxMean" double precision,
    "iFPFluxMeanErr" double precision,
    "iFPFluxSigma" double precision,
    "zPSFluxMean" double precision,
    "zPSFluxMeanErr" double precision,
    "zPSFluxSigma" double precision,
    "zPSFluxChi2" double precision,
    "zPSFluxNdata" integer NOT NULL,
    "zFPFluxMean" double precision,
    "zFPFluxMeanErr" double precision,
    "zFPFluxSigma" double precision,
    "yPSFluxMean" double precision,
    "yPSFluxMeanErr" double precision,
    "yPSFluxSigma" double precision,
    "yPSFluxChi2" double precision,
    "yPSFluxNdata" integer NOT NULL,
    "yFPFluxMean" double precision,
    "yFPFluxMeanErr" double precision,
    "yFPFluxSigma" double precision,
    "uLcPeriodic" bytea,
    "gLcPeriodic" bytea,
    "rLcPeriodic" bytea,
    "iLcPeriodic" bytea,
    "zLcPeriodic" bytea,
    "yLcPeriodic" bytea,
    "uLcNonPeriodic" bytea,
    "gLcNonPeriodic" bytea,
    "rLcNonPeriodic" bytea,
    "iLcNonPeriodic" bytea,
    "zLcNonPeriodic" bytea,
    "yLcNonPeriodic" bytea,
    "nearbyObj1" bigint NOT NULL,
    "nearbyObj1Dist" double precision,
    "nearbyObj1LnP" double precision,
    "nearbyObj2" bigint NOT NULL,
    "nearbyObj2Dist" double precision,
    "nearbyObj2LnP" double precision,
    "nearbyObj3" bigint NOT NULL,
    "nearbyObj3Dist" double precision,
    "nearbyObj3LnP" double precision,
    "nearbyExtObj1" bigint,
    "nearbyExtObj1Sep" double precision,
    "nearbyExtObj2" bigint,
    "nearbyExtObj2Sep" double precision,
    "nearbyExtObj3" bigint,
    "nearbyExtObj3Sep" double precision,
    "nearbyLowzGal" character(1),
    "nearbyLowzGalSep" double precision,
    "uTOTFluxMean" double precision,
    "uTOTFluxMeanErr" double precision,
    "uTOTFluxSigma" double precision,
    "gTOTFluxMean" double precision,
    "gTOTFluxMeanErr" double precision,
    "gTOTFluxSigma" double precision,
    "rTOTFluxMean" double precision,
    "rTOTFluxMeanErr" double precision,
    "rTOTFluxSigma" double precision,
    "iTOTFluxMean" double precision,
    "iTOTFluxMeanErr" double precision,
    "iTOTFluxSigma" double precision,
    "zTOTFluxMean" double precision,
    "zTOTFluxMeanErr" double precision,
    "zTOTFluxSigma" double precision,
    "yTOTFluxMean" double precision,
    "yTOTFluxMeanErr" double precision,
    "yTOTFluxSigma" double precision,
    "uPSFluxMAD" double precision,
    "uPSFluxSkew" double precision,
    "uPSFluxPercentile05" double precision,
    "uPSFluxPercentile25" double precision,
    "uPSFluxPercentile50" double precision,
    "uPSFluxPercentile75" double precision,
    "uPSFluxPercentile95" double precision,
    "uPSFluxMin" double precision,
    "uPSFluxMax" double precision,
    "uPSFluxStetsonJ" double precision,
    "uPSFluxLinearSlope" double precision,
    "uPSFluxLinearIntercept" double precision,
    "uPSFluxMaxSlope" double precision,
    "uPSFluxErrMean" double precision,
    "gPSFluxMAD" double precision,
    "gPSFluxSkew" double precision,
    "gPSFluxPercentile05" double precision,
    "gPSFluxPercentile25" double precision,
    "gPSFluxPercentile50" double precision,
    "gPSFluxPercentile75" double precision,
    "gPSFluxPercentile95" double precision,
    "gPSFluxMin" double precision,
    "gPSFluxMax" double precision,
    "gPSFluxStetsonJ" double precision,
    "gPSFluxLinearSlope" double precision,
    "gPSFluxLinearIntercept" double precision,
    "gPSFluxMaxSlope" double precision,
    "gPSFluxErrMean" double precision,
    "rPSFluxMAD" double precision,
    "rPSFluxSkew" double precision,
    "rPSFluxPercentile05" double precision,
    "rPSFluxPercentile25" double precision,
    "rPSFluxPercentile50" double precision,
    "rPSFluxPercentile75" double precision,
    "rPSFluxPercentile95" double precision,
    "rPSFluxMin" double precision,
    "rPSFluxMax" double precision,
    "rPSFluxStetsonJ" double precision,
    "rPSFluxLinearSlope" double precision,
    "rPSFluxLinearIntercept" double precision,
    "rPSFluxMaxSlope" double precision,
    "rPSFluxErrMean" double precision,
    "iPSFluxMAD" double precision,
    "iPSFluxSkew" double precision,
    "iPSFluxPercentile05" double precision,
    "iPSFluxPercentile25" double precision,
    "iPSFluxPercentile50" double precision,
    "iPSFluxPercentile75" double precision,
    "iPSFluxPercentile95" double precision,
    "iPSFluxMin" double precision,
    "iPSFluxMax" double precision,
    "iPSFluxStetsonJ" double precision,
    "iPSFluxLinearSlope" double precision,
    "iPSFluxLinearIntercept" double precision,
    "iPSFluxMaxSlope" double precision,
    "iPSFluxErrMean" double precision,
    "zPSFluxMAD" double precision,
    "zPSFluxSkew" double precision,
    "zPSFluxPercentile05" double precision,
    "zPSFluxPercentile25" double precision,
    "zPSFluxPercentile50" double precision,
    "zPSFluxPercentile75" double precision,
    "zPSFluxPercentile95" double precision,
    "zPSFluxMin" double precision,
    "zPSFluxMax" double precision,
    "zPSFluxStetsonJ" double precision,
    "zPSFluxLinearSlope" double precision,
    "zPSFluxLinearIntercept" double precision,
    "zPSFluxMaxSlope" double precision,
    "zPSFluxErrMean" double precision,
    "yPSFluxMAD" double precision,
    "yPSFluxSkew" double precision,
    "yPSFluxPercentile05" double precision,
    "yPSFluxPercentile25" double precision,
    "yPSFluxPercentile50" double precision,
    "yPSFluxPercentile75" double precision,
    "yPSFluxPercentile95" double precision,
    "yPSFluxMin" double precision,
    "yPSFluxMax" double precision,
    "yPSFluxStetsonJ" double precision,
    "yPSFluxLinearSlope" double precision,
    "yPSFluxLinearIntercept" double precision,
    "yPSFluxMaxSlope" double precision,
    "yPSFluxErrMean" double precision,
    flags bigint DEFAULT '0'::bigint NOT NULL,
    "lastNonForcedSource" timestamp without time zone NOT NULL,
    "nDiaSources" integer NOT NULL,
    "pixelId" bigint NOT NULL
);


ALTER TABLE public."DiaObject" OWNER TO wfst;

--
-- Name: DiaObject_To_Object_Match; Type: TABLE; Schema: public; Owner: wfst
--

CREATE TABLE public."DiaObject_To_Object_Match" (
    "diaObjectId" bigint NOT NULL,
    "objectId" bigint NOT NULL,
    dist double precision,
    "lnP" double precision
);


ALTER TABLE public."DiaObject_To_Object_Match" OWNER TO wfst;

--
-- Name: DiaSource; Type: TABLE; Schema: public; Owner: wfst
--

CREATE TABLE public."DiaSource" (
    "diaSourceId" bigint NOT NULL,
    "ccdVisitId" bigint NOT NULL,
    "diaObjectId" bigint,
    "ssObjectId" bigint,
    "parentDiaSourceId" bigint,
    "prv_procOrder" integer DEFAULT 0 NOT NULL,
    "ssObjectReassocTime" timestamp without time zone,
    "midPointTai" double precision NOT NULL,
    ra double precision NOT NULL,
    "raErr" double precision,
    decl double precision NOT NULL,
    "declErr" double precision,
    "ra_decl_Cov" double precision,
    x double precision NOT NULL,
    "xErr" double precision,
    y double precision NOT NULL,
    "yErr" double precision,
    "x_y_Cov" double precision,
    "apFlux" double precision,
    "apFluxErr" double precision,
    snr double precision,
    "psFlux" double precision,
    "psFluxErr" double precision,
    "psRa" double precision,
    "psRaErr" double precision,
    "psDecl" double precision,
    "psDeclErr" double precision,
    "psFlux_psRa_Cov" double precision,
    "psFlux_psDecl_Cov" double precision,
    "psRa_psDecl_Cov" double precision,
    "psLnL" double precision,
    "psChi2" double precision,
    "psNdata" integer,
    "trailFlux" double precision,
    "trailFluxErr" double precision,
    "trailRa" double precision,
    "trailRaErr" double precision,
    "trailDecl" double precision,
    "trailDeclErr" double precision,
    "trailLength" double precision,
    "trailLengthErr" double precision,
    "trailAngle" double precision,
    "trailAngleErr" double precision,
    "trailFlux_trailRa_Cov" double precision,
    "trailFlux_trailDecl_Cov" double precision,
    "trailFlux_trailLength_Cov" double precision,
    "trailFlux_trailAngle_Cov" double precision,
    "trailRa_trailDecl_Cov" double precision,
    "trailRa_trailLength_Cov" double precision,
    "trailRa_trailAngle_Cov" double precision,
    "trailDecl_trailLength_Cov" double precision,
    "trailDecl_trailAngle_Cov" double precision,
    "trailLength_trailAngle_Cov" double precision,
    "trailLnL" double precision,
    "trailChi2" double precision,
    "trailNdata" integer DEFAULT 0 NOT NULL,
    "dipMeanFlux" double precision,
    "dipMeanFluxErr" double precision,
    "dipFluxDiff" double precision,
    "dipFluxDiffErr" double precision,
    "dipRa" double precision,
    "dipRaErr" double precision,
    "dipDecl" double precision,
    "dipDeclErr" double precision,
    "dipLength" double precision,
    "dipLengthErr" double precision,
    "dipAngle" double precision,
    "dipAngleErr" double precision,
    "dipMeanFlux_dipFluxDiff_Cov" double precision,
    "dipMeanFlux_dipRa_Cov" double precision,
    "dipMeanFlux_dipDecl_Cov" double precision,
    "dipMeanFlux_dipLength_Cov" double precision,
    "dipMeanFlux_dipAngle_Cov" double precision,
    "dipFluxDiff_dipRa_Cov" double precision,
    "dipFluxDiff_dipDecl_Cov" double precision,
    "dipFluxDiff_dipLength_Cov" double precision,
    "dipFluxDiff_dipAngle_Cov" double precision,
    "dipRa_dipDecl_Cov" double precision,
    "dipRa_dipLength_Cov" double precision,
    "dipRa_dipAngle_Cov" double precision,
    "dipDecl_dipLength_Cov" double precision,
    "dipDecl_dipAngle_Cov" double precision,
    "dipLength_dipAngle_Cov" double precision,
    "dipLnL" double precision,
    "dipChi2" double precision,
    "dipNdata" integer DEFAULT 0 NOT NULL,
    "totFlux" double precision,
    "totFluxErr" double precision,
    "diffFlux" double precision,
    "diffFluxErr" double precision,
    "fpBkgd" double precision,
    "fpBkgdErr" double precision,
    ixx double precision,
    "ixxErr" double precision,
    iyy double precision,
    "iyyErr" double precision,
    ixy double precision,
    "ixyErr" double precision,
    "ixx_iyy_Cov" double precision,
    "ixx_ixy_Cov" double precision,
    "iyy_ixy_Cov" double precision,
    "ixxPSF" double precision,
    "iyyPSF" double precision,
    "ixyPSF" double precision,
    extendedness double precision,
    spuriousness double precision,
    flags bigint DEFAULT '0'::bigint NOT NULL,
    "filterName" character(1),
    "isDipole" boolean,
    "bboxSize" bigint,
    "pixelId" bigint NOT NULL
);


ALTER TABLE public."DiaSource" OWNER TO wfst;

--
-- Name: SSObject; Type: TABLE; Schema: public; Owner: wfst
--

CREATE TABLE public."SSObject" (
    "ssObjectId" bigint NOT NULL,
    "discoverySubmissionDate" double precision,
    "firstObservationDate" double precision,
    arc double precision,
    "numObs" integer,
    "lcPeriodic" bytea,
    "MOID" double precision,
    "MOIDTrueAnomaly" double precision,
    "MOIDEclipticLongitude" double precision,
    "MOIDDeltaV" double precision,
    "uH" double precision,
    "uG12" double precision,
    "uHErr" double precision,
    "uG12Err" double precision,
    "uH_uG12_Cov" double precision,
    "uChi2" double precision,
    "uNdata" integer,
    "gH" double precision,
    "gG12" double precision,
    "gHErr" double precision,
    "gG12Err" double precision,
    "gH_gG12_Cov" double precision,
    "gChi2" double precision,
    "gNdata" integer,
    "rH" double precision,
    "rG12" double precision,
    "rHErr" double precision,
    "rG12Err" double precision,
    "rH_rG12_Cov" double precision,
    "rChi2" double precision,
    "rNdata" integer,
    "iH" double precision,
    "iG12" double precision,
    "iHErr" double precision,
    "iG12Err" double precision,
    "iH_iG12_Cov" double precision,
    "iChi2" double precision,
    "iNdata" integer,
    "zH" double precision,
    "zG12" double precision,
    "zHErr" double precision,
    "zG12Err" double precision,
    "zH_zG12_Cov" double precision,
    "zChi2" double precision,
    "zNdata" integer,
    "yH" double precision,
    "yG12" double precision,
    "yHErr" double precision,
    "yG12Err" double precision,
    "yH_yG12_Cov" double precision,
    "yChi2" double precision,
    "yNdata" integer,
    "maxExtendedness" double precision,
    "minExtendedness" double precision,
    "medianExtendedness" double precision,
    flags bigint DEFAULT '0'::bigint NOT NULL
);


ALTER TABLE public."SSObject" OWNER TO wfst;

--
-- Name: DiaForcedSource DiaForcedSource_pkey; Type: CONSTRAINT; Schema: public; Owner: wfst
--

ALTER TABLE ONLY public."DiaForcedSource"
    ADD CONSTRAINT "DiaForcedSource_pkey" PRIMARY KEY ("diaObjectId", "ccdVisitId");


--
-- Name: DiaObject DiaObject_pkey; Type: CONSTRAINT; Schema: public; Owner: wfst
--

ALTER TABLE ONLY public."DiaObject"
    ADD CONSTRAINT "DiaObject_pkey" PRIMARY KEY ("diaObjectId", "validityStart");


--
-- Name: DiaSource DiaSource_pkey; Type: CONSTRAINT; Schema: public; Owner: wfst
--

ALTER TABLE ONLY public."DiaSource"
    ADD CONSTRAINT "DiaSource_pkey" PRIMARY KEY ("diaSourceId");


--
-- Name: SSObject SSObject_pkey; Type: CONSTRAINT; Schema: public; Owner: wfst
--

ALTER TABLE ONLY public."SSObject"
    ADD CONSTRAINT "SSObject_pkey" PRIMARY KEY ("ssObjectId");


--
-- Name: IDX_DiaForcedSource_ccdVisitId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaForcedSource_ccdVisitId" ON public."DiaForcedSource" USING btree ("ccdVisitId");


--
-- Name: IDX_DiaObjectToObjectMatch_diaObjectId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaObjectToObjectMatch_diaObjectId" ON public."DiaObject_To_Object_Match" USING btree ("diaObjectId");


--
-- Name: IDX_DiaObjectToObjectMatch_objectId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaObjectToObjectMatch_objectId" ON public."DiaObject_To_Object_Match" USING btree ("objectId");


--
-- Name: IDX_DiaObject_pixelId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaObject_pixelId" ON public."DiaObject" USING btree ("pixelId");


--
-- Name: IDX_DiaObject_validityStart; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaObject_validityStart" ON public."DiaObject" USING btree ("validityStart");


--
-- Name: IDX_DiaSource_ccdVisitId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaSource_ccdVisitId" ON public."DiaSource" USING btree ("ccdVisitId");


--
-- Name: IDX_DiaSource_diaObjectId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaSource_diaObjectId" ON public."DiaSource" USING btree ("diaObjectId");


--
-- Name: IDX_DiaSource_pixelId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaSource_pixelId" ON public."DiaSource" USING btree ("pixelId");


--
-- Name: IDX_DiaSource_ssObjectId; Type: INDEX; Schema: public; Owner: wfst
--

CREATE INDEX "IDX_DiaSource_ssObjectId" ON public."DiaSource" USING btree ("ssObjectId");


--
-- PostgreSQL database dump complete
--

Our APDB was created in early 2024 using make_apdb.py from LSST Science Pipelines v24.0.0.

Please let us know if this information is sufficient, or if you need additional details (e.g., column types, indexes, or sample rows).

Thank you again for your support!

#4

Thanks,

quick look reveals that your schema is one of the earlier versions that is not yet supported by dax_apdb_migrate. I think I can update the tools to enable support for that old version, I’ll work on that tomorrow, will let you know when it’s ready.

Can you tell how much data is in your database? Output from SELECT COUNT(*) from "DiaSource" is one measure.

#5

Thank you so much for your willingness to extend support for our schema version — we truly appreciate it!

We actually maintain two separate APDB instances, both created with make_apdb.py in v24 and containing real science data:

1. Database: ap

  • Row counts:
SELECT COUNT(*) FROM "DiaSource";       --    243,224,349
SELECT COUNT(*) FROM "DiaObject";       --    243,226,702
SELECT COUNT(*) FROM "DiaForcedSource"; -- 16,554,332,326

2. Database: ap_wf

  • Row counts:
SELECT COUNT(*) FROM "DiaSource";       --   261,571,306
SELECT COUNT(*) FROM "DiaObject";       --   261,587,730
SELECT COUNT(*) FROM "DiaForcedSource"; -- 2,447,881,141

(Numbers are approximate as of today.)

Both databases share the same table structure, but contain independent datasets.

Please let us know if you need additional details.

Thanks again for your support!

#6

OK, I think I have something reasonable that should work to upgrade your databases to the version that matches release v29. I have tested it on your schema, but without any data, of course. We actually never ran schema migrations on SQL backend with that scale of data, be warned (I do not expect Postgres to handle a production-scale volume of data in general, we use SQL backend for testing only).

Schema version in release v29 is “6.0.0”, you can check that in sdm_schemas/yml/apdb.yaml file. There is also an additional version number associated with the code, that lives in dax_apdb/python/lsst/dax/apdb/sql/abdSql.py, for v29 it should be “0.1.1”.

Here are the steps that should upgrade your schema:

  1. Most important - make a full backup of the database.

  2. All next steps should run with v29 release.

  3. Clone dax_apdb_migrate package:

     cd <some work dir>
    git clone https://github.com/lsst-dm/dax_apdb_migrate.git
    cd dax_apdb_migrate
    setup -k -r .
    scons
  1. Check that you can run migration tool against your database. Note that if your database tables are not in public schema, then you have to use -s SCHEMA_NAME option with all commands below:
       DB_URL=<db_url from your APDB config file>
       apdb-migrate-sql show-current --no-metadata -m $DB_URL

This should produce:

       ApdbSql: 0.1.0 -> ApdbSql_0.1.0
       schema: 0.1.0 -> schema_0.1.0

(versions 0.1.0 were assigned to all pre-historic schemas).

  1. Next step is to “stamp” that version to the database, this creates an addition table in the database used by the migration tool (which is based on Alembic):
       apdb-migrate-sql stamp --no-metadata $DB_URL
  1. Migration to schema 0.1.1, this will make a lot of column renaming and add metadata table:
       apdb-migrate-sql upgrade --no-metadata $DB_URL schema_0.1.1
  1. Upgrade ApdbSql version:
       apdb-migrate-sql upgrade $DB_URL ApdbSql_0.1.1
  1. Next migration is more involved because it created and populates columns in the table. As your tables are large, it will take some time. It also needs access to Butler repository which contains data for your instrument, this is needed to load packer code which knows how to unpack ccdVisit into visit+detector. To import the packer code the package that contains the instrument code needs to be setup as well. For LSST, for example, we have obs_lsst package. So you have to provide a couple of extra parameters:
       setup -k obs_something  # the package for your instrument.
       BUTLER=<name/config of the Butler for your instrument>
       INSTRUMENT=<instrument name as defined in butler>
       apdb-migrate-sql upgrade --options butler-repo=$BUTLER --options instrument=$INSTRUMENT $DB_URL schema_1.0.0
  1. Remaining migrations are simpler and should run faster, I prefer to run them one-by-one, though you can run one command and give it the final revision:
       apdb-migrate-sql upgrade $DB_URL schema_2.0.0
       apdb-migrate-sql upgrade $DB_URL schema_2.0.1
       apdb-migrate-sql upgrade $DB_URL schema_3.0.0
       apdb-migrate-sql upgrade $DB_URL schema_4.0.0
       apdb-migrate-sql upgrade $DB_URL schema_5.0.0
       apdb-migrate-sql upgrade $DB_URL schema_6.0.0

Please try this and let me know what kind of issues you see.

#7

Hi,

Thank you again for providing the migration instructions and updating dax_apdb_migrate to support our pre-v29 APDB schema. We’ve started testing the upgrade process and would like to share our progress and a blocking issue we encountered.

Goal

Test end-to-end compatibility between v24 and v29 processing by:

  1. Processing one visit (r-band) with v24 pipeline into an APDB.
  2. Upgrading that APDB to v29-compatible schema using dax_apdb_migrate.
  3. Processing a later visit (r-band) with v29 pipeline into the same upgraded DB, to verify source association continuity.

Environment

  • Source data: WFST NEP field, raw images ingested via obs_wfc
  • v24 stack: lsst_distrib v24.0.0
  • v29 stack: lsst_distrib v29.2.0
  • Database: PostgreSQL 14, local instance

Steps Taken

  1. Created a fresh v24 APDB test_update_ap and processed one visit through apPipe → diaPipe, successfully generating DIA sources/objects and alerts.

  2. Cloned the database for migration testing:

createdb -T test_update_ap test_update_ap_v29 -U caimx
  1. Built dax_apdb_migrate in v29 environment:
git clone https://github.com/lsst-dm/dax_apdb_migrate.git
cd dax_apdb_migrate
setup -k -r .
scons
  1. Successfully ran show-current:
DB_URL='postgresql://caimx:<PASSWORD>@10.10.10.50/test_update_ap_v29'
apdb-migrate-sql show-current --no-metadata -m $DB_URL

Output:

ApdbSql: 0.1.0 -> ApdbSql_0.1.0
schema: 0.1.0 -> schema_0.1.0
  1. Attempted stamp — this failed:
apdb-migrate-sql stamp --no-metadata $DB_URL

:red_circle: Error Message

Traceback (most recent call last):

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 143, in __init__

    self._dbapi_connection = engine.raw_connection()

                             ^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3301, in raw_connection

    return self.pool.connect()

           ^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 447, in connect

    return _ConnectionFairy._checkout(self)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 711, in checkout

    rec = pool._do_get()

          ^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 306, in _do_get

    return self._create_connection()

           ^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 388, in _create_connection

    return _ConnectionRecord(self)

           ^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 673, in __init__

    self.__connect()

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 899, in __connect

    with util.safe_reraise():

         ^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__

    raise exc_value.with_traceback(exc_tb)

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 895, in __connect

    self.dbapi_connection = connection = pool._invoke_creator(self)

                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 661, in connect

    return dialect.connect(*cargs, **cparams)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 629, in connect

    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/psycopg2/__init__.py", line 122, in connect

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

psycopg2.OperationalError: connection to server at "10.10.10.50", port 5432 failed: FATAL:  password authentication failed for user "caimx"

connection to server at "10.10.10.50", port 5432 failed: FATAL:  password authentication failed for user "caimx"

  

  

The above exception was the direct cause of the following exception:

  

Traceback (most recent call last):

  File "/NAS/v29_test/20250106_apdb_update_test/dax_apdb_migrate/bin/apdb-migrate-sql", line 5, in <module>

    sys.exit(main())

             ^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/click/core.py", line 1161, in __call__

    return self.main(*args, **kwargs)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/click/core.py", line 1082, in main

    rv = self.invoke(ctx)

         ^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/click/core.py", line 1697, in invoke

    return _process_result(sub_ctx.command.invoke(sub_ctx))

                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/click/core.py", line 1443, in invoke

    return ctx.invoke(self.callback, **ctx.params)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/click/core.py", line 788, in invoke

    return __callback(*args, **kwargs)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/20250106_apdb_update_test/dax_apdb_migrate/python/lsst/dax/apdb_migrate/sql/cli/apdb_migrate_sql.py", line 130, in stamp

    script.migrate_stamp(*args, **kwargs)

  File "/NAS/v29_test/20250106_apdb_update_test/dax_apdb_migrate/python/lsst/dax/apdb_migrate/sql/script/migrate_stamp.py", line 107, in migrate_stamp

    command.stamp(cfg, rev, purge=purge)

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/alembic/command.py", line 789, in stamp

    script.run_env()

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/alembic/script/base.py", line 545, in run_env

    util.load_python_file(self.dir, "env.py")

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file

    module = load_module_py(module_id, path)

             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py

    spec.loader.exec_module(module)  # type: ignore

    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "<frozen importlib._bootstrap_external>", line 999, in exec_module

  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed

  File "/NAS/v29_test/20250106_apdb_update_test/dax_apdb_migrate/migrations/sql/_alembic/env.py", line 72, in <module>

    run_migrations_online()

  File "/NAS/v29_test/20250106_apdb_update_test/dax_apdb_migrate/migrations/sql/_alembic/env.py", line 58, in run_migrations_online

    with connectable.connect() as connection:

         ^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3277, in connect

    return self._connection_cls(self)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 145, in __init__

    Connection._handle_dbapi_exception_noconnection(

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2440, in _handle_dbapi_exception_noconnection

    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 143, in __init__

    self._dbapi_connection = engine.raw_connection()

                             ^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3301, in raw_connection

    return self.pool.connect()

           ^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 447, in connect

    return _ConnectionFairy._checkout(self)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 711, in checkout

    rec = pool._do_get()

          ^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 306, in _do_get

    return self._create_connection()

           ^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 388, in _create_connection

    return _ConnectionRecord(self)

           ^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 673, in __init__

    self.__connect()

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 899, in __connect

    with util.safe_reraise():

         ^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 224, in __exit__

    raise exc_value.with_traceback(exc_tb)

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 895, in __connect

    self.dbapi_connection = connection = pool._invoke_creator(self)

                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 661, in connect

    return dialect.connect(*cargs, **cparams)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 629, in connect

    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/NAS/v29_test/lsst_stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/psycopg2/__init__.py", line 122, in connect

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "10.10.10.50", port 5432 failed: FATAL:  password authentication failed for user "caimx"

connection to server at "10.10.10.50", port 5432 failed: FATAL:  password authentication failed for user "caimx"

  

(Background on this error at: https://sqlalche.me/e/20/e3q8)

Our Checks So Far

  • Can connect to the DB via psql "$DB_URL" and query tables.

We’re happy to adjust our workflow based on your recommendation.

Could you please advise how we can resolve this password authentication error during the stamp step, given that direct psql connections with the same URL work fine?

Thanks again for your support!

#8

This is the reason, I think you need to define proper credentials in ~/.pgpass.

#9

Sorry for being so terse. I suspect that there may be a bug in either Alembic or in our code that does not handle password in URL correctly. Could you try to add credentials to ~/.pgpass file, one line that should look like:

10.10.10.50:5432:test_update_ap_v29:caimx:<PASSWORD>

(and do chmod 600 ~/.pgpass so that it’s not world-readable)

If it does not fix the error, let me know, I’ll have to look into it deeper.

#10

Hi,

Thanks for the suggestion!

I added the credentials to ~/.pgpass as you recommended:

10.10.10.50:5432:test_update_ap_v29:caimx:<PASSWORD>

and removed the password from the database URL.

This fixed the authentication error completely.

All commands (show-current, stamp, and all upgrade steps up to schema_6.0.0) now run successfully.

I was also able to process a new visit with the v29 pipeline into the upgraded APDB, and source association between v24 and v29 data works correctly.

So the issue was indeed related to password handling in the URL. Using .pgpass is a solid workaround.

Thanks again for your help!

#11

Thanks for confirming. I’ll try to investigate why password in URL does not work.

1 Like