DataBase Server Creation - woveon/wovtools GitHub Wiki

< Home

Woveon Logo

;TLDR - a database server is for WovDatabases that have database instances for each stage.

Database Server

  • a server that stores persistent data and has multiple databases. You could have servers for each stage or one for all; it's up to you in your configuration. (ex. an AWS RDS Postgres Database Server) WovDatabase
  • The name given to a persistent store of data. This has different instances for each stage, which are indepdentent of the database server (i.e. I like to have one database server for all stages until production picks up and I create a production server and then do all dev and developer database instances on a separate database server). Database Instance
  • a database entry in a database server, for a named database stage (NOTE: AWS calls database servers 'instances' which should not be confused with this.)

Example. Consider a small website named Foo, with a WovDatabase foodb. In AWS, Foo has an AWS RDS Instance named foo-aws-va-live-db-foodb, which is teh Database Server in WovTools naming. On this, it runs all Database Instances, so created with the Postgres command 'CREATE DATABASE', there are databases named foocw, foodev and fooprod, with foocw belonging to the developer Chad Wingrave (initials). When Chad needs to connect, he (already tunneled into the bastion server) types wov-db-connect foodb and he gets a psql prompt.

Important: - WovTools supports an AWS RDS Postgres database only so far (some Mongodb support) - DBNAME - The name of the database server running locally or in the cloud. - DBDATA - The data found in wovtools/data.

Usage wov-db-connect

Use the wov-db-connect command to:

  • Connect: wov-db-connect DBNAME
  • Initialize a Database: wov-db-connect --db-init
  • List the WovDatabases: wov-db-connect -ldb
  • List the DataSets: wov-db-connect -ldbs
  • Info: wov-db-connect alywandb --info
  • Help: wov-db-connect -h

For more manual control:

  • Load an SQL file: wov-db-connect DBNAME -f FILE
  • Run a command: wov-db-connect DBNAME -c COMMAND
  • Get schema: wov-db-connect DBNAMEDBSERVER --schema
  • Get schema hash: wov-db-connect DBNAMEDBSERVER --schema-hash
  • Diff the schema against version X: wov-db-connect DBNAMEDBSERVER --schema-diff X

Troubleshooting

Start SSH Tunnel through Bastion (as per .ssh/config): wov-bastion-connect -c

  • TODO: explain how to create basion entry!!!

To see your database configuration: wov-env --envs | grep WOV_(DBNAME)

Test db connection: pg_isready -h localhost -p DBLOCALPORT

Connecting manually: psql -h localhost -p DBLOCALPORT -U DBUSER -d DBNAME -W

Database Server

Database servers are run in the default VPC, in private subnets, with a peering connection to the custer's subnets. This ensures the database itself persists between cluster creation/destruction and protects it in a private subnet but allows access through your bastion server.

Network Setup and Config

The cluster creation script wov-cluster-configdbnet sets up a common database area in your AWS account for databases. These should be common across clusters. This script creates:

  • two subnets in the default VPC, in two separate zones
  • one database subnet group for these subnets
  • a peering connection from these subnets, into your cluster's VPC
  • instructions on creating an ssh tunnel to the database through your bastion server

Creation

The cluster database creation script wov-cluster-createdb X creates the database in RDS, though for now it only spews the configuration information needed for you to create it in in the AWS RDS GUI. This places the database into the default VPC, but peers its connections over to your cluster's VPC.

To create a database named DBSERVER in your current cluster:

wov-cluster-configdbnet             # and wait
wov-cluster-createdb DBNAME     # and follow the prompts... it uses your configuration to set up the server
wov-db-connect DBNAME --db-init

WovDatabase

WovDatabases are named for easy reference and while the server may or may not change, the database instance should change with the stage. WovDatabases are named so loading [DataSets] and configuring code will be conscistent as your code is pushed towards production.

JSON Configuration

These are the options in your SECRETS JSON file.

# The following are used for database operation
# NOTE: these are turned into env variables in most cases:
#   foodb.endpoint       => WOV_foodb_endpoint
#   foodb.instance.class => WOV_foodb_instance_class
{
"foodb" : {
  "database" : 'DB INSTANCE NAME', # the name of the database inside the database server to connect to
  "endpoint  : 'localhost',        # usually localhost since your are ssh tunneling to it
  "instanceAvailabilityZone"    : "c",
  "instanceAvailabilityZoneAlt" : "d",
  "instance" : {                   # used for building
      "class"   : "db.t2.small",
      "storage" : 20
      },
    "password" : "openssl rand -hex 20", # create a password
    "port"     : "65432",                # port ssh tunneling opens
    "subhost"  : "localhost",            # AWS resource name of the host
    "type"     : "postgres",             # database server type
    "username" : "postgres",             # name to log into db with
    "version"  : "9.6.11-R1"             # version of DB server
}

Database Instance

Create an instance with wov-db-connect --db-init. This will ensure that the database server is initialized to support database instances (i.e. it CREATE DATABASE wovtools, to log changes) and it CREATE DATABASE dbinstance.