Implementing an Active/Standby K5 DBaaS Instance across Availability Zones

This blog will walk you through the implementation of the K5 DBaaS, across two availability zones for improved HA/DR capability. The database is deployed in Active/Standby mode, with automatic asynchronous replication between availability zones taking place. Note: The standby database should not be accessed, and indeed can not be accessed via the DBaaS host name when in standby mode. At the time of writing, K5 provides the option of deploying either a Entperise Postgres v9.6 or Symfoware v12.1 database engine. In this blog I will deploy the former.

In writing this blog, I am assuming that you are already familiar with the K5 API and will be obtaining the necessary API tokens and configuring endpoints as per the simplifying-retrieving-an-authentication-token blog. If this is not the case, then see my getting-started blog. You may also need to manually configure certain endpoint variables used below, if not using the  set-k5env.sh script.

Pre-requisites

Before you begin, please create a network/subnet/router attached to an external network and local subnet in both availability zones. In this blog, I will used the following names/details :

Availability Zone Virtual Resource   Name
uk-1a Router DBaaSRouter1a
uk-1a Network DBaaSNetwork1a
uk-1a Subnet DBaaSSubnet1a
uk-1a CIDR

DNS

192.168.1.0/24

62.60.39.9 / 62.60.39.10

uk-1b Router DBaaSRouter1b
uk-1b Network DBaaSNetwork1b
uk-1b Subnet DBaaSSubnet1b
uk-1b CIDR

DNS

192.168.10.0/24

62.60.42.9 / 62.60.42.10

Both Port 5432
uk-1a Static IP Address 192.168.1.10
uk-1b Static IP Address 192.168.10.10

The IDs of the above created resources will be required by the API commands detailed throughout this blog.

 

Deploying Network Connector to connect both networks across AZs

Once you have created a network in each AZ, it is necessary to connect both networks together by deploying a K5 Network Connector between them as detailed below. This is required so that the load balanced connection to the database, is able to function across both AZs, when operating in Active/Standby mode.

  1. Create an empty Security Group (SG) with a name & description of your choosing, to be used by network connector. e.g. Dummy_SG.  Note: Although SGs are currently not applied at the Network Connector level, using a ‘dummy’ SG avoids the Network Connector being assigned the default Security Group, which may lead to some confusion.
  2. Obtain the ID of this SG either via the Portal or using the below API command:

Successful output will be of the form:

{

“Dummy_SG”
“FOR_DBAAS”
“d6d6ea9a-bf70-447e-962f-605ac7dd1114”

}

3. Create the Network Connector using the below API command and environmental variables :

Variables to declare:

NAME=DBaaS-NWC

Successful output will be of the form:

{
“network_connector”: {
“id”: “545c7084-5e63-4b1e-9a8e-337cc039aa5c”,
“name”: “DBaaS-NWC”,
“tenant_id”: “750198f98e174875b915294855efd8dc”,
“network_connector_endpoints”: [],
“network_connector_pool_id”: “a488254e-1fd6-483f-9192-a9a2885e1fa8”
}
}

4. Create the Connector End Point for AZ1 using the below API command and environmental variables :

NAME=dbaas_nwcep_az1

EP_TYPE=availability_zone

AZ=uk-1a

NETCON_ID=<ID Returned from step 3>

Successful output will be of the form:

{
“network_connector_endpoint”: {
“id”: “69d568f5-dd26-42dd-97dc-c7317f1f1039”,
“name”: “dbaas_nwcep_az1”,
“network_connector_id”: “545c7084-5e63-4b1e-9a8e-337cc039aa5c”,
“tenant_id”: “750198f98e174875b915294855efd8dc”,
“endpoint_type”: “availability_zone”,
“location”: “uk-1a”
}
}

5. Repeat this last step to create the Connector End Point for AZ2

NAME=dbaas_nwcep_az2

EP_TYPE=availability_zone

AZ=uk-1b

NETCON_ID=<ID Returned from step 3>

Successful output will be of the form:

{
“network_connector_endpoint”: {
“id”: “8a11fc04-2638-4e0a-a99f-35b40c1a3e14”,
“name”: “dbaas_nwcep_az2”,
“network_connector_id”: “545c7084-5e63-4b1e-9a8e-337cc039aa5c”,
“tenant_id”: “750198f98e174875b915294855efd8dc”,
“endpoint_type”: “availability_zone”,
“location”: “uk-1b”
}
}

