postgres

README

PostgreSQL

Environment based configuration

Configuration for the PostgreSQL connection pool of the microservice.

• POSTGRES_PORT default: 5432
  • Port to be used for listening used if address is not specified
• POSTGRES_HOST default: localhost
  • Host where the PostgreSQL can be found (dns or IP)
• POSTGRES_PASSWORD default: pace1234!
  • password to access the database
• POSTGRES_USER default: postgres
  • postgres user to access the database
• POSTGRES_DB default: postgres
  • database to access
• POSTGRES_MAX_RETRIES default: 5
  • Maximum number of retries before giving up
• POSTGRES_RETRY_STATEMENT_TIMEOUT default: false
  • Whether to retry queries cancelled because of statement_timeout
• POSTGRES_MIN_RETRY_BACKOFF default: 250ms
  • Minimum backoff between each retry
• POSTGRES_MAX_RETRY_BACKOFF default: 4s
  • Maximum backoff between each retry
• POSTGRES_DIAL_TIMEOUT default: 5s
  • Dial timeout for establishing new connections
• POSTGRES_READ_TIMEOUT default: 30s
  • Timeout for socket reads. If reached, commands will fail with a timeout instead of blocking
• POSTGRES_WRITE_TIMEOUT default: 30s
  • Timeout for socket writes. If reached, commands will fail with a timeout instead of blocking.
• POSTGRES_POOL_SIZE default: 100
  • Maximum number of socket connections
• POSTGRES_MIN_IDLE_CONNECTIONS default: 10
  • Minimum number of idle connections which is useful when establishing new connection is slow
• POSTGRES_MAX_CONN_AGE default: 30m
  • Connection age at which client retires (closes) the connection
• POSTGRES_POOL_TIMEOUT default: 31s
  • Time for which client waits for free connection if all connections are busy before returning an error
• POSTGRES_IDLE_TIMEOUT default: 5m
  • Amount of time after which client closes idle connections
• POSTGRES_IDLE_CHECK_FREQUENCY default: 1m
  • Frequency of idle checks made by idle connections reaper
• POSTGRES_HEALTH_CHECK_TABLE_NAME default: healthcheck
  • Name of the Table that is created to try if database is writeable
• POSTGRES_HEALTH_CHECK_RESULT_TTL default: 10s
  • Amount of time to cache the last health check result

Metrics

Prometheus metrics exposed.

• pace_postgres_query_total{database} Collects stats about the number of postgres queries made
• pace_postgres_query_failed{database} Collects stats about the number of postgres queries failed
• pace_postgres_query_duration_seconds{database} Collects performance metrics for each postgres query
• pace_postgres_query_rows_total{database} Collects stats about the number of rows returned by a postgres query
• pace_postgres_query_affected_total{database} Collects stats about the number of rows affected by a postgres query
• pace_postgres_connection_pool_hits{database} Collects number of times free connection was found in the pool
• pace_postgres_connection_pool_misses{database} Collects number of times free connection was NOT found in the pool
• pace_postgres_connection_pool_timeouts{database} Collects number of times a wait timeout occurred
• pace_postgres_connection_pool_total_conns{database} Collects number of total connections in the pool
• pace_postgres_connection_pool_idle_conns{database} Collects number of idle connections in the pool
• pace_postgres_connection_pool_stale_conns{database} Collects number of stale connections removed from the pool

Documentation

Overview

Package postgres helps creating PostgreSQL connection pools

Index

• Variables
• func ConnectionPool() *pg.DB
• func CustomConnectionPool(opts *pg.Options) *pg.DB
• func DefaultConnectionPool() *pg.DB
• type Config
• type ConnectionPoolMetrics
  • func NewConnectionPoolMetrics() *ConnectionPoolMetrics
  • func (m *ConnectionPoolMetrics) Collect(ch chan<- prometheus.Metric)
  • func (m *ConnectionPoolMetrics) Describe(ch chan<- *prometheus.Desc)
  • func (m *ConnectionPoolMetrics) ObserveRegularly(ctx context.Context, db *pg.DB, poolName string) error
  • func (m *ConnectionPoolMetrics) ObserveWhenTriggered(trigger <-chan chan<- struct{}, db *pg.DB, poolName string) error
