const (
EpochColumnName string = "epoch_ns" // this column (epoch in nanoseconds) is expected in every metric query
TagPrefix string = "tag_"
)
MigrationsCount is the total number of migrations in pgwatch.migration table
const MigrationsCount = 2
var (
ErrNeedsMigration = errors.New("config database schema is outdated, please run migrations using `pgwatch config upgrade` command")
ErrMetricNotFound = errors.New("metric not found")
ErrPresetNotFound = errors.New("preset not found")
ErrInvalidMetric = errors.New("invalid metric")
ErrInvalidPreset = errors.New("invalid preset")
ErrMetricExists = errors.New("metric already exists")
ErrPresetExists = errors.New("preset already exists")
)
make sure *dbMetricReaderWriter implements the Migrator interface
var _ db.Migrator = (*dbMetricReaderWriter)(nil)
var defaultMetricsYAML []byte
var initMigrator = func(dmrw *dbMetricReaderWriter) (*migrator.Migrator, error) { return migrator.New( migrator.TableName("pgwatch.migration"), migrator.SetNotice(func(s string) { log.GetLogger(dmrw.ctx).Info(s) }), migrations(), ) }
var initSchema = func(ctx context.Context, conn db.PgxIface) (err error) { var exists bool if exists, err = db.DoesSchemaExist(ctx, conn, "pgwatch"); err != nil || exists { return err } tx, err := conn.Begin(ctx) if err != nil { return err } defer func() { _ = tx.Rollback(ctx) }() if _, err := tx.Exec(ctx, sqlConfigSchema); err != nil { return err } if err := writeMetricsToPostgres(ctx, tx, GetDefaultMetrics()); err != nil { return err } if err := tx.Commit(ctx); err != nil { return err } return nil }
migrations holds function returning all updgrade migrations needed
var migrations func() migrator.Option = func() migrator.Option { return migrator.Migrations( &migrator.Migration{ Name: "00179 Apply metrics migrations for v3", Func: func(context.Context, pgx.Tx) error { return nil }, }, &migrator.Migration{ Name: "00824 Refactor recommendations", Func: func(ctx context.Context, tx pgx.Tx) error { _, err := tx.Exec(ctx, ` -- 1. Update all reco_ metrics to use metric_storage_name = 'recommendations' UPDATE pgwatch.metric SET storage_name = 'recommendations' WHERE name LIKE 'reco_%' AND COALESCE(storage_name, '') = ''; -- 2. Remove the placeholder 'recommendations' metric if it exists DELETE FROM pgwatch.metric WHERE name = 'recommendations'; -- 3. Update 'exhaustive' and 'full' presets to replace 'recommendations' with individual reco_ metrics UPDATE pgwatch.preset SET metrics = metrics - 'recommendations' || $reco_metrics${ "reco_add_index": 43200, "reco_default_public_schema": 50400, "reco_disabled_triggers": 57600, "reco_drop_index": 64800, "reco_nested_views": 72000, "reco_partial_index_candidates": 79200, "reco_sprocs_wo_search_path": 86400, "reco_superusers": 93600 }$reco_metrics$::jsonb WHERE name IN ('exhaustive', 'full') AND metrics ? 'recommendations'; -- 4. Insert new 'recommendations' preset if it doesn't exist INSERT INTO pgwatch.preset (name, description, metrics) VALUES ('recommendations', 'performance and security recommendations', $reco_metrics${ "reco_add_index": 43200, "reco_default_public_schema": 50400, "reco_disabled_triggers": 57600, "reco_drop_index": 64800, "reco_nested_views": 72000, "reco_partial_index_candidates": 79200, "reco_sprocs_wo_search_path": 86400, "reco_superusers": 93600 }$reco_metrics$::jsonb) ON CONFLICT (name) DO NOTHING; -- 5. Update source configs to replace 'recommendations' with individual reco_ metrics UPDATE pgwatch.source SET config = config - 'recommendations' || $reco_metrics${ "reco_add_index": 43200, "reco_default_public_schema": 50400, "reco_disabled_triggers": 57600, "reco_drop_index": 64800, "reco_nested_views": 72000, "reco_partial_index_candidates": 79200, "reco_sprocs_wo_search_path": 86400, "reco_superusers": 93600 }$reco_metrics$::jsonb WHERE config ? 'recommendations'; -- 6. Update source standby configs to replace 'recommendations' with individual reco_ metrics UPDATE pgwatch.source SET config_standby = config_standby - 'recommendations' || $reco_metrics${ "reco_add_index": 43200, "reco_default_public_schema": 50400, "reco_disabled_triggers": 57600, "reco_drop_index": 64800, "reco_nested_views": 72000, "reco_partial_index_candidates": 79200, "reco_sprocs_wo_search_path": 86400, "reco_superusers": 93600 }$reco_metrics$::jsonb WHERE config_standby ? 'recommendations'; `) return err }, }, ) }
var sqlConfigSchema string
func GetDefaultBuiltInMetrics() []string
func RowToMeasurement(row pgx.CollectableRow) (map[string]any, error)
RowToMap returns a map scanned from row.
func writeMetricsToPostgres(ctx context.Context, conn db.PgxIface, metricDefs *Metrics) error
writeMetricsToPostgres writes the metrics and presets definitions to the pgwatch.metric and pgwatch.preset tables in the ConfigDB.
CmdOpts specifies metric command-line options
type CmdOpts struct {
Metrics string `short:"m" long:"metrics" mapstructure:"metrics" description:"Postgres URI or path to YAML file with metrics definitions" env:"PW_METRICS"`
DirectOSStats bool `hidden:"true" long:"direct-os-stats" mapstructure:"direct-os-stats" description:"Extract OS related psutil statistics not via PL/Python wrappers but directly on host" env:"PW_DIRECT_OS_STATS"`
InstanceLevelCacheMaxSeconds int64 `long:"instance-level-cache-max-seconds" mapstructure:"instance-level-cache-max-seconds" description:"Max allowed staleness for instance level metric data shared between DBs of an instance. Set to 0 to disable" env:"PW_INSTANCE_LEVEL_CACHE_MAX_SECONDS" default:"30"`
EmergencyPauseTriggerfile string `long:"emergency-pause-triggerfile" mapstructure:"emergency-pause-triggerfile" description:"When the file exists no metrics will be temporarily fetched" env:"PW_EMERGENCY_PAUSE_TRIGGERFILE" default:"/tmp/pgwatch-emergency-pause"`
}
func (c CmdOpts) CacheAge() time.Duration
type ExtensionInfo struct {
ExtName string `yaml:"ext_name"`
ExtMinVersion string `yaml:"ext_min_version"`
}
type ExtensionOverrides struct {
TargetMetric string `yaml:"target_metric"`
ExpectedExtensionVersions []ExtensionInfo `yaml:"expected_extension_versions"`
}
type Measurement map[string]any
func NewMeasurement(epoch int64) Measurement
func (m Measurement) GetEpoch() int64
func (m *Measurement) ScanRow(rows pgx.Rows) error
type MeasurementEnvelope struct {
DBName string
MetricName string
CustomTags map[string]string
Data Measurements
}
type Measurements []map[string]any
func (m Measurements) DeepCopy() Measurements
func (m Measurements) GetEpoch() int64
func (m Measurements) IsEpochSet() bool
func (m Measurements) Touch()
Touch updates the last modified time of the metric definitions
type Metric struct {
SQLs SQLs
InitSQL string `yaml:"init_sql,omitempty"`
NodeStatus string `yaml:"node_status,omitempty"`
Gauges []string `yaml:",omitempty"`
IsInstanceLevel bool `yaml:"is_instance_level,omitempty"`
StorageName string `yaml:"storage_name,omitempty"`
Description string `yaml:"description,omitempty"`
}
func (m Metric) GetSQL(version int) string
func (m Metric) PrimaryOnly() bool
func (m Metric) StandbyOnly() bool
type MetricAttrs struct {
ExtensionVersionOverrides []ExtensionOverrides `yaml:"extension_version_based_overrides,omitempty"`
IsPrivate bool `yaml:"is_private,omitempty"` // used only for extension overrides currently and ignored otherwise
DisabledDays string `yaml:"disabled_days,omitempty"` // Cron style, 0 = Sunday. Ranges allowed: 0,2-4
DisableTimes []string `yaml:"disabled_times,omitempty"` // "11:00-13:00"
StatementTimeoutSeconds int64 `yaml:"statement_timeout_seconds,omitempty"` // overrides per monitored DB settings
}
type MetricDefs map[string]Metric
type Metrics struct {
MetricDefs MetricDefs `yaml:"metrics,omitempty"`
PresetDefs PresetDefs `yaml:"presets,omitempty"`
}
func GetDefaultMetrics() (metrics *Metrics)
func (m *Metrics) FilterByNames(names []string) (*Metrics, error)
FilterByNames returns a new Metrics struct containing only the specified metrics and/or presets. When a preset is requested, it includes both the preset definition and all its metrics. If names is empty, returns a full copy of all metrics and presets. Returns an error if any name is not found.
type Preset struct {
Description string
Metrics map[string]float64
}
type PresetDefs map[string]Preset
type Reader interface {
GetMetrics() (*Metrics, error)
}
type ReaderWriter interface {
Reader
Writer
}
func NewDefaultMetricReader(context.Context) (ReaderWriter, error)
NewDefaultMetricReader creates a new default metric reader with an empty path.
func NewPostgresMetricReaderWriter(ctx context.Context, connstr string) (ReaderWriter, error)
func NewPostgresMetricReaderWriterConn(ctx context.Context, conn db.PgxPoolIface) (ReaderWriter, error)
func NewYAMLMetricReaderWriter(ctx context.Context, path string) (ReaderWriter, error)
type SQLs map[int]string
type Writer interface {
WriteMetrics(metricDefs *Metrics) error
DeleteMetric(metricName string) error
DeletePreset(presetName string) error
UpdateMetric(metricName string, metric Metric) error
UpdatePreset(presetName string, preset Preset) error
CreateMetric(metricName string, metric Metric) error
CreatePreset(presetName string, preset Preset) error
}
type dbMetricReaderWriter struct {
ctx context.Context
configDb db.PgxIface
}
func (dmrw *dbMetricReaderWriter) CreateMetric(metricName string, metric Metric) error
func (dmrw *dbMetricReaderWriter) CreatePreset(presetName string, preset Preset) error
func (dmrw *dbMetricReaderWriter) DeleteMetric(metricName string) error
func (dmrw *dbMetricReaderWriter) DeletePreset(presetName string) error
func (dmrw *dbMetricReaderWriter) GetMetrics() (metricDefMapNew *Metrics, err error)
ReadMetricsFromPostgres reads the metrics and presets definitions from the pgwatch.metric and pgwatch.preset tables from the ConfigDB.
func (dmrw *dbMetricReaderWriter) Migrate() error
MigrateDb upgrades database with all migrations
func (dmrw *dbMetricReaderWriter) NeedsMigration() (bool, error)
NeedsMigration checks if database needs migration
func (dmrw *dbMetricReaderWriter) UpdateMetric(metricName string, metric Metric) error
func (dmrw *dbMetricReaderWriter) UpdatePreset(presetName string, preset Preset) error
func (dmrw *dbMetricReaderWriter) WriteMetrics(metricDefs *Metrics) error
type defaultMetricReader struct{}
func (dmrw *defaultMetricReader) CreateMetric(string, Metric) error
func (dmrw *defaultMetricReader) CreatePreset(string, Preset) error
func (dmrw *defaultMetricReader) DeleteMetric(string) error
func (dmrw *defaultMetricReader) DeletePreset(string) error
func (dmrw *defaultMetricReader) GetMetrics() (*Metrics, error)
func (dmrw *defaultMetricReader) UpdateMetric(string, Metric) error
func (dmrw *defaultMetricReader) UpdatePreset(string, Preset) error
func (dmrw *defaultMetricReader) WriteMetrics(*Metrics) error
type fileMetricReader struct {
ctx context.Context
path string
}
func (fmr *fileMetricReader) CreateMetric(metricName string, metric Metric) error
func (fmr *fileMetricReader) CreatePreset(presetName string, preset Preset) error
func (fmr *fileMetricReader) DeleteMetric(metricName string) error
func (fmr *fileMetricReader) DeletePreset(presetName string) error
func (fmr *fileMetricReader) GetMetrics() (metrics *Metrics, err error)
func (fmr *fileMetricReader) UpdateMetric(metricName string, metric Metric) error
func (fmr *fileMetricReader) UpdatePreset(presetName string, preset Preset) error
func (fmr *fileMetricReader) WriteMetrics(metricDefs *Metrics) error
func (fmr *fileMetricReader) getMetrics(metricsFilePath string) (metrics *Metrics, err error)