6. Next, create a new unallocated port on AZ1 using the below API command and environmental variables :

PORT_NAME=dbaas_port_az1

NETWORK_ID=<ID of AZ1 network created  as per the pre-reqs section above>

SUBNET_ID=<ID of AZ1 subnet created  as per the pre-reqs section above>

FIXED_IP_ADDRESS=<Static IP address from AZ1 CIDR defined above>

SG_ID=<ID of SG created in Step 2 above>

AZ=uk-1a

Successful output will be of the form:

{
“port”: {
“status”: “DOWN”,
“name”: “dbaas_port_az1”,
“allowed_address_pairs”: [],
“admin_state_up”: true,
“network_id”: “8f4d62d3-e167-4063-9711-c78f0b9057cf”,
“tenant_id”: “750198f98e174875b915294855efd8dc”,
“binding:vnic_type”: “normal”,
“device_owner”: “”,
“mac_address”: “fa:16:3e:80:c0:cc”,
“fixed_ips”: [
{
“subnet_id”: “866e36d3-b2fe-48e4-bf32-8298c9b9ed53”,
“ip_address”: “192.168.1.200”
}
],
“id”: “7d9b120b-f853-4e13-a721-dda3f92427c2”,
“security_groups”: [
“d6d6ea9a-bf70-447e-962f-605ac7dd1114”
],
“device_id”: “”,
“availability_zone”: “uk-1a”
}
}

7. Repeat previous step to created Port for AZ2

PORT_NAME=dbaas_port_az2

NETWORK_ID=<ID of AZ2 network created  as per the pre-reqs section above>

SUBNET_ID=<ID of AZ2 subnet created  as per the pre-reqs section above>

FIXED_IP_ADDRESS=<Static IP address from AZ2 CIDR defined above>

SG_ID=<ID of SG created in Step 2 above>

AZ=uk-1b

Successful output will be of the form:

{
“port”: {
“status”: “DOWN”,
“name”: “dbaas_port_az2”,
“allowed_address_pairs”: [],
“admin_state_up”: true,
“network_id”: “89afaae8-0ff1-422d-8a48-deb80f3aeb39”,
“tenant_id”: “750198f98e174875b915294855efd8dc”,
“binding:vnic_type”: “normal”,
“device_owner”: “”,
“mac_address”: “fa:16:3e:10:06:3a”,
“fixed_ips”: [
{
“subnet_id”: “8f4fd912-ac9e-4e66-aa03-a0e0e722909f”,
“ip_address”: “192.168.10.200”
}
],
“id”: “06cacc97-dbfd-4827-8d17-9b220c3a196a”,
“security_groups”: [
“d6d6ea9a-bf70-447e-962f-605ac7dd1114”
],
“device_id”: “”,
“availability_zone”: “uk-1b”
}
}

8. Attach Port to Connector End Point in AZ1

NETCON_EP_ID=<ID of Network Connector End Point created in step 4>

PORT_ID=<ID of AZ1 Port created in step 6>

Successful output will be of the form:

{
“interface”: {
“port_id”: “7d9b120b-f853-4e13-a721-dda3f92427c2”
}
}

9. Repeat the last command to attach the Port to the Connector End Point in AZ2

NETCON_EP_ID=<ID of Network Connector End Point created in step 5>

PORT_ID=<ID of AZ2 Port created in step 7>

Output of the form:

{
“interface”: {
“port_id”: “06cacc97-dbfd-4827-8d17-9b220c3a196a”
}
}

10. Update Router Routing for AZ1, if using a different IP addresses please update the nexthop and destination address accordingly

ROUTER_ID_AZ1=<ID of AZ1 Router created as per the pre reqs section above>

 

11. Update Router Routing for AZ2, if using a different IP addresses please update the nexthop and destination address accordingly

ROUTER_ID_AZ2=<ID of AZ2 Router created as per the pre reqs section above>

Deploying a Database Client VM

When using the K5 DBaaS functionality, it is necessary to deploy a client Virtual Machine in order to remotely access and manage the deployed “PostgreSQL” database.

For the purposes of this blog, both a Linux (CentOS)/Windows client will be deployed on subnet DBaaSSubnet1a. In a production system, you should also install your standby clients on DBaaSSubnet1b also.

