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