Page MenuHomeSoftware Heritage
Differential D5393

staging: Install new swh-web db
ClosedPublic
Actions

Authored by ardumont on Apr 1 2021, 10:30 AM.

Details

Reviewers
anlambert
Group Reviewers
System administrators
Maniphest Tasks
T2945: Migrate swh-web production database from SQLite to PostgreSQL
Commits
rSPSITEff4940c92eeb: staging: Install new swh-web db
Summary

Related to T2945

Test Plan

octocatalog-diff and vagrant happy:

$ bin/octocatalog-diff --octocatalog-diff-args --no-truncate-details --to staging db1.internal.staging.swh.network
Found host db1.internal.staging.swh.network
*** Running octocatalog-diff on host db1.internal.staging.swh.network
I, [2021-04-01T10:57:33.751270 #13533]  INFO -- : Catalogs compiled for db1.internal.staging.swh.network
I, [2021-04-01T10:57:35.924704 #13533]  INFO -- : Diffs computed for db1.internal.staging.swh.network
diff origin/production/db1.internal.staging.swh.network current/db1.internal.staging.swh.network
*******************************************
  Concat::Fragment[_postgres] =>
   parameters =>
     content =>
      @@ -8,3 +8,4 @@
       clearly-defined = host=localhost port=5433 auth_user=postgres
       clearly-defined-full = host=localhost port=5433 auth_user=postgres
      +swh-web = host=localhost port=5433 auth_user=postgres
      _
*******************************************
  Concat_fragment[_postgres] =>
   parameters =>
     content =>
      @@ -8,3 +8,4 @@
       clearly-defined = host=localhost port=5433 auth_user=postgres
       clearly-defined-full = host=localhost port=5433 auth_user=postgres
      +swh-web = host=localhost port=5433 auth_user=postgres
      _
*******************************************
  File[/home/ardumont/.pg_service.conf] =>
   parameters =>
     content =>
      @@ -36,4 +36,9 @@
       port=5432
       user=swh-indexer
      +[admin-swh-web]
      +dbname=swh-web
      +host=db1.internal.staging.swh.network
      +port=5432
      +user=swh-web
       [swh]
       dbname=swh
      @@ -70,4 +75,9 @@
       host=db1.internal.staging.swh.network
       port=5432
      +user=guest
      +[swh-web]
      +dbname=swh-web
      +host=db1.internal.staging.swh.network
      +port=5432
       user=guest
      _
*******************************************
  File[/home/ardumont/.pgpass] =>
   parameters =>
     content =>
      @@ -9,4 +9,5 @@
       somerset.internal.softwareheritage.org:5432:swh:postgres:swh-deploy-storage-db-password
       db1.internal.staging.swh.network:5432:swh-indexer:swh-indexer:swh-deploy-indexer-storage-db-password
      +db1.internal.staging.swh.network:5432:swh-web:swh-web:swh-deploy-webapp-db-password
       db1.internal.staging.swh.network:5432:swh:guest:guest
       db1.internal.staging.swh.network:5432:swh-deposit:guest:guest
      @@ -16,3 +17,4 @@
       somerset.internal.softwareheritage.org:5432:swh:guest:guest
       db1.internal.staging.swh.network:5432:swh-indexer:guest:guest
      +db1.internal.staging.swh.network:5432:swh-web:guest:guest
      _
*******************************************
  File[/home/zack/.pg_service.conf] =>
   parameters =>
     content =>
      @@ -36,4 +36,9 @@
       port=5432
       user=swh-indexer
      +[admin-swh-web]
      +dbname=swh-web
      +host=db1.internal.staging.swh.network
      +port=5432
      +user=swh-web
       [swh]
       dbname=swh
      @@ -70,4 +75,9 @@
       host=db1.internal.staging.swh.network
       port=5432
      +user=guest
      +[swh-web]
      +dbname=swh-web
      +host=db1.internal.staging.swh.network
      +port=5432
       user=guest
      _
*******************************************
  File[/home/zack/.pgpass] =>
   parameters =>
     content =>
      @@ -9,4 +9,5 @@
       somerset.internal.softwareheritage.org:5432:swh:postgres:swh-deploy-storage-db-password
       db1.internal.staging.swh.network:5432:swh-indexer:swh-indexer:swh-deploy-indexer-storage-db-password
      +db1.internal.staging.swh.network:5432:swh-web:swh-web:swh-deploy-webapp-db-password
       db1.internal.staging.swh.network:5432:swh:guest:guest
       db1.internal.staging.swh.network:5432:swh-deposit:guest:guest
      @@ -16,3 +17,4 @@
       somerset.internal.softwareheritage.org:5432:swh:guest:guest
       db1.internal.staging.swh.network:5432:swh-indexer:guest:guest
      +db1.internal.staging.swh.network:5432:swh-web:guest:guest
      _
*******************************************
  File[/root/.pg_service.conf] =>
   parameters =>
     content =>
      @@ -36,4 +36,9 @@
       port=5432
       user=swh-indexer
      +[admin-swh-web]
      +dbname=swh-web
      +host=db1.internal.staging.swh.network
      +port=5432
      +user=swh-web
       [swh]
       dbname=swh
      @@ -70,4 +75,9 @@
       host=db1.internal.staging.swh.network
       port=5432
      +user=guest
      +[swh-web]
      +dbname=swh-web
      +host=db1.internal.staging.swh.network
      +port=5432
       user=guest
      _
*******************************************
  File[/root/.pgpass] =>
   parameters =>
     content =>
      @@ -9,4 +9,5 @@
       somerset.internal.softwareheritage.org:5432:swh:postgres:swh-deploy-storage-db-password
       db1.internal.staging.swh.network:5432:swh-indexer:swh-indexer:swh-deploy-indexer-storage-db-password
      +db1.internal.staging.swh.network:5432:swh-web:swh-web:swh-deploy-webapp-db-password
       db1.internal.staging.swh.network:5432:swh:guest:guest
       db1.internal.staging.swh.network:5432:swh-deposit:guest:guest
      @@ -16,3 +17,4 @@
       somerset.internal.softwareheritage.org:5432:swh:guest:guest
       db1.internal.staging.swh.network:5432:swh-indexer:guest:guest
      +db1.internal.staging.swh.network:5432:swh-web:guest:guest
      _
*******************************************
  Pgbouncer::Databases[pgbouncer_module_databases] =>
   parameters =>
     databases =>
      - [{"source_db"=>"swh", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh"}, {"source_db"=>"swh-scheduler", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-scheduler"}, {"source_db"=>"swh-vault", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-vault"}, {"source_db"=>"swh-lister", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-lister"}, {"source_db"=>"swh-deposit", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-deposit"}, {"source_db"=>"swh-indexer", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-indexer"}, {"source_db"=>"clearly-defined", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-clearly-defined"}, {"source_db"=>"clearly-defined-full", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-clearly-defined-full"}]
      + [{"source_db"=>"swh", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh"}, {"source_db"=>"swh-scheduler", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-scheduler"}, {"source_db"=>"swh-vault", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-vault"}, {"source_db"=>"swh-lister", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-lister"}, {"source_db"=>"swh-deposit", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-deposit"}, {"source_db"=>"swh-indexer", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-indexer"}, {"source_db"=>"clearly-defined", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-clearly-defined"}, {"source_db"=>"clearly-defined-full", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-clearly-defined-full"}, {"source_db"=>"swh-web", "host"=>"localhost", "auth_user"=>"postgres", "port"=>5433, "alias"=>"staging-swh-web"}]
*******************************************
+ Postgresql::Server::Database[swh-web] =>
   parameters =>
      "connect_settings": {}
      "dbname": "swh-web"
      "istemplate": false
      "owner": "swh-web"
      "template": "template0"
*******************************************
+ Postgresql::Server::Database_grant[GRANT swh-web - ALL - swh-web] =>
   parameters =>
      "db": "swh-web"
      "privilege": "ALL"
      "role": "swh-web"
*******************************************
+ Postgresql::Server::Database_grant[swh-web] =>
   parameters =>
      "db": "swh-web"
      "privilege": "connect"
      "role": "guest"
*******************************************
+ Postgresql::Server::Db[swh-web] =>
   parameters =>
      "dbname": "swh-web"
      "grant": "ALL"
      "istemplate": false
      "owner": "swh-web"
      "password": "swh-deploy-webapp-db-password"
      "template": "template0"
      "user": "swh-web"
*******************************************
+ Postgresql::Server::Grant[database:GRANT swh-web - ALL - swh-web] =>
   parameters =>
      "connect_settings": {}
      "db": "swh-web"
      "ensure": "present"
      "object_arguments": []
      "object_name": "swh-web"
      "object_type": "DATABASE"
      "onlyif_exists": false
      "port": 5433
      "privilege": "ALL"
      "psql_db": "postgres"
      "psql_user": "postgres"
      "role": "swh-web"
*******************************************
+ Postgresql::Server::Grant[database:swh-web] =>
   parameters =>
      "connect_settings": {}
      "db": "swh-web"
      "ensure": "present"
      "object_arguments": []
      "object_name": "swh-web"
      "object_type": "DATABASE"
      "onlyif_exists": false
      "port": 5433
      "privilege": "connect"
      "psql_db": "postgres"
      "psql_user": "postgres"
      "role": "guest"
*******************************************
+ Postgresql::Server::Role[swh-web] =>
   parameters =>
      "connect_settings": {}
      "connection_limit": "-1"
      "createdb": false
      "createrole": false
      "db": "postgres"
      "ensure": "present"
      "inherit": true
      "login": true
      "password_hash": "swh-deploy-webapp-db-password"
      "replication": false
      "superuser": false
      "update_password": true
      "username": "swh-web"
*******************************************
+ Postgresql_psql[ALTER DATABASE "swh-web" OWNER TO "swh-web"] =>
   parameters =>
      "connect_settings": {}
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_database JOIN pg_roles rol ON datdba = rol.oid WHERE datname = 'swh-web' AND rolname = 'swh-web'"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" CONNECTION LIMIT -1] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolconnlimit = -1"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" INHERIT] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolinherit = true"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" LOGIN] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolcanlogin = true"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" NOCREATEDB] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolcreatedb = false"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" NOCREATEROLE] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolcreaterole = false"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" NOREPLICATION] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolreplication = false"
*******************************************
+ Postgresql_psql[ALTER ROLE "swh-web" NOSUPERUSER] =>
   parameters =>
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web' AND rolsuper = false"
*******************************************
+ Postgresql_psql[ALTER ROLE swh-web ENCRYPTED PASSWORD ****] =>
   parameters =>
      "command": "ALTER ROLE \"swh-web\" ENCRYPTED PASSWORD '$NEWPGPASSWD'"
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "environment": "NEWPGPASSWD=swh-deploy-webapp-db-password"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_shadow WHERE usename = 'swh-web' AND passwd = 'md5394ca7114dbffdca1b3b44b3333aa61f'"
*******************************************
+ Postgresql_psql[CREATE DATABASE "swh-web"] =>
   parameters =>
      "command": "CREATE DATABASE \"swh-web\" WITH TEMPLATE = \"template0\"   "
      "connect_settings": {}
      "db": "postgres"
      "notify": ["Postgresql_psql[REVOKE CONNECT ON DATABASE \"swh-web\" FROM public]"]
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_database WHERE datname = 'swh-web'"
*******************************************
+ Postgresql_psql[CREATE ROLE swh-web ENCRYPTED PASSWORD ****] =>
   parameters =>
      "command": "CREATE ROLE \"swh-web\" ENCRYPTED PASSWORD '$NEWPGPASSWD' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1"
      "connect_settings": {}
      "cwd": "/tmp"
      "db": "postgres"
      "environment": "NEWPGPASSWD=swh-deploy-webapp-db-password"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_roles WHERE rolname = 'swh-web'"
