postgres_schema.go

Documentation: github.com/cybertec-postgresql/pgwatch/v6/internal/metrics

     1  package metrics
     2  
     3  import (
     4  	"context"
     5  	_ "embed"
     6  	"fmt"
     7  
     8  	"github.com/cybertec-postgresql/pgwatch/v6/internal/db"
     9  	"github.com/cybertec-postgresql/pgwatch/v6/internal/log"
    10  	migrator "github.com/cybertec-postgresql/pgx-migrator"
    11  	"github.com/jackc/pgx/v5"
    12  )
    13  
    14  //go:embed postgres_schema.sql
    15  var sqlConfigSchema string
    16  
    17  var initSchema = func(ctx context.Context, conn db.PgxIface) (err error) {
    18  	var exists bool
    19  	if exists, err = db.DoesSchemaExist(ctx, conn, "pgwatch"); err != nil || exists {
    20  		return err
    21  	}
    22  	tx, err := conn.Begin(ctx)
    23  	if err != nil {
    24  		return err
    25  	}
    26  	defer func() { _ = tx.Rollback(ctx) }()
    27  	if _, err := tx.Exec(ctx, sqlConfigSchema); err != nil {
    28  		return err
    29  	}
    30  	if err := writeMetricsToPostgres(ctx, tx, GetDefaultMetrics()); err != nil {
    31  		return err
    32  	}
    33  	if err := tx.Commit(ctx); err != nil {
    34  		return err
    35  	}
    36  	return nil
    37  }
    38  
    39  var initMigrator = func(ctx context.Context) (*migrator.Migrator, error) {
    40  	return migrator.New(
    41  		migrator.TableName("pgwatch.migration"),
    42  		migrator.SetNotice(func(s string) {
    43  			log.GetLogger(ctx).Info(s)
    44  		}),
    45  		migrations(),
    46  	)
    47  }
    48  
    49  // EnsureConfigSchema creates the pgwatch configuration schema in the database if it
    50  // does not exist yet, seeding it with the default metric and preset definitions.
    51  func EnsureConfigSchema(ctx context.Context, conn db.PgxIface) error {
    52  	return initSchema(ctx, conn)
    53  }
    54  
    55  // MigrateConfigSchema applies all pending configuration schema migrations to the database.
    56  func MigrateConfigSchema(ctx context.Context, conn db.PgxIface) error {
    57  	m, err := initMigrator(ctx)
    58  	if err != nil {
    59  		return fmt.Errorf("cannot initialize migration: %w", err)
    60  	}
    61  	return m.Migrate(ctx, conn)
    62  }
    63  
    64  // NeedsConfigSchemaMigration reports if the configuration schema in the database is outdated.
    65  func NeedsConfigSchemaMigration(ctx context.Context, conn db.PgxIface) (bool, error) {
    66  	m, err := initMigrator(ctx)
    67  	if err != nil {
    68  		return false, err
    69  	}
    70  	return m.NeedUpgrade(ctx, conn)
    71  }
    72  
    73  // MigrateDb upgrades database with all migrations
    74  func (dmrw *dbMetricReaderWriter) Migrate() error {
    75  	return MigrateConfigSchema(dmrw.ctx, dmrw.configDb)
    76  }
    77  
    78  // NeedsMigration checks if database needs migration
    79  func (dmrw *dbMetricReaderWriter) NeedsMigration() (bool, error) {
    80  	return NeedsConfigSchemaMigration(dmrw.ctx, dmrw.configDb)
    81  }
    82  
    83  // MigrationsCount is the total number of migrations in pgwatch.migration table
    84  const MigrationsCount = 3
    85  
    86  // migrations holds function returning all upgrade migrations needed
    87  var migrations func() migrator.Option = func() migrator.Option {
    88  	return migrator.Migrations(
    89  		&migrator.Migration{
    90  			Name: "00179 Apply metrics migrations for v3",
    91  			Func: func(context.Context, pgx.Tx) error {
    92  				// "migrations" table will be created automatically
    93  				return nil
    94  			},
    95  		},
    96  
    97  		&migrator.Migration{
    98  			Name: "00824 Refactor recommendations",
    99  			Func: func(ctx context.Context, tx pgx.Tx) error {
   100  				_, err := tx.Exec(ctx, `
   101  					-- 1. Update all reco_ metrics to use metric_storage_name = 'recommendations'
   102  					UPDATE pgwatch.metric 
   103  					SET storage_name = 'recommendations' 
   104  					WHERE name LIKE 'reco_%' AND COALESCE(storage_name, '') = '';
   105  
   106  					-- 2. Remove the placeholder 'recommendations' metric if it exists
   107  					DELETE FROM pgwatch.metric WHERE name = 'recommendations';
   108  
   109  					-- 3. Update 'exhaustive' and 'full' presets to replace 'recommendations' with individual reco_ metrics
   110  					UPDATE pgwatch.preset 
   111  					SET metrics = metrics - 'recommendations' || $reco_metrics${
   112  						"reco_add_index": 43200, 
   113  						"reco_default_public_schema": 50400, 
   114  						"reco_disabled_triggers": 57600, 
   115  						"reco_drop_index": 64800, 
   116  						"reco_nested_views": 72000, 
   117  						"reco_partial_index_candidates": 79200, 
   118  						"reco_sprocs_wo_search_path": 86400, 
   119  						"reco_superusers": 93600
   120  					}$reco_metrics$::jsonb
   121  					WHERE name IN ('exhaustive', 'full') AND metrics ? 'recommendations';
   122  
   123  					-- 4. Insert new 'recommendations' preset if it doesn't exist
   124  					INSERT INTO pgwatch.preset (name, description, metrics) 
   125  					VALUES ('recommendations', 'performance and security recommendations', 
   126  						$reco_metrics${
   127  							"reco_add_index": 43200, 
   128  							"reco_default_public_schema": 50400, 
   129  							"reco_disabled_triggers": 57600, 
   130  							"reco_drop_index": 64800, 
   131  							"reco_nested_views": 72000, 
   132  							"reco_partial_index_candidates": 79200, 
   133  							"reco_sprocs_wo_search_path": 86400, 
   134  							"reco_superusers": 93600
   135  						}$reco_metrics$::jsonb)
   136  					ON CONFLICT (name) DO NOTHING;
   137  
   138  					-- 5. Update source configs to replace 'recommendations' with individual reco_ metrics
   139  					UPDATE pgwatch.source 
   140  					SET config = config - 'recommendations' || 
   141  						$reco_metrics${
   142  							"reco_add_index": 43200, 
   143  							"reco_default_public_schema": 50400, 
   144  							"reco_disabled_triggers": 57600, 
   145  							"reco_drop_index": 64800, 
   146  							"reco_nested_views": 72000, 
   147  							"reco_partial_index_candidates": 79200, 
   148  							"reco_sprocs_wo_search_path": 86400, 
   149  							"reco_superusers": 93600
   150  						}$reco_metrics$::jsonb
   151  					WHERE config ? 'recommendations';
   152  
   153  					-- 6. Update source standby configs to replace 'recommendations' with individual reco_ metrics
   154  					UPDATE pgwatch.source 
   155  					SET config_standby = config_standby - 'recommendations' || 
   156  						$reco_metrics${
   157  							"reco_add_index": 43200, 
   158  							"reco_default_public_schema": 50400, 
   159  							"reco_disabled_triggers": 57600, 
   160  							"reco_drop_index": 64800, 
   161  							"reco_nested_views": 72000, 
   162  							"reco_partial_index_candidates": 79200, 
   163  							"reco_sprocs_wo_search_path": 86400, 
   164  							"reco_superusers": 93600
   165  						}$reco_metrics$::jsonb
   166  					WHERE config_standby ? 'recommendations';
   167  				`)
   168  				return err
   169  			},
   170  		},
   171  
   172  		&migrator.Migration{
   173  			Name: "01405 Add prometheus to source dbtype check constraint",
   174  			Func: func(ctx context.Context, tx pgx.Tx) error {
   175  				_, err := tx.Exec(ctx, `
   176  					ALTER TABLE pgwatch.source
   177  						DROP CONSTRAINT IF EXISTS source_dbtype_check,
   178  						ADD CHECK (dbtype IN ('postgres', 'pgbouncer', 'postgres-continuous-discovery', 'patroni', 'pgpool', 'prometheus'));
   179  				`)
   180  				return err
   181  			},
   182  		},
   183  
   184  		// adding new migration here, update "pgwatch"."migration" in "postgres_schema.sql"!
   185  
   186  		// &migrator.Migration{
   187  		// 	Name: "000XX Short description of a migration",
   188  		// 	Func: func(ctx context.Context, tx pgx.Tx) error {
   189  		// 		return executeMigrationScript(ctx, tx, "000XX.sql")
   190  		// 	},
   191  		// },
   192  	)
   193  }
   194