manager

package
v1.1.1 Latest Latest
Warning

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

 Go to latest Published: Jan 11, 2026 License: Apache-2.0 Imports: 7 Imported by: 0

README

PostgreSQL Manager

The manager package provides an API for managing PostgreSQL server resources. It enables introspection and management of databases, roles, schemas, tables, and other PostgreSQL objects through both a direct Go API and HTTP interfaces. There are unit and integration tests included.

To test the package:

git clone github.com/mutablelogic/go-pg
make tests

You'll need to have docker installed in order to run the integration tests, which will create a PostgreSQL server in a container. There is a command line client included for testing:

git clone github.com/mutablelogic/go-pg
make cmd/pgmanager

This places a binary in the build folder which you can use as a server or client. To run the server on localhost, port 8080:

build/pgmanager run postgres://localhost:5432/postgres

To use the client:

build/pgmanager databases

Run build/pgmanager --help for more information.

Architecture

The package is organized into four main components:

Manager (this package folder)

The core component that provides direct access to PostgreSQL management functions. It wraps a connection pool and exposes methods for querying and managing server resources.

import "github.com/mutablelogic/go-pg/pkg/manager"

// Create a manager from an existing connection pool
mgr, err := manager.New(ctx, conn)

Documentation for all manager methods can be found here.

Schema (schema/)

Defines all data types, request/response structures, and SQL queries for PostgreSQL resources. Each resource type has its own file containing:

  • Structs - Go types representing PostgreSQL objects (e.g., Role, Database, Schema)
  • List requests - Parameters for filtering and pagination
  • SQL generation - Methods that produce parameterized SQL queries
HTTP Handler (httphandler/)

Provides REST API endpoints for all management operations. Register handlers with an http.ServeMux:

import "github.com/mutablelogic/go-pg/pkg/manager/httphandler"

httphandler.RegisterHandlers(mux, "/api/v1", mgr)

Includes a Prometheus metrics endpoint at /api/v1/metrics exposing:

  • Connection counts by database and state
  • Database and tablespace sizes
  • Table and index sizes
  • Dead tuple ratios for vacuum monitoring
  • Replication slot status and lag
HTTP Client (httpclient/)

A typed client for consuming the REST API from Go applications:

import "github.com/mutablelogic/go-pg/pkg/manager/httpclient"

client, err := httpclient.New("http://localhost:8080/api/v1")
roles, err := client.ListRoles(ctx)

Managed Resources

Resource Description
Roles Database users and groups with their attributes and memberships
Databases Database instances with size, owner, encoding, and connection settings
Schemas Namespaces within databases containing tables and other objects
Objects Tables, views, indexes, sequences, and other database objects
Tablespaces Storage locations for database files
Extensions PostgreSQL extensions installed on the server
Connections Active database connections with state and query information
Settings Server configuration parameters
Statements Query statistics from pg_stat_statements (when available)
Replication Slots Logical and physical replication slots with lag metrics

API Patterns

All list operations follow a consistent pattern:

// Request with optional filtering and pagination
req := schema.RoleListRequest{
    OffsetLimit: pg.OffsetLimit{
        Offset: 0,
        Limit:  types.Uint64Ptr(100),
    },
    Name: types.StringPtr("admin%"),  // LIKE pattern
}

// Response includes total count and body
list, err := mgr.ListRoles(ctx, req)
fmt.Printf("Total: %d, Returned: %d\n", list.Count, len(list.Body))

REST API Endpoints

All endpoints are prefixed with the configured path (e.g., /api/v1):

Method Path Description
GET /roles List roles
GET /roles/{name} Get role by name
GET /databases List databases
GET /databases/{name} Get database by name
GET /schemas List schemas
GET /objects List objects (tables, views, indexes, etc.)
GET /tablespaces List tablespaces
GET /extensions List extensions
GET /connections List active connections
GET /settings List server settings
GET /statements List statement statistics
GET /replicationslots List replication slots
GET /metrics Prometheus metrics

Query parameters support filtering and pagination:

  • offset - Skip N results
  • limit - Maximum results to return
  • Resource-specific filters (e.g., database, schema, type)

Dependencies

  • github.com/mutablelogic/go-pg - PostgreSQL connection pool
  • github.com/mutablelogic/go-server - HTTP utilities
  • github.com/prometheus/client_golang - Prometheus metrics

Documentation

Overview

Package manager provides a comprehensive API for managing PostgreSQL server resources including roles, databases, schemas, tables, connections, and more.