• type HealthCheck
  • func (h *HealthCheck) CleanUp(ctx context.Context) error
  • func (h *HealthCheck) HealthCheck(ctx context.Context) servicehealthcheck.HealthCheckResult
  • func (h *HealthCheck) Init(ctx context.Context) error

Examples

• ConnectionPoolMetrics

Variables

var (
	ErrNotUnique = errors.New("not unique")
)

errors

Functions

func ConnectionPool

func ConnectionPool() *pg.DB

ConnectionPool returns a new database connection pool that is already configured with the correct credentials and instrumented with tracing and logging

func CustomConnectionPool

func CustomConnectionPool(opts *pg.Options) *pg.DB

CustomConnectionPool returns a new database connection pool that is already configured with the correct credentials and instrumented with tracing and logging using the passed options

Fot a health check for this connection a PgHealthCheck needs to be registered:

servicehealthcheck.RegisterHealthCheck(...)

func DefaultConnectionPool

func DefaultConnectionPool() *pg.DB

DefaultConnectionPool returns a the default database connection pool that is configured using the POSTGRES_* env vars and instrumented with tracing, logging and metrics.

Types

type Config

type Config struct {
	Port     int    `env:"POSTGRES_PORT" envDefault:"5432"`
	Host     string `env:"POSTGRES_HOST" envDefault:"postgres"`
	Password string `env:"POSTGRES_PASSWORD" envDefault:"mysecretpassword"`
	User     string `env:"POSTGRES_USER" envDefault:"postgres"`
	Database string `env:"POSTGRES_DB" envDefault:"postgres"`

	// ApplicationName is the application name. Used in logs on Pg side.
	// Only availaible from pg-9.0.
	ApplicationName string `env:"POSTGRES_APPLICATION_NAME" envDefault:"-"`
	// Maximum number of retries before giving up.
	MaxRetries int `env:"POSTGRES_MAX_RETRIES" envDefault:"5"`
	// Whether to retry queries cancelled because of statement_timeout.
	RetryStatementTimeout bool `env:"POSTGRES_RETRY_STATEMENT_TIMEOUT" envDefault:"false"`
	// Minimum backoff between each retry.
	// -1 disables backoff.
	MinRetryBackoff time.Duration `env:"POSTGRES_MIN_RETRY_BACKOFF" envDefault:"250ms"`
	// Maximum backoff between each retry.
	// -1 disables backoff.
	MaxRetryBackoff time.Duration `env:"POSTGRES_MAX_RETRY_BACKOFF" envDefault:"4s"`
	// Dial timeout for establishing new connections.
	DialTimeout time.Duration `env:"POSTGRES_DIAL_TIMEOUT" envDefault:"5s"`
	// Timeout for socket reads. If reached, commands will fail
	// with a timeout instead of blocking.
	ReadTimeout time.Duration `env:"POSTGRES_READ_TIMEOUT" envDefault:"30s"`
	// Timeout for socket writes. If reached, commands will fail
	// with a timeout instead of blocking.
	WriteTimeout time.Duration `env:"POSTGRES_WRITE_TIMEOUT" envDefault:"30s"`
	// Maximum number of socket connections.
	PoolSize int `env:"POSTGRES_POOL_SIZE" envDefault:"100"`
	// Minimum number of idle connections which is useful when establishing
	// new connection is slow.
	MinIdleConns int `env:"POSTGRES_MIN_IDLE_CONNECTIONS" envDefault:"10"`
	// Connection age at which client retires (closes) the connection.
	// It is useful with proxies like PgBouncer and HAProxy.
	MaxConnAge time.Duration `env:"POSTGRES_MAX_CONN_AGE" envDefault:"30m"`
	// Time for which client waits for free connection if all
	// connections are busy before returning an error.
	PoolTimeout time.Duration `env:"POSTGRES_POOL_TIMEOUT" envDefault:"31s"`
	// Amount of time after which client closes idle connections.
	// Should be less than server's timeout.
	// -1 disables idle timeout check.
	IdleTimeout time.Duration `env:"POSTGRES_IDLE_TIMEOUT" envDefault:"5m"`
	// Frequency of idle checks made by idle connections reaper.
	// -1 disables idle connections reaper,
	// but idle connections are still discarded by the client
	// if IdleTimeout is set.
	IdleCheckFrequency time.Duration `env:"POSTGRES_IDLE_CHECK_FREQUENCY" envDefault:"1m"`
	// Name of the Table that is created to try if database is writeable
	HealthCheckTableName string `env:"POSTGRES_HEALTH_CHECK_TABLE_NAME" envDefault:"healthcheck"`
	// Amount of time to cache the last health check result
	HealthCheckResultTTL time.Duration `env:"POSTGRES_HEALTH_CHECK_RESULT_TTL" envDefault:"10s"`
}

