Docker with Thingworx + PostgreSQL fails to start

[A_Macierzynski]

Hi!

I am struggling a lot with OOTB Docker images which PTC provides for Thingworx 8.4 & PostgreSQL setup.

What I am trying to do is to run Docker containers on my Ubuntu 18.04 Virtual Machine.

Everything seems to be installed correctly, but it fails to start with this error log:

platform_1 | 2019-08-14 07:29:28.639+0000 [L: INFO] [O: c.t.p.p.PostgresDatasource] [I: ] [U: SuperUser] [S: ] [T: main] Reading configuration from configTableCol
platform_1 | 2019-08-14 07:29:28.997+0000 [L: INFO] [O: c.t.s.ThingWorxServer] [I: ] [U: SuperUser] [S: ] [T: main] Managers...INITIALIZED
platform_1 | 2019-08-14 07:29:29.000+0000 [L: INFO] [O: c.t.s.ThingWorxServer] [I: ] [U: SuperUser] [S: ] [T: main] Database initialization started...
postgresql_1 | ERROR: relation "system_version" does not exist at character 128
postgresql_1 | STATEMENT: SELECT server_name, server_code, model_schema_version, data_schema_version, major_version, minor_version, revision, build FROM system_version order by pid desc limit 1
postgresql_1 | ERROR: current transaction is aborted, commands ignored until end of transaction block
postgresql_1 | STATEMENT:
platform_1 | 2019-08-14 07:29:29.276+0000 [L: ERROR] [O: c.t.p.p.PostgresModelExceptionTranslator] [I: ] [U: SuperUser] [S: ] [T: main] [message: ERROR: relation "system_version" does not exist
platform_1 | Position: 128]

docker-compose-postgres.yml:

Spoiler

version: '2.2'

services:
  postgresql:
    image: thingworx/postgres-db:latest
    ports:
      - "5432"
    healthcheck:
      test: pg_isready -U postgres
      interval: 15s
    environment:
      - "TWX_DATABASE_USERNAME=twadmin"
      - "TWX_DATABASE_SCHEMA=thingworx"
      - "TWX_DATABASE_PASSWORD=password"
    #volumes:
      #- "/home/devadmin/twx/ThingworxPostgresqlStorage:/home/ThingworxPostgresqlStorage"
      #- "/home/devadmin/twx/postgres-data:/var/lib/postgresql/data"

  platform:
    image: thingworx/platform-postgres:latest
    healthcheck:
      test: curl -s -w '%{http_code}' -U 'bad:creds' localhost:8080/Thingworx/Subsystems/PlatformSubsystem | grep -w 401
      interval: 15s
    depends_on:
      postgresql:
        condition: service_healthy
    ports:
      - "8080:8080"
      - "8443:8443"
    environment:
      - "INITIAL_HEAP=2"
      - "MAX_HEAP=4"
      - "DATABASE_HOST=postgresql"
      - "DATABASE_PORT=5432"
      # NOTE: Set TWX_DATABASE_USERNAME, TWX_DATABASE_PASSWORD TWX_DATABASE_SCHEMA for
      # the thingworx database setup that will either be created by the docker image,
      # or connected to if it already exists.
      - "TWX_DATABASE_USERNAME=twadmin"
      - "TWX_DATABASE_SCHEMA=thingworx"
      - "TWX_DATABASE_PASSWORD=password"
      # NOTE: The following must be set for the Platform to start. This will be
      # the initial Administrator password.
      - "THINGWORX_INITIAL_ADMIN_PASSWORD=Administrator123!@#"
    #volumes:
      #- "/home/devadmin/twx/ThingworxPlatform:/ThingworxPlatform"
      #- "/home/devadmin/twx/ThingworxStorage:/ThingworxStorage"
      #- "/home/devadmin/twx/ThingworxBackupStorage:/ThingworxBackupStorage"
      #- "/home/devadmin/twx/tomcat-logs:/opt/apache-tomcat/logs"

Build.env file:

Spoiler

# Required
JAVA_VERSION=8u201
TOMCAT_VERSION=9.0.17

PLATFORM_VERSION=8.4.4

TEMPLATE_PROCESSOR_VERSION=12.1.0.12

PLATFORM_SETTINGS_FILE=platform-settings.json

# If you wish to build the test DB images for dev/testing for PostgreSQL and
# MSSQL set BUILD_TEST_DBS to true. Otherwise, set to false to disable.
BUILD_TEST_DBS=true

# H2 Builds Only
PLATFORM_H2_VERSION=${PLATFORM_VERSION}
PLATFORM_H2_ARCHIVE=Thingworx-Platform-H2-${PLATFORM_H2_VERSION}.zip

# PostgreSQL Builds Only
PLATFORM_POSTGRES_VERSION=${PLATFORM_VERSION}
PLATFORM_POSTGRES_ARCHIVE=Thingworx-Platform-Postgres-${PLATFORM_POSTGRES_VERSION}.zip

