#!/bin/bash

_PGDATA=/var/services/pgsql
PGDATA=$(/usr/bin/readlink ${_PGDATA})
PGDIRNAME=$(/usr/bin/dirname "$PGDATA")
PGDATA_OLD=$PGDIRNAME/pgsql.old
PGMAJORVERSION=11
PGUPGRADE_FLAG="/tmp/.UpgradePGSQLDatabase"
PGSTOP_IMMEDIATE_FLAG="/tmp/.StopPGSQLImmediate"
BOOL_INIT_DB=false
BOOL_REUPGRADE=false
BOOL_UPGRADE_PARTAIL_FAILED=false
PG93_BINDIR=/usr/share/pgsql/9.3/bin
PG93_32BIT_BINDIR=/usr/share/pgsql/9.3/32/bin
PG11_32BIT_BINDIR=/usr/share/pgsql/11/32/bin
NPROC=$(/bin/nproc)
NPROC=${NPROC:-2}
PGSTARTSTOP_TIMEOUT=1800

enum ()
{
    shift
    AA=${@##*\{} # get string strip after {
    AA=${AA%\}*} # get string strip before }
    AA=${AA//,/} # delete commaa
    local I=0
    for A in $AA ; do
        eval "$A=$I"
        ((I++))
    done
}

# DBUPGRADE_TYPE: Upgrade situations
# DBUPGRADE_NONE			: 11  32bit => 11 32bit / 11  64bit -> 11 64bit and pg_controldata --check-compatibility is ok
# DBUPGRADE_93_TO_11 			: 9.3 32bit => 11 32bit / 9.3 64bit -> 11 64bit
# DBUPGRADE_93_32BIT_TO_11_64BIT	: 9.3 32bit => 11 64bit
# DBUPGRADE_11_32BIT_TO_11_64BIT	: 11  32bit => 11 64bit
# DBUPGRADE_CANT			: No DB at the first startup / 9.3/11 64bit => 11 32bit / pg_controldata --check-compatibility isn't ok
enum db_upgarde "{ DBUPGRADE_NONE, DBUPGRADE_93_TO_11, DBUPGRADE_93_32BIT_TO_11_64BIT, DBUPGRADE_11_32BIT_TO_11_64BIT, DBUPGRADE_CANT }"
DBUPGRADE_TYPE=DBUPGRADE_NONE

EchoDate()
{
	echo "[$(/bin/date '+%Y-%m-%d %H:%M:%S %Z')]: $1"
}

StartFailedExit()
{
	/usr/syno/bin/synosocket --emit-guest-sdk "pgsql" "status=failed"
	exit 1
}

ChkPGSQLShare()
{
	/usr/syno/bin/servicetool --get-service-path pgsql >/dev/null 2>&1
	if [ $? -eq 0 ]; then
		EchoDate "No volume to start PostgreSQL."
		return 1
	fi
	return 0
}

ChkExecEnv()
{
	if ! ChkPGSQLShare; then
		StartFailedExit
	fi

	if [ ! -f /etc/postgresql/postgresql.conf ]; then
		/bin/cp -a /etc.defaults/postgresql /etc
	fi

	/bin/mkdir -p /run/postgresql
	/bin/chown -R postgres: /etc/postgresql /run/postgresql
	/bin/chmod 700 /etc/postgresql
	/bin/chmod 700 "$PGDATA"
	/usr/bin/find "$PGDATA/" -type d ! -perm 700 -print0 | xargs -0 -r -P "$NPROC" /bin/chmod 700
	/usr/bin/find "$PGDATA/" -type f ! -perm 600 -print0 | xargs -0 -r -P "$NPROC" /bin/chmod 600

	if [ ! -L "$PGDATA/postgresql.conf" ]; then
		/bin/ln -sf /etc/postgresql/postgresql.conf "$PGDATA/postgresql.conf"
	fi
}

CreateCalendarTablespaceDir()
{
	# this is a workaround to prevent pgsql can't start up due to calendar shutodwn improperly
	# which leak some table and tablespace config with no map files in fs
	if [ ! -d /tmp/synocalendar ]; then
		/bin/mkdir /tmp/synocalendar
		/bin/chmod 777 /tmp/synocalendar
		/bin/chown postgres /tmp/synocalendar
	fi
}

TuneParams()
{
	local datadir=$1
	local tunetype=$2
	declare -A params
	local max_connections shared_buffers config

	##### Get total memory size in system
	local mem
	mem=$(grep MemTotal /proc/meminfo | sed -E 's/^MemTotal:\s*([0-9]+)\s*kB$/\1/g')
	local re='^[0-9]+$'

	if ! [[ $mem =~ $re ]]; then
		EchoDate "Memory size is not a number: $mem"
		return 1
	fi

	##### Decide parameter values

	# max_connections
	max_connections=$((mem / 4096))
	max_connections=$((max_connections > 2000 ? 2000 : max_connections))
	max_connections=$((max_connections < 100  ? 100  : max_connections))
	params[max_connections]="${max_connections}"

	# shared_buffers
	shared_buffers=$((mem / 8))
	shared_buffers=$((shared_buffers > 524288 ? 524288 : shared_buffers)) # max 512MB
	shared_buffers=$((shared_buffers < 24576  ? 24576  : shared_buffers)) # min 24MB
	params[shared_buffers]="${shared_buffers}kB"

	# effective_cache_size
	params[effective_cache_size]="$((mem / 2))kB"

	# timezone
	for i in $(seq 1 3); do
		params[TimeZone]="$(/usr/syno/sbin/synodate --getNameInTZDB)"
		if [[ "$?" -ne "0" ]]; then
			sleep 1
			unset params[TimeZone]
		else
			break
		fi
	done

	if [ "repair" == "$tunetype" ];then
		params[ignore_checksum_failure]=on
		params[zero_damaged_pages]=on
		params[ignore_system_indexes]=on
	else
		params[ignore_checksum_failure]=off
		params[zero_damaged_pages]=off
		params[ignore_system_indexes]=off
	fi

	##### Replace parameter values
	config="$datadir/postgresql.conf"
	for key in "${!params[@]}"; do
		EchoDate "Replacing parameter: $key = ${params[$key]}"
		sed -i --follow-symlinks -E "s|${key}\s*=.*|${key} = ${params[$key]}|g" "$config"
	done
}

TuneRepairParams()
{
	TuneParams "$1" "repair"
}

ChkCompat()
{
	/usr/bin/pg_controldata --check-compatibility "$PGDATA" 2>/dev/null
	if [ $? -ne 0 ]; then
		EchoDate "Compatibility check is failed. Please check /var/log/postgresql.log for further information."
		/bin/mv -f "$PGDATA" "$PGDIRNAME/.pgsql.$(/bin/date +%s)"
		InitDB
	fi
}

CleanPID()
{
	local datadir=${1:-PGDATA}

	if [ -f "$datadir/postmaster.pid" ]; then
		EchoDate "Find postmaster.pid file"
		EchoDate "-------start postmaster.pid-----"
		/bin/cat "$datadir/postmaster.pid"
		EchoDate "-------end postmaster.pid-------"
		/bin/rm -f "$datadir/postmaster.pid"
	fi
}

InitDB()
{
	local enable_checksum=""

	if [ "x$(/bin/get_key_value /etc.defaults/synoinfo.conf support_postgresql_data_checksums)" == "xyes" ]; then
		enable_checksum="--data-checksums"
	fi

	if [ ! -d "$PGDATA/base" ]; then
		EchoDate "Initialize PostgreSQL database"

		/bin/mkdir -p "$PGDATA"
		/bin/chown -R postgres: "$PGDATA"
		/bin/chmod 700 /etc/postgresql "$PGDATA"
		/bin/su postgres -c "/usr/bin/initdb --locale=POSIX --pgdata=$PGDATA ${enable_checksum}" >/dev/null 2>&1
		/bin/ln -sf /etc/postgresql/postgresql.conf "$PGDATA/postgresql.conf"

		BOOL_INIT_DB=true
		EchoDate "Initialize PostgreSQL database done"
	fi
}


DoChkDB()
{
	local datadir=$1

	if [ "x$(/bin/cat "$datadir/PG_VERSION")" != "x$PGMAJORVERSION" ]; then
		/usr/share/pgsql/9.3/bin/pg_controldata --check-compatibility "$datadir" 2>/dev/null
		if [ $? -eq 0 ]; then
			echo DBUPGRADE_93_TO_11
			return
		fi
		if [ -f /usr/share/pgsql/9.3/32/bin/pg_controldata ]; then
			/usr/share/pgsql/9.3/32/bin/pg_controldata --check-compatibility "$datadir" 2>/dev/null
			if [ $? -eq 0 ]; then
				echo DBUPGRADE_93_32BIT_TO_11_64BIT
				return
			fi
		fi
		echo DBUPGRADE_CANT
		return
	fi

	/usr/bin/pg_controldata --check-compatibility "$datadir" 2>/dev/null
	if [ $? -ne 0 ]; then
		if [ -f /usr/share/pgsql/11/32/bin/pg_controldata ]; then
			/usr/share/pgsql/11/32/bin/pg_controldata --check-compatibility "$datadir" 2>/dev/null
			if [ $? -eq 0 ]; then
				echo DBUPGRADE_11_32BIT_TO_11_64BIT
				return
			fi
		fi
		echo DBUPGRADE_CANT
		return
	fi
	echo DBUPGRADE_NONE
}

IsOldDBExist()
{
	if [ -L "$PGDATA_OLD" -a -d "$(/usr/bin/readlink "$PGDATA_OLD")" -a -d "$PGDATA_OLD/base" ];then
		return 0
	fi
	return 1
}

# NOTE:
# Check db when start every time, but not only in upgrading/migrating DSM (Not check if /var/.UpgradeBootup exists or not)
# because the user can reboot DSM with the soft check of poweroffcheck after upgrading/migrating DSM
ChkDB()
{
	IsOldDBExist
	if [ $? -eq 0 ];then
		local datadir
		datadir=$(/usr/bin/readlink "$PGDATA_OLD")
		local type
		type=$(DoChkDB "$datadir")
		if [ "$type" != DBUPGRADE_NONE -a "$type" != DBUPGRADE_CANT ]; then
			EchoDate "Upgrade DB from the exited data $datadir"
			BOOL_REUPGRADE=true
			DBUPGRADE_TYPE=$type
			return 1
		fi
		rm -f "$PGDATA_OLD"
	fi
	if [ ! -d "$PGDATA/base" ]; then
		DBUPGRADE_TYPE=DBUPGRADE_CANT
		return 1
	fi

	DBUPGRADE_TYPE=$(DoChkDB "$PGDATA")
	if [ "$DBUPGRADE_TYPE" == DBUPGRADE_NONE ];then
		return 0
	fi

	return 1
}


CopyPGConf()
{
	local sharedir=$1
	local datadir=$2

	if [ -f "$datadir/pg_hba.conf" ];then
		mv -f "$datadir/pg_hba.conf" "$datadir/pg_hba.conf.$(/bin/date +%s)"
	fi
	/bin/cp -f "$sharedir/etc/postgresql/pg_hba.conf" "$datadir/pg_hba.conf"
	/bin/chown postgres: "$datadir/pg_hba.conf"
	/bin/chmod 700 "$datadir/pg_hba.conf"
	if [ -f "$datadir/postgresql.conf" ];then
		mv -f "$datadir/postgresql.conf" "$datadir/postgresql.conf.$(/bin/date +%s)"
	fi
	/bin/cp -f "$sharedir/etc/postgresql/postgresql.conf" "$datadir/postgresql.conf"
	/bin/chown postgres: "$datadir/postgresql.conf"
	/bin/chmod 700 "$datadir/postgresql.conf"
}

PG_Start()
{
	local bindir=$1
	local datadir=$2

	CleanPID "$datadir"
	EchoDate "Start pgsql, bindir: $bindir, datadir: $datadir"
	EchoDate "Start pgsql, bindir: $bindir, datadir: $datadir" >> "/var/log/postgresql_upgrade.log"
	/bin/su postgres -c "$bindir/pg_ctl start -s -D $datadir -w -t $PGSTARTSTOP_TIMEOUT" >> "/var/log/postgresql_upgrade.log" 2>&1
	local ret=$?
	if [ $ret -ne 0 ]; then
		# Error handling
		if /bin/tail -n 6 "/var/log/postgresql_upgrade.log" | /bin/grep "could not load pg_hba.conf"; then
			EchoDate "pg_hba.conf is recovered in $datadir"
			CopyPGConf "$bindir/.." "$datadir"
			/bin/su postgres -c "$bindir/pg_ctl start -s -D $datadir -w -t $PGSTARTSTOP_TIMEOUT"
			ret=$?
		elif /bin/tail -n 6 "/var/log/postgresql_upgrade.log" | /bin/grep -E 'configuration file .* contains errors' > /dev/null; then
			CopyPGConf "$bindir/.." "$datadir"
			EchoDate "postgresql.conf is recovered in $datadir"
			/bin/su postgres -c "$bindir/pg_ctl start -s -D $datadir -w -t $PGSTARTSTOP_TIMEOUT"
			ret=$?
		fi
	fi

	if [ $ret -ne 0 ]; then
		EchoDate "Can't start pgsql, bindir: $bindir, datadir: $datadir"
	else
		EchoDate "Start pgsql done, bindir: $bindir, datadir: $datadir"
	fi
	return $ret
}

PG_Stop()
{
	local bindir=$1
	local datadir=$2

	EchoDate "Stop pgsql, bindir: $bindir, datadir: $datadir"
	/bin/su postgres -c "$bindir/pg_ctl stop -s -D $datadir -w -t $PGSTARTSTOP_TIMEOUT -m fast"
	local ret=$?
	CleanPID "$datadir"
	EchoDate "Stop pgsql done, bindir: $bindir, datadir: $datadir"

	return $ret
}

PG93_Start()
{
	PG_Start $PG93_BINDIR "$PGDATA_OLD"
	return $?
}

PG93_Stop()
{
	PG_Stop $PG93_BINDIR "$PGDATA_OLD"
	return $?
}

UpgradeDB_MoveDB()
{
	if $BOOL_REUPGRADE; then
		rm -rf "$PGDATA"
	else
		local olddatadir
		olddatadir=$PGDIRNAME/pgsql.old.$(/bin/date +%s)
		if [ -e "$PGDATA" ]; then
			/bin/mv "$PGDATA" "$olddatadir"
			EchoDate "Move DB from $PGDATA to $olddatadir"
		fi
		rm -f "$PGDIRNAME/pgsql.old"
		ln -sf "$olddatadir" "$PGDIRNAME/pgsql.old"
	fi
	PGDATA_OLD=$(/usr/bin/readlink "$PGDIRNAME/pgsql.old")

	# Note: Copy the default postgresql 9.3 confiugre to postgresql 9.3/11 datadir
	CopyPGConf "$PG93_BINDIR/.." "$PGDATA_OLD"
	TuneParams "$PGDATA_OLD"

	/bin/chown -R postgres: "$PGDATA_OLD"
	/bin/chmod 700 "$PGDATA_OLD"
}

sqlexec()
{
	local oldbindir=$PG93_BINDIR
	local db="$1"
	local sql="$2"
	export PGOPTIONS='--client-min-messages=warning';$oldbindir/psql -h /run/postgresql -U postgres -d "$db" -XAqt -F $'\t' -c "$sql"
	return $?
}

UpgradeDB_Drop()
{
	local dbs
	dbs=$(sqlexec postgres 'SELECT datname FROM pg_database WHERE datistemplate=false')
	EchoDate "Database list: $dbs"

	EchoDate "Drop begin"
	local global_tablespaces=()
	for db in $dbs; do
		EchoDate "Drop db $db begin"
		# 1. Drop triggers in /var/packages
		local result_trigger
		result_trigger=$(sqlexec "$db" "SELECT format('%I(%s)', p.proname, oidvectortypes(p.proargtypes)) FROM pg_proc p WHERE probin LIKE '/var/packages%'")
		mapfile -t triggers <<< "$result_trigger"
		for trigger in "${triggers[@]}"; do
			if [ -z "$trigger" ];then
				continue;
			fi
			EchoDate "Drop trigger $trigger in db $db"
			sqlexec "$db" "DROP FUNCTION IF EXISTS $trigger CASCADE"
		done

		# 2. Drop talbes in non-existed tablespaces
		local result_tablespace_tables
		result_tablespace_tables=$(sqlexec "$db" "SELECT c.relname,t.spcname,t.location FROM pg_catalog.pg_class c INNER JOIN (SELECT oid,spcname,pg_catalog.pg_tablespace_location(oid) AS location FROM pg_catalog.pg_tablespace WHERE pg_catalog.pg_tablespace_location(oid) != '') t ON spcname !~ '^pg_' AND relname !~ '^pg_' AND c.reltablespace=t.oid")
		mapfile -t tablespace_tables <<< "$result_tablespace_tables"
		for tablespace_table in "${tablespace_tables[@]}"; do
			if [ -z "$tablespace_table" ];then
				continue;
			fi
			local values=($tablespace_table)
			local tablespace_location=${values[2]}
			if [ -e "$tablespace_location" ];then
				continue;
			fi
			local table=${values[0]}
			local tablespace=${values[1]}
			EchoDate "Drop table $table in tablespace $tablespace in db $db"
			sqlexec "$db" "DROP TABLE IF EXISTS $table"
			global_tablespaces+=("$tablespace")
		done
		EchoDate "Drop db $db end"
	done
	# 3. Drop non-existed tablespaces
	if [ ${#global_tablespaces[@]} -ne 0 ];then
		mapfile -t global_unique_tablespaces <<< "$(printf "%s\n" "${global_tablespaces[@]}" | sort -u)"
		for global_unique_tablespace in "${global_unique_tablespaces[@]}"; do
			if [ -z "$global_unique_tablespace" ];then
				continue;
			fi
			EchoDate "Drop tablespace $global_unique_tablespace"
			sqlexec postgres "DROP TABLESPACE IF EXISTS $global_unique_tablespace"
		done
	fi

	EchoDate "Drop end"
	return 0;
}

UpgradeDB_FastUpgradeCheck()
{
	local oldbindir=$PG93_BINDIR
	local olddatadir=$PGDATA_OLD

	cd "$PGDATA_OLD"
	/bin/su postgres -c "/usr/bin/pg_upgrade -c -j $NPROC -b $oldbindir -B /usr/bin -d $olddatadir -D $PGDATA"
	local ret=$?
	cd -
	if [ $? -ne 0 ]; then
		return 1
	fi

	# NOTE:
	# 1. pg_upgrade can't run when parent's colume A is NOT NULL, but child's colume A is NULL
	# 2. pg_upgrade -c can't check the situation
	# 3. It can be removed if postgreSQL fixs 1. or 2.
	local dbs
	dbs=$(sqlexec postgres 'SELECT datname FROM pg_database WHERE datistemplate=false')
	for db in $dbs; do
		local result
		result=$(sqlexec "$db" "WITH RECURSIVE parents AS (SELECT i.inhrelid, i.inhparent FROM pg_catalog.pg_inherits i UNION ALL SELECT p.inhrelid,i.inhparent FROM parents p JOIN pg_catalog.pg_inherits i ON i.inhrelid=p.inhparent) SELECT n.nspname, c.relname, ac.attname FROM parents p, pg_catalog.pg_attribute ac,pg_catalog.pg_attribute ap,pg_catalog.pg_class c,pg_catalog.pg_namespace n WHERE NOT ac.attnotnull AND ac.attrelid=p.inhrelid AND ap.attrelid=p.inhparent AND ac.attname=ap.attname AND ap.attnotnull AND c.oid=ac.attrelid AND c.relnamespace=n.oid")
		if [ -n "$result" ];then
			return 1
		fi
	done
	return 0
}

UpgradeDB_FastUpgrade()
{
	# NOTE: PG11_Start for testing to run pgsql 11 and fixing postgresql.conf
	PG11_Start
	if [ $? -ne 0 ]; then
		EchoDate "Failed to start pgsql with data $PGDATA"
		return 1
	fi
	PG11_Stop
	PG93_Start
	if [ $? -ne 0 ]; then
		EchoDate "Failed to start pgsql93"
		return 1
	fi
	UpgradeDB_Drop
	if [ $? -ne 0 ]; then
		PG93_Stop
		EchoDate "Failed to drop triggers"
		return 1
	fi
	UpgradeDB_FastUpgradeCheck
	local ret=$?
	PG93_Stop
	if [ $ret -ne 0 ]; then
		EchoDate "Can't run pg_upgrade"
		return 1
	fi
	local oldbindir=$PG93_BINDIR
	local olddatadir=$PGDATA_OLD

	cd "$PGDATA_OLD"
	/bin/su postgres -c "/usr/bin/pg_upgrade -U postgres -j $NPROC -b $oldbindir -B /usr/bin -d $olddatadir -D $PGDATA"
	ret=$?
	if [ $ret -ne 0 ]; then
		EchoDate "Failed to do pg_upgrade"
	else
		rm -f "$oldbindir/pg_upgrade_internal.log"
		rm -f "$oldbindir/pg_upgrade_server.log"
		rm -f "$oldbindir/pg_upgrade_utility.log"
		rm -f "$oldbindir/analyze_new_cluster.sh"
		rm -f "$oldbindir/delete_old_cluster.sh"
	fi
	cd -
	return $ret
}

UpgradeDB_DumpDB_Full()
{
	local bindir=$1
	local datadir=$PGDATA_OLD
	shift

	UpgradeDB_Drop
	if [ $? -ne 0 ]; then
		EchoDate "Failed to drop triggers"
		return 1
	fi

	EchoDate "Dump postgres global data begin"
	"$bindir/pg_dumpall" -U postgres -h /run/postgresql -g --disable-triggers "$@" -f "$datadir/pgsql-global.sql"
	if [ $? -ne 0 ]; then
		EchoDate "Dump global data failed"
		return 1
	fi
	EchoDate "Dump postgres global data done"

	/bin/sed -i "$datadir/pgsql-global.sql" \
		-e 's/^CREATE ROLE postgres;$//g' \
		-e 's/^ALTER ROLE postgres .*;$//g'

	local dbs
	dbs=$(sqlexec postgres 'SELECT datname FROM pg_database WHERE datistemplate=false')
	EchoDate "Database list: $dbs"

	echo "$dbs" > "$datadir/pgsql-dblist"
	for db in $dbs; do
		if [ true == $BOOL_UPGRADE_PARTAIL_FAILED -a -f "$datadir/pgsql-$db.sql" ];then
			continue;
		fi
		local db_log="/var/log/postgresql_upgrade_$db.log"
		EchoDate "Dump $db database begin"
		EchoDate "Dump $db database begin" >> "/var/log/postgresql_upgrade.log"
		local create_opts="-C"
		if [ "$db" == "postgres" ];then
			create_opts=""
		fi
		local ret
		"$bindir/pg_dump" -U postgres -h /run/postgresql $create_opts --disable-triggers "$@" -f "$datadir/pgsql-$db.sql" "$db" > "$db_log" 2>&1
		ret=$?
		cat "$db_log" >> "/var/log/postgresql_upgrade.log"
		if [ $ret -ne 0 ]; then
			rm -f "$datadir/pgsql-$db.sql"
			/bin/grep -l "\(catalog is missing\)\|\(Please REINDEX it\)\|\(page verification failed\)\|\(invalid page in block\)" "$db_log" > /dev/null 2>&1
			if [ $? -eq 0 ]; then
				EchoDate "Dump $db database failed"
				EchoDate "Dump $db database failed" >> "/var/log/postgresql_upgrade.log"
				#1. Vaccum database
				EchoDate "Vaccum $db database begin"
				"$bindir/vacuumdb" -U postgres -h /run/postgresql -f -d "$db"
				EchoDate "Vaccum $db database end"
				#2. Reindex database
				EchoDate "Reindex $db database begin"
				"$bindir/reindexdb" -U postgres -h /run/postgresql -d "$db"
				EchoDate "Reindex $db database end"
				#3. Dump database again
				EchoDate "Dump $db database begin"
				EchoDate "Dump $db database begin" >> "/var/log/postgresql_upgrade.log"
				"$bindir/pg_dump" -U postgres -h /run/postgresql $create_opts --disable-triggers "$@" -f "$datadir/pgsql-$db.sql" "$db" > "$db_log" 2>&1
				ret=$?
				cat "$db_log" >> "/var/log/postgresql_upgrade.log"
			fi
		fi
		if [ $ret -ne 0 ]; then
			BOOL_UPGRADE_PARTAIL_FAILED=true
			rm -f "$datadir/pgsql-$db.sql"
			EchoDate "Dump $db database failed"
			EchoDate "Dump $db database failed" >> "/var/log/postgresql_upgrade.log"
		else
			EchoDate "Dump $db database done"
			EchoDate "Dump $db database done" >> "/var/log/postgresql_upgrade.log"
		fi
		rm -f "$db_log"
	done

	return 0
}

UpgradeDB_DumpDB_Action()
{
	local bindir=$1
	local datadir="$PGDATA_OLD"
	shift

	PG_Start "$bindir" "$datadir"
	if [ $? -ne 0 ]; then
		return 1
	fi
	UpgradeDB_DumpDB_Full "$bindir" "$@"
	ret=$?
	PG_Stop "$bindir" "$datadir"
	if [ $ret -eq 0 ]; then
		return 0
	fi
	return 1
}

UpgradeDB_DumpDB()
{
	local bindir=$1
	local datadir="$PGDATA_OLD"
	shift

	UpgradeDB_DumpDB_Action "$bindir" "$@"
	if [ $? -eq 0 -a false == $BOOL_UPGRADE_PARTAIL_FAILED ]; then
		return 0
	fi
	EchoDate "Try to repair dbs ..."
	TuneRepairParams "$datadir"
	UpgradeDB_DumpDB_Action "$bindir" "$@"
	if [ $? -eq 0 ]; then
		return 0
	fi
	return 1
}

UpgradeDB_DoUpgrade()
{
	if [ "$DBUPGRADE_TYPE" != DBUPGRADE_NONE ]; then
		CleanPID
		UpgradeDB_MoveDB
		if [ "$DBUPGRADE_TYPE" != DBUPGRADE_CANT ]; then
			/usr/syno/bin/synodsmnotify @administrators pgsql_upgrade_timeout
			echo "start" > "$PGUPGRADE_FLAG"
		fi
		EchoDate "Upgrade DB with data $PGDATA_OLD"
		InitDB
	fi

	EchoDate "Upgrade Type: $DBUPGRADE_TYPE"
	case $DBUPGRADE_TYPE in
		DBUPGRADE_93_TO_11 )
			EchoDate "Fast upgrade database begin"
			UpgradeDB_FastUpgrade
			local ret=$?
			if [ $ret -eq 0 ]; then
				EchoDate "Fast upgrade database done"
				return 0
			else
				EchoDate "Fast upgrade database failed"
			fi

			rm -rf "$PGDATA" # Remove partial data which is restored in pg_upgrade
			InitDB
			UpgradeDB_DumpDB $PG93_BINDIR
			if [ $? -eq 0 ]; then
				return 0
			fi
		;;
		DBUPGRADE_93_32BIT_TO_11_64BIT )
			UpgradeDB_DumpDB $PG93_32BIT_BINDIR
			if [ $? -eq 0 ]; then
				return 0
			fi
		;;
		DBUPGRADE_11_32BIT_TO_11_64BIT )
			UpgradeDB_DumpDB $PG11_32BIT_BINDIR --no-sync
			if [ $? -eq 0 ]; then
				return 0
			fi
		;;
		DBUPGRADE_CANT )
			return 0;
		;;
	esac
	return 1
}

