pg_upgrade on new boot / Where to mount filesystems for tablespaces / other zfs questions

I'm rebuilding my database server, correcting errors I made when building it the first time. Note this is a analysis server where uptime is not critical nor is immediate data integrity (usually I have the original files the data came from). Backups are through occasional pg_dumps.

Errors include:

  *   Booting Ubuntu onto a zfs volume (possible but annoying)
  *   Not creating separate zfs filesystems for the database tablespaces and WAL

I am also upgrading to Ubuntu 18.04 and PostgreSQL 11 in the process.

I will follow the advice here as I rebuild the server:  https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices

I will make two backups. One is a pg_dumpall, and the second is an image of the root filesystem on the SSDs that I will then mount someplace convenient (on one of the HDD arrays) after the rebuild. I am making one hardware change, I'm adding a 3rd SSD to be a parity drive in a raidz configuration as the previous two are ~40% spent and I ended up striping them.

Questions:

  *   Is there a way to do a pg_upgrade smoothly after installing Ubuntu & Postgres to a new partition or should I just plan on restoring the pg_dumpall? (I also secondarily have pg_dumps of each database.)
  *   What directories need to be on drives with parity beyond the WAL directory?
  *   At what path does one traditionally mount additional tablespaces if one has the freedom to put them anywhere?
  *   The current zfs filesystems use 128k blocks, but I will add two _fastwrite tablespaces wich do have 16K blocks ... or should I just stick to 16k blocks for database filesystems?
  *   What is the right blocksize for the WAL filesystem and also for the "scratch area" for Postgres to do sorts (on the SSD array).

I know these are kind of hard questions, thanks all for your help.

--Stephen

________________________________
Stephen Froehlich
Sr. Strategist, CableLabs(r)

[email protected]<mailto:[email protected]>
Tel: +1 (303) 661-3708