Specifying a username while running initdb causes error in log on start of PostgreSQL process

Question

I have been forced to use a different default username for PostgreSQL. Before you ask: no, this is not something I can avoid. It's dumb, I know. Let's move on:

In order to do this in an existing Cent OS 7/PostgreSQL 9.3 install, I:

1. stopped PostgreSQL
2. moved the data directory from /var/lib/pgsql to a backup location
3. ran initdb with --username='my_postgres_user' (it's worth noting that I actually copied /usr/pgsql-9.3/bin/postgresql93-setup from my yum install and added the username parameter to the initdb command it eventually runs)
4. start the server using sudo systemctl start postgresql-9.3 (also worth noting here that these were used with the previous install of PostgreSQL)

The problem is that after startup, it looks like something is still expecting the postgres user. In the logs, I see:

< 2016年01月21日 17:20:23.412 EST >LOG: database system was shut down at 2016年01月21日 17:20:21 EST
< 2016年01月21日 17:20:23.416 EST >LOG: database system is ready to accept connections
< 2016年01月21日 17:20:23.417 EST >LOG: autovacuum launcher started
< 2016年01月21日 17:20:24.382 EST >FATAL: role "postgres" does not exist

Details of my postgresql93-setup file, the service config, and the full initdb log file are below.

My /usr/lib/systemd/system/postgresql-9.3.service:

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql-9.3.service",
# containing
# .include /lib/systemd/system/postgresql-9.3.service
# ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=PostgreSQL 9.3 database server
After=syslog.target
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
Environment=PGDATA=/var/lib/pgsql/9.3/data/
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
ExecStartPre=/usr/pgsql-9.3/bin/postgresql93-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-9.3/bin/pg_ctl start -D ${PGDATA} -s -w -t 300
ExecStop=/usr/pgsql-9.3/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target

My postgresql93-setup file:

#!/bin/sh
#
# postgresql-setup Initialization and upgrade operations for PostgreSQL
# PGVERSION is the full package version, e.g., 9.3.0
# Note: the specfile inserts the correct value during package build
PGVERSION=9.3.7
# PGMAJORVERSION is major version, e.g., 9.3 (this should match PG_VERSION)
PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/1円/'`
# PGENGINE is the directory containing the postmaster executable
# Note: the specfile inserts the correct value during package build
PGENGINE=/usr/pgsql-9.3/bin
# PREVMAJORVERSION is the previous major version, e.g., 9.1, for upgrades
PREVMAJORVERSION=9.1
# PREVPGENGINE is the directory containing the previous postmaster executable
PREVPGENGINE=/usr/pgsql-$PREVMAJORVERSION/bin
# The second parameter is the new database version, i.e. $PGMAJORVERSION in this case.
# Use "postgresql-$PGMAJORVERSION" service, if not specified.
SERVICE_NAME="2ドル"
if [ x"$SERVICE_NAME" = x ]
then
 SERVICE_NAME=postgresql-$PGMAJORVERSION
fi
# The third parameter is the old database version, i.e. $PREVMAJORVERSION in this case.
# Use "postgresql-$PREVMAJORVERSION" service, if not specified.
OLD_SERVICE_NAME="3ドル"
if [ x"$OLD_SERVICE_NAME" = x ]
then
 OLD_SERVICE_NAME=postgresql-$PREVMAJORVERSION
fi
# Find the unit file for new version.
if [ -f "/etc/systemd/system/${SERVICE_NAME}.service" ]
then
 SERVICE_FILE="/etc/systemd/system/${SERVICE_NAME}.service"
elif [ -f "/lib/systemd/system/${SERVICE_NAME}.service" ]
then
 SERVICE_FILE="/lib/systemd/system/${SERVICE_NAME}.service"
else
 echo "Could not find systemd unit file ${SERVICE_NAME}.service"
 exit 1
fi
# Log file for pg_upgrade
PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade.log
# Log file for initdb
PGLOG=/var/lib/pgsql/9.3/initdb.log
# Get port number and data directory from the service file
PGPORT=`sed -n 's/Environment=PGPORT=//p' "${SERVICE_FILE}"`
PGDATA=`sed -n 's/Environment=PGDATA=//p' "${SERVICE_FILE}"`
export PGPORT
export PGDATA
# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]
then
 SU=runuser
else
 SU=su
fi
script_result=0
# code shared between initdb and upgrade actions
perform_initdb(){
 if [ ! -e "$PGDATA" -a ! -h "$PGDATA" ]
 then
 mkdir -p "$PGDATA" || return 1
 chown postgres:postgres "$PGDATA"
 chmod go-rwx "$PGDATA"
 fi
 # Clean up SELinux tagging for PGDATA
 [ -x /sbin/restorecon ] && /sbin/restorecon "$PGDATA"
 # Create the initdb log file if needed
 if [ ! -e "$PGLOG" -a ! -h "$PGLOG" ]
 then
 touch "$PGLOG" || return 1
 chown postgres:postgres "$PGLOG"
 chmod go-rwx "$PGLOG"
 [ -x /sbin/restorecon ] && /sbin/restorecon "$PGLOG"
 fi
 # Initialize the database
 $SU -l postgres -c "$PGENGINE/initdb --pgdata='$PGDATA' --username='my_postgres_user' --auth='ident'" >> "$PGLOG" 2>&1 < /dev/null
 # Create directory for postmaster log files
 mkdir "$PGDATA/pg_log"
 chown postgres:postgres "$PGDATA/pg_log"
 chmod go-rwx "$PGDATA/pg_log"
 if [ -f "$PGDATA/PG_VERSION" ]
 then
 return 0
 fi
 return 1
}
initdb(){
 if [ -f "$PGDATA/PG_VERSION" ]
 then
 echo $"Data directory is not empty!"
 echo
 script_result=1
 else
 echo -n $"Initializing database ... "
 if perform_initdb
 then
 echo $"OK"
 else
 echo $"failed, see $PGLOG"
 script_result=1
 fi
 echo
 fi
}
upgrade(){
## Absorb configuration settings from the specified systemd service files.
# Do the same for the old PostgreSQL version.
if [ -f "/etc/systemd/system/${OLD_SERVICE_NAME}.service" ]
then
 OLD_SERVICE_FILE="/etc/systemd/system/${OLD_SERVICE_NAME}.service"
elif [ -f "/lib/systemd/system/${OLD_SERVICE_NAME}.service" ]
then
 OLD_SERVICE_FILE="/lib/systemd/system/${OLD_SERVICE_NAME}.service"
else
 echo "Could not find systemd unit file ${OLD_SERVICE_NAME}.service"
 exit 1
fi
## Get port number and data directory from the service file
NEWPGPORT=`sed -n 's/Environment=PGPORT=//p' "${SERVICE_FILE}"`
NEWPGDATA=`sed -n 's/Environment=PGDATA=//p' "${SERVICE_FILE}"`
## Get port number and data directory from the service file
OLDPGPORT=`sed -n 's/Environment=PGPORT=//p' "${OLD_SERVICE_FILE}"`
OLDPGDATA=`sed -n 's/Environment=PGDATA=//p' "${OLD_SERVICE_FILE}"`
# must see previous version in PG_VERSION
 if [ ! -f "$OLDPGDATA/PG_VERSION" -o \
 x`cat "$OLDPGDATA/PG_VERSION"` != x"$PREVMAJORVERSION" ]
 then
 echo
 echo $"Cannot upgrade because database is not of version $PREVMAJORVERSION."
 echo
 exit 1
 fi
 if [ ! -x "$PGENGINE/pg_upgrade" ]
 then
 echo
 echo $"Please install the postgresql92-contrib RPM."
 echo
 exit 5
 fi
# Perform initdb on the new server
$PGENGINE/postgresql92-setup initdb
RETVAL=$?
if [ $RETVAL -ne 0 ]
 then
 echo "initdb failed!"
 exit 1
fi
# Check the clusters first, without changing any data:
su -l postgres -c "$PGENGINE/pg_upgrade -b $PGPREVENGINE -B $PGENGINE/ -d $OLDPGDATA -D $NEWPGDATA -p $OLDPGPORT -P $NEWPGPORT -c"
RETVAL=$?
if [ $RETVAL -eq 0 ]
 then
 echo "Clusters checked successfully, proceeding with upgrade from $PREVMAJORVERSION to $PGMAJORVERSION"
 echo "Stopping old cluster"
 /bin/systemctl stop $OLD_SERVICE_NAME.service
 #/sbin/service $OLD_INIT_SCRIPT stop
 # Set up log file for pg_upgrade
 rm -f "$PGUPLOG"
 touch "$PGUPLOG" || exit 1
 chown postgres:postgres "$PGUPLOG"
 chmod go-rwx "$PGUPLOG"
 [ -x /sbin/restorecon ] && /sbin/restorecon "$PGUPLOG"
 echo "Performing upgrade"
 su -l postgres -c "$PGENGINE/pg_upgrade \
 -b $PGPREVENGINE -B $PGENGINE/ \
 -d $OLDPGDATA -D $NEWPGDATA -p $OLDPGPORT -P $NEWPGPORT" >> "$PGUPLOG" 2>&1 < /dev/null
 else
 echo "Cluster check failed. Please see the output above."
 exit 1
fi
 echo
exit 0
}
# See how we were called.
case "1ドル" in
 initdb)
 initdb
 ;;
 upgrade)
 upgrade
 ;;
 *)
 echo $"Usage: 0ドル {initdb|upgrade} [ service_name ]"
 exit 2