*******************************************
+ Postgresql_psql[REVOKE CONNECT ON DATABASE "swh-web" FROM public] =>
   parameters =>
      "connect_settings": {}
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "refreshonly": true
*******************************************
+ Postgresql_psql[UPDATE pg_database SET datistemplate = false WHERE datname = 'swh-web'] =>
   parameters =>
      "connect_settings": {}
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 FROM pg_database WHERE datname = 'swh-web' AND datistemplate = false"
*******************************************
+ Postgresql_psql[grant:database:GRANT swh-web - ALL - swh-web] =>
   parameters =>
      "command": "GRANT ALL ON DATABASE \"swh-web\" TO \"swh-web\""
      "connect_settings": {}
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 WHERE has_database_privilege('swh-web', 'swh-web', 'CREATE') = true"
*******************************************
+ Postgresql_psql[grant:database:swh-web] =>
   parameters =>
      "command": "GRANT CONNECT ON DATABASE \"swh-web\" TO \"guest\""
      "connect_settings": {}
      "db": "postgres"
      "port": 5433
      "psql_group": "postgres"
      "psql_path": "/usr/bin/psql"
      "psql_user": "postgres"
      "unless": "SELECT 1 WHERE has_database_privilege('guest', 'swh-web', 'CONNECT') = true"