It wraps a connection pool and exposes methods for querying and managing PostgreSQL system catalogs. The package supports introspection of database objects and server configuration.

Creating a Manager 

mgr, err := manager.New(ctx, pool)
if err != nil {
    panic(err)
}

Listing Resources

All list operations follow a consistent pattern with filtering and pagination: 

roles, err := mgr.ListRoles(ctx, schema.RoleListRequest{
    OffsetLimit: pg.OffsetLimit{Limit: ptr(100)},
})

Managed Resources

The manager provides access to:

  • Roles (users and groups)
  • Databases
  • Schemas
  • Objects (tables, views, indexes, sequences)
  • Tablespaces
  • Extensions
  • Connections
  • Settings
  • Statements (pg_stat_statements)
  • Replication slots

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type Manager 

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

func New 

func New(ctx context.Context, conn pg.PoolConn) (*Manager, error)

New creates a new database manager.

func (*Manager) CreateDatabase 

func (manager *Manager) CreateDatabase(ctx context.Context, meta schema.DatabaseMeta) (*schema.Database, error)

CreateDatabase creates a new database with the specified metadata. The database creation cannot be done in a transaction, but ACL grants are applied within a transaction. If ACL grants fail, the database is deleted to maintain consistency.

func (*Manager) CreateExtension 

func (manager *Manager) CreateExtension(ctx context.Context, meta schema.ExtensionMeta, cascade bool) (*schema.Extension, error)

CreateExtension installs an extension in a database. The Database field in meta specifies which database to install into. If cascade is true, dependent extensions are also installed.

func (*Manager) CreateReplicationSlot 

func (manager *Manager) CreateReplicationSlot(ctx context.Context, meta schema.ReplicationSlotMeta) (*schema.ReplicationSlot, error)

CreateReplicationSlot creates a new replication slot with the specified metadata. Type must be "physical" or "logical". Logical slots require a plugin name.

func (*Manager) CreateRole 

func (manager *Manager) CreateRole(ctx context.Context, meta schema.RoleMeta) (*schema.Role, error)

CreateRole creates a new role with the specified metadata. The name must be a valid identifier and cannot have the reserved "pg_" prefix.

func (*Manager) CreateSchema 

func (manager *Manager) CreateSchema(ctx context.Context, database string, meta schema.SchemaMeta) (*schema.Schema, error)

CreateSchema creates a new schema in the specified database with the given metadata. ACL grants are applied after schema creation. If ACL grants fail, the schema is deleted to maintain consistency.

func (*Manager) CreateTablespace 

func (manager *Manager) CreateTablespace(ctx context.Context, meta schema.TablespaceMeta, location string) (*schema.Tablespace, error)

CreateTablespace creates a new tablespace with the specified metadata and location. The tablespace creation cannot be done in a transaction, but ACL grants are applied within a transaction. If ACL grants fail, the tablespace is deleted to maintain consistency.

func (*Manager) DeleteConnection 

func (manager *Manager) DeleteConnection(ctx context.Context, pid uint64) (*schema.Connection, error)

DeleteConnection terminates a connection by process ID and returns the terminated connection. Returns an error if the pid is zero or the connection is not found.

func (*Manager) DeleteDatabase 

func (manager *Manager) DeleteDatabase(ctx context.Context, name string, force bool) (*schema.Database, error)

DeleteDatabase drops a database by name and returns its metadata before deletion. If force is true, the database is dropped even if there are active connections.

func (*Manager) DeleteExtension 

func (manager *Manager) DeleteExtension(ctx context.Context, database, name string, cascade bool) error

func (*Manager) DeleteReplicationSlot 

func (manager *Manager) DeleteReplicationSlot(ctx context.Context, name string) (*schema.ReplicationSlot, error)

DeleteReplicationSlot drops a replication slot by name. Returns the slot metadata before deletion.

func (*Manager) DeleteRole 

func (manager *Manager) DeleteRole(ctx context.Context, name string) (*schema.Role, error)

DeleteRole deletes a role by name and returns the deleted role. Returns an error if the name is empty, has a reserved prefix, or the role is not found.

func (*Manager) DeleteSchema 

func (manager *Manager) DeleteSchema(ctx context.Context, database, namespace string, force bool) (*schema.Schema, error)

DeleteSchema drops a schema by database and namespace name, returning its metadata before deletion. If force is true, the schema is dropped with CASCADE even if there are dependent objects.

