psqlfront

package module
v0.5.4 Latest Latest
Warning

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

 Go to latest Published: Feb 25, 2023 License: MIT Imports: 35 Imported by: 0

README

psql-front

Documentation Latest GitHub release Github Actions test License

NOTICE: Currently psql-front is an experimental application

psql-front is a cache server that implements the PostgreSQL wire protocol v3. It temporarily stores data retrieved from a configured Origin in a PostgreSQL cache database so that PostgresSQL clients can reference the data with SQL.

Usage

For example, if you set up config.yaml

required_version: ">=v0.0.0"

cache_database:
  host: "{{ env `POSTGRES_HOST` `localhost` }}"
  username: "{{ env `PSOTGRES_USER` `postgres` }}"
  password: "{{ env `PSOTGRES_PASSWORD` `postgres` }}"
  port: 5432
  database: "postgres"

default_ttl: 24h

certificates:
  - cert: server.crt
    key: server.key

origins:
  - id: open_data
    type: HTTP
    schema: public
    tables:
      - name: syukujitsu
        url: https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv
        format: csv
        ignore_lines: 1
        text_encoding: Shift_JIS
        columns:
          - name: ymd
            data_type: DATE
            constraint: NOT NULL
          - name: name
            data_type: VARCHAR
            length: 64
            constraint: NOT NULL

You can try to set up an example using this config.yaml by using docker-compose.example.yaml.

$ docker compose -f docker-compose.example.yaml up
[+] Running 3/3
 ⠿ Network psql-front_app             Created 0.1s
 ⠿ Container psql-front-postgres-1    Created 0.1s
 ⠿ Container psql-front-psql-front-1  Created 0.1s

Access with psql client in this state.

$ psql -h localhost -U postgres -p 5434
Password for user postgres: 
psql (14.2, server 14.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, bits: 128, compression: off)
Type "help" for help.

postgres=# select * from public.syukujitsu;
postgres=# select * from public.syukujitsu LIMIT 1;
NOTICE:  cache hit: ["public"."syukujitsu"]
    ymd     | name 
------------+------
 1955-01-01 | 元日
(1 row)

postgres=#
Install
Binary packages

Releases

Docker

GitHub Packages

$ docker pull ghcr.io/mashiike/psql-front:latest
Options
$ psql-front -h    
Usage of psql-front:
Version: v0.0.0
  -config string
        psql-front config
  -log-level string
        log level (default "info")
  -port uint
        psql-front port (default 5434)
Monitoring

You can configure settings related to Stats monitoring in the configuration file.

required_version: ">= v0.1.0"

cache_database:
  host: "localhost"
  username: "postgres"
  password: "{{ env `PSOTGRES_DB_PASSWORD` `postgres` }}"
  port: 5432
  database: "postgres"
  ssl_mode: "{{ env `PSOTGRES_SSLMODE` `prefer` }}"


stats:
  enable: true
  monitoring_interval: 30s # Interval at which stats are collected
  store_database: true # If true, output to psqlfront.stats in the cache database.

If stats.store_database is true, the cache database can be accessed and aggregated.

$ psql -h localhost -U postgres -p 5432
Password for user postgres: 
psql (14.2, server 14.4)
Type "help" for help.

postgres=# select * from psqlfront.stats;
   hostname   | pid | uptime |        time         | version | curr_connections | total_connections | queries | cache_hits | cache_misses | memory_alloc 
--------------+-----+--------+---------------------+---------+------------------+-------------------+---------+------------+--------------+--------------
 d11d18ed9af7 |   1 |     30 | 2022-08-04 16:27:54 |  v0.1.0 |               10 |                12 |    4250 |        348 |            4 |      7428576
 d11d18ed9af7 |   1 |     60 | 2022-08-04 16:28:24 |  v0.1.0 |               10 |                12 |   12510 |       1036 |            4 |      9275328
 d11d18ed9af7 |   1 |     90 | 2022-08-04 16:28:54 |  v0.1.0 |               10 |                12 |   20679 |       1719 |            4 |      8571120
 d11d18ed9af7 |   1 |    120 | 2022-08-04 16:29:24 |  v0.1.0 |               10 |                12 |   27544 |       2289 |            4 |      6078544
 d11d18ed9af7 |   1 |    150 | 2022-08-04 16:29:54 |  v0.1.0 |               10 |                12 |   34749 |       2890 |            4 |      6225504
 d11d18ed9af7 |   1 |    180 | 2022-08-04 16:30:23 |  v0.1.0 |               10 |                12 |   41841 |       3481 |            4 |      6345872
 d11d18ed9af7 |   1 |    210 | 2022-08-04 16:30:53 |  v0.1.0 |               10 |                12 |   50436 |       4197 |            4 |      7655392
 750020e74b36 |   1 |     30 | 2022-08-04 16:41:42 |  v0.1.0 |                0 |                 0 |       0 |          0 |            0 |      4993256

