16.3. Propagate Design Changes

After doing new design or making changes on database dev5, and performing application tests in your development server, the next step is propagating the changes to the following two types of databases:

• your database app5

• all of your cloud customers' databases

Propagation must been carried out in the following order:

1. Lock database app5.

   During the locked period, customers' renting Section 16.2.4 application system code "5" is disallowed.

   Developer should run either one of the following commands in order to lock database app5:

   wget -q --auth-no-challenge --http-user=john@example.biz --http-password=MyYyPPassSwwordD -O - https://www.terarows.com/pdbs/lock/5

   or

   curl -u john@example.biz:MyYyPPassSwwordD https://www.terarows.com/pdbs/lock/5

   If the execution succeeds, it returns Y. Otherwise, it returns N.

2. Run your propagation script against your database app5 and all customers' databases associated with your application system code "5".

   The developer will now run either one of the following commands in order to download your database app5 and all your customers' server IP addresses and database names:

   wget -q --auth-no-challenge --http-user=john@example.biz --http-password=MyYyPPassSwwordD -O /tmp/hosts.txt https://www.terarows.com/pdbs/hosts/5

   or

   curl -u john@example.biz:MyYyPPassSwwordD -o /tmp/hosts.txt https://www.terarows.com/pdbs/hosts/5

   If above command succeeds in execution, file /tmp/hosts.txt contains data. Otherwise, file /tmp/hosts.txt is not created or is empty.

   Assume file /tmp/hosts.txt now contains the following three target databases:

   192.168.1.200 app5 192.168.2.15 udb456 10.0.2.2 udb789

3. Unlock database app5.

   After the database is unlocked, customers' renting Section 16.2.4 application system code "5" is allowed again.

   Developer should run either one of the following commands in order to unlock database app5:

   wget -q --auth-no-challenge --http-user=john@example.biz --http-password=MyYyPPassSwwordD -O - https://www.terarows.com/pdbs/unlock/5

   or

   curl -u john@example.biz:MyYyPPassSwwordD https://www.terarows.com/pdbs/unlock/5

   If the execution succeeds, it returns Y. Otherwise, it returns N.

The above john@example.biz is supposed to be your e-mail address, and MyYyPPassSwwordD is supposed to be your sign in password. Replace them accordingly!

Below is a complete example propagation script.

Prepare file /tmp/patch-db.sql in your local machine. This file contains all required PostgreSQL commands to be executed on all target databases:

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) RETURNS VOID 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(TEXT);

COMMIT;
		

Prepare file /tmp/patch-db.sh in your local machine:

#!/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
		
		

Type command chmod u+x /tmp/patch-db.sh in your local machine to make it executable. Run this command /tmp/patch-db.sh to perform this example "propagating design changes".