Re: Postgresql 9.4 and ZFS?

On Wednesday, September 30, 2015 03:49:44 PM Keith Fiske wrote:
> We've run postgres on ZFS for years with great success (first on
> OpenSolaris, now on OmniOS, and I personally run it on FreeBSD). The
> snapshotting feature makes upgrades on large clusters much less scary
> (snapshot and revert if it goes bad) and being able to bring a snapshot
> backup up as a clone to restore an accidentally dropped table is great.

Somebody mentioned some trouble running it with ZFS on Linux, which is exactly
how we're planning our roll out. (We're a RHEL/CentOS shop) Have you tried
that config, and has it worked for you?

> Others have given a lot of great advice as far as system tuning. Only other
> thing I can add is you definitely do want your data directory on its own
> pool. But I recommend putting the actual data in a folder under that pool
> (possibly by major version name). For example if your pool is
>
> /data/postgres
>
> Create a folder under that directory to actually put the data:
>
> mkdir /data/postgres/9.4
>
> This allows pg_upgrade's --link option to work during major upgrades since
> you can't have an upgrade destination on a different filesystem. Just make
> a 9.5 directory in the same spot when the time comes around. With ZFS
> snapshots available, there's really no reason not to use the --link option
> to greatly speed up upgrades.

Recently, the PGDG RPMs provided by PostgreSQL have done something similar by
moving from /var/lib/pgsql/ to (EG) /var/lib/pgsql/9.4 and we've followed
suit, trying to keep things "stock" where possible.

Our intent is to make /var/lib/pgsql a filesystem in a pool containing no other
file systems, with SSD-based VDEVs that aren't shared for any other purpose.