Talk to PostgreSQL from nginx, without anything in between. A location holds a query, the answer comes back as text or JSON, and what the database did -- rows or no rows, a change or no change -- becomes the status code.
The connection is non-blocking all the way through: the module drives libpq asynchronously inside nginx's event loop, so a worker handling a slow query goes on serving everything else. Connections are kept in a pool per worker rather than opened per request.
syntax: postgres_server <ip>[:<port>] | unix:<dir> [port=<port>] [dbname=<name>] [user=<user>] [password=<pass>]
context: upstream
upstream database {
postgres_server 127.0.0.1:5432 dbname=shop user=www password=secret;
}Several postgres_server lines in one upstream block are used in turn.
syntax: postgres_keepalive off | max=<count> [mode=single|multi] [overflow=ignore|reject]
default: max=10 mode=single overflow=ignore
context: upstream
How many connections a worker keeps open, and what happens when they are
all busy: ignore lets the request have a connection of its own and closes
it afterwards, reject answers 503.
mode=single matches a pooled connection by server only, mode=multi also
by database and user -- which is what a configuration with several
dbname= or user= values in one upstream needs.
syntax: postgres_pass <upstream>
context: location, if in location
syntax: postgres_query [<methods>] <query>
context: http, server, location, if in location
The query to send. It may contain variables, and a location may hold one query per method:
location = /cats {
postgres_pass database;
postgres_query HEAD GET "select name from cats";
postgres_query POST "insert into cats (name) values ($name)";
postgres_output json;
}A request whose method has no query, and where a method list was given,
gets a 405.
syntax: postgres_output rds | text | value | binary_value | json | hex | none
default: rds
context: http, server, location, if in location
text-- every value, row by row, separated by newlines, with(null)for a NULL and no trailing newlinevalue-- one row with one column and nothing else, or a500binary_value-- the same, unconvertedjson-- an array of objects; NULL isnull, integers are numbers, a singlejsonorjsonbcolumn is passed through as it standshex-- the value in hexadecimal, for abyteards-- the row-data-stream formatngx_rds_jsonandngx_rds_csvreadnone-- nothing, for a location that only sets variables or a status
syntax: postgres_rewrite [<methods>] <condition> [=]<status>
context: http, server, location, if in location
Turn what the database did into a status code. An = in front of the
status keeps the response body; without it the body is dropped, which is
what a 204 or a 304 requires.
The conditions are no_changes and changes -- whether a statement
touched any row -- no_rows and rows, and no_errors and errors.
The last pair is not about the query failing: a query the database refuses
never reaches a rewrite and answers 500. They look for a column whose
name starts with error and has a value in the first row, which is for a
stored procedure that reports its own trouble in the result set rather than
by raising.
location = /cats {
postgres_pass database;
postgres_query GET "select name from cats where id = $id";
postgres_rewrite no_rows 404;
postgres_output json;
}syntax: postgres_set $<variable> <row> <column> [optional|required]
context: http, server, location
Put one field of the result into a variable. The row is a number, the
column a number or a name. required answers 500 when the field is
missing or NULL; optional, the default, leaves the variable empty.
syntax: postgres_escape $<escaped> [[=]$<unescaped>]
context: http, server, location
Quote a value for use in a query: the result is the value with any quote
in it doubled, wrapped in single quotes, ready to be put straight into
postgres_query.
A value that is not there, or is empty, becomes NULL -- so a missing
argument gives where name = NULL, which matches nothing, rather than a
query that will not parse. An = in front of the source variable changes
that one case: an empty value then becomes '' instead of NULL.
location = /cats {
postgres_pass database;
postgres_escape $name $arg_name;
postgres_query "select id from cats where name = $name";
postgres_output value;
}default: 10s
context: http, server, location
default: 30s
context: http, server, location
How long to wait for the result of a query. A query that takes longer ends the request; it does not end the query, which goes on running in the database.
$postgres_columns, $postgres_rows, $postgres_affected and
$postgres_query -- the shape of the result, how many rows a statement
changed, and the query that was sent.
Needs libpq and its headers: libpq-dev on Debian and its relatives, the
postgresqlNN-client port on FreeBSD.
./configure --add-module=/path/to/nginx-postgres-moduleor as a loadable module:
./configure --with-compat --add-dynamic-module=/path/to/nginx-postgres-moduleload_module modules/ngx_postgres_module.so;config asks pg_config where libpq is, which is the answer on any system
with a PostgreSQL client installed. Where that is not on the path:
PG_CONFIG=/opt/pgsql/bin/pg_config ./configure --add-module=...
LIBPQ_INC=/opt/pgsql/include LIBPQ_LIB=/opt/pgsql/lib ./configure --add-module=...ci/build.sh 1.30.5 /tmp/nginx-postgres
ci/postgres.sh
TEST_NGINX_BINARY=/tmp/nginx-postgres/sbin/nginx prove -r t/
ci/pool.sh /tmp/nginx-postgres/sbin/nginxci/postgres.sh starts a cluster of its own in ci-work/, with the rows
the suite expects, and throws it away with that directory. It does not
touch a system cluster, and it leaves an already running server on its port
alone.
ci/pool.sh is the one the suite cannot replace. Test::Nginx sends a
request and waits for the answer, so the keepalive pool is never under
pressure in t/: a module that opened a connection per request and never
gave one back would pass every block there. That script sends forty
requests at once through a pool of two and then asks pg_stat_activity how
many connections are left.
BSD 2-Clause, see LICENSE.