Create the PostgreSQL Aurora for OFBiz

PostgreSQL database (OFBiz)

  1. Open AWS Console

  2. In the AWS Console, Navigate to Services > Database, RDS

  3. In the RDS screen, click on Databases, then click on Create Database. create pgsqlrds

  4. In Choose a database creation method select Standard Create. And then, in Engine options use the settings:

    • Engine type: Amazon Aurora
    • Edition: Amazon Aurora with PostgreSQL compatibility
    • Version: Keep the default selected select pgsqlrds 1
  5. In Templates select Dev/Test

    We are using Dev/Test selection on this exercise because there are no active users running the application. For a production environment prefer the Production template.

  6. In Settings use the following settings:

    • DB cluster identifier: MID-OFBiz
    • Master username: ofbiz
    • Master password: For EventEngine events use the same Linux password as defined in your team dashboard. If you are using your own AWS account use the same password defined during the CloudFormation launch

    select mysqlrds 2

    select mysqlrds 3

  7. In DB Instance size select Burstable classes. Change the size to db.t3.medium and, in Availability & durability select Don’t create an Aurora Replica

    select mysqlrds 4

  8. In Connectivity change the following settings:

    • Virtual Private Cloud (VPC) to Target
    • Expand Additional connectivity configuration
      • Public accessible select No
      • VPC security group, select Choose existing. Keep default and add the one that contains TargetSecurityGroup in the name.
      • Availability zone select the one that ends with suffix a

    select mysqlrds 5

    select mysqlrds 6

  9. In Database authentication select Password authentication. And then, expand Additional configuration

    • Initial database name leave blank

    select mysqlrds 7

  10. Still in Additional configuration

    • Encryption uncheck Enable Encryption
    • Performance Insights uncheck Enable Performance Insights
    • Moniroting uncheck Enable Enhanced monitoring

    select mysqlrds 8

  11. Click on Create database

    Create PostgreSQL RDS

  12. Wait until the Available status for the Aurora ofbiz database: Wait until available status

  13. Click on mid-ofbiz-instance-1 Writer database and write down the endpoint. Keep it, we will use it during this lab. Write down Wordpress DB endpoint

  14. From your BASTION host, open putty: Click on putty

  15. Connect to the server ofbiz-db. For EventEngine event, the username and password are in your EventEngine dashboard. If you are running this lab in your own AWS account, the username is user and the password will be the same that you defined during the CloudFormation launch. Connect to ofbiz-db

  16. Connect to the Aurora Postgres server. Replace <INSERT AURORA DNS VALUE FROM STEP 12> before running the command.

    sudo psql postgres --username=ofbiz -h  <INSERT AURORA DNS VALUE FROM STEP 12>
    
  17. Now, let’s create the databases ofbiz, ofbizolap and ofbiztenant. We will also grant privileges to the user ofbiz to all databases. Run the following command (using the same password that you specified on step 6 to connect to your Aurora Postgres database. Once connected run the following commands:

    CREATE DATABASE ofbiz;
    GRANT ALL PRIVILEGES ON DATABASE ofbiz TO ofbiz;
    CREATE DATABASE ofbizolap;
    GRANT ALL PRIVILEGES ON DATABASE ofbizolap TO ofbiz;
    CREATE DATABASE ofbiztenant;
    GRANT ALL PRIVILEGES ON DATABASE ofbiztenant TO ofbiz;
    \q
    

Create ofbiz user and additional databases

HINT: Use a text editor to arrange the commands before past it into the putty