(I’m assuming you know how to do this, if not see the official Fujitsu K5 IaaS support guides  https://k5-doc.jp-east-1.paas.cloud.global.fujitsu.com/doc/en/iaas/document/api-reference/index.html)

The next step is to create two Security groups, one for the client VM and one for the Database service.

1. Create two SG’s called ‘SG_DB_Client’ and ‘SG_DB_Service’

2. Within ‘SG_DB_Client’, delete the default rules and add the following rule:

3. Within ‘SG_DB_Service’, delete the default rules and add the following rules:

4. Next deploy your client VM(s). This  will require a floating IP address and appropriate security group/security group rules to allow SSH/RDP access from the Internet., as well as the SG_DB_Client group created in step 2.

5. How to Install the client on the CentOS VM

sudo -i

yum update -y

yum -y install postgresql

6. How to Install the client on the Windows VM

Download the latest Windows Client installation files e.g. https://www.pgadmin.org/download/pgadmin-4-windows/

Run the setup file and accept the default options to install the client:

  • Welcome screen | ‘Next’
  • License screen| select ‘I accept…’ and ‘Next’
  • Accept the default location and click ‘Next’
  • Click ‘Install’
  • Click ‘Finish’ to complete the install

Deployment of the Database Service across AZs

1. Record the ID of the subnets on which the DBaaS Clients are connected to, either via the Portal or using the below API command and Subnet name variables:

CHK_SUBNET_NAME=”DBaaSSubnet1a”

CHK_SUBNET_NAME=”DBaaSSubnet1b”

2. Record the ID of the Database Service Security Group using the following API command:

CHK_SG_NAME=”SG_DB_Service”

3. Run the following command to create a ‘Subnet Group’ consisting of the two subnets previously created.

SUBNET_GR_NAME=dbaas-subnet-group
SUBNET_ID1=<subnet1a id from step 1 above>
SUBNET_ID2=<subnet1b id from step 1 above>

DESCRIPTION=”dbaas-subnet-group-multi-az”

Output as follows:

{
“subnetgroup”: {
“name”: “dbaas-subnet-group”,
“id”: “UNFIrmpkfVV-4NYwNhvkDocCEwHdDyh-dTEm-f9T6Q6O64QqDIFnY96pFSxZE6r”,
“status”: “Available”,
“subnetIds”: [
{
“subnetId”: “acf89fc8-f146-463b-be4a-179d5fa48304”
},
{
“subnetId”: “9b530e11-bba0-41a7-b314-112900345787”
}
],
“created”: “2018-03-29T10:57:19Z”,
“description”: “dbaas-subnet-group-multi-az”
}
}

Note the id for use with SUBNET_GR variable shortly

4. Run the following command to create the DB parameter group. Here I am creating

PARAM_GROUP_NAME=db-param-group

PARAM_GROUP_FAMILY=enterprisepostgres_v9.6  (For Symfoware, change this to symfoware_v12.1)

DESCRIPTION=”db param group for enterprise postgres”

Output of the form:

{
“parameterGroup”: {
“id”: “uBSYMKxQOKS-1Ro1uZ2hEMj7wHNBoTc-29wP-ymJg6GIdeQuGKskZQkYJdaVEwY”,
“description”: “db-param-group-for-enterprise-postgres”,
“name”: “db-param-group”,
“parameterGroupFamily”: “enterprisepostgres_v9.6”
}
}

Note the id for use with PARAM_ID variable shortly

5. Run the following command to deploy the database instance.  If deploying a Symfoware database, then change the Engine value to ‘ symfoware’.

Note: I’ve not included any specific settings for either a backup or maintenance window so a default random period will be used. For details of specifying your own window, and other configurable options please see the K5 Application Platform Server API guide  :

NAME=my-postgres-dbaas
AZ=uk-1a
ENGINE=enterprisepostgres
FLAVOR_REF=1101
VOL_SIZE=10
VOL_TYPE=M1
SUBNET_GR=<ID from step 3 above>
PORT_NUM=5432
SG_ID=<ID from step 2 above>
MASTER_USER_NAME=root
MASTER_USER_PW=root
CHAR_SET=utf8
DB_NAME=database
DB_USER=user
DB_USER_PW=postgres
PUBLICLYACCESSIBLE=false
MULTI=true
MULTIAZ=true
BACKUPRETENTIONPERIOD=10
PARAM_ID=<id from step 2 above>

Output of the form:

{
“instance”: {
“volume”: {
“size”: 10,
“type”: “M1”
},
“id”: “OIFgBYjRmIW-nmUzEEZVj0I4BX5oJwa-ygSx-2YrEtDLgbhafJVXC4aODWFmYGY”,
“name”: “my-postgres-dbaas”,
“description”: null,
“multiAZ”: true,
“multi”: true,
“port”: 5432,
“preferredBackupWindow”: “07:37-08:07”,
“preferredMaintenanceWindow”: “tue:02:28-tue:02:58”,
“preferredRecoveryTime”: {
“applyImmediately”: true,
“time”: null
},
“securityGroupIds”: [
{
“securityGroupId”: “74bcef95-b359-4417-9c8a-c6bd834f5208”
}
],
“parameterGroupId”: “uBSYMKxQOKS-1Ro1uZ2hEMj7wHNBoTc-29wP-ymJg6GIdeQuGKskZQkYJdaVEwY”,
“backupRetentionPeriod”: 10,
“autoMinorVersionUpgrade”: true,
“engineVersion”: “9.6”,
“autoMaintenance”: true,
“downloadLogExtension”: false,
“availabilityZone”: “uk-1a”,
“subnetGroupId”: “UNFIrmpkfVV-4NYwNhvkDocCEwHdDyh-dTEm-f9T6Q6O64QqDIFnY96pFSxZE6r”,
“publiclyAccessible”: false,
“engine”: “enterprisepostgres”,
“masterUserName”: “root”,
“characterSet”: “UTF8”,
“collate”: “C”,
“created”: “2018-03-29T11:31:46Z”,
“flavor”: {
“id”: “1101”,
“links”: [
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/v1.0/750198f98e174875b915294855efd8dc/flavors/1101&#8221;,
“rel”: “SELF”
},
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/flavors/1101&#8221;,
“rel”: “BOOKMARK”
}
]
},
“links”: [
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/v1.0/750198f98e174875b915294855efd8dc/instances/OIFgBYjRmIW-nmUzEEZVj0I4BX5oJwa-ygSx-2YrEtDLgbhafJVXC4aODWFmYGY&#8221;,
“rel”: “SELF”
},
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/instances/OIFgBYjRmIW-nmUzEEZVj0I4BX5oJwa-ygSx-2YrEtDLgbhafJVXC4aODWFmYGY&#8221;,
“rel”: “BOOKMARK”
}
],
“status”: “Build”,
“updated”: null,
“privateAddress”: null,
“privateIp”: null,
“publicAddress”: null,
“publicIp”: null,
“subPrivateIp”: null,
“subPublicIp”: null,
“pendingModifiedValues”: null,
“readReplicaDBInstanceIdentifiers”: null,
“readReplicaSrcDBInstanceIdentifier”: null,
“secondaryAvailabilityZone”: null,
“replicaStatus”: null,
“engineMinorVersion”: “0”
}
}

