pgvertica

package module
v0.0.0-...-cd047d4 Latest Latest
Warning

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

 Go to latest Published: Jul 17, 2023 License: Apache-2.0 Imports: 24 Imported by: 0

README

PGVertica

PGVertica is a proxy server designed to be an interface to connect from BI tools to Vertica using PostgreSQL connectors.

This project utilizes the PostgreSQL wire protocol to direct data-centric queries towards a Vertica database while managing all other queries, such as metadata retrievals from pg_catalog, INSERT operations to temporary tables, and others, using a local PostgreSQL instance with recreated Vertica schemas metadata.

This project is built on top of the existing PostgreSQL-sqlite proxy, postlite. For interfacing with Vertica, we use the github.com/vertica/vertica-sql-go library, while the PostgreSQL protocol implementation is taken care of by the github.com/jackc/pgproto3/v2 library.

Supported features:

  • TLS encryption
  • automatic synchronization of selected schemas metadata, between Vertica and Postgres
  • automatic synchronization of users and roles between Vertica and Postgres
  • extensive support for prepared statements
  • handling multiple datatypes using TEXT/BINARY protocol
  • fetching data forward with explicitly declared cursors

Note, that this proxy does not support

  • queries that add data to Vertica - all changes will only be reflected in the local postgres instance
  • SELECT queries which uses PostgreSQL specific syntax, which is not supported by Vertica dialect.

Supported clients

This proxy has been tested with following BI tools:

  • DBeaver
  • Tableau
  • PowerBI
  • Excel
  • Apache Superset
  • ArcGIS

Usage

Build PGVertica
go build -o pgvertica ./cmd/pgvertica/main.go
Run tests
go test -v
Run PGVertica
./pgvertica

Usage of ./pgvertica:
  -addr string
        proxy server bind address (default ":5432")
  -log-level string
        logger level (default "INFO")
  -pgconn string
        Postgres connection string
  -require-password
        whether this proxy should ask for password
  -schemas-sync-interval-s int
        time interval between schemas synchronization (default 60)
  -vconn string
        Vertica connection string in format vertica://user:password@addr:port/db
  -x509-cert-path string
        Path to SSL x509 cert file, if empty proxy won't support SSL

To build proxy and run docker with postgres you can use. Script will fill only --pgconn parameter, you need to pass the rest of parameters, eg.:

./start-proxy.sh --vconn "vertica://vuser:vpass@vertica-host:5433/dbname" --require-password

Contribution Policy

PGVertica is open to code contributions for bug fixes & documentation fixes only. Features carry a long-term maintenance burden so they will not be accepted at this time. Please submit an issue if you have a feature you'd like to request.

Documentation

Index

Constants

View Source 
const (
	ApplicationName = "PostgresProxy"
)
View Source 
const (
	ServerVersion = "15.2.0"
)

Postgres settings.

Variables

View Source 
var (
	POSTGRES_TO_VERTICA_TYPE_MAPPING = map[string]string{
		"boolean":     "boolean",
		"smallint":    "int",
		"integer":     "int",
		"bigint":      "int",
		"real":        "float",
		"numeric":     "numeric",
		"decimal":     "numeric",
		"date":        "date",
		"timestamp":   "timestamp",
		"timestamptz": "timestamptz",
		"time":        "time",
		"interval":    "interval",
		"varchar":     "varchar",
		"text":        "varchar",
		"bytea":       "varbinary",
		"json":        "long varchar",
		"jsonb":       "long varchar",
		"uuid":        "varchar(50)",
		"inet":        "varchar(39)",
		"cidr":        "varchar(43)",
	}
	VERTICA_TO_POSTGRES_TYPE_MAPPING = map[string]string{
		"boolean":     "boolean",
		"int":         "bigint",
		"float":       "double precision",
		"numeric":     "numeric",
		"date":        "date",
		"timestamp":   "timestamp",
		"timestamptz": "timestamptz",
		"time":        "time",
		"timetz":      "timetz",
		"interval":    "interval",
	}
)
View Source 
var Logger *slog.Logger = slog.Default()

Functions

func InitLogger 

func InitLogger(level slog.Level)

func LoadTLSConfig 

func LoadTLSConfig(x509SSLCertPath string) (*tls.Config, error)

Types

type BackendWrapper 

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

func (*BackendWrapper) Receive 

func (bw *BackendWrapper) Receive() (pgproto3.FrontendMessage, error)

func (*BackendWrapper) ReceiveStartupMessage 

func (bw *BackendWrapper) ReceiveStartupMessage() (pgproto3.FrontendMessage, error)

type Conn 

type Conn struct {
	net.Conn
	// contains filtered or unexported fields
}

func (*Conn) Close 

func (c *Conn) Close() (err error)

type Cursor 

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

type CursorType 

type CursorType string
const (
	BINARY CursorType = "BINARY"
	TEXT   CursorType = "TEXT"
)

type DBOpener 

type DBOpener interface {
	Open(driverName, dataSourceName string) (*sql.DB, error)
}

type DeclareCursorQuery 

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

type FetchQuery 

type FetchQuery struct {
	Direction  string
	Count      int
	CursorName string
}

type Listener 

type Listener interface {
	Accept() (net.Conn, error)
	Close() error
	Addr() net.Addr
}

type MessageBuffer 

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

type MessageBufferInterface 

type MessageBufferInterface interface {
	// contains filtered or unexported methods
}

type ParsedConnString 

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

type PreparedStatement 

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

type QueryExecutor 

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

type QueryUtil 

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

type RealDBOpener 

type RealDBOpener struct{}

func (RealDBOpener) Open 

func (ro RealDBOpener) Open(driverName, dataSourceName string) (*sql.DB, error)

type Receiver 

type Receiver interface {
	Receive() (pgproto3.FrontendMessage, error)
	ReceiveStartupMessage() (pgproto3.FrontendMessage, error)
}

type SchemasSynchronizator 

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

func NewSchemasSynchronizator 

func NewSchemasSynchronizator(vConnStr string, pgConnStr string) (*SchemasSynchronizator, error)

func (*SchemasSynchronizator) Close 

func (s *SchemasSynchronizator) Close() (err error)

func (*SchemasSynchronizator) ListSchemas 

func (s *SchemasSynchronizator) ListSchemas() ([]string, error)

func (*SchemasSynchronizator) SyncDBschemas 

func (s *SchemasSynchronizator) SyncDBschemas() error

type Server 

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

func NewServer 

func NewServer(config *ServerConfig) *Server

func (*Server) Close 

func (s *Server) Close() (err error)

func (*Server) CloseClientConnection 

func (s *Server) CloseClientConnection(conn *Conn) (err error)

CloseClientConnection disconnects a Postgres connections.

func (*Server) CloseClientConnections 

func (s *Server) CloseClientConnections() (err error)

CloseClientConnections disconnects all Postgres connections.

func (*Server) Open 

func (s *Server) Open() (err error)

type ServerConfig 

type ServerConfig struct {
	Addr                     string
	VerticaConnectionString  string
	PostgresConnectionString string
	RequirePassword          bool
	LogLevel                 int
	TlsConfig                *tls.Config
	SynchronizedSchemas      []string
}

Source Files

View all Source files

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.