Traffic Ops database is not locked during Postinstall

[zrhoffman]

This Bug Report affects these Traffic Control components:

• Traffic Ops

Current behavior:

We have no mechanism to verify that multiple Traffic Ops instances are not trying to run the TO Postinstall at the same time.

Previously filed in #4984; sort of fixed in #5138 by making create_tables.sql alone idempotent; idempotency is lost as soon as squash_migrations.sh from #6096 is used.

Expected behavior:

When the Postinstall script starts, it should check if a postinstall lock exists in the DB, and if so, wait for it to be cleared. If there is no lock, it should set one and clear it once Postinstall completes (with or without an error).

The cdn_lock table cannot be used because the username and cdn_name are non-nullable foreign keys.

Also, we should be using --single-transaction for all of our psql commands in the Postinstall script and in db/admin.

Steps to reproduce:

Try to run the Traffic Ops Postinstall on the same database from two separate TO instances at the same time.

[zrhoffman]

To be clear, the Postinstall script is designed to be able to be run more than once:

trafficcontrol/traffic_ops/install/bin/_postinstall.py

Lines 1113 to 1123 in 9516de3

	tables_found_query = '''
	SELECT EXISTS(
	SELECT 1
	FROM pg_tables
	WHERE schemaname = 'public'
	AND tablename = 'tm_user'
	);'''
	if exec_psql(conn_str, tables_found_query) == "t":
	logging.info("Found existing tables skipping table creation")
	else:
	invoke_db_admin_pl("load_schema", root, False)

trafficcontrol/traffic_ops/install/bin/_postinstall.pl

Lines 600 to 608 in 9516de3

	my $tables_found;
	while ( my $row = $stmt->fetch() ) {
	$tables_found = $row->[0];
	}
	if ($tables_found) {
	InstallUtils::logger( "Found existing tables skipping table creation", "info" );
	} else {
	invoke_db_admin_pl("load_schema");
	}

but if the Postinstall script is being run simultaneously, for the first time, on 2 separate Traffic Ops hosts against the same TO DB, the check may still fail to find existing tables.