2

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
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.

| improve this answer | |
  • Wow. I've been looking for this exact answer for days. Glad I posted and you were able to help me! – Chris Williams Jan 22 '16 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.