17.3. 分发设计变更内容

BEGIN;

CREATE TABLE x_y_ (
	c1 TEXT PRIMARY KEY,
	c2 INTEGER;
);

COPY x_y_ FROM stdin;
AA	1
BB	2
\.

CREATE FUNCTION my_func(param TEXT) AS $$
BEGIN
	INSERT INTO x_y_ VALUES (param,3);
END $$ LANGUAGE PLPGSQL VOLATILE;

SELECT my_func('CC');

DROP TABLE x_y_;

DROP FUNCTION my_func();

COMMIT;
		

#!/bin/bash

APPLICATION=5
SQL_FILE=/tmp/patch-db.sql
HOST_DB_FILE=/tmp/hosts.txt
USER_PASSWORD=john@example.biz:MyYyPPassSwwordD
URL_PREFIX=https://www.terarows.com/pdbs

#Lock template database.
RESULT=`curl -s -u $USER_PASSWORD $URL_PREFIX/lock/$APPLICATION`
if [ "$RESULT" != "Y" ]; then
	echo "Failed to lock template database app${APPLICATION}."
	exit 1
fi

function unlock_template_database {
	RESULT=`curl -s -u $USER_PASSWORD $URL_PREFIX/unlock/$APPLICATION`
	if [ "$RESULT" != "Y" ]; then
		echo "Failed to unlock template database. Customers can't subscribe application# $APPLICATION now!"
	fi
}

#Download list of target hosts and databases.
rm -f $HOST_DB_FILE
curl -s -u $USER_PASSWORD -o $HOST_DB_FILE $URL_PREFIX/hosts/$APPLICATION
if [ ! -f "$HOST_DB_FILE" ]; then
	unlock_template_database
	echo "Failed to download list of target hosts and databases."
	exit 1
fi

#Apply this patch on all target databases in all target servers.
while read -a fields; do
	echo "===${fields[0]}	${fields[1]}==="
	cat $SQL_FILE |
		ssh dba@${fields[0]} "psql -U postgres --set AUTOCOMMIT=off --set ON_ERROR_STOP=on ${fields[1]}"
	if [ "$?" -ne 0 ]; then
		unlock_template_database
		exit 1
	fi
done < $HOST_DB_FILE

unlock_template_database
exit 0