# MSSQL Builds Only - Required Credentials for Build Process
# The Following must be set for MSSQL builds due to how the test DB is created,
# if you have enabled building the test DBs with BUILD_TEST_DBS. The settings
# chosen here must match the variables passed into the ThingWorx Platform on startup
MSSQL_DB_TWX_DATABASE_PASSWORD=
MSSQL_DB_TWX_DATABASE_USERNAME=
MSSQL_DB_TWX_DATABASE_SCHEMA=
MSSQL_DB_SA_PASSWORD=

# MSSQL Builds Only
PLATFORM_MSSQL_VERSION=${PLATFORM_VERSION}
PLATFORM_MSSQL_ARCHIVE=Thingworx-Platform-Mssql-${PLATFORM_MSSQL_VERSION}.zip
SQLDRIVER_VERSION=6.0.8112.200

# AzureSQL Builds Only
PLATFORM_AZURESQL_VERSION=${PLATFORM_VERSION}
PLATFORM_AZURESQL_ARCHIVE=Thingworx-Platform-Azuresql-${PLATFORM_AZURESQL_VERSION}.zip
AZURESQL_SQLDRIVER_VERSION=6.0.8112.200

# Optional to configure. If the Files do not match the pattern, they may need
# to be manually modified.
TOMCAT_ARCHIVE=tomcat-${TOMCAT_VERSION}.tar.gz
JAVA_ARCHIVE=jdk-${JAVA_VERSION}-linux-x64.tar.gz
SQLDRIVER_ARCHIVE=sqljdbc_${SQLDRIVER_VERSION}_enu.tar.gz
AZURESQL_SQLDRIVER_ARCHIVE=sqljdbc_${AZURESQL_SQLDRIVER_VERSION}_enu.tar.gz
TEMPLATE_PROCESSOR_ARCHIVE=template-processor-${TEMPLATE_PROCESSOR_VERSION}-application.tar.gz

 platform-settings.json file:

Spoiler

{
	"PlatformSettingsConfig": {
		"AdministratorUserSettings": {
			"InitialPassword": "trUf6yuz2?_Gub"
		},
		"ExtensionPackageImportPolicy":
		{
			"importEnabled": true,
			"allowJarResources": true,
			"allowJavascriptResources": true,
			"allowCSSResources": true,
			"allowJSONResources": true,
			"allowWebAppResources": true,
			"allowEntities": true,
			"allowExtensibleEntities": true
		},
		"ContentTypeSettings": {
			"supportedMediaEntityContentTypes" : ["image/svg+xml", "image/png", "image/gif", "image/bmp", "image/jpeg", "application/pdf", "image/vnd.microsoft.icon"]
		}
	},

	"PersistenceProviderPackageConfigs": {
		"PostgresPersistenceProviderPackage": {
			"ConnectionInformation": {
				"acquireIncrement": 5,
				"acquireRetryAttempts": 3,
				"acquireRetryDelay": 10000,
				"checkoutTimeout": 1000000,
				"driverClass": "org.postgresql.Driver",
				"fetchSize": 5000,
				"idleConnectionTestPeriod": 60,
				"initialPoolSize": 5,
				"jdbcUrl": "jdbc:postgresql://localhost:5432/thingworx",
				"maxConnectionAge": 0,
				"maxIdleTime": 0,
				"maxIdleTimeExcessConnections": 300,
				"maxPoolSize": 100,
				"maxStatements": 100,
				"minPoolSize": 5,
				"numHelperThreads": 8,
				"password": "password",
				"testConnectionOnCheckout": false,
				"unreturnedConnectionTimeout": 0,
				"username": "twadmin"
			},
			
			"StreamProcessorSettings": {
				"maximumBlockSize": 2500,
				"maximumQueueSize": 250000,
				"maximumWaitTime": 10000,
				"numberOfProcessingThreads": 5,
				"scanRate": 5,
				"sizeThreshold": 1000
			},

			"DataTableProcessorSettings": {
				"maximumBlockSize": 2500,
				"maximumQueueSize": 250000,
				"maximumWaitTime": 10000,
				"numberOfProcessingThreads": 1,
				"scanRate": 5,
				"sizeThreshold": 1
			},

			"ValueStreamProcessorSettings": {
				"maximumBlockSize": 2500,
				"maximumWaitTime": 10000,
				"maximumQueueSize": 500000,
				"numberOfProcessingThreads": 5,
				"scanRate": 5,
				"sizeThreshold": 1000
			},

			"PersistentPropertyProcessorSettings": {
				"maximumBlockSize": 2500,
				"maximumWaitTime": 1000,
				"maximumQueueSize": 100000,
				"numberOfProcessingThreads": 20,
				"scanRate": 25,
				"sizeThreshold": 1000
			}
		}
	}
}

[Constantine]

Hello,

Since it cannot find the relation, it looks like the database schema didn't initialize correctly. I suggest looking earlier in the docker-compose log, up to the point where it executes /docker-entrypoint-initdb.d/db-setup.sh

Regards,
Constantine

[A_Macierzynski · Accepted solution]

Actually I found a solution.

According to the doc those variables:

      - "TWX_DATABASE_USERNAME=twadmin"
      - "TWX_DATABASE_SCHEMA=thingworx

NEED TO BE THE SAME. I changed it for thingworx everywhere and it is working like a charm.

I have no idea why there are 2 variables exposed, but only one value can be assign to both in one time.

Nevertheless that resolved my issue.