Implementing an Active/Standby K5 DBaaS Instance across Availability Zones

This blog will walk you through the establishment 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.

Pre-requisites

Before you begin, please create a network/subnet/router attached to an external network 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                                 192.168.1.0/24

uk-1b                                Router                              DBaaSRouter1b

uk-1b                                Network                          DBaaSNetwork1b

uk-1b                                Subnet                             DBaaSSubnet1b

uk-1b                                 CIDR                                 192.168.10.0/24

Port                                    5432

(OPTIONAL) Deploying Network Connector to connect both networks across AZs

This section is optional and not required for database replication, but may be required by your implementation, depending on whether you require both networks to be connected across AZs.

1.  Create an empty Security Group (SG) with a name & description of your choosing, to be used by network connector.  Security Groups are currently not applied to Network Connectors, but it avoids the Network Connector being assigned the default Security Group.

2. Obtain the ID via the API :

curl -s $NETWORK/v2.0/security-groups -X GET -H “X-Auth-Token: $OS_AUTH_TOKEN” | jq ‘.security_groups[]| .name, .description, .id’

Output of the form:

{

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

}

3. Create Network Connector

NAME=DBaaS-NWC

curl -X POST $NETWORK/v2.0/network_connectors -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“network_connector”:{ “name”: “‘$NAME'”}}’ | jq .

Output 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. Creation of Connector End Point for AZ1

NAME=dbaas_nwcep_az1

NETCON_ID=

EP_TYPE=availability_zone

AZ=uk-1a

curl -X POST $NETWORK/v2.0/network_connector_endpoints -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“network_connector_endpoint”:{ “name”: “‘$NAME'”, “network_connector_id”: “‘$NETCON_ID'”, “endpoint_type”: “‘$EP_TYPE'”, “location”: “‘$AZ'”}}’ | jq .

Output 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. Creation of Connector End Point for AZ2

NAME=dbaas_nwcep_az2

NETCON_ID=

EP_TYPE=availability_zone

AZ=uk-1b

curl -X POST $NETWORK/v2.0/network_connector_endpoints -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“network_connector_endpoint”:{ “name”: “‘$NAME'”, “network_connector_id”: “‘$NETCON_ID'”, “endpoint_type”: “‘$EP_TYPE'”, “location”: “‘$AZ'”}}’ | jq .

Output 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. Creation of Port for AZ1

PORT_NAME=dbaas_port_az1

NETWORK_ID=

SUBNET_ID=

FIXED_IP_ADDRESS=

SG_ID=

AZ=uk-1a

curl $NETWORK/v2.0/ports -X POST -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type: application/json” -d ‘{“port”:{“network_id”: “‘$NETWORK_ID'”, “name”: “‘$PORT_NAME'”, “availability_zone”: “‘$AZ'”, “fixed_ips”: [{“subnet_id”: “‘$SUBNET_ID'”, “ip_address”: “‘$FIXED_IP_ADDRESS'”}], “security_groups”: [“‘$SG_ID'”] }}’ | jq .

Output 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. Creation of Port for AZ2

PORT_NAME=dbaas_port_az2

NETWORK_ID=

SUBNET_ID=

FIXED_IP_ADDRESS=

SG_ID=

AZ=uk-1b

curl $NETWORK/v2.0/ports -X POST -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type: application/json” -d ‘{“port”:{“network_id”: “‘$NETWORK_ID'”, “name”: “‘$PORT_NAME'”, “availability_zone”: “‘$AZ'”, “fixed_ips”: [{“subnet_id”: “‘$SUBNET_ID'”, “ip_address”: “‘$FIXED_IP_ADDRESS'”}], “security_groups”: [“‘$SG_ID'”] }}’ | jq .

Output 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=

PORT_ID=

