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®
s.froehlich@cablelabs.com
Tel: +1 (303) 661-3708