LICENSE

MIT License

Copyright (c) 2022 IKEDA Masashi

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	Version = "current"
)

Functions

func GetRemoteAddr 

func GetRemoteAddr(ctx context.Context) string

func PtrValue added in v0.1.0 

func PtrValue[T any](t T) *T

func RegisterOriginType 

func RegisterOriginType(typeName string, originConfigConstructor func() OriginConfig)

func UnregisterOriginType 

func UnregisterOriginType(typeName string)

func WithProxyConnOnQueryReceived 

func WithProxyConnOnQueryReceived(handler ProxyConnOnQueryReceivedHandlerFunc) func(opts *ProxyConnOptions)

func WithProxyConnTLS 

func WithProxyConnTLS(tlsConfig *tls.Config) func(opts *ProxyConnOptions)

Types

type CacheDatabaseConfig 

type CacheDatabaseConfig struct {
	Host     string `yaml:"host,omitempty"`
	Username string `yaml:"username,omitempty"`
	Password string `yaml:"password,omitempty"`
	Port     int    `yaml:"port,omitempty"`
	Database string `yaml:"database,omitempty"`
	SSLMode  string `yaml:"ssl_mode,omitempty"`
}

func (*CacheDatabaseConfig) DSN 

func (cfg *CacheDatabaseConfig) DSN() string

type CacheInfo 

type CacheInfo struct {
	SchemaName, TableName, OriginID string
	CachedAt, ExpiredAt             time.Time
}

type CacheWriter 

type CacheWriter interface {
	DeleteRows(ctx context.Context) error
	ReplaceCacheTable(ctx context.Context, t *Table) error
	AppendRows(context.Context, [][]interface{}) error
	TargetTable() *Table
}

type CertificateConfig 

type CertificateConfig struct {
	Cert string `yaml:"cert,omitempty"`
	Key  string `yaml:"key,omitempty"`
	// contains filtered or unexported fields
}

type Column 

type Column struct {
	Name      string
	DataType  string
	Length    *int
	Contraint string
}

type CommonOriginConfig 

type CommonOriginConfig struct {
	ID   string         `yaml:"id"`
	Type string         `yaml:"type"`
	TTL  *time.Duration `yaml:"ttl"`

	OriginConfig OriginConfig `yaml:"-"`
}

func (*CommonOriginConfig) NewOrigin 

func (cfg *CommonOriginConfig) NewOrigin() (Origin, error)

func (*CommonOriginConfig) Ristrict 

func (cfg *CommonOriginConfig) Ristrict() error

func (*CommonOriginConfig) UnmarshalYAML 

func (cfg *CommonOriginConfig) UnmarshalYAML(unmarshal func(interface{}) error) error

type Config 

type Config struct {
	RequiredVersion string `yaml:"required_version,omitempty"`

	CacheDatabase *CacheDatabaseConfig  `yaml:"cache_database,omitempty"`
	Certificates  []*CertificateConfig  `yaml:"certificates,omitempty"`
	DefaultTTL    time.Duration         `yaml:"default_ttl,omitempty"`
	Origins       []*CommonOriginConfig `yaml:"origins,omitempty"`

	InitialFetch         bool           `yaml:"initial_fetch,omitempty"`
	IdleTimeout          *time.Duration `yaml:"idle_timeout,omitempty"`
	CacheControllTimeout *time.Duration `yaml:"cache_controll_timeout,omitempty"`

	Stats *StatsConfig `yaml:"stats,omitempty"`
	// contains filtered or unexported fields
}

func DefaultConfig 

func DefaultConfig() *Config

func (*Config) Load 

func (cfg *Config) Load(path string) error

Load loads configuration file from file paths.

func (*Config) Restrict 

func (cfg *Config) Restrict() error

type Notifier 

