postgres

package
Version: v0.4.14 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 23, 2023 License: MIT Imports: 21 Imported by: 0

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

Examples

Constants

This section is empty.

Variables

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

Functions

func ConnectionPool 

func ConnectionPool(opts ...ConfigOption) *pg.DB

ConnectionPool returns a new database connection pool that is already configured with the correct credentials and instrumented with tracing and logging Used Config is taken from the env and it's default values. These values can be overwritten by the use of ConfigOption.

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 added in v0.1.17 

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.

func IsErrConnectionFailed added in v0.4.5 

func IsErrConnectionFailed(err error) bool

Types

type Config added in v0.1.77 

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"`
	// Indicator whether write (insert,update,delete) queries should be logged
	LogWrite bool `env:"POSTGRES_LOG_WRITES" envDefault:"true"`
	// Indicator whether read (select) queries should be logged
	LogRead bool `env:"POSTGRES_LOG_READS" envDefault:"false"`
}

type ConfigOption added in v0.3.4 

type ConfigOption func(cfg *Config)

func WithApplicationName added in v0.3.4 

func WithApplicationName(applicationName string) ConfigOption

WithApplicationName -ApplicationName is the application name. Used in logs on Pg side. Only available from pg-9.0.

func WithDatabase added in v0.3.4 

func WithDatabase(database string) ConfigOption

WithDatabase - customise the db name

func WithDialTimeout added in v0.3.4 

func WithDialTimeout(dialTimeout time.Duration) ConfigOption

WithDialTimeout - Dial timeout for establishing new connections.

func WithHealthCheckResultTTL added in v0.3.4 

func WithHealthCheckResultTTL(healthCheckResultTTL time.Duration) ConfigOption

WithHealthCheckResultTTL - Amount of time to cache the last health check result

func WithHealthCheckTableName added in v0.3.4 

func WithHealthCheckTableName(healthCheckTableName string) ConfigOption

WithHealthCheckTableName - Name of the Table that is created to try if database is writeable

func WithHost added in v0.3.4 

func WithHost(host string) ConfigOption

WithHost - customise the db host

func WithIdleCheckFrequency added in v0.3.4 

func WithIdleCheckFrequency(idleCheckFrequency time.Duration) ConfigOption

WithIdleCheckFrequency - Frequency of idle checks made by idle connection's reaper. -1 disables idle connection's reaper, but idle connections are still discarded by the client if IdleTimeout is set.

func WithIdleTimeout added in v0.3.4 

func WithIdleTimeout(idleTimeout time.Duration) ConfigOption

WithIdleTimeout - Amount of time after which client closes idle connections. Should be less than server's timeout. -1 disables idle timeout check.

func WithMaxConnAge added in v0.3.4 

func WithMaxConnAge(maxConnAge time.Duration) ConfigOption

WithMaxConnAge - Connection age at which client retires (closes) the connection. It is useful with proxies like PgBouncer and HAProxy.

func WithMaxRetries added in v0.3.4 

func WithMaxRetries(maxRetries int) ConfigOption

WithMaxRetries - Maximum number of retries before giving up.

func WithMaxRetryBackoff added in v0.3.4 

func WithMaxRetryBackoff(maxRetryBackoff time.Duration) ConfigOption

WithMaxRetryBackoff - Maximum backoff between each retry. -1 disables backoff.

func WithMinIdleConns added in v0.3.4 

func WithMinIdleConns(minIdleConns int) ConfigOption

WithMinIdleConns - Minimum number of idle connections which is useful when establishing new connection is slow.

func WithMinRetryBackoff added in v0.3.4 

func WithMinRetryBackoff(minRetryBackoff time.Duration) ConfigOption

WithMinRetryBackoff - Minimum backoff between each retry. -1 disables backoff.

func WithPassword added in v0.3.4 

func WithPassword(password string) ConfigOption

WithPassword - customise the db password

func WithPoolSize added in v0.3.4 

func WithPoolSize(poolSize int) ConfigOption

WithPoolSize - Maximum number of socket connections.

func WithPoolTimeout added in v0.3.4 

func WithPoolTimeout(poolTimeout time.Duration) ConfigOption

WithPoolTimeout - Time for which client waits for free connection if all connections are busy before returning an error.

func WithPort added in v0.3.4 

func WithPort(port int) ConfigOption

WithPort - customize the db port

func WithQueryLogging added in v0.4.0 

func WithQueryLogging(logRead, logWrite bool) ConfigOption

func WithReadTimeout added in v0.3.4 

func WithReadTimeout(readTimeout time.Duration) ConfigOption

WithReadTimeout - Timeout for socket reads. If reached, commands will fail with a timeout instead of blocking.

func WithRetryStatementTimeout added in v0.3.4 

func WithRetryStatementTimeout(retryStatementTimeout bool) ConfigOption

WithRetryStatementTimeout - Whether to retry queries cancelled because of statement_timeout.

func WithUser added in v0.3.4 

func WithUser(user string) ConfigOption

WithUser - customise the db user

func WithWriteTimeout added in v0.3.4 

func WithWriteTimeout(writeTimeout time.Duration) ConfigOption

WithWriteTimeout - Timeout for socket writes. If reached, commands will fail with a timeout instead of blocking.

type ConnectionPoolMetrics added in v0.1.17 

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)

Output:

func NewConnectionPoolMetrics added in v0.1.17 

func NewConnectionPoolMetrics() *ConnectionPoolMetrics

NewConnectionPoolMetrics returns a new metrics collector for postgres connection pools.

func (*ConnectionPoolMetrics) Collect added in v0.1.17 

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

Collect collects all the embedded prometheus metrics.

func (*ConnectionPoolMetrics) Describe added in v0.1.17 

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

Describe descibes all the embedded prometheus metrics.

func (*ConnectionPoolMetrics) ObserveRegularly added in v0.1.17 

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 added in v0.1.17 

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 added in v0.1.15 

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 added in v0.1.15 

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

CleanUp drops the test table.

func (*HealthCheck) HealthCheck added in v0.1.15 

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 added in v0.1.15 

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

Init initializes the test table

Source Files

View all

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.