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:
- stopped PostgreSQL
- moved the data directory from
/var/lib/pgsqlto a backup location - ran
initdbwith--username='my_postgres_user'(it's worth noting that I actually copied/usr/pgsql-9.3/bin/postgresql93-setupfrom my yum install and added the username parameter to theinitdbcommand it eventually runs) - 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