type ConnectionPoolMetrics

type ConnectionPoolMetrics struct {
	// contains filtered or unexported fields
}

ConnectionPoolMetrics is the metrics collector for postgres connection pools (pace_postgres_connection_pool_*). It is capable of running an observer that periodically gathers those stats.

Example

myDB := ConnectionPool()

// collect stats about my db every minute
metrics := NewConnectionPoolMetrics()
if err := metrics.ObserveRegularly(context.Background(), myDB, "my_db"); err != nil {
	panic(err)
}
prometheus.MustRegister(metrics)

func NewConnectionPoolMetrics

func NewConnectionPoolMetrics() *ConnectionPoolMetrics

NewConnectionPoolMetrics returns a new metrics collector for postgres connection pools.

func (*ConnectionPoolMetrics) Collect

func (m *ConnectionPoolMetrics) Collect(ch chan<- prometheus.Metric)

Collect collects all the embedded prometheus metrics.

func (*ConnectionPoolMetrics) Describe

func (m *ConnectionPoolMetrics) Describe(ch chan<- *prometheus.Desc)

Describe descibes all the embedded prometheus metrics.

func (*ConnectionPoolMetrics) ObserveRegularly

func (m *ConnectionPoolMetrics) ObserveRegularly(ctx context.Context, db *pg.DB, poolName string) error

ObserveRegularly starts observing the given postgres pool. The provided pool name must be unique as it distinguishes multiple pools. The pool name is exposed as the "pool" label in the metrics. The metrics are collected once per minute for as long as the passed context is valid.

func (*ConnectionPoolMetrics) ObserveWhenTriggered

func (m *ConnectionPoolMetrics) ObserveWhenTriggered(trigger <-chan chan<- struct{}, db *pg.DB, poolName string) error

ObserveWhenTriggered starts observing the given postgres pool. The pool name behaves as decribed for the ObserveRegularly method. The metrics are observed for every emitted value from the trigger channel. The trigger channel allows passing a response channel that will be closed once the metrics were collected. It is also possible to pass nil. You should close the trigger channel when done to allow cleaning up.

type HealthCheck

type HealthCheck struct {
	Pool postgresQueryExecutor
	// contains filtered or unexported fields
}

HealthCheck checks the state of a postgres connection. It must not be changed after it was registered as a health check.

func (*HealthCheck) CleanUp

func (h *HealthCheck) CleanUp(ctx context.Context) error

CleanUp drops the test table.

func (*HealthCheck) HealthCheck

func (h *HealthCheck) HealthCheck(ctx context.Context) servicehealthcheck.HealthCheckResult

HealthCheck performs the read test on the database. If enabled, it performs a write test as well.

func (*HealthCheck) Init

func (h *HealthCheck) Init(ctx context.Context) error

Init initializes the test table