The Build

18:12

Mountpoints and the Single PostgreSQL Server

27 March 2018

Ultimately, a PostgreSQL database is just files, and those files have to be stored somewhere. Absent tablespaces, they are all stored in a single directory structure, traditionally called PGDATA. While smaller instllations can just use the defaults associated with the packaging, larger databases are often installed on their own volume with its own mountpoint.

Two rules to keep in mind when choosing a name for the mountpoint and the directory structure:

  1. Always include the major version number in the directory, but,
  2. Never include the major version in the mountpoint.

For example, /pgsql is a great mountpoint name, but /pgsql/10 isn’t. Instead, call the mountpoint /pgsql and call PGDATA /pgsql/10/data (for example) under it.

Why?

At some point, you’ll probably want to have more than one major version installed on the same server. For example, you may want to use pg_upgrade to upgrade between major versions. In --link mode (which is the most sensible way to use pg_upgrade), you need to create a new cluster for the major version, but that new cluster needs to be on the same file system as the old cluster, so that pg_upgrade can create hard links between the data files.

If the major version is baked into the mountpoint, you start getting things like /pgdata/9.6/10 and similar awful situations.

So, make life easy for yourself and make the mountpoint generic, and use a directory path with the major version in it for PGDATA.

Comments are closed.