postgres_schema.go

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

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