Work out how to get testsmoke API talking to DB

[ranguard]

Problem:

Error while loading /var/lib/coresmokedb-api/bin/app.psgi: DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: DBI connect('host=dbserver;dbname=coresmokedb','coresmokedb',...) failed: could not translate host name "dbserver"

This means the container is not picking up the overrides we are trying to deploy with the DO DB connection details.

Investigation:

Please try copy: https://github.com/metacpan/metacpan-conf-private/blob/master/test-smoke/docker_local.yaml into environments/docker_local.yaml in the API container see if you can start the server:

https://github.com/metacpan/Perl5-CoreSmokeDB-API/blob/preview/Dockerfile#L23-L24 (note the -E docker).

If you can then the issue is how I am deploying that docker_local.yaml file in K8s, if you can NOT then please workout why and let me know so I can then fix in the k8s deploy.

Also - deploy schema whilst you are there!

https://github.com/metacpan/Perl5-CoreSmokeDB-API/blob/preview/README.md#deploy-the-database-schema

[ranguard]

https://github.com/metacpan/metacpan-k8s/blob/main/apps/test-smoke/base/deployment.yaml#L60 specifies where we are placing the docker_local.yaml I tried /var/lib/coresmokedb-api/environments/docker_local.yaml as well as /var/lib/coresmokedb-api/docker_local.yaml

[oalders]

The issue appears to be that docker_local.yaml needs to be renamed to docker_local.yml. Beyond that we need to ensure the following volume map exists:

-v $PWD/environments:/var/lib/coresmokedb-api/environments

[oalders]

I tried to deploy the schema from my local container.

sudo docker run --rm -it \
  --entrypoint bash \
  -v $PWD/environments:/var/lib/coresmokedb-api/environments \
  metacpan/perl5-coresmokedb-api:latest

First I had to:

cpm install -g Perl5::CoreSmokeDB::Schema

Then I ran:

perl -Ilib -MPerl5::CoreSmokeDB::Schema -wE \
    'Perl5::CoreSmokeDB::Schema->connect(
      "dbi:Pg:dbname=coresmokedb;host=[REDACTED];port=[REDACTED]","coresmokedb","[REDACTED:seekrit]",{ignore_version => 1}
    )->deploy'

I got:

Perl5::CoreSmokeDB::Schema::pg_pre_deploy(): DBI Exception: DBD::Pg::db do failed: ERROR: permission denied for schema public at /usr/local/lib/perl5/site_perl/5.36.3 /Perl5/CoreSmokeDB/Schema.pm line 108

Once I switched to the doadmin user and password, I was able to run the deploy without getting any errors.

[ranguard]

I think I've granted coresmokedb user permissions on public.* in the coresmokedb db and switched ownership of the existing tablers to coresmokedb user

[ranguard]

Connecting to running API container:

kubectl get pod -n apps--testsmoke
NAME                             READY   STATUS             RESTARTS        AGE
testsmoke-api-6565c57c6d-2sw4d   1/2     CrashLoopBackOff   6 (2m28s ago)   8m33s
testsmoke-api-85fffbf74b-2dc99   0/2     CrashLoopBackOff   4 (19s ago)     49s
testsmoke-api-85fffbf74b-2xq55   0/2     CrashLoopBackOff   4 (23s ago)     52s
testsmoke-api-85fffbf74b-n4qx5   0/2     CrashLoopBackOff   4 (19s ago)     45s
testsmoke-web-577bf495bc-96hw8   1/1     Running            0               17d
testsmoke-web-577bf495bc-n9mjt   1/1     Running            0               38d
testsmoke-web-577bf495bc-nmhdh   1/1     Running            0               38d

Want the ready one... - this gets a terminal

kubectl exec --stdin --tty -c api -n apps--testsmoke testsmoke-api-6565c57c6d-2sw4d  -- /bin/bash

[ranguard]

In the above container I can run:

perl -I local/lib/perl5 local/bin/plackup -s Starman -p 8080 -E docker bin/app.psgi

2024/08/11-09:47:52 Starman::Server (type Net::Server::PreFork) starting! pid(25)
Resolved [*]:8080 to [0.0.0.0]:8080, IPv4
Binding to TCP port 8080 on host 0.0.0.0 with IPv4
Setting gid to "0 0 0"

[ranguard]

Confirmed API up:

Connect to container

kubectl exec --stdin --tty -c api -n apps--testsmoke testsmoke-api-55797f87bf-22kxk  -- /bin/bash

Curl on container to get api version info

root@testsmoke-api-55797f87bf-22kxk:/var/lib/coresmokedb-api# curl localhost:5050/api/version
{"db_version":"3","schema_version":"3","version":"1.04"}

Just adding comment as might be useful later!

[ranguard]

Think this might be working! - please add some data to into DB

https://testsmoke-web.do.metacpan.org/ https://testsmoke-api.do.metacpan.org/api/version

Next step would be to setup fastly to then proxy to these domains

[ranguard]

I've added indexes:

CREATE INDEX idx_report_hostname_plevel_smokedate ON report (hostname, plevel, smoke_date);
CREATE INDEX idx_report_query_optimization 
ON report (hostname, plevel, smoke_date)
INCLUDE (architecture, osname, osversion, perl_id, git_id, git_describe, smoke_branch, username, summary, cpu_count, cpu_description);

[ranguard]

NOTE: We have NO backups on this DB yet!