# FRiCKLE pres. ngx\_postgres

**URL:** <https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109>\
**Category:** NGINX\
**Created:** [May 5, 2010, 9:34pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109 "2010-05-05T21:34:05Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 5, 2010, 9:34pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/1 "2010-05-05T21:34:05Z")

</div>

Hello guys,  
I’m pleased to announce that ngx\_postgres is finally publicly available!

Website and distfiles are available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 6, 2010, 10:39am UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/2 "2010-05-06T10:39:00Z")

</div>

Good Jobs! 🙂

Posted at Nginx Forum:

> **[Re: FRiCKLE pres. ngx\_postgres](https://forum.nginx.org/read.php?2,82992,83190#msg-83190)**
>
> Hello guys, I'm pleased to announce that ngx\_postgres is finally publicly available! Website and distfiles are available at: http://labs.frickle.com/nginx\_ngx\_postgres/ GitHub repository is available at: http://github.com/FRiCKLE/ngx\_postgres

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 6, 2010, 12:29pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/3 "2010-05-06T12:29:03Z")

</div>

Hello!  
Awesome job, really.

I can imagine one scenario where similar plugins (polling backends for  
info) would be useful, where nginx can act as a traffic router,  
something like:  
$backend = select backend from route\_table where host=’$http\_host’

proxy\_pass http://$backend

By looking at the docs I’m guessing this isn’t possible at the moment  
(as well as the docs for ngx\_drizzle, …) - but are there any plans for  
similar behavior?

Cheers,  
Johan

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 6, 2010, 6:34pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/4 "2010-05-06T18:34:23Z")

</div>

Hello,

> I can imagine one scenario where similar plugins (polling backends for  
> info) would be useful, where nginx can act as a traffic router, something  
> like:  
> $backend = select backend from route\_table where host=‘$http\_host’
> 
> proxy\_pass http://$backend
> 
> By looking at the docs I’m guessing this isn’t possible at the moment (as  
> well as the docs for ngx\_drizzle, …) - but are there any plans for  
> similar behavior?

This is indeed good use case for ngx\_databases. I was solving such  
problem  
in the past (before ngx\_drizzle was released), but I totally forgot  
about it  
while developing ngx\_postgres. Thanks for the reminder 😉

Anyway, I’ve just pushed changes into ngx\_postgres’s GitHub repository  
[1]  
and this is now possible. Please note that you’ll need agentzh’s fork of  
eval module [2] to be able to use it with ngx\_postgres.

This, along with some other changes, will be released as  
ngx\_postgres-0.2  
early next week.

location / {  
eval\_subrequest\_in\_memory off;

```
eval $backend {
    postgres_pass database;
    postgres_query "select * from backends limit 1";
    postgres_get_value 0 0;
}

proxy_pass $backend;

```

}

