postgres_schema.go

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

     1  package metrics
     2  
     3  import (
     4  	"context"
     5  	_ "embed"
     6  	"fmt"
     7  
     8  	"github.com/cybertec-postgresql/pgwatch/v5/internal/db"
     9  	"github.com/cybertec-postgresql/pgwatch/v5/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(dmrw *dbMetricReaderWriter) (*migrator.Migrator, error) {
    40  	return migrator.New(
    41  		migrator.TableName("pgwatch.migration"),
    42  		migrator.SetNotice(func(s string) {
    43  			log.GetLogger(dmrw.ctx).Info(s)
    44  		}),
    45  		migrations(),
    46  	)
    47  }
    48  
    49  // MigrateDb upgrades database with all migrations
    50  func (dmrw *dbMetricReaderWriter) Migrate() error {
    51  	m, err := initMigrator(dmrw)
    52  	if err != nil {
    53  		return fmt.Errorf("cannot initialize migration: %w", err)
    54  	}
    55  	return m.Migrate(dmrw.ctx, dmrw.configDb)
    56  }
    57  
    58  // NeedsMigration checks if database needs migration
    59  func (dmrw *dbMetricReaderWriter) NeedsMigration() (bool, error) {
    60  	m, err := initMigrator(dmrw)
    61  	if err != nil {
    62  		return false, err
    63  	}
    64  	return m.NeedUpgrade(dmrw.ctx, dmrw.configDb)
    65  }
    66  
    67  // MigrationsCount is the total number of migrations in pgwatch.migration table
    68  const MigrationsCount = 2
    69  
    70  // migrations holds function returning all updgrade migrations needed
    71  var migrations func() migrator.Option = func() migrator.Option {
    72  	return migrator.Migrations(
    73  		&migrator.Migration{
    74  			Name: "00179 Apply metrics migrations for v3",
    75  			Func: func(context.Context, pgx.Tx) error {
    76  				// "migrations" table will be created automatically
    77  				return nil
    78  			},
    79  		},
    80  
    81  		&migrator.Migration{
    82  			Name: "00824 Refactor recommendations",
    83  			Func: func(ctx context.Context, tx pgx.Tx) error {
    84  				_, err := tx.Exec(ctx, `
    85  					-- 1. Update all reco_ metrics to use metric_storage_name = 'recommendations'
    86  					UPDATE pgwatch.metric 
    87  					SET storage_name = 'recommendations' 
    88  					WHERE name LIKE 'reco_%' AND COALESCE(storage_name, '') = '';
    89  
    90  					-- 2. Remove the placeholder 'recommendations' metric if it exists
    91  					DELETE FROM pgwatch.metric WHERE name = 'recommendations';
    92  
    93  					-- 3. Update 'exhaustive' and 'full' presets to replace 'recommendations' with individual reco_ metrics
    94  					UPDATE pgwatch.preset 
    95  					SET metrics = metrics - 'recommendations' || $reco_metrics${
    96  						"reco_add_index": 43200, 
    97  						"reco_default_public_schema": 50400, 
    98  						"reco_disabled_triggers": 57600, 
    99  						"reco_drop_index": 64800, 
   100  						"reco_nested_views": 72000, 
   101  						"reco_partial_index_candidates": 79200, 
   102  						"reco_sprocs_wo_search_path": 86400, 
   103  						"reco_superusers": 93600
   104  					}$reco_metrics$::jsonb
   105  					WHERE name IN ('exhaustive', 'full') AND metrics ? 'recommendations';
   106  
   107  					-- 4. Insert new 'recommendations' preset if it doesn't exist
   108  					INSERT INTO pgwatch.preset (name, description, metrics) 
   109  					VALUES ('recommendations', 'performance and security recommendations', 
   110  						$reco_metrics${
   111  							"reco_add_index": 43200, 
   112  							"reco_default_public_schema": 50400, 
   113  							"reco_disabled_triggers": 57600, 
   114  							"reco_drop_index": 64800, 
   115  							"reco_nested_views": 72000, 
   116  							"reco_partial_index_candidates": 79200, 
   117  							"reco_sprocs_wo_search_path": 86400, 
   118  							"reco_superusers": 93600
   119  						}$reco_metrics$::jsonb)
   120  					ON CONFLICT (name) DO NOTHING;
   121  
   122  					-- 5. Update source configs to replace 'recommendations' with individual reco_ metrics
   123  					UPDATE pgwatch.source 
   124  					SET config = config - 'recommendations' || 
   125  						$reco_metrics${
   126  							"reco_add_index": 43200, 
   127  							"reco_default_public_schema": 50400, 
   128  							"reco_disabled_triggers": 57600, 
   129  							"reco_drop_index": 64800, 
   130  							"reco_nested_views": 72000, 
   131  							"reco_partial_index_candidates": 79200, 
   132  							"reco_sprocs_wo_search_path": 86400, 
   133  							"reco_superusers": 93600
   134  						}$reco_metrics$::jsonb
   135  					WHERE config ? 'recommendations';
   136  
   137  					-- 6. Update source standby configs to replace 'recommendations' with individual reco_ metrics
   138  					UPDATE pgwatch.source 
   139  					SET config_standby = config_standby - 'recommendations' || 
   140  						$reco_metrics${
   141  							"reco_add_index": 43200, 
   142  							"reco_default_public_schema": 50400, 
   143  							"reco_disabled_triggers": 57600, 
   144  							"reco_drop_index": 64800, 
   145  							"reco_nested_views": 72000, 
   146  							"reco_partial_index_candidates": 79200, 
   147  							"reco_sprocs_wo_search_path": 86400, 
   148  							"reco_superusers": 93600
   149  						}$reco_metrics$::jsonb
   150  					WHERE config_standby ? 'recommendations';
   151  				`)
   152  				return err
   153  			},
   154  		},
   155  
   156  		// adding new migration here, update "pgwatch"."migration" in "postgres_schema.sql"
   157  		// and "dbapi" variable in main.go!
   158  
   159  		// &migrator.Migration{
   160  		// 	Name: "000XX Short description of a migration",
   161  		// 	Func: func(ctx context.Context, tx pgx.Tx) error {
   162  		// 		return executeMigrationScript(ctx, tx, "000XX.sql")
   163  		// 	},
   164  		// },
   165  	)
   166  }
   167