Note the  randomly generated ID for use in the next command. If you would prefer to create a database with an ID of your choosing or a more friendly name, then ID and value pair can be included in the above command, see the K5 Application Platform Server API guide  for more details.

6. Run the following command to verify the status of the newly created DBaaS instance:

DB_ID=<ID from Step 5>

Output of the form:

{
“volume”: {
“size”: 10,
“type”: “M1”
},
“id”: “NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3”,
“name”: “example-dbaas”,
“description”: null,
“multiAZ”: true,
“multi”: true,
“port”: 5432,
“preferredBackupWindow”: “20:31-21:01”,
“preferredMaintenanceWindow”: “thu:17:56-thu:18:26”,
“preferredRecoveryTime”: {
“applyImmediately”: true,
“time”: null
},
“securityGroupIds”: [
{
“securityGroupId”: “a82f0caa-512f-49aa-9619-5ce93d2a1f08”
}
],
“parameterGroupId”: “LmwkKafJAsn-N3sXu12Du31WpptYEiv-QANk-oEbb9mPgNkTzRZd0eulBj35v7p”,
“backupRetentionPeriod”: 10,
“autoMinorVersionUpgrade”: true,
“engineVersion”: “12.1”,
“autoMaintenance”: true,
“availabilityZone”: “uk-1a”,
“subnetGroupId”: “rKVRcgQlXNN-OSZ7sBCb1PQdOzdgVnl-fkUq-IuQwCz9K1WZbaB1k1HDS5LwN8H”,
“publiclyAccessible”: false,
“engine”: “symfoware”,
“masterUserName”: “root”,
“characterSet”: “UTF8”,
“collate”: “C”,
“created”: “2017-04-13T14:53:09Z”,
“flavor”: {
“id”: “1101”,
“links”: [
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/v1.0/750198f98e174875b915294855efd8dc/flavors/1101&#8221;,
“rel”: “SELF”
},
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/flavors/1101&#8221;,
“rel”: “BOOKMARK”
}
]
},
“links”: [
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/v1.0/750198f98e174875b915294855efd8dc/instances/NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3&#8221;,
“rel”: “SELF”
},
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/instances/NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3&#8221;,
“rel”: “BOOKMARK”
}
],
“status”: “Build”,
“updated”: null,
“privateAddress”: null,
“privateIp”: “192.168.1.161”,
“publicAddress”: null,
“publicIp”: null,
“subPrivateIp”: “192.168.10.6”,
“subPublicIp”: null,
“pendingModifiedValues”: null,
“readReplicaDBInstanceIdentifiers”: [],
“readReplicaSrcDBInstanceIdentifier”: null,
“secondaryAvailabilityZone”: “uk-1b”,
“replicaStatus”: null
}