[1] [GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL database. · GitHub](http://github.com/FRiCKLE/ngx_postgres)  
[2] [GitHub - openresty/nginx-eval-module: A module for evaluating memcached or proxy response into variable · GitHub](http://github.com/agentzh/nginx-eval-module)

Best regards,  
Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 7, 2010, 10:06am UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/5 "2010-05-07T10:06:12Z")

</div>

Hey,

> Ah, I love that postgres\_get\_value directive! 😃

🙂

> But to be honest, I’ve been planning a ngx\_rds\_picker filter module  
> that can do such “cell picking” in the result sets on the RDS level so  
> that specific DB drivers don’t have to do it themselves 😉

This seems like a more flexible way to do things, but I don’t think that  
generating whole RDS response just to get single value out of it is the  
best  
idea 😉

Of course, ideally “cell picking” should be done on the query level, so  
that  
database would return only the value that we are really interested in…  
But  
then comes the caching, so…

Having said that, I’m looking forward to more of your RDS filters 🙂

Best regards,  
Piotr S. \< [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 12, 2010, 9:42pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/6 "2010-05-12T21:42:00Z")

</div>

Version 0.2 is now available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Changes:  
2010-05-12 VERSION 0.2  
\* Add various improvements to build and testing infrastructures.  
Mostly done by Yichun Z. (agentzh).

```
* Put more restrictions on "postgres_pass" and "postgres_query"
  directives. Handle their bad configuration properly.

```

2010-05-10  
\* Log PostgreSQL errors into error.log.  
Reminded by Yichun Z. (agentzh).

2010-05-06  
\* Remove connection timeout from re-used keepalive connection.

```
* Fix libpq headers detection on Debian.
  Patch from Weibin Y..

* Add "postgres_get_value" directive.
  Requested by Johan Bergstroem.

* Bring back fail-safe check that got lost during pre-release
  refactorization. Without this check performance was reduced
  few times under high load, because about 1% of keepalive
  connections got disconnected.

```

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [May 7, 2010, 4:05am UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/7 "2010-05-07T04:05:07Z")

</div>

2010/5/7 Piotr S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> eval $backend {  
> postgres\_pass database;  
> postgres\_query “select \* from backends limit 1”;  
> postgres\_get\_value 0 0;  
> }

Ah, I love that postgres\_get\_value directive! 😃

But to be honest, I’ve been planning a ngx\_rds\_picker filter module  
that can do such “cell picking” in the result sets on the RDS level so  
that specific DB drivers don’t have to do it themselves 😉

Cheers,  
-agentzh

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [June 17, 2010, 4:22pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/8 "2010-06-17T16:22:08Z")

</div>

Version 0.3 is now available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Changes:  
2010-06-13 VERSION 0.3  
\* Allow configuration of method-specific queries.

```
* Restrict "postgres_pass" directive to "location" context.

```

2010-06-07  
\* Free keepalive connections on nginx shutdown.  
Requested by Yichun Z. (agentzh).

```
* Fix memory leak that was happening when nginx was configured
  to use non-existing database tables, etc.
  Found by Valgrind, reported by Yichun Z. (agentzh).

```

2010-06-04  
\* Use recently standardized error codes in RDS format.

2010-06-03  
\* Allow request methods other than GET and HEAD.  
From Yichun Z. (agentzh) via ngx\_drizzle.

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [August 3, 2010, 5:42pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/9 "2010-08-03T17:42:05Z")

</div>

Version 0.5 is now available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Changes:  
2010-08-03 VERSION 0.5  
\* Fix compatibility with nginx-0.8.47+.

2010-07-20  
\* Add “postgres\_escape” directive.

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [July 5, 2010, 4:17am UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/10 "2010-07-05T04:17:57Z")

</div>

Hi guys,  
I’m thrilled to announce new release of ngx\_postgres.

This is MAJOR update focused on new features and interoperability with  
number of 3rd party modules (including: ngx\_http\_auth\_request\_module,  
nginx-eval-module and ngx\_lua). When combined with ngx\_form\_input or  
ngx\_lua  
(for extracting values from client’s request body), this module allows  
one  
to create fully RESTful webservices using only nginx.conf! And it’s  
production-ready 🙂

This release is accompanied by the release of ngx\_rds\_json v0.10, which  
produces JSON responses for all 2xx status codes. This release is pretty  
much required for people wanting to use “postgres\_rewrite” directive.

Distfiles and documentation are available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Changes:  
2010-07-05 VERSION 0.4  
\* Optimize generation of RDS output.

2010-07-02  
\* Fix serious bug that under certain conditions (query evaluated  
to empty string, failed connection to the database, etc) would  
lead to segmentation fault on versions older than nginx-0.8.17  
(including nginx-0.7.x).

2010-06-30  
\* When returning row or value, use Content-Type specified by  
“default\_type” directive instead of “text/plain”.

```
* Allow column to be specified by its name instead of its number
  (in "postgres_output" and "postgres_set" directives).

```

2010-06-23  
\* Add “postgres\_rewrite” directive.

```
* Add "$postgres_affected" variable.

```

2010-06-22  
\* Fix issue that would stop gzip filter from processing  
responses in RDS format.  
Found by Qing Lin (kindy), fixed by Yichun Z. (agentzh).

2010-06-21  
\* Add “postgres\_output” directive.

2010-06-18  
\* Add “$postgres\_query” variable.

2010-06-16  
\* Add “postgres\_set” directive.

```
* Add "$postgres_columns" and "$postgres_rows" variables.

```

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [August 15, 2010, 11:43pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/11 "2010-08-15T23:43:54Z")

</div>

Version 0.6 is now available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

2010-08-15 VERSION 0.6  
\* Fix linking issue that manifested itself when nginx was build  
with both: ngx\_postgres and ngx\_supervisord modules.  
Reported by Sergey A. Osokin.

2010-08-09  
\* Fix pointer signedness mismatch, which broke build on Darwin  
and probably few other operating systems.  
Reported by sahuguet, fixed by Yichun Z. (agentzh).

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [September 30, 2010, 9:13pm UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/12 "2010-09-30T21:13:35Z")

</div>

Version 0.7 is now available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Changes:  
2010-09-30 VERSION 0.7  
\* Add option to send original response body with error responses  
set by “postgres\_rewrite” directive.

2010-08-25  
\* Fix error that could lead to failed connection to the database.

```
* Log more details on failed connection to the database.

```

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [December 23, 2010, 4:39am UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/13 "2010-12-23T04:39:04Z")

</div>

On Thu, Dec 23, 2010 at 11:27 AM, Piotr S.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 2010-12-23 VERSION 0.8
> 
> - Add option to return content in binary format using  
> “binary\_value” output format in “postgres\_output” directive.  
> Mostly done by Yichun Z. (agentzh).

Just a brief note on this:

I originally introduced this just because [Qunar.com](http://Qunar.com) (the biggest  
air-ticket search engine here in China) is storing (small) images into  
their PostgreSQL databases, and they’d like to serve out the images  
directly to the web browsers, and without introducing any extra middle  
layers to maximize speed.

> 2010-11-01
> 
> - Support “postgres\_pass”, “postgres\_query”, “postgres\_rewrite”  
> and “postgres\_output” directives in “if” pseudo-locations.  
> From Yichun Z. (agentzh).

This allows simple code branches by using nginx’s if mechanism. And  
yeah, nginx’s “if” is evil but it can make simple things easy. It’s  
recommended to use ngx\_lua’s set\_by\_lua and other stuffs to do  
complicated code branching though 🙂

Cheers,  
-agentzh

---

<div class="post-metadata">

**Author:** ![piotrs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@piotrs](https://www.ruby-forum.com/u/piotrs)\
**Post date:** [December 23, 2010, 4:29am UTC](https://www.ruby-forum.com/t/frickle-pres-ngx-postgres/188109/14 "2010-12-23T04:29:31Z")

</div>

Version 0.8 is now available at:  
[http://labs.frickle.com/nginx\_ngx\_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

GitHub repository is available at:

> **[GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to...](https://github.com/FRiCKLE/ngx_postgres)**
>
> upstream module that allows nginx to communicate directly with PostgreSQL database. - GitHub - FRiCKLE/ngx\_postgres: upstream module that allows nginx to communicate directly with PostgreSQL datab...

Changes:  
2010-12-23 VERSION 0.8  
\* Add option to return content in binary format using  
“binary\_value” output format in “postgres\_output” directive.  
Mostly done by Yichun Z. (agentzh).

2010-11-01  
\* Support “postgres\_pass”, “postgres\_query”, “postgres\_rewrite”  
and “postgres\_output” directives in “if” pseudo-locations.  
From Yichun Z. (agentzh).

2010-10-02  
\* Major rewrite of “postgres\_escape” directive.

Best regards,  
Piotr S. \< removed\_email\_address@domain.invalid \>