Installing PostgreSQL as AMS Database


Prev		Next

Table of Contents

Configuring AMS With PostgreSQL Instance
Creating New PostgreSQL Server and Databaseon AWS
Creating New AMS Database on Existing PostgreSQL Server

AMS 1.5.0 and higher support the PostgreSQL database as an alternative to the AMS-supplied embedded H2 database. AMS supports the following PostgreSQL configuration options:

Creating a new PostgreSQL server and database, "locally" or on Amazon AWS
Creating a new AMS database on existing PostgreSQL server
Migrating an existing AMS embedded H2 database to PostgreSQL. When the AMS Server is running using the embedded H2 database, the contents of the H2 database are not automatically migrated to the PostgreSQL database. See Data Migration for more information.

AMS supports PostgreSQL 10.6 or higher.

Configuring AMS With PostgreSQL Instance

Open your AMS.conf file which is the minimum starting point for customizing your AMS configuration. Located in $AMS_HOME/conf.
- If you have another AMS.conf in your classpath or you specify your AMS configuration using the --conf ams-server command option, open that file instead.

Edit the Database section by changing the following configuration properties:

	Note
	The default AMS-provided `AMS.conf` file does not include the Database section; create your own or copy the Database section from the AMS configuration sample: `install-path/ams/samples/resources/AMS-sample.conf` and paste it into your `AMS.conf` file.

Set: databaseType = "POSTGRES"

Comment out: filePath = "..."

Set: hostPort = "End-Point:Port"

Set: databaseName = "Database name"

Set: driverClass = "PostgreSQL JDBC Driver"

Set: userName = "Master User Name"

Set: password = "Master User Password"

TIBCO recommends that you always encrypt passwords instead of leaving them as plain text. See Encrypting AMS Configuration Data for more information.

Database {
  databaseType = "POSTGRES"
     // filePath = "..."
     hostPort = "localhost:5432"
     databaseName = "tibco-ams"
     driverClass = org.postgresql.ds.PGSimpleDataSource
     userName = "sa"
     password = ""
  }

Save the file.

Start the AMS server with the following command:
```
ams-server start
```
At this point the PostgreSQL AMS database is empty. Now:
- The AMS server creates the AMS schema (the log shows the database migration starting with version 1.0).
- Database is seeded with permission, roles, users, service addresses, and source control management data from the AMS configuration.

Creating New PostgreSQL Server and Databaseon AWS

These steps assume familiarity with Amazon AWS and apply to clients starting AMS from a fresh/empty PostgreSQL database.

From your browser, log into Amazon AWS per local practice.
From the AWS Management Console, Select: Services (upper left of screen) > Database > RDS. The Amazon RDS or Amazon RDS Database page appears.
Select Create Database in the Dashboard or Databases section. The Select Engine page appears.

If your AWS account does not have permission to create a database, a Failed to load data... message appears. Contact your AWS account manager for access.
Select: PostgreSQL.

When setting up an AMS test environment, AMS is compatible with with the RDS Free Usage Tier. Select Only enable options eligible for RDS Free Usage Tier when applicable.

Click Next. When RDS Free Usage Tier appears, go to step 6.
Choose the use-case that fits your scenario (this dialog appears when RDS Free Usage Tier is not selected):
- Production
- Dev/Test
Select Database Instance Specifications and Settings. AMS supports PostgreSQL 10.6 or higher.

TIBCO recommends:
- Instance specifications: License, version, CPUs, memory, replication, and storage. The values selected depend on your selection in steps 4 and 5.
  - Note: RDS Free Usage Tier pre-defines the CPUs, memory, replication, and storage for the instance.
  - Note: Artifact content is stored in the database. Having a large number of artifacts or a large amount of data in the content will affect the size of the database.
- Settings: Configure the database ID, master user name, and password.
- For proof-of-concept or other non-production scenarios, choose: RDS Free Usage Tier.
  - db.t2.micro: 1 CPU, 1GB Ram, 20GB GP Disk, No Replication
- For production, the minimum requirements are:
  - db.t2.xlarge: 4 CPUs, 16GB Ram, 100GB GP Disk, Replication (as needed)
Click Next.

Select network, security, encryption, backup and other database options. These setting vary greatly by the needs and environment of your AMS server.

In the Database options section, set the database name and port.

TIBCO recommends:

For proof-of-concept or similar scenarios:

Network and Security: Default VPC with Public accessibility

Database options: Disable IAM DB authentication

Encryption: Disable

Backup: 0 day retention period (turns off backup)

Monitoring: Disable

Performance Insights: Disable

Log exports: None

Maintenance: Disable auto minor version upgrade, No preferences

Deletion protection: None

For production scenarios, consult your application and database engineers to define the environment.

Select: Create database.

AWS starts the database provisioning (this takes a few minutes depending on system performance). Navigate to: Amazon RDS > Databases.
- While the database is provisioning, the status is Creating. The status changes to Available when complete.
Once the database is available, select the link under the DB identifier column for the database. Note that:
- The Database Summary page appears.
- The Connectivity and Security > Endpoint and Port section contains the end-point (host) and port to reach the PostgreSQL database instance.
- An empty database with the specified Database Name exists and is owned by the Master User.
Retain the information gathered from the previous steps as the following is needed to connect to the database via AMS:
- User Name: Master User Name (Settings)
- User Password: Master User Password (Settings)
- Database Name: Database Name (Database options)
- Host: End-Point (Connectivity and security)
- Port: Port (Database options or Connectivity and security)

Creating New AMS Database on Existing PostgreSQL Server

Connect to your existing PostgreSQL server, which must be 10.6 or higher. This requires an admin user with privileges to create users and databases.
Create a user specifically for AMS. This is the "Master User Name" and Password used to access the AMS database.
```
SQL: CREATE USER "master-user-name" WITH LOGIN INHERIT CREATEDB PASSWORD 
  'master-user-password' 
```

Create the AMS database to be owned by the Master User:

SQL: CREATE DATABASE "database-name" WITH OWNER="master-user-name" ENCODING='UTF8' 
  LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TABLESPACE=pg_default CONNECTION 
  LIMIT=-1;

The following information is needed to connect to the database via AMS:
- User Name: Master User Name (CREATE USER)
- User Password: Master User Password (CREATE USER)
- Database Name: Database Name (CREATE DATABASE)
- Host: Database Server Name or IP Address
- Port: Database Server Port