changing the PostgreSQL default user
dnewman at networktest.com
Mon Oct 21 06:23:15 UTC 2019
FreeBSD recently changed its default version of PostgreSQL from 9.5 to 11.
However, attempts to run 'pg_upgrade' on the databases failed for me
because my 9.5 install had a default user of 'pgsql' and version 11 goes
with 'postgres' instead .
My hack was to edit /usr/local/etc/rc.d/postgresql, set postgresql_user
to pgsql, (re)run initdb, and then do the pg_upgrade conversion. It
works, but future upgrades might clobber the edit in the rc.d file.
This article suggests one ALTER command will change the default
Is that, plus chown'ing the data directory, sufficient to effect a name
 The pg_upgrade program has a -U switch to specify user, but it still
fails because the pgsql user can't read stuff owned by the postgres user
and vice-versa. Just running chown on either binary or data directory
and its contents doesn't work.
More information about the freebsd-questions