func (*Manager) DeleteTablespace 

func (manager *Manager) DeleteTablespace(ctx context.Context, name string) (*schema.Tablespace, error)

DeleteTablespace drops a tablespace by name and returns its metadata before deletion. Returns an error if the name is empty or the tablespace is not found.

func (*Manager) GetConnection 

func (manager *Manager) GetConnection(ctx context.Context, pid uint64) (*schema.Connection, error)

GetConnection retrieves a single connection by process ID. Returns an error if the pid is zero or the connection is not found.

func (*Manager) GetDatabase 

func (manager *Manager) GetDatabase(ctx context.Context, name string) (*schema.Database, error)

GetDatabase retrieves a single database by name. Returns an error if the name is empty or the database is not found.

func (*Manager) GetExtension 

func (manager *Manager) GetExtension(ctx context.Context, name string) (*schema.Extension, error)

func (*Manager) GetObject 

func (manager *Manager) GetObject(ctx context.Context, database, namespace, name string) (*schema.Object, error)

func (*Manager) GetReplicationSlot 

func (manager *Manager) GetReplicationSlot(ctx context.Context, name string) (*schema.ReplicationSlot, error)

GetReplicationSlot retrieves a single replication slot by name. Returns an error if the name is empty or the slot is not found.

func (*Manager) GetRole 

func (manager *Manager) GetRole(ctx context.Context, name string) (*schema.Role, error)

GetRole retrieves a single role by name. Returns an error if the name is empty or the role is not found.

func (*Manager) GetSchema 

func (manager *Manager) GetSchema(ctx context.Context, database, namespace string) (*schema.Schema, error)

GetSchema retrieves a single schema by database and namespace name. Returns an error if the database or namespace is empty or the schema is not found.

func (*Manager) GetSetting 

func (manager *Manager) GetSetting(ctx context.Context, name string) (*schema.Setting, error)

GetSetting returns a single setting by name.

func (*Manager) GetTablespace 

func (manager *Manager) GetTablespace(ctx context.Context, name string) (*schema.Tablespace, error)

GetTablespace retrieves a single tablespace by name. Returns an error if the name is empty or the tablespace is not found.

func (*Manager) ListConnections 

func (manager *Manager) ListConnections(ctx context.Context, req schema.ConnectionListRequest) (*schema.ConnectionList, error)

ListConnections returns a list of active database connections matching the request criteria. It supports filtering by database, role, and state, as well as pagination.

func (*Manager) ListDatabases 

func (manager *Manager) ListDatabases(ctx context.Context, req schema.DatabaseListRequest) (*schema.DatabaseList, error)

ListDatabases returns a list of databases matching the request criteria. It supports pagination through the OffsetLimit fields in the request.

func (*Manager) ListExtensions 

func (manager *Manager) ListExtensions(ctx context.Context, req schema.ExtensionListRequest) (*schema.ExtensionList, error)

ListExtensions returns a list of extensions. If Database is specified, shows extensions for that specific database (with installed status). If Database is not specified, shows available extensions cluster-wide from the current connection. Use the Installed filter to show only installed, only not-installed, or all extensions.

func (*Manager) ListObjects 

func (manager *Manager) ListObjects(ctx context.Context, req schema.ObjectListRequest) (*schema.ObjectList, error)

func (*Manager) ListReplicationSlots 

func (manager *Manager) ListReplicationSlots(ctx context.Context, req schema.ReplicationSlotListRequest) (*schema.ReplicationSlotList, error)

ListReplicationSlots returns a list of replication slots with their status. Includes lag information for connected replicas.

func (*Manager) ListRoles 

func (manager *Manager) ListRoles(ctx context.Context, req schema.RoleListRequest) (*schema.RoleList, error)

ListRoles returns a list of roles matching the request criteria. It supports pagination through the OffsetLimit fields in the request.

func (*Manager) ListSchemas 

func (manager *Manager) ListSchemas(ctx context.Context, req schema.SchemaListRequest) (*schema.SchemaList, error)

ListSchemas returns a list of schemas across all databases matching the request criteria. It supports pagination through the OffsetLimit fields in the request. If Database is specified in the request, only schemas from that database are returned.

func (*Manager) ListSettingCategories 

func (manager *Manager) ListSettingCategories(ctx context.Context) (*schema.SettingCategoryList, error)

ListSettingCategories returns all distinct setting categories.

func (*Manager) ListSettings 

func (manager *Manager) ListSettings(ctx context.Context, req schema.SettingListRequest) (*schema.SettingList, error)