UpgradeDB()
{

	EchoDate "Upgrade DB begin"
	/usr/syno/bin/synosocket --emit-guest-sdk "pgsql" "status=upgrading"
	if ! UpgradeDB_DoUpgrade; then
		rm -f "$PGUPGRADE_FLAG"
		rm -f "$PGDIRNAME/pgsql.old"
		EchoDate "UpgradeDB Failed"
	fi
}

UpdatePGSQLWorker()
{
	local i=""
	local pkg=""

	for i in /var/packages/*/conf/resource; do
		[ -f "$i" ] || continue
		if [ "$(jq '.["pgsql-db"]' "$i")" != "null" ]; then
			pkg=$(basename "$(dirname "$(dirname "$i")")")
			echo "Update ${pkg} pgsql-db worker."
			synopkghelper update "$pkg" pgsql-db
			echo "Done updating ${pkg} pgsql-db worker."
		fi
	done
}

PG11_Start_Service()
{
	rm -f "$PGSTOP_IMMEDIATE_FLAG"

	/bin/systemctl reset-failed pgsql
	/usr/syno/bin/synosystemctl start pgsql
	return $?
}

PG11_Stop_Service()
{
	local ret
	local writable
	local volume

	volume="/$(echo "$PGDATA" | /bin/cut -d "/" -f2)"

	/usr/syno/sbin/synostgvolume --is-writable "$volume"
	writable=$?

	if [ $writable -ne 0 ]; then
		/bin/touch "$PGSTOP_IMMEDIATE_FLAG"
		/bin/chown postgres: "$PGSTOP_IMMEDIATE_FLAG"
	fi

	/usr/syno/bin/synosystemctl stop pgsql
	ret=$?

	if [ $writable -eq 0 ]; then
		/usr/bin/pg_isready -h /run/postgresql -U postgres
		if [ $? -le 1 ]; then
			/bin/su postgres -c "/usr/bin/pg_ctl -D ${_PGDATA} stop -m immediate -t 60"
			ret=$?
		fi
	fi
	EchoDate "pgsql is stopped"
	return $ret
}

PG11_Stop_Deamon() {
	local pid=$1

	if [ ! -e "$_PGDATA" ] && [ -n "$pid" ]; then
		/bin/kill -SIGQUIT "$pid"
		local sec=1
		while [ ${sec} -le 6 ]
		do
			/usr/bin/pg_isready -h /run/postgresql -U postgres
			if [ $? -ge 2 ]; then
				break;
			fi
			sec=$((sec + 1))
			sleep 1
		done
		rm -f "$PGSTOP_IMMEDIATE_FLAG"
	elif [ -f "$PGSTOP_IMMEDIATE_FLAG" ]; then
		/usr/bin/pg_ctl -D ${_PGDATA} stop -m immediate -t 6
		rm -f "$PGSTOP_IMMEDIATE_FLAG"
	else
		/usr/bin/pg_ctl -D ${_PGDATA} stop -m fast -t 60
	fi
	return $?
}

PG11_Start()
{
	local ret=1
	PG11_Start_Service
	local sec=1
	while [ ${sec} -le $PGSTARTSTOP_TIMEOUT ]
	do
		/bin/systemctl is-active pgsql --quiet
		if [ $? -eq 0 ]; then
			/usr/bin/pg_isready -h /run/postgresql -U postgres
			ret=$?
			# NOTE:
			# 1. pg_isready retern value: PQPING_OK (0), PQPING_REJECT (1), PQPING_NO_RESPONSE(2), PQPING_NO_ATTEMPT(3)
			# 2. PQPING_REJECT is returned if (1) the database system is starting up (2) the database system is shutting down (3) the database system is in recovery mode
			# 3. pgsql.service is maybe starting in recovery mode even if pgsql-adapter.service is starting over the PGSTARTSTOP_TIMEOUT timeout
			if [ ${ret} -eq 0 ]; then
				EchoDate "pgsql is ready after waiting ${sec} seconds..."
				break
			fi
		else
			# Error handling
			if /bin/tail -n 6 /var/log/postgresql.log | /bin/grep "could not load pg_hba.conf"; then
				EchoDate "pg_hba.conf is recovered"
				/bin/cp /etc.defaults/postgresql/pg_hba.conf /etc/postgresql/pg_hba.conf
				PG11_Start_Service
			elif /bin/tail -n 6 /var/log/postgresql.log | /bin/grep -E 'configuration file .* contains errors' > /dev/null; then
				rm -f "$PGDATA/postgresql.conf"
				rm -f /etc/postgresql/postgresql.conf
				ChkExecEnv
				EchoDate "postgresql.conf is recovered"
				PG11_Start_Service
			# NOTE: Already checked in pg_controldata --check-compatibility and need to skip the cheching when UpgradeDB_FastUpgrade() call it
			# elif /usr/bin/tail -n 6 /var/log/postgresql.log | /bin/grep "USE_FLOAT8_BYVAL"; then
			#	PG11_Stop_Service
			#	EchoDate "USE_FLOAT8_BYVAL incompatable due to migrate."
			#	UpgradeDB
			#	PG11_Start_Service
			else
				EchoDate "postgrest process start up failed."
				break
			fi
		fi
		sec=$((sec + 1))
		sleep 1
	done
	return $ret
}

PG11_Stop()
{
	PG11_Stop_Service
}

case $1 in
	start)
		EchoDate "Start PostgreSQL"
		ChkExecEnv
		TuneParams "$PGDATA"
		ChkDB
		if [ $? -ne 0 ];then
			UpgradeDB
		fi
		CleanPID
		CreateCalendarTablespaceDir
		PG11_Start
		ret=$?
		if [ ${ret} -ne 0 ]; then
			EchoDate "Failed to start PostgreSQL. Please check /var/log/postgresql.log"
			if /bin/tail -n 6 /var/log/postgresql.log | /bin/grep "No space left on device"; then
				/usr/syno/bin/synodsmnotify @administrators pgsql_startup_failed
				EchoDate "no space left on device for starting PostgreSQL"
			fi
			StartFailedExit
		elif [ -f "$PGUPGRADE_FLAG" ];then
			BOOL_DOVACUUM=false
			if [ -f "$PGDATA_OLD/pgsql-global.sql" ]; then
				EchoDate "Restore database begin"
				EchoDate "Restore global data begin" >> "/var/log/postgresql_upgrade.log"
				export PGOPTIONS='--client-min-messages=warning';/usr/bin/psql -U postgres -Xq -v ON_ERROR_STOP=1 --pset pager=off -f "$PGDATA_OLD/pgsql-global.sql" >> "/var/log/postgresql_upgrade.log" 2>&1
				if [ $? -ne 0 ]; then
					BOOL_UPGRADE_PARTAIL_FAILED=true
					EchoDate "Restore global data failed" >> "/var/log/postgresql_upgrade.log"
				else
					EchoDate "Restore global data done" >> "/var/log/postgresql_upgrade.log"

					rm -f "$PGDATA_OLD/pgsql-global.sql"
					mapfile -t dbs < "$PGDATA_OLD/pgsql-dblist"
					for db in "${dbs[@]}"; do
						if [ -z "$db" -o ! -e "$PGDATA_OLD/pgsql-$db.sql" ];then
							continue;
						fi
						EchoDate "Restore $db database begin" >> "/var/log/postgresql_upgrade.log"
						export PGOPTIONS='--client-min-messages=warning';/usr/bin/psql -U postgres -Xq -v ON_ERROR_STOP=1 --pset pager=off -f "$PGDATA_OLD/pgsql-$db.sql" >> "/var/log/postgresql_upgrade.log" 2>&1
						if [ $? -ne 0 ]; then
							BOOL_UPGRADE_PARTAIL_FAILED=true
							EchoDate "Restore $db database failed" >> "/var/log/postgresql_upgrade.log"
						else
							rm -f "$PGDATA_OLD/pgsql-$db.sql"
							EchoDate "Restore $db database done" >> "/var/log/postgresql_upgrade.log"
						fi
					done
					BOOL_DOVACUUM=true
				fi

				rm -f "$PGDATA_OLD/pgsql-dblist"
				#rm -f "/var/log/postgresql_upgrade.log"
				if $BOOL_UPGRADE_PARTAIL_FAILED;then
					EchoDate "Restore database failed"
				else
					EchoDate "Restore database done"
				fi
			elif [ -f "$PGDATA_OLD/reindex_hash.sql" ];then
				export PGOPTIONS='--client-min-messages=warning';/usr/bin/psql -U postgres -Xq -v ON_ERROR_STOP=1 --pset pager=off -f "$PGDATA_OLD/reindex_hash.sql"
				rm -f "$PGDATA_OLD/reindex_hash.sql"
				BOOL_DOVACUUM=true
			fi
			rm -f "$PGUPGRADE_FLAG"
			# NOTE: Only remove soft link, but not remove old db data for support
			rm -f "$PGDIRNAME/pgsql.old"
			if $BOOL_DOVACUUM ;then
				EchoDate "Vaccum begin"
				/bin/su postgres -c "/usr/bin/vacuumdb -a -z -j $NPROC"
				EchoDate "Vaccum done"
			fi
			if $BOOL_UPGRADE_PARTAIL_FAILED ;then
				EchoDate "Upgrade pgsql data is failed"
				/usr/syno/bin/synodsmnotify @administrators pgsql_upgrade_fail
			else
				EchoDate "Upgrade pgsql data is done"
				/usr/syno/bin/synodsmnotify @administrators pgsql_upgrade_done
			fi
		fi

		if $BOOL_INIT_DB; then
			UpdatePGSQLWorker
		fi
		/usr/syno/bin/synosocket --emit-guest-sdk "pgsql" "status=active"
		;;
	stop)
		PG11_Stop
		;;

	stop-pgsql)
		PG11_Stop_Deamon "$2"
		;;

	restart)
		ChkExecEnv
		ChkCompat
		TuneParams "$PGDATA"
		PG11_Stop
		PG11_Start
		;;

	reload)
		TuneParams "$PGDATA"
		/bin/su postgres -c "/usr/bin/pg_ctl -s -D ${_PGDATA} reload" || exit 1
		;;

	status)
		if ! ChkPGSQLShare; then
			exit 1
		fi
		/bin/su postgres -c "/usr/bin/pg_ctl -s -D ${_PGDATA} status" || exit 1
		;;

	*)
		echo "usage: $(basename "$0") {start|stop|restart|reload|status}" >&2
		exit 64
		;;
esac
exit 0