Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-36658

Install pg_sphere Postgres extension for use with obscore.

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: None

      Description

      For spatial indexing of obscore table we need pg_sphere extension enabled. Standard postgres deployment usually comes without that extension, so it has to be installed separately. Details for that process depend on how postgres is deployed. Some Linux distributions may have that extension in their repos, and also Postgres has it in its repos as well.

      I think we need that for USDF and also for cloud instance, likely very different approaches needed for those two cases.

        Attachments

          Activity

          Hide
          salnikov Andy Salnikov added a comment -

          Yee Li, I saw few instances of this exception:

          • 2022-11-04T00:57:16.401-07:00
          • 2022-11-03T23:10:09.020-07:00
          • 2022-11-03T19:21:27.423-07:00
          • 2022-11-03T17:30:57.513-07:00

          All seem to be related to a long-running transaction.

          Show
          salnikov Andy Salnikov added a comment - Yee Li , I saw few instances of this exception: 2022-11-04T00:57:16.401-07:00 2022-11-03T23:10:09.020-07:00 2022-11-03T19:21:27.423-07:00 2022-11-03T17:30:57.513-07:00 All seem to be related to a long-running transaction.
          Hide
          salnikov Andy Salnikov added a comment - - edited

          pg_sphere extension was installed yesterday by DBAs on usdf-butler instance and added to lsstdb1 database. Today I loaded ci_hsc data into a separate schema in that database and ran obscore migration scripts on to test that pg_sphere is in usable shape. Everything works perfectly, queries run successfully and spatial indexing works as well.

          I'll wait for feedback on DM-36701 to finalize configuration for OGA repository and will add obscore to that repo. This ticket is finished, no code to review.

          Yee Li, thanks a lot for coordinating this installation!

          Show
          salnikov Andy Salnikov added a comment - - edited pg_sphere extension was installed yesterday by DBAs on usdf-butler instance and added to lsstdb1 database. Today I loaded ci_hsc data into a separate schema in that database and ran obscore migration scripts on to test that pg_sphere is in usable shape. Everything works perfectly, queries run successfully and spatial indexing works as well. I'll wait for feedback on DM-36701 to finalize configuration for OGA repository and will add obscore to that repo. This ticket is finished, no code to review. Yee Li , thanks a lot for coordinating this installation!
          Hide
          gpdf Gregory Dubois-Felsmann added a comment -

          Andy Salnikov, what is the origin of the pgsphere extension that you've actually been installing? Are we getting an RPM from outside, or building it ourselves from source, or...?

          Show
          gpdf Gregory Dubois-Felsmann added a comment - Andy Salnikov , what is the origin of the pgsphere extension that you've actually been installing? Are we getting an RPM from outside, or building it ourselves from source, or...?
          Hide
          salnikov Andy Salnikov added a comment -

          Gregory Dubois-Felsmann, I believe it was built from source, our DBAs made a special image that includes pg_sphere. Yee Li can probably point you to specifics.

          Show
          salnikov Andy Salnikov added a comment - Gregory Dubois-Felsmann , I believe it was built from source, our DBAs made a special image that includes pg_sphere. Yee Li can probably point you to specifics.
          Show
          ytl Yee Li added a comment - yep - from source. see  https://github.com/slaclab/rubin-usdf-butler-deploy/blob/main/Dockerfile#L17

            People

            Assignee:
            salnikov Andy Salnikov
            Reporter:
            salnikov Andy Salnikov
            Watchers:
            Andy Salnikov, Gregory Dubois-Felsmann, Jim Bosch, Russ Allbery, Tim Jenness, Yee Li
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Jenkins

                No builds found.