ListSettings returns all server settings, optionally filtered by category.

func (*Manager) ListStatements 

func (manager *Manager) ListStatements(ctx context.Context, req schema.StatementListRequest) (*schema.StatementList, error)

ListStatements returns a list of statement statistics from pg_stat_statements. The request can filter by database, user, and order by various fields. Returns ErrNotAvailable if pg_stat_statements is not installed.

func (*Manager) ListTablespaces 

func (manager *Manager) ListTablespaces(ctx context.Context, req schema.TablespaceListRequest) (*schema.TablespaceList, error)

ListTablespaces returns a list of tablespaces matching the request criteria. It supports pagination through the OffsetLimit fields in the request.

func (*Manager) ReloadConfig 

func (manager *Manager) ReloadConfig(ctx context.Context) error

ReloadConfig calls pg_reload_conf() to reload server configuration. This applies changes to settings with 'sighup' context without requiring a restart.

func (*Manager) ResetStatements 

func (manager *Manager) ResetStatements(ctx context.Context) error

ResetStatements resets the statistics for all statements. Returns ErrNotAvailable if pg_stat_statements is not installed.

func (*Manager) StatStatementsAvailable 

func (manager *Manager) StatStatementsAvailable() bool

StatStatementsAvailable returns true if pg_stat_statements extension is available

func (*Manager) UpdateDatabase 

func (manager *Manager) UpdateDatabase(ctx context.Context, name string, meta schema.DatabaseMeta) (*schema.Database, error)

UpdateDatabase modifies an existing database's metadata including name, owner, and ACLs. All changes are applied within a transaction to ensure atomicity. If meta.Name is provided and differs from name, the database is renamed. ACL changes are synchronized by revoking removed privileges and granting new ones.

func (*Manager) UpdateExtension 

func (manager *Manager) UpdateExtension(ctx context.Context, name string, meta schema.ExtensionMeta) (*schema.Extension, error)

UpdateExtension updates an extension's version and/or schema. The Database field in meta specifies which database to update. The Version field specifies the target version (empty means latest). The Schema field specifies a new schema to move the extension to (only for relocatable extensions). Note: Name and Owner cannot be changed for extensions in PostgreSQL.

func (*Manager) UpdateRole 

func (manager *Manager) UpdateRole(ctx context.Context, name string, meta schema.RoleMeta) (*schema.Role, error)

UpdateRole updates an existing role with the specified metadata. If meta.Name is set and different from the current name, the role is renamed. If meta.Groups is set (even if empty), the group memberships are updated.

func (*Manager) UpdateSchema 

func (manager *Manager) UpdateSchema(ctx context.Context, database, namespace string, meta schema.SchemaMeta) (*schema.Schema, error)

UpdateSchema modifies an existing schema's metadata including name, owner, and ACLs. If meta.Name is provided and differs from namespace, the schema is renamed. ACL changes are synchronized by revoking removed privileges and granting new ones.

func (*Manager) UpdateSetting 

func (manager *Manager) UpdateSetting(ctx context.Context, name string, meta schema.SettingMeta) (*schema.Setting, error)

UpdateSetting updates a setting value. If meta.Value is nil, the setting is reset to default. Returns the updated setting. Check the Context field to determine if ReloadConfig() or a server restart is needed for the change to take effect. Returns an error for settings with 'internal' context (cannot be changed) or 'postmaster' context (requires server restart, not supported via API).

func (*Manager) UpdateTablespace 

func (manager *Manager) UpdateTablespace(ctx context.Context, name string, meta schema.TablespaceMeta) (*schema.Tablespace, error)

UpdateTablespace modifies an existing tablespace's metadata including name, owner, and ACLs. All changes are applied within a transaction to ensure atomicity. If meta.Name is provided and differs from name, the tablespace is renamed. ACL changes are synchronized by revoking removed privileges and granting new ones.

Source Files

View all Source files

Directories

Path Synopsis
Package httpclient provides a typed Go client for consuming the PostgreSQL management REST API.
 Package httpclient provides a typed Go client for consuming the PostgreSQL management REST API.
Package httphandler provides REST API endpoints for PostgreSQL management operations.
 Package httphandler provides REST API endpoints for PostgreSQL management operations.
Package schema defines all data types, request/response structures, and SQL queries for PostgreSQL management resources.
 Package schema defines all data types, request/response structures, and SQL queries for PostgreSQL management resources.

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.