tilelive-pgquery
v1.5.0
Published
PostgreSQL tilelive tile generator that runs an MVT-returning query and can load-balance between multiple PG servers
Downloads
22
Readme
tilelive-pgquery
This tilelive module runs a PostgreSQL query created by the OpenMapTiles MVT tools, and returns the data blob from the query results.
This module can connect to more than one postgreSQL server and load-balance requests based on the number of pending queries, weighted by the maxpool param.
This module expects either a parametrized query, or the name of a PostgreSQL function with three parameters: z, x, y
. The result is expected to be zero or one row,
with the first column being the tile data blob. The data blob could be gzip-compressed by the server, in which case tilelive-pgquery will work faster by skipping the compression step. An optional second column may contain a hash string, i.e. the result of the MD5(tile)
which will also speed up tilelive-pgquery tile retrieval. Without the hash, tilelive-pgquery will have to compute MD5 hash string itself. Tilelive-pgquery will determine the structure of the response during the startup by querying a tile specified by the testOnStartup
parameter (or default tile 14/9268/3575
).
Parameters
database
(string, required) - PostgreSQL database name. UsesPGDATABASE
env var if not set.host
(string, required) - PostgreSQL host. Could be used multiple times for load balancing. UsesPGHOST
env var if not set.port
(integer) - PostgreSQL port. Could be used multiple times for load balancing. If given, must be used once or the same number of times as there are hosts. UsesPGPORT
env var if not set.username
(string) - PostgreSQL username. UsesPGUSER
env var if not set.password
(string) - PostgreSQL password. UsesPGPASSWORD
env var if not set.maxpool
(integer) - size of the per-server connection pool (default=10). If given, must be used once or the same number of times as there are hosts.minzoom
(zoom) - minimum allowed zoom (default=0)maxzoom
(zoom) - maximum allowed zoom (default=22)testOnStartup
(tile index) - set which tile (in z/x/y or z,x,y format) to get on startup to verify database connection. By default, uses a simple tile in Norway on zoom 14. The default test tile will be adjusted depending on minzoom/maxzoom.serverInfo
(boolean) - if non-empty or not given, prints PostgreSQL & PostGIS version data and key metrics. To disable, set to an empty value.specInfo
(boolean) - if non-empty or not given, prints tilejson spec data. To disable, set to an empty value.prepareStatement
(boolean) - use prepared statements (defaults tofalse
for funcZXY and query,true
forqueryFile
).resolveDns
(boolean) - convert host value(s) to corresponding IPs on startup. If DNS resolves to multiple IPs, all IPs will be used in a pool.errorsAsEmpty
(boolean) - if set, treats all query errors as empty tiles, returning standardTile does not exist
error.connectionInitQuery
(string) - if set, run this query each time a new connection is made to a server.name
(string) - if set, adds this name to the metadata name fieldkey
(boolean) - if set, assumes the second query result column is a key (hash) value that should be attached to the result buffer. By default, auto-detects it by looking at the response.gzip
(boolean) - if set, will gzip-compresses data tile. By default, auto-detects if the server has gzip-compressed data by trying to un-gzip thetestOnStartup
tile. Thenogzip
obsolete parameter will be used ifgzip
is not set, and has the inverse meaning.contentType
(string) - setcontent-type
header. Usesauto
by default, detecting the tile type by queryingtestOnStartup
tile. If the tile content is recognized, content type will be set to one of these values:application/x-protobuf
,image/jpeg
, orimage/png
contentEncoding
(string) - setcontent-encoding
header. Usesauto
by default --gzip
for vector tiles, and unset for jpg/png images.sslmode
(string) - if set, specifies if SSL should be used when connecting to PostgreSQL. Onlydisable
(default) andrequire
are supported. See PG SSL info. Related upstream feature request.
Exactly one of the following 3 parameters must be given.
funcZXY
(string) - name of the function that accepts theZ, X, Y
int parameters.query
(string) - an SQL statement that uses$1, $2, $3
parameters forZ, X, Y
.queryFile
(string) - filename of a file that contains the query with$1, $2, $3
parameters forZ, X, Y
.
Testing
Testing requires a local PostgreSQL service, even if it is empty and runs inside a docker container:
docker run -it --rm --name pg-docker -e POSTGRES_PASSWORD=openmaptiles -e POSTGRES_USER=openmaptiles -e POSTGRES_DB=openmaptiles -p 5432:5432 postgres
# For multi-host test, run another instance on a different port
docker run -it --rm --name pg-docker2 -e POSTGRES_PASSWORD=openmaptiles -e POSTGRES_USER=openmaptiles -e POSTGRES_DB=openmaptiles -p 5434:5432 postgres
Run tests in a separate shell using yarn run test