*******************************************
*** End octocatalog-diff on db1.internal.staging.swh.network

Diff Detail

Repository
rSPSITE puppet-swh-site
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.

Event Timeline

ardumont requested review of this revision.Apr 1 2021, 10:30 AM
ardumont created this revision.
Harbormaster completed remote builds in B20378: Diff 19309.Apr 1 2021, 10:30 AM
anlambert accepted this revision.Apr 1 2021, 10:51 AM
anlambert added a subscriber: anlambert.

Looks good, thanks !

This revision is now accepted and ready to land.Apr 1 2021, 10:51 AM
ardumont updated this revision to Diff 19310.Apr 1 2021, 10:54 AM
  • Add missing pgservice aliases
  • Fix one typo for staging db host
Harbormaster completed remote builds in B20379: Diff 19310.Apr 1 2021, 10:54 AM
ardumont updated this revision to Diff 19311.Apr 1 2021, 10:55 AM

Use the right repository to update the diff might return more sensible result

Harbormaster completed remote builds in B20380: Diff 19311.Apr 1 2021, 10:55 AM
ardumont edited the test plan for this revision. (Show Details)Apr 1 2021, 10:58 AM
Closed by commit rSPSITEff4940c92eeb: staging: Install new swh-web db (authored by ardumont). · Explain WhyApr 1 2021, 11:00 AM
This revision was automatically updated to reflect the committed changes.
ardumont added a commit: rSPSITEff4940c92eeb: staging: Install new swh-web db.
ardumont mentioned this in T2945: Migrate swh-web production database from SQLite to PostgreSQL.Apr 1 2021, 4:42 PM

Revision Contents
Changeset List

PathSize
data/
common/
16 lines
deployments/
staging/
11 lines

Diff 19312

View Options

data/common/common.yaml

Loading...
View Options

data/deployments/staging/common.yaml

Loading...
About · Developer information · Legal