esac
exit $script_result

The log from initdb follows:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/pgsql/9.3/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/9.3/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data/
or
 /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile start

Answer 1

This might work for you, though you'll need to test this thoroughly.

Start with an initdb command like this as the postgres system user:

/usr/pgsql-9.3/bin/initdb -D 9.3/data/ -U pgsql

(be sure to change pgsql to whatever your new superuser value will be)

and with a line in your pg_ident.conf file with the contents of:

localmap postgres pgsql

along with a line to the top of your pg_hba.conf consisting of:

local all pgsql peer map=localmap

And modifying your /usr/lib/systemd/system/postgresql-9.3.service to add the line:

Environment=PGUSER=pgsql

after the PGDATA environment variable setting in the file

And uncommenting and changing log_connections to on in your postgresql.conf

You should get the following output:

< 2016年01月21日 19:02:19.233 EST >LOG: database system was shut down at 2016年01月21日 19:00:46 EST
< 2016年01月21日 19:02:19.234 EST >LOG: MultiXact member wraparound protections are now enabled
< 2016年01月21日 19:02:19.235 EST >LOG: database system is ready to accept connections
< 2016年01月21日 19:02:19.236 EST >LOG: autovacuum launcher started
< 2016年01月21日 19:02:20.220 EST >LOG: connection received: host=[local]
< 2016年01月21日 19:02:20.221 EST >LOG: connection authorized: user=pgsql database=postgres

Which now has a changed superuser (postgres to pgsql), attempting an peer style login for statistics collector process (which is critical for autovacuum and overall database health and monitoring), as the postgres system user.

• Chris Williams: Wow. I've been looking for this exact answer for days. Glad I posted and you were able to help me!