Repeat till status is shown as ‘Active’

Then note down the value of the privateAddress attribue which will then be displayed. e.g. NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1-1cf000.database-uk-1.internal.cloud.global.fujitsu.com

and the availability zone e.g. “availabilityZone”: “uk-1a”,.

This is the internal (private) URL for accessing the database and the availability zone containing the Active DBaaS instance.

Connecting to Active DBaaS from the Linux Database Client

1. SSH onto the Linux Database client VM from the active Availability Zone.

2. Configure variable DB_SERVER with value of the privateAddress from above:

DB_SERVER=NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1-1cf000.database-uk-1.internal.cloud.global.fujitsu.com

3. Connect to the DBaaS instance using the following command, where root is the username and database is the database name specified above:

psql -h $DB_SERVER -U root database

When prompted, enter the password as root

‘ctrl’ & ‘z’ to exit database and return to Linux shell

Some Basic commands :

To view help for psql commands, type \?.

To view help for SQL commands, type \h.

Create an Empty Database

From Linux Prompt:

createdb -h $DB_SERVER -U root porsche928

From within psql prompt

CREATE DATABASE porsche911;

Create a Table

From within psql prompt:

CREATE TABLE CARS (ID INT, Registration VARCHAR, PRIMARY KEY(ID));

INSERT INTO CARS VALUES (1, ‘A928 GTS’);

SELECT * FROM CARS;

Database General

To view information about the current database connection, type \conninfo.

To list the database’s tables and their respective owners, type \dt.

To list all of the tables, views, and sequences in the database, type \z.

To exit the psql program, type \q.

Caution if using PG_DUMP

From Linux Prompt:

pg_dump -h $DB_SERVER -U root database

If using an older version of CentOS, you may encounter an error where pg_dump is out dated. To overcome this, perform the following commands:

sudo rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.2/redhat/rhel-7-x86_64/pgdg-centos92-9.2-3.noarch.rpm

sudo yum install postgresql92

find / -name pg_dump -type f 2>/dev/null

/usr/pgsql-9.2/bin/pg_dump -h 192.168.1.98 -U root database

Default Postgre Superuser

K5 DBaaS install does not include the default postgres user, if required, this can be created with the following psql command:

CREATE USER postgres SUPERUSER;

CREATE DATABASE Porsche944 WITH OWNER postgres;

Connecting to Active DBaaS from the Windows Database Client

1. Open pgAdmin4

2. On the Dashboad click ‘Add New Server’

dbaseaa

3.  Enter  a name for your Database e.g. K5Postgressql and click the Connection tab:

dbaasa

4. Enter the DBaaS privateaddress from above as the ‘Host name/address’ and username and password of root and click ‘Save’

dbaasb

5. The client should then connect to the database,  start by expanding the menu on the left hand side

dbaaasd

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s