type Notifier interface {
	Notify(ctx context.Context, resp *pgproto3.NoticeResponse) error
}

type Origin 

type Origin interface {
	ID() string
	GetTables(ctx context.Context) ([]*Table, error)
	RefreshCache(context.Context, CacheWriter) error
}

type OriginConfig 

type OriginConfig interface {
	Type() string
	Restrict() error
	NewOrigin(id string) (Origin, error)
}

func GetOriginConfig 

func GetOriginConfig(typeName string) (OriginConfig, bool)

type OriginNotFoundError 

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

func WrapOriginNotFoundError 

func WrapOriginNotFoundError(err error) *OriginNotFoundError

func (*OriginNotFoundError) Error 

func (onfe *OriginNotFoundError) Error() string

func (*OriginNotFoundError) Unwrap 

func (onfe *OriginNotFoundError) Unwrap() error

type ProxyConn 

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

func NewProxyConn 

func NewProxyConn(client net.Conn, upstream net.Conn, optFns ...func(opts *ProxyConnOptions)) (*ProxyConn, error)

func (*ProxyConn) ExtendDeadline added in v0.1.0 

func (conn *ProxyConn) ExtendDeadline() (time.Time, error)

func (*ProxyConn) Run 

func (conn *ProxyConn) Run(ctx context.Context) error

func (*ProxyConn) SetIdleTimeout added in v0.1.0 

func (conn *ProxyConn) SetIdleTimeout(idleTimeout time.Duration)

type ProxyConnOnQueryReceivedHandlerFunc added in v0.1.0 

type ProxyConnOnQueryReceivedHandlerFunc func(ctx context.Context, query string, isPreparedStmt bool, notifier Notifier) error

type ProxyConnOptions 

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

type ProxyConnection 

type ProxyConnection struct {
	*pgx.Conn
	// contains filtered or unexported fields
}

func (*ProxyConnection) Close 

func (conn *ProxyConnection) Close(ctx context.Context) error

func (*ProxyConnection) ID 

func (conn *ProxyConnection) ID() string

type Server 

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

func New 

func New(ctx context.Context, cfg *Config) (*Server, error)

func (*Server) GetStats added in v0.1.0 

func (server *Server) GetStats() *ServerStats

GetStats returns ServerStats of app

func (*Server) RunWithContext 

func (server *Server) RunWithContext(ctx context.Context, address string) error

func (*Server) RunWithContextAndListener 

func (server *Server) RunWithContextAndListener(ctx context.Context, listener net.Listener) error

type ServerStats added in v0.1.0 

type ServerStats struct {
	Hostname         string `ltsv:"hostname"`
	Pid              int    `ltsv:"pid"`
	Uptime           int64  `ltsv:"uptime"`
	Time             int64  `ltsv:"time"`
	Version          string `ltsv:"version"`
	CurrConnections  int64  `ltsv:"curr_connections"`
	TotalConnections int64  `ltsv:"total_connections"`
	Queries          int64  `ltsv:"queries"`
	CacheHits        int64  `ltsv:"cache_hits"`
	CacheMisses      int64  `ltsv:"cache_misses"`
	MemoryAlloc      uint64 `ltsv:"memory_alloc"`
}

func (*ServerStats) InsertInto added in v0.1.0 

func (stats *ServerStats) InsertInto(ctx context.Context, tx pgx.Tx) error

func (*ServerStats) Loatate added in v0.1.0 

func (stats *ServerStats) Loatate(ctx context.Context, tx pgx.Tx) error

func (*ServerStats) String added in v0.1.0 

func (stats *ServerStats) String() string

type StatsConfig added in v0.1.0 

type StatsConfig struct {
	Enabled            *bool         `yaml:"enabled,omitempty"`
	MonitoringInterval time.Duration `yaml:"monitoring_interval,omitempty"`
	StoreDatabase      bool          `yaml:"store_database,omitempty"`
}

type Table 

type Table struct {
	SchemaName string
	RelName    string

	Columns     []*Column
	Constraints []string
}

func AnalyzeQuery 

func AnalyzeQuery(query string) ([]*Table, error)

func (*Table) GenerateDDL 

func (t *Table) GenerateDDL() (string, error)

func (*Table) GoString 

func (t *Table) GoString() string

func (*Table) String 

func (t *Table) String() string

Source Files

View all Source files

Directories

Path Synopsis
cmd
psql-front
gdrive
http
static

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.