Notes for installing PostgreSQL in a TrueNAS jail

[stualden]

The link below is to an up-to-date (as of June 2021) guide to installing PostgreSQL 13 in an iocage jail under TrueNAS 12. I am aware of the guides posted here and here, but a few things have changed since then. These guidelines cover only the installation of Postgres itself; your choice of access/query tool (PgAdmin or DBeaver or whatever) is yours to decide.

https://github.com/stualden/postgresql-misc/blob/main/install_postgres_on_truenas.md

I anticipate the entire process will take at most 30 minutes - much quicker if you already have experience in setting up datasets and jails. I use the TrueNAS GUI wherever possible.

I know that some people may question TrueNAS as a base for a Postgres installation, but for me it made sense, since my TrueNAS box had plenty of extra storage and it is one of my more powerful computers. I have been very pleased with the results. I was always surprised that there was no plugin for PostgreSQL, but perhaps the setup is a bit too customized to work well as a generic plugin.

All comments, corrections and questions are welcome. I'll try to keep it up-to-date as things change.

 

[debackerl]

Thanks a lot for this nice and comprehensive guide!

One thing that you could try is disabling the full_page_writes parameters in the postgres config file. There is a nice guide from 2ndquadrant showing a 70% performance boost when writing. They explain that it's not needed on ZFS because that file system will never write partial pages, so corruption would not be possible anyway.

Apparently, you can also get a performance boost by using different datasets for the WAL and Data files. That may be more work than the simple flag above though.

Keep up the good work!