PostgreSQL protocol gateway for Presto

Prestogres is a gateway server that allows clients to use PostgreSQL protocol to run queries on Presto.

You can use any PostgreSQL clients (see also Limitation section):

Prestogres also offers password-based authentication and SSL.

Documents

How it works?
Limitation
Installation
- 1. Install PostgreSQL >= 9.3
- 2. Install Prestogres
Running servers
- Setting shmem max parameter
Configuration
Authentication
- md5 method
- external method
FAQ

How it works?

Prestogres uses modified version of pgpool-II to rewrite queries before sending them to PostgreSQL. pgpool-II is originally a middleware to provide connection pool and load balancing to PostgreSQL. Prestogres hacked it as following:

When a client connects to pgpool-II, the modified pgpool-II runs SELECT setup_system_catalog(...) statement on PostgreSQL.
- This function is implemented on PostgreSQL using PL/Python.
- It gets list of tables from Presto, and runs CREATE TABLE for each tables.
- Those created tables are empty, but clients can get the table schemas.
When the client runs a regular SELECT statement, the modified pgpool-II rewrites the query to run SELECT * FROM fetch_presto_query_results(...) statement.
- This function runs the original query on Presto and returns the results.
If the statement is not regular SELECT (such as SET, SELECT from system catalogs, etc.), pgpool-II simply forwards the statement to PostgreSQL without rewriting.

In fact, there're some more tricks. See prestogres/pgsql/prestogres.py for the real behavior.

Limitation

Extended query is not supported
- ODBC driver needs to set:
  - Server side prepare = no property (UseServerSidePrepare=0 at .ini file)
  - Level of rollback on errors = Transaction property (Protocol=7.4-0 or Protocol=6.4 at .ini file)
  - Unicode mode
- JDBC driver needs to set:
  - protocolVersion=2 property
Temporary table is not supported
Some SQL commands of Presto don't work
- Supported:
  - SELECT
  - EXPLAIN
  - INSERT INTO
  - CREATE TABLE
  - CREATE VIEW
- Not supported:
  - DROP TABLE

Installation

1. Install PostgreSQL >= 9.3

You need to install PostgreSQL separately. Following commands install PostgreSQL 9.3 from postgresql.org:

Ubuntu/Debian:

# add apt source
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
# install PostgreSQL
$ sudo apt-get install postgresql-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 postgresql-plpython-9.3
# install other dependencies
$ sudo apt-get install gcc make libssl-dev libpcre3-dev

RedHat/CentOS:

# add yum source
$ sudo yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
# install PostgreSQL
$ sudo yum install postgresql93-server postgresql93-contrib postgresql93-devel postgresql93-plpython
# install other dependencies
$ sudo yum install gcc make openssl-devel pcre-devel

Mac OS X:

You can install PostgreSQL using Homebrew.

brew install postgresql

2. Install Prestogres

Download the latest release from releases or clone the git repository. You can install the binary as following:

$ ./configure --program-prefix=prestogres- # if error occurs, add pg_config command $PATH (e.g. $ export PATH=/usr/pgsql-9.3/bin:$PATH)
$ make
$ sudo make install

You can find prestogres-ctl command:

$ prestogres-ctl --help

Running servers

You need to run 2 server programs: pgpool-II and PostgreSQL. You can use prestogres-ctl command to setup & run them as following:

# 1. Configure configuration file (at least presto_server parameter):
$ vi /usr/local/etc/prestogres.conf

# 2. Create a data directory:
$ prestogres-ctl create pgdata
# vi pgdata/postgresql.conf  # edit configuration if necessary

# 3. Start PostgreSQL
$ prestogres-ctl postgres -D pgdata

# 4. Open another shell, and initialize the database to install PL/Python functions
$ prestogres-ctl migrate

# 5. Start pgpool-II:
$ prestogres-ctl pgpool

# 6. Finally, you can connect to pgpool-II using psql command.
#    Database name ('hive') is name of a Presto catalog:
$ psql -h 127.0.0.1 -p 5439 -U presto hive

If configuration is correct, you can run SELECT * FROM sys.node; query. Otherwise, see log messages.

Setting shmem max parameter

Above command fails first time on most of environments! Error message is:

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=6432001, size=3809280, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded
available memory or swap space, or exceeded your kernel's SHMALL parameter.  You can either
reduce the request size or reconfigure the kernel with larger SHMALL.  To reduce the request
size (currently 3809280 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing
shared_buffers or max_connections.

You need to set 2 kernel parameters to run PostgreSQL.

Linux:

sudo bash -c "echo kernel.shmmax = 17179869184 >> /etc/sysctl.conf"
sudo bash -c "echo kernel.shmall = 4194304 >> /etc/sysctl.conf"
sudo sysctl -p /etc/sysctl.conf

Mac OS X:

$ sudo sysctl -w kern.sysv.shmmax=1073741824
$ sudo sysctl -w kern.sysv.shmall=1073741824

Configuration

Please read pgpool-II documentation for most of parameters used in prestogres.conf file. Following parameters are unique to Prestogres:

presto_server: address:port of a presto coordinator.
presto_catalog: (optional) catalog name of Presto (such as hive, etc.). By default, login database name is used as the catalog name
presto_schema: (optional) schema name of Presto (such as hive, etc.). By default, login database name is used as the schema name
presto_external_auth_prog: (optional) path to an external authentication program used by external authentication moethd. See following Authentication section for details.

You can overwrite these parameters for each connecting users (and databases) using prestogres_hba.conf file. See also following Authentication section.

Authentication

By default, Prestogres accepts all connections from 127.0.0.1 without password and rejects any other connections. You can change this behavior by updating $prefix/etc/prestogres_hba.conf file.

See sample prestogres_hba.conf file for details. Basic syntax is:

# TYPE  DATABASE  USER    CIDR-ADDRESS             METHOD        OPTIONS

# trust from 192.168.x.x without password
host    all       all     127.0.0.1/32             trust

# trust from 192.168.x.x without password
host    all       all     192.168.0.0/16           trust

# trust from 10.{1,2}.x.x without password
host    all       all     10.0.0.0/16,10.1.0.0/16  trust

# require password authentication from 10.3.x.x
host    all       all     10.3.0.0/16              md5

# overwrite presto_server address and catalog name if the login database name is altdb
host    altdb     all     0.0.0.0/0                md5           presto_server:alt.presto.example.com:8190,presto_catalog:hive

# run external command to authenticate if login user name is myuser
host    all       myuser  0.0.0.0/0                external      auth_prog:/opt/prestogres/auth.py

md5 method

This authentication method uses a password file ($prefix/etc/prestogres_passwd) to authenticate an user. You can use prestogres passwd command to add an user to this file:

$ prestogres-pg_md5 -pm -u myuser
password: (enter password here)

In prestogres_hba.conf file, you can set following options to the OPTIONS field:

presto_server: address:port of a presto coordinator, which overwrites presto_servers parameter in prestogres.conf.
presto_catalog: catalog name of Presto, which overwrites presto_catalog parameter in prestogres.conf.
presto_schema: schema name of Presto, which overwrites presto_schema parameter in prestogres.conf.
presto_user: user name to run queries on Presto (X-Presto-User). By default, login user name is used. Following pg_user parameter doesn't affect

Prestogres

Install / Use

README