curl -s -X PUT $NETWORK/v2.0/network_connector_endpoints/$NETCON_EP_ID/connect -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“interface”: {“port_id”: “‘$PORT_ID'”}}’ | jq .

Output of the form

$ curl -s -X PUT $NETWORK/v2.0/network_connector_endpoints/$NETCON_EP_ID/connect -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“interface”: {“port_id”: “‘$PORT_ID'”}}’ | jq .
{
“interface”: {
“port_id”: “7d9b120b-f853-4e13-a721-dda3f92427c2”
}
}

9. Attach Port to Connector End Point in AZ2

NETCON_EP_ID=

PORT_ID=

curl -s -X PUT $NETWORK/v2.0/network_connector_endpoints/$NETCON_EP_ID/connect -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“interface”: {“port_id”: “‘$PORT_ID'”}}’ | jq .

Output of the form:

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

10. Update Subnet Routing for AZ1

SUBNET_ID_AZ1=866e36d3-b2fe-48e4-bf32-8298c9b9ed53

HOST_ROUTES_AZ1={\”nexthop\”:\”192.168.1.200\”,\”destination\”:\”192.168.10.0/24\”},{\”nexthop\”:\”192.168.1.1\”,\”destination\”:\”0.0.0.0/0\”}

curl -i $NETWORK/v2.0/subnets/$SUBNET_ID_AZ1 -X PUT -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type: application/json” -d ‘{“subnet”: { “host_routes”: [‘$HOST_ROUTES_AZ1′] }}’

Output of the form:

{“subnet”:{“name”:”dmzsubnet”,”enable_dhcp”:true,”network_id”:”8f4d62d3-e167-4063-9711-c78f0b9057cf”,”tenant_id”:”750198f98e174875b915294855efd8dc”,”dns_nameservers”:[“62.60.39.10″,”62.60.39.9″],”allocation_pools”:[{“start”:”192.168.1.2″,”end”:”192.168.1.254″}],”host_routes”:[],”ip_version”:4,”gateway_ip”:”192.168.1.1″,”cidr”:”192.168.1.0/24″,”id”:”866e36d3-b2fe-48e4-bf32-8298c9b9ed53″,”availability_zone”:”uk-1a”}}

************

curl -s -X PUT $NETWORK/v2.0/routers/2569f14a-c51c-4121-b5be-774db6dd4df9 -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“router”: {“routes”: [{“nexthop”:”192.168.1.200″,”destination”:”192.168.10.0/24″}]}}’ | jq .

11. Update Subnet Routing for AZ2

SUBNET_ID_AZ2=8f4fd912-ac9e-4e66-aa03-a0e0e722909f

HOST_ROUTES_AZ2={\”nexthop\”:\”192.168.10.200\”,\”destination\”:\”192.168.1.0/24\”},{\”nexthop\”:\”192.168.10.1\”,\”destination\”:\”0.0.0.0/0\”}

curl -i $NETWORK/v2.0/subnets/$SUBNET_ID_AZ2 -X PUT -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type: application/json” -d ‘{“subnet”: { “host_routes”: [‘$HOST_ROUTES_AZ2′] }}’

Output of the form:

{“subnet”:{“name”:”dmzsubnet1b”,”enable_dhcp”:true,”network_id”:”89afaae8-0ff1-422d-8a48-deb80f3aeb39″,”tenant_id”:”750198f98e174875b915294855efd8dc”,”dns_nameservers”:[“62.60.42.10″,”62.60.42.9″],”allocation_pools”:[{“start”:”192.168.10.2″,”end”:”192.168.10.254″}],”host_routes”:[{“nexthop”:”192.168.10.1″,”destination”:”0.0.0.0/0″},{“nexthop”:”192.168.10.200″,”destination”:”192.168.1.0/24″}],”ip_version”:4,”gateway_ip”:”192.168.10.1″,”cidr”:”192.168.10.0/24″,”id”:”8f4fd912-ac9e-4e66-aa03-a0e0e722909f”,”availability_zone”:”uk-1b”}}

****
curl -s -X PUT $NETWORK/v2.0/routers/49358a05-b4b2-447a-b8f8-098547006c26 -H “X-Auth-Token: $OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“router”: {“routes”: [{“nexthop”:”192.168.10.200″,”destination”:”192.168.1.0/24″}]}}’ | jq .

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/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 official Fujitsu K5 IaaS support guides http://www.fujitsu.com/uk/solutions/cloud/k5/guides/)

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:

DBaaS1

3. Within ‘SG_DB_Service’, delete the default rules and add the following rules:DBaaS24. Next deploy both the client VMs.

This requires a floating IP address and appropriate security group/security group rules to allow SSH/RDP access from the Internet.

5. How to Install the client on the CentOS VM

sudo su

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.postgresql.org/ftp/pgadmin/pgadmin4/v1.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 subnet on which the DBaaS Clients are connected to, either via the Portal or using the below API command:

CHK_SUBNET_NAME=”DBaaSSubnet1a”

curl -s $NETWORK/v2.0/subnets -X GET -H “X-Auth-Token: $OS_AUTH_TOKEN” | jq ‘.subnets[]’ | jq ‘select(.name==”‘$CHK_SUBNET_NAME'”)’

CHK_SUBNET_NAME=”DBaaSSubnet1b”

curl -s $NETWORK/v2.0/subnets -X GET -H “X-Auth-Token: $OS_AUTH_TOKEN” | jq ‘.subnets[]’ | jq ‘select(.name==”‘$CHK_SUBNET_NAME'”)’

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

CHK_SG_NAME=”SG_DB_Service”

curl -s $NETWORK/v2.0/security-groups -X GET -H “X-Auth-Token: $OS_AUTH_TOKEN” | jq ‘.security_groups[]’ | jq ‘select(.name==”‘$CHK_SG_NAME'”)’

 

3. Run the following command to setup the database service

SUBNET_GR_NAME=example-subnet-groupmultiaz
SUBNET_ID1=866e36d3-b2fe-48e4-bf32-8298c9b9ed53
SUBNET_ID2=8f4fd912-ac9e-4e66-aa03-a0e0e722909f

DESCRIPTION=”example-subnet-groupmultiaz”

curl -X POST -i $DB/v1.0/$PROJECT_ID/subnetgroups -H “X-Auth-Token:$OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“subnetgroup”:{“name”:”‘$SUBNET_GR_NAME'”, “subnetIds”: [{“subnetId”: “‘$SUBNET_ID1′”},{“subnetId”: “‘$SUBNET_ID2′”}], “description”: “‘$DESCRIPTION'” }}}’

Output as follows:

{“subnetgroup”:{“name”:”example-subnet-groupmultiaz”,”id”:”rKVRcgQlXNN-OSZ7sBCb1PQdOzdgVnl-fkUq-IuQwCz9K1WZbaB1k1HDS5LwN8H”,”status”:”Available”,”subnetIds”:[{“subnetId”:”866e36d3-b2fe-48e4-bf32-8298c9b9ed53″},{“subnetId”:”8f4fd912-ac9e-4e66-aa03-a0e0e722909f”}],”created”:”2017-04-13T14:50:06Z”,”description”:”example-subnet-groupmultiaz”}}

Note the id for use with SUBNET_GR variable shortly

2. Run the following command to create the DB parameter group

PARAM_GROUP_NAME=example-param-group
DESCRIPTION=example-param-group

curl -X POST -i $DB/v1.0/$PROJECT_ID/parametergroups -H “X-Auth-Token:$OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“parametergroup”:{“parameterGroupFamily”: “symfoware_v12.1”, “name”:”‘$PARAM_GROUP_NAME'”, “description”: “‘$DESCRIPTION'”}}’

Output of the form:

{“parameterGroup”:{“id”:”LmwkKafJAsn-N3sXu12Du31WpptYEiv-QANk-oEbb9mPgNkTzRZd0eulBj35v7p”,”description”:”example-param-group”,”name”:”example-param-group”,”parameterGroupFamily”:”symfoware_v12.1″}}

Note the id for use with PARAM_ID variable shortly

3. Run the following command to deploy the database server

NAME=example-dbaas
AZ=uk-1a
FLAVOR_REF=1101
VOL_SIZE=10
VOL_TYPE=M1
SUBNET_GR=rKVRcgQlXNN-OSZ7sBCb1PQdOzdgVnl-fkUq-IuQwCz9K1WZbaB1k1HDS5LwN8H
PORT_NUM=5432
SG_ID=a82f0caa-512f-49aa-9619-5ce93d2a1f08
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=LmwkKafJAsn-N3sXu12Du31WpptYEiv-QANk-oEbb9mPgNkTzRZd0eulBj35v7p

curl -X POST -i $DB/v1.0/$PROJECT_ID/instances -H “X-Auth-Token:$OS_AUTH_TOKEN” -H “Content-Type:application/json” -d ‘{“instance”:{“flavorRef”:”‘$FLAVOR_REF'”,”volume”:{“size”:”‘$VOL_SIZE'”,”type”:”‘$VOL_TYPE'”},”name”:”‘$NAME'”,”availabilityZone”:”‘$AZ'”,”multiAZ”:”‘$MULTIAZ'”,”multi”:”‘$MULTI'”,”subnetGroupId”:”‘$SUBNET_GR'”,”parameterGroupId”: “‘$PARAM_ID'”,”port”:”‘$PORT_NUM'”,”publiclyAccessible”:”‘$PUBLICLYACCESSIBLE'”,”backupRetentionPeriod”: “‘$BACKUPRETENTIONPERIOD'”,”securityGroupIds”:[{“securityGroupId”:”‘$SG_ID'”}],”masterUserName”:”‘$MASTER_USER_NAME'”,”masterUserPassword”:”‘$MASTER_USER_PW'”,”databases”:[{“name”:”‘$DB_NAME'”}],”users”:[{“databases”:[{“name”:”‘$DB_NAME'”}],”name”:”‘$DB_USER'”,”password”:”‘$DB_USER_PW'”}]}}’

Output of the form:

{“instance”:{“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″,”rel”:”SELF”},{“href”:”http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/flavors/1101″,”rel”:”BOOKMARK”}]},”links”:[{“href”:”http://database.uk-1.internal.cloud.global.fujitsu.com/v1.0/750198f98e174875b915294855efd8dc/instances/NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3″,”rel”:”SELF”},{“href”:”http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/instances/NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3″,”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}}

Note the ID for use in the next command

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

DB_ID=NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3

curl -X GET -s $DB/v1.0/$PROJECT_ID/instances/$DB_ID -H “X-Auth-Token:$OS_AUTH_TOKEN” -H “Content-Type:application/json” | jq .instance

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”,
“rel”: “SELF”
},
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/flavors/1101”,
“rel”: “BOOKMARK”
}
]
},
“links”: [
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/v1.0/750198f98e174875b915294855efd8dc/instances/NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3”,
“rel”: “SELF”
},
{
“href”: “http://database.uk-1.internal.cloud.global.fujitsu.com/750198f98e174875b915294855efd8dc/instances/NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1mFDvsWsedG4jB3”,
“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. e.g. NvPFMyHdelF-hdzbAI6f0xeCWxHoEin-mUWl-oiGABiKRjpE1-1cf000.database-uk-1.internal.cloud.global.fujitsu.com 

and AvailabilityZone 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’ Configure variable DB_SERVER with value of the privateAddress from above:

dbaseaa

3. Enter a suitable Name 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’

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

dbaaasd

 

 

Advertisements