Merge branch 'sh-add-sql-migrate-status' into 'master'

Add Praefect command to show migration status

See merge request gitlab-org/gitaly!2041

12 files changed, 258 insertions, 12 deletions

diff --git a/NOTICE b/NOTICE
index 247d037cc..503167b2e 100644
--- a/NOTICE
+++ b/NOTICE
@@ -671,6 +671,30 @@ The above copyright notice and this permission notice shall be included in all c
 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+LICENSE - github.com/mattn/go-runewidth
+The MIT License (MIT)
+
+Copyright (c) 2016 Yasuhiro Matsumoto
+
+Permission is hereby granted, free of charge, to any person obtaining a copy
+of this software and associated documentation files (the "Software"), to deal
+in the Software without restriction, including without limitation the rights
+to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+copies of the Software, and to permit persons to whom the Software is
+furnished to do so, subject to the following conditions:
+
+The above copyright notice and this permission notice shall be included in all
+copies or substantial portions of the Software.
+
+THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
+SOFTWARE.
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 LICENSE - github.com/matttproud/golang_protobuf_extensions/pbutil
                                  Apache License
                            Version 2.0, January 2004
@@ -879,6 +903,28 @@ NOTICE - github.com/matttproud/golang_protobuf_extensions/pbutil
 Copyright 2012 Matt T. Proud (matt.proud@gmail.com)
 
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+LICENSE.md - github.com/olekukonko/tablewriter
+Copyright (C) 2014 by Oleku Konko
+
+Permission is hereby granted, free of charge, to any person obtaining a copy
+of this software and associated documentation files (the "Software"), to deal
+in the Software without restriction, including without limitation the rights
+to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+copies of the Software, and to permit persons to whom the Software is
+furnished to do so, subject to the following conditions:
+
+The above copyright notice and this permission notice shall be included in
+all copies or substantial portions of the Software.
+
+THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+THE SOFTWARE.
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 LICENSE - github.com/opentracing/opentracing-go
 The MIT License (MIT)
 
diff --git a/changelogs/unreleased/sh-add-sql-migrate-status.yml b/changelogs/unreleased/sh-add-sql-migrate-status.yml
new file mode 100644
index 000000000..40e5a3574
--- /dev/null
+++ b/changelogs/unreleased/sh-add-sql-migrate-status.yml
@@ -0,0 +1,5 @@
+---
+title: Add Praefect command to show migration status
+merge_request: 2041
+author:
+type: added
diff --git a/cmd/praefect/main.go b/cmd/praefect/main.go
index 24be3ce48..53561b9db 100644
--- a/cmd/praefect/main.go
+++ b/cmd/praefect/main.go
@@ -14,7 +14,17 @@
 //
 // The subcommand "sql-migrate" will apply any outstanding SQL migrations.
 //
-//     praefect -config PATH_TO_CONFIG sql-migrate
+//     praefect -config PATH_TO_CONFIG sql-migrate [-ignore-unknown]
+//
+// The migration will not start if there are any migrations in the
+// database that are not known by the Praefect binary.
+//
+// "-ignore-unknown" will skip this check.
+//
+// The subcommand "sql-migrate-status" will show which SQL migrations have
+// been applied and which ones have not:
+//
+//     praefect -config PATH_TO_CONFIG sql-migrate-status
 //
 // Dial Nodes
 //
