changing the PostgreSQL default user

David Newman dnewman at
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 [1].

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
PostgreSQL user:

Is that, plus chown'ing the data directory, sufficient to effect a name



[1] 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 mailing list