[ProGet] Manual database upgrade (docker, kubernetes)

atripp

I think we're really close, actually. You're right, the service code (copied below), sets the version, so this wouldn't work anyways.

However, I think if we just write-out a script at build time that does something like EXEC Configuration_SetValue 'Internal.DbSchemaVersion', $VersionNumber... and then incluse that in SqlScripts.zip`, it would always work.

Ultimately what I'd love to do is build a guide like the Docker Compose Installation Guide, but have it be the Kubernetes Installation Guide.

If we get the version-number-setter script included in SqlScripts.zip, how close do you think we'll be to a Kubernetes install guide? Would it basically be the same as that Compose guide, but just have k8-commands and k8-sample code instead?

        private static int UpdateDatabaseSchema()
        {
            Console.WriteLine($"ProGet version is {typeof(Program).Assembly.GetName().Version}.");
            var currentVersion = getCurrentVersion();
            Console.WriteLine($"Current DB schema version is {currentVersion?.ToString() ?? "unknown"}.");
            if (currentVersion == typeof(Program).Assembly.GetName().Version)
                return 0;

            using (var p = Process.Start(getStartInfo()))
            {
                p.WaitForExit();

                if (p.ExitCode == 0)
                    setCurrentVersion();

                return p.ExitCode;
            }

            static ProcessStartInfo getStartInfo()
            {
#if NET452
                return new()
                {
                    FileName = "mono",
                    Arguments = $"/usr/local/proget/db/inedosql.exe update /usr/local/proget/db/SqlScripts.zip --connection-string=\"{SharedConfig.ConnectionString}\""
                };
#else
                return new()
                {
                    FileName = "/usr/local/proget/db/inedosql",
                    ArgumentList =
                    {
                        "update",
                        "/usr/local/proget/db/SqlScripts.zip",
                        $"--connection-string={SharedConfig.ConnectionString}"
                    }
                };
#endif
            }

            static Version getCurrentVersion()
            {
                try
                {
                    var s = DB.Configuration_GetValue("Internal.DbSchemaVersion")?.Value_Text;
                    Version.TryParse(s, out var v);
                    return v;
                }
                catch
                {
                    return null;
                }
            }

            static void setCurrentVersion()
            {
                try
                {
                    DB.Configuration_SetValue("Internal.DbSchemaVersion", typeof(Program).Assembly.GetName().Version.ToString());
                }
                catch
                {
                }
            }
        }

viceice

It would be nice, if there is a possibillity to check the version and only run the update if the version doesn't match.

here a simplified stateful set:

kind: StatefulSet
apiVersion: apps/v1
metadata:
  name: proget
  namespace: services
  labels:
    app: proget
spec:
  replicas: 1
  selector:
    matchLabels:
      app: proget
  template:
    metadata:
      labels:
        app: proget
    spec:
      volumes:
        - name: packages
          emptyDir: {} # should be pvc
        - name: extensions
          emptyDir: {} # should be pvc
      init_container:
        - name: upgrade-db
          image: 'ghcr.io/visualon/proget:5.3.21'
          command: ["/usr/local/proget/db/inedosql", "update", "/usr/local/proget/db/SqlScripts.zip"] # should only update if required
          env:
            - name: SQL_CONNECTION_STRING
              value: Data Source=proget-sql; Initial Catalog=ProGet; User ID=sa; Password=<YourStrong!Passw0rd> # should be come from secret
          volumeMounts:
            - name: packages
              mountPath: /var/proget/packages
            - name: extensions
              mountPath: /var/proget/extensions
      containers:
        - name: proget
          image: 'ghcr.io/visualon/proget:5.3.21'
          ports:
            - containerPort: 80
              protocol: TCP
          env:
            - name: SQL_CONNECTION_STRING
              value: Data Source=proget-sql; Initial Catalog=ProGet; User ID=sa; Password=<YourStrong!Passw0rd> # should be come from secret
          volumeMounts:
            - name: packages
              mountPath: /var/proget/packages
            - name: extensions
              mountPath: /var/proget/extensions
  serviceName: proget

viceice

So the init container runs before the normal proget container.

Normally the proget container gets some startup and liveness probes, if they fail proget will be restartet.

So if i upgrade the image and a database update is needed, this would currently trap theed by the probes and i get an endless restart loop.

Of cause i can extend the startup probes timeout, but that would cause a lot of failues in kubernetes logs.

atripp

@viceice sorry on the slow reply, but I presented this at our engineering meeting and our product manager thinks it's a great idea. But, he asked me to write the documentation on it so they know exactly what changes need to be made, and how it will be used...

So I wonder if you can help?

https://docs.inedo.com/docs/proget/installation/installation-guide/linux-docker#upgrading

Basically I think we should add another heading like this.

Upgrading the Database (Optional)

The ProGet container will automatically upgrade the database when starting up; this upgrade might take a few minutes, which may appear to cause delays to automated probes (??) like Kubernetes.

You can run the following command to instruct the ProGet container to upgrade the database, and then exit.

docker run ???? docker exec ???

I'm just stuck at how to document the new command to run.

From a code/C# standpoint, we can simply just add a option to ProGet.Service.exe to upgrade the database and exit. And then, we won't upgrade the database if the version matches.

viceice

ok, database upgrade command can be run in init_container (wicch can be without any probes)

here a new sample:

kind: StatefulSet
apiVersion: apps/v1
metadata:
  name: proget
  namespace: services
  labels:
    app: proget
spec:
  replicas: 1
  selector:
    matchLabels:
      app: proget
  template:
    metadata:
      labels:
        app: proget
    spec:
      volumes:
        - name: packages
          emptyDir: {} # should be pvc
        - name: extensions
          emptyDir: {} # should be pvc
      init_container:
        - name: upgrade-db
          image: 'ghcr.io/visualon/proget:5.3.21'
          command: ["/usr/local/proget/service/ProGet.Service.exe", "upgrade"] # Updates the database if required
          env:
            - name: SQL_CONNECTION_STRING
              value: Data Source=proget-sql; Initial Catalog=ProGet; User ID=sa; Password=<YourStrong!Passw0rd> # should be come from secret
          volumeMounts:
            - name: packages
              mountPath: /var/proget/packages
            - name: extensions
              mountPath: /var/proget/extensions
      containers:
        - name: proget
          image: 'ghcr.io/visualon/proget:5.3.21'
          ports:
            - containerPort: 80
              protocol: TCP
          env:
            - name: SQL_CONNECTION_STRING
              value: Data Source=proget-sql; Initial Catalog=ProGet; User ID=sa; Password=<YourStrong!Passw0rd> # should be come from secret
          volumeMounts:
            - name: packages
              mountPath: /var/proget/packages
            - name: extensions
              mountPath: /var/proget/extensions
          livenessProbe: # additional startupProbe and readyness probes are available
            httpGet:
              path: /log-in
              port: 80
            initialDelaySeconds: 15
            periodSeconds: 5
  serviceName: proget

This will call the database update command with an init container which has no timeouts. After that is done (exit code zero), kubernetes will start the normal container(s) which can skip upgrade, as it's aready done.

So an proget upgrade would simply be an image update

probes: https://kubernetes.io/docs/tasks/configure-pod-container/configure-liveness-readiness-startup-probes

atripp

@viceice thanks! We will add a command via PG-1897 to handle this, it seems quite straight forward...

Stay tuned :)

viceice

The issue is closed, so can i implement it to my kubernetes deployment?

atripp

@viceice it was shipped in 5.3.23, so please give it a shot and let us know!

We didn't test it in a Kubernetes deployment (I hope we can get great docs on that some day!), but in the meantime I did add this to the documentation:

https://github.com/Inedo/inedo-docs/commit/358be6d03160ff569d791532f14cc5f05012b2a8

If you have any suggestions, especially on how to improve the docs, please share it or submit a PR to our docs :)

viceice

https://github.com/Inedo/inedo-docs/pull/223

viceice

getting this when trying to run upgrade command

For help on a specific command, type: ProGet.Service help [command]
To run interactively, type: ProGet.Service interactive
Invalid command: upgrade

ProGet.Service 5.3.24.7
Usage: ProGet.Service [command]

Commands:
run Runs the ProGet service and/or the ProGet web server interactively.
install Installs the ProGet service as a Windows service.
uninstall Uninstalls the ProGet Windows service.
resetadminpassword Switches to the built-in user directory and changes the Admin account password to "Admin".
upgradedb Upgrades the database schema version to this version of ProGet.

For help on a specific command, type: ProGet.Service help [command]
To run interactively, type: ProGet.Service interactive

viceice

@viceice said in [ProGet] Manual database upgrade (docker, kubernetes):

upgradedb

needs to be ProGet.Service upgradedb. fixed it in my pr

viceice

It's now always dooing upgrade

ProGet version is 5.3.24.7.
Current DB schema version is 5.3.24.
Executing untracked script DDL-DML/0000. Untracked/10. Block 5.3 Upgrades.sql...
Executing untracked script DDL-DML/0000. Untracked/10. Block SQL Server 2005.sql...
Tracked script DDL-DML/v1-3/0002. 0.1.0/10 SET DATABASE PROPERTIES.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/20 CREATE TYPES.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/21 CREATE ROLE ProGetUser_Role.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30 CREATE TABLE Configuration.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30 CREATE TABLE Environments.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30CTC1 CREATE TABLE Connectors.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30CTC2 CREATE TABLE ConnectorFilters.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30EVT1 CREATE TABLE EventTypes.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30EVT2 CREATE TABLE EventTypeDetails.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30EVT3 CREATE TABLE EventOccurences.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30EVT4 CREATE TABLE EventOccurenceDetails.sql has already been executed; skipping...
Tracked script DDL-DML/v1-3/0002. 0.1.0/30USR1 CREATE TABLE Groups.sql has already been executed; skipping...

It should skip updates update at all if already uptodate, otherwise startup will be very slow

atripp

Thanks for making the pull request @viceice !!

Well, I can see the issue... 5.3.24.7 != 5.3.24

Okay, so next version it should work. Diagnostics.DbVersion is set in the build/release process, so we just changed the code that sets Diagnostics.DbVersion.

Next time it should work.

viceice

I know, i saw that difference. Just wanted to inform you

saml_4392

I can't wait to see this works. Is this one fixed in 5.3.25? Also could we have a helm chart? If you guys don't mind, I think you guys can work with https://github.com/bitnami/charts They are pretty good at creating helm chart.

stevedennis

@saml_4392 in theory it's fixed, but we didn't test it

Could you open a new thread about a creating a Helm chart for ProGet itself? That'd be a great place to start that discussion, and get community direction and feedback from users - and provide nice opportunities to partner with organizations like bitnami, who could help