diff --git a/cmd/praefect/subcmd.go b/cmd/praefect/subcmd.go
index ff8cf783e..6d8261133 100644
--- a/cmd/praefect/subcmd.go
+++ b/cmd/praefect/subcmd.go
@@ -24,11 +24,12 @@ type subcmd interface {
 
 var (
 	subcommands = map[string]subcmd{
-		"sql-ping":         &sqlPingSubcommand{},
-		"sql-migrate":      &sqlMigrateSubcommand{},
-		"dial-nodes":       &dialNodesSubcommand{},
-		"reconcile":        &reconcileSubcommand{},
-		"sql-migrate-down": &sqlMigrateDownSubcommand{},
+		"sql-ping":           &sqlPingSubcommand{},
+		"sql-migrate":        &sqlMigrateSubcommand{},
+		"dial-nodes":         &dialNodesSubcommand{},
+		"reconcile":          &reconcileSubcommand{},
+		"sql-migrate-down":   &sqlMigrateDownSubcommand{},
+		"sql-migrate-status": &sqlMigrateStatusSubcommand{},
 	}
 )
 
diff --git a/cmd/praefect/subcmd_sqlstatus.go b/cmd/praefect/subcmd_sqlstatus.go
new file mode 100644
index 000000000..f71d9d200
--- /dev/null
+++ b/cmd/praefect/subcmd_sqlstatus.go
@@ -0,0 +1,55 @@
+package main
+
+import (
+	"flag"
+	"os"
+	"sort"
+
+	"github.com/olekukonko/tablewriter"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/datastore"
+)
+
+type sqlMigrateStatusSubcommand struct{}
+
+func (s *sqlMigrateStatusSubcommand) FlagSet() *flag.FlagSet {
+	return flag.NewFlagSet("sql-migrate-status", flag.ExitOnError)
+}
+
+func (s *sqlMigrateStatusSubcommand) Exec(flags *flag.FlagSet, conf config.Config) error {
+	migrations, err := datastore.MigrateStatus(conf)
+	if err != nil {
+		return err
+	}
+
+	table := tablewriter.NewWriter(os.Stdout)
+	table.SetHeader([]string{"Migration", "Applied"})
+	table.SetColWidth(60)
+
+	// Display the rows in order of name
+	var keys []string
+	for k := range migrations {
+		keys = append(keys, k)
+	}
+	sort.Strings(keys)
+
+	for _, k := range keys {
+		m := migrations[k]
+		applied := "no"
+
+		if m.Unknown {
+			applied = "unknown migration"
+		} else if m.Migrated {
+			applied = m.AppliedAt.String()
+		}
+
+		table.Append([]string{
+			k,
+			applied,
+		})
+	}
+
+	table.Render()
+
+	return err
+}
diff --git a/doc/sql_migrations.md b/doc/sql_migrations.md
index 617da054a..a20a34f20 100644
--- a/doc/sql_migrations.md
+++ b/doc/sql_migrations.md
@@ -12,6 +12,43 @@ Praefect SQL migrations should be applied automatically when you deploy Praefect
 praefect -config /path/to/config.toml sql-migrate
 ```
 
+The migration will not start if there are any migrations in the database
+that are not known by the Praefect binary.
+
+Adding the `-ignore-unknown` will skip this check:
+
+```shell
+praefect -config /path/to/config.toml sql-migrate -ignore-unknown
+```
+
+## Showing the status of migrations
+
+To see which migrations have been applied, run:
+
+```
+praefect -config /path/to/config.toml sql-migrate-status
+```
+
+For example, the output may look like:
+
+```
++----------------------------------------+--------------------------------------+
+|               MIGRATION                |               APPLIED                |
++----------------------------------------+--------------------------------------+
+| 20200109161404_hello_world             | 2020-02-26 16:00:32.486129 -0800 PST |
+| 20200113151438_1_test_migration        | 2020-02-26 16:00:32.486871 -0800 PST |
+| 20200224220728_job_queue               | 2020-03-25 16:27:21.384917 -0700 PDT |
+| 20200324001604_add_sql_election_tables | no                                   |
+| 20200401010230_add_some_table          | unknown migration                    |
++----------------------------------------+--------------------------------------+
+```
+
+The first column contains the migration ID, and the second contains one of three items:
+
+1. The date on which the migration was applied
+2. `no` if the migration has not yet been applied
+3. `unknown migration` if the migration is not known by the current Praefect binary
+
 ## Rolling back migrations
 
 Rolling back SQL migrations in Praefect works a little differently
diff --git a/go.mod b/go.mod
index e863dcfc6..067f4d24f 100644
--- a/go.mod
+++ b/go.mod
@@ -11,6 +11,7 @@ require (
 	github.com/grpc-ecosystem/go-grpc-prometheus v1.2.0
 	github.com/kelseyhightower/envconfig v1.3.0
 	github.com/lib/pq v1.2.0
+	github.com/olekukonko/tablewriter v0.0.2
 	github.com/prometheus/client_golang v1.0.0
 	github.com/prometheus/procfs v0.0.3 // indirect
 	github.com/rubenv/sql-migrate v0.0.0-20191213152630-06338513c237
diff --git a/go.sum b/go.sum
index ca408701c..c46e84daa 100644
--- a/go.sum
+++ b/go.sum
@@ -188,6 +188,7 @@ github.com/mattn/go-isatty v0.0.3/go.mod h1:M+lRXTBqGeGNdLjl/ufCoiOlB5xdOkqRJdNx
 github.com/mattn/go-isatty v0.0.7/go.mod h1:Iq45c/XA43vh69/j3iqttzPXn0bhXyGjM0Hdxcsrc5s=
 github.com/mattn/go-isatty v0.0.8/go.mod h1:Iq45c/XA43vh69/j3iqttzPXn0bhXyGjM0Hdxcsrc5s=
 github.com/mattn/go-isatty v0.0.9/go.mod h1:YNRxwqDuOph6SZLI9vUUz6OYw3QyUt7WiY2yME+cCiQ=
+github.com/mattn/go-runewidth v0.0.4 h1:2BvfKmzob6Bmd4YsL0zygOqfdFnK7GR4QL06Do4/p7Y=
 github.com/mattn/go-runewidth v0.0.4/go.mod h1:LwmH8dsx7+W8Uxz3IHJYH5QSwggIsqBzpuz5H//U1FU=
 github.com/mattn/go-sqlite3 v1.12.0 h1:u/x3mp++qUxvYfulZ4HKOvVO0JWhk7HtE8lWhbGz/Do=
 github.com/mattn/go-sqlite3 v1.12.0/go.mod h1:FPy6KqzDD04eiIsT53CuJW3U88zkxoIYsOqkbpncsNc=
@@ -203,6 +204,7 @@ github.com/modern-go/reflect2 v1.0.1/go.mod h1:bx2lNnkwVCuqBIxFjflWJWanXIb3Rllmb
 github.com/moul/http2curl v1.0.0/go.mod h1:8UbvGypXm98wA/IqH45anm5Y2Z6ep6O31QGOAZ3H0fQ=
 github.com/mwitkow/go-conntrack v0.0.0-20161129095857-cc309e4a2223/go.mod h1:qRWi+5nqEBWmkhHvq77mSJWrCKwh8bxhgT7d/eI7P4U=
 github.com/olekukonko/tablewriter v0.0.1/go.mod h1:vsDQFd/mU46D+Z4whnwzcISnGGzXWMclvtLoiIKAKIo=
+github.com/olekukonko/tablewriter v0.0.2 h1:sq53g+DWf0J6/ceFUHpQ0nAEb6WgM++fq16MZ91cS6o=
 github.com/olekukonko/tablewriter v0.0.2/go.mod h1:rSAaSIOAGT9odnlyGlUfAJaoc5w2fSBUmeGDbRWPxyQ=
 github.com/onsi/ginkgo v1.6.0/go.mod h1:lLunBs/Ym6LB5Z9jYTR76FiuTmxDTDusOGeTQH+WWjE=
 github.com/onsi/ginkgo v1.7.0 h1:WSHQ+IS43OoUrWtD1/bbclrwK8TTH5hzp+umCiuxHgs=
diff --git a/internal/praefect/datastore/glsql/testing.go b/internal/praefect/datastore/glsql/testing.go
index f9bf61738..91efa6286 100644
--- a/internal/praefect/datastore/glsql/testing.go
+++ b/internal/praefect/datastore/glsql/testing.go
@@ -92,9 +92,9 @@ func GetDB(t testing.TB, database string) DB {
 	return testDB
 }
 
-func initGitalyTestDB(t testing.TB, database string) *sql.DB {
-	t.Helper()
-
+// GetDBConfig returns the database configuration determined by
+// environment variables.  See GetDB() for the list of variables.
+func GetDBConfig(t testing.TB, database string) config.DB {
 	getEnvFromGDK(t)
 
 	host, hostFound := os.LookupEnv("PGHOST")
@@ -106,13 +106,19 @@ func initGitalyTestDB(t testing.TB, database string) *sql.DB {
 	require.NoError(t, pErr, "PGPORT must be a port number of the Postgres database listens for incoming connections")
 
 	// connect to 'postgres' database first to re-create testing database from scratch
-	dbCfg := config.DB{
+	return config.DB{
 		Host:    host,
 		Port:    portNumber,
-		DBName:  "postgres",
+		DBName:  database,
 		SSLMode: "disable",
 		User:    os.Getenv("PGUSER"),
 	}
+}
+
+func initGitalyTestDB(t testing.TB, database string) *sql.DB {
+	t.Helper()
+
+	dbCfg := GetDBConfig(t, "postgres")
 
 	postgresDB, oErr := OpenDB(dbCfg)
 	require.NoError(t, oErr, "failed to connect to 'postgres' database")
diff --git a/internal/praefect/datastore/init_test.go b/internal/praefect/datastore/init_test.go
index bfbffa166..a9601791f 100644
--- a/internal/praefect/datastore/init_test.go
+++ b/internal/praefect/datastore/init_test.go
@@ -7,6 +7,7 @@ import (
 	"os"
 	"testing"
 
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
 	"gitlab.com/gitlab-org/gitaly/internal/praefect/datastore/glsql"
 )
 
@@ -19,4 +20,5 @@ func TestMain(m *testing.M) {
 	os.Exit(code)
 }
 
-func getDB(t testing.TB) glsql.DB { return glsql.GetDB(t, "datastore") }
+func getDB(t testing.TB) glsql.DB        { return glsql.GetDB(t, "datastore") }
+func getDBConfig(t testing.TB) config.DB { return glsql.GetDBConfig(t, "datastore") }
diff --git a/internal/praefect/datastore/postgres.go b/internal/praefect/datastore/postgres.go
index 0cb05e34a..8e6d1744d 100644
--- a/internal/praefect/datastore/postgres.go
+++ b/internal/praefect/datastore/postgres.go
@@ -12,6 +12,14 @@ import (
 	"gitlab.com/gitlab-org/gitaly/internal/praefect/datastore/migrations"
 )
 
+// MigrationStatusRow represents an entry in the schema migrations table.
+// If the migration is in the database but is not listed, Unknown will be true.
+type MigrationStatusRow struct {
+	Migrated  bool
+	Unknown   bool
+	AppliedAt time.Time
+}
+
 // CheckPostgresVersion checks the server version of the Postgres DB
 // specified in conf. This is a diagnostic for the Praefect Postgres
 // rollout. https://gitlab.com/gitlab-org/gitaly/issues/1755
@@ -66,6 +74,47 @@ func MigrateDown(conf config.Config, max int) (int, error) {
 	return migrate.ExecMax(db, sqlMigrateDialect, migrationSource(), migrate.Down, max)
 }
 
+// MigrateStatus returns the status of database migrations. The key of the map
+// indexes the migration ID.
+func MigrateStatus(conf config.Config) (map[string]*MigrationStatusRow, error) {
+	db, err := glsql.OpenDB(conf.DB)
+	if err != nil {
+		return nil, fmt.Errorf("sql open: %v", err)
+	}
+	defer db.Close()
+
+	migrations, err := migrationSource().FindMigrations()
+	if err != nil {
+		return nil, err
+	}
+
+	records, err := migrate.GetMigrationRecords(db, sqlMigrateDialect)
+	if err != nil {
+		return nil, err
+	}
+
+	rows := make(map[string]*MigrationStatusRow)
+
+	for _, m := range migrations {
+		rows[m.Id] = &MigrationStatusRow{
+			Migrated: false,
+		}
+	}
+
+	for _, r := range records {
+		if rows[r.Id] == nil {
+			rows[r.Id] = &MigrationStatusRow{
+				Unknown: true,
+			}
+		}
+
+		rows[r.Id].Migrated = true
+		rows[r.Id].AppliedAt = r.AppliedAt
+	}
+
+	return rows, nil
+}
+
 func migrationSource() *migrate.MemoryMigrationSource {
 	return &migrate.MemoryMigrationSource{Migrations: migrations.All()}
 }
diff --git a/internal/praefect/datastore/postgres_test.go b/internal/praefect/datastore/postgres_test.go
new file mode 100644
index 000000000..c23c84af6
--- /dev/null
+++ b/internal/praefect/datastore/postgres_test.go
@@ -0,0 +1,32 @@
+// +build postgres
+
+package datastore
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
+)
+
+func TestMigrateStatus(t *testing.T) {
+	db := getDB(t)
+
+	config := config.Config{
+		DB: getDBConfig(t),
+	}
+
+	_, err := db.Exec("INSERT INTO schema_migrations VALUES ('2020_01_01_test', NOW())")
+	require.NoError(t, err)
+
+	rows, err := MigrateStatus(config)
+	require.NoError(t, err)
+
+	m := rows["20200109161404_hello_world"]
+	require.True(t, m.Migrated)
+	require.False(t, m.Unknown)
+
+	m = rows["2020_01_01_test"]
+	require.True(t, m.Migrated)
+	require.True(t, m.Unknown)
+}