[CI/CD Improvements] Update and simplify CD workflows

[mikealfare]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-postgres functionality, rather than a Big Idea better suited to a discussion

Describe the feature

As a maintainer, I want to simplify the CD jobs, so that deployment is more reliable and stable.

Acceptance Criteria

Preview Give feedback

1. All workflows adhere to the style standards below
2. This package is automatically published to dbt's private PyPI daily from main; the current workflow is cancelled in favor of the new workflow when this job is resubmitted
3. This package can be published on demand to the public PyPI, GitHub, and Docker for the specified branch; the current workflow is cancelled in favor of the new workflow when this job is resubmitted for the same branch
4. This package is tested against appropriate published packages prior to releasing
5. I can publish to both test and production versions of publishing targets
6. Publishing to PyPI is authorized via Trusted Publishers
7. Integration tests are only configured in one place (e.g. environment variables, the Postgres service, etc.)
8. Legacy workflows are removed (e.g. release, release-internal, release_prep_hatch, version-bump, etc.)

Suggestions

• Create two workflows:
  • release-cloud.yml
    • Exists as release-internal
    • Run on schedule: 22:00 UTC
    • Run on workflow_dispatch
    • Test against private PyPI
    • Slack notification on failure
  • release-open-source.yml
    • Exists as release
    • Run on workflow_dispatch
    • Test against public PyPI
    • Slack notification on failure

Style standards:

• Job ids, step ids, and variables are in kebab case
• Job names, step names, and description fields follow dbt docs standards (capitalize first word only)
• Extra descriptors should be avoided unless required for disambiguation, e.g.
  • version-number -> version
  • archive-name -> archive
• Workflow files use a four space tab
• Scripts (inline or separate files) use environment variables in env instead of inline substitution like ${{ inputs.value }}

[mikealfare]

This will happen as part of the monorepo work, hence this is duplicative.