Home > ServerTemplates > v13.5 LTS > ST > Database Manager for PostgreSQL 9.1 (v13.5 LTS)

MindTouch
Copyright (c) 2006-2014 MindTouch Inc.
http://mindtouch.com

This file and accompanying files are licensed under the MindTouch Master Subscription Agreement (MSA).

At any time, you shall not, directly or indirectly: (i) sublicense, resell, rent, lease, distribute, market, commercialize or otherwise transfer rights or usage to: (a) the Software, (b) any modified version or derivative work of the Software created by you or for you, or (c) MindTouch Open Source (which includes all non-supported versions of MindTouch-developed software), for any purpose including timesharing or service bureau purposes; (ii) remove or alter any copyright, trademark or proprietary notice in the Software; (iii) transfer, use or export the Software in violation of any applicable laws or regulations of any government or governmental agency; (iv) use or run on any of your hardware, or have deployed for use, any production version of MindTouch Open Source; (v) use any of the Support Services, Error corrections, Updates or Upgrades, for the MindTouch Open Source software or for any Server for which Support Services are not then purchased as provided hereunder; or (vi) reverse engineer, decompile or modify any encrypted or encoded portion of the Software.

A complete copy of the MSA is available at http://www.mindtouch.com/msa

Database Manager for PostgreSQL 9.1 (v13.5 LTS)

 

Table of Contents    

Long Term Support

Stable, tested ServerTemplate assets

   ►  Overview

Description

Configures a PostgreSQL 9.1 database server. This ServerTemplate provides a high-availability master/slave database configuration that can be used as the backbone for a variety of applications and workloads.

The ServerTemplate supports multiple clouds, using either instance-based storage or attachable volumes. Instance-based snapshot backups are automatically uploaded to your choice of remote object storage (Amazon S3, Rackspace Cloud Files, SoftLayer Object Storage, etc.). It also includes iptables management for clouds that do not have firewall services.

Note: For a complete list of the ServerTemplate's features and support, view the detailed description under its Info tab.

Technical Overview

Software Application Versions

  • PostgreSQL 9.1

Database Filesystem

The ServerTemplate's recipes create a filesystem for storing data and taking LVM snapshots. If the database server is launched into a cloud that supports volumes, such as EBS volumes for EC2, volume storage will be used. However, if the cloud does not support volumes (i.e. Rackspace legacy cloud and SoftLayer), the filesystem will be created on the instance's local ephemeral drive. In both cases, an LVM volume is created for data storage purposes so that the filesystem functions the same regardless of whether the data is stored on the ephemeral drive, a single mounted volume, or a stripe of mounted volumes.

By default, an LVM volume will be created where 90% of the filesystem is used for data storage and 10% is reserved for taking LVM snapshots or restoring a database.

diag-DB_Mgr-v2.png

Note: Primary backups may fail if there is not enough space available to complete an LVM snapshot. Change the 'Percentage of the LVM used for data (1)' input accordingly depending on the size of the database and the number of database modifications that are made between backups.

Security and Firewall Permissions

The way in which you access your PostgreSQL database server depends on your cloud provider. For Amazon EC2CloudStack, and other clouds that support security groups, you must use security group(s) with the appropriate permissions set. See Deployment Prerequisites (Linux).  For example, the database servers need to allow connections from the application servers over TCP port 5432 (the default PostgreSQL port), as well as for master-slave database replication. 

In all clouds, regardless of whether they support security groups or not, Iptables is used to set IP-specific port permissions, which provides better firewall permission control than only using security groups because access permissions are IP-specific (not group-specific). 

For example, the database server only listens for requests from servers defined in its Iptables configuration file. Remember, in most cloud infrastructures, you typically cannot control what private IP address is assigned to an instance. (e.g., EC2, instances are randomly assigned a private IP address at launch time.)

Before an application (e.g. PHP) can perform an action on a database (e.g. create a new record), the servers must first be granted access at the network-level before successfully making an application-level request.

diag-db_security_postgresql_network-v1.png

Once the database server has updated its permissions to allow access between the application and database tiers, the application can connect to the database using the required information. For example, the application locates the "master" database server using the 'Database Master FQDN' input (e.g. master-db.example.com). The application accesses the database, which is defined by the 'Database Schema Name' input by using the username and password of a database user that has database access privileges, which is specified by the 'Database Application Username' and 'Database Application Password' inputs.

Similarly, the Slave-DB will start replicating with the Master-DB by logging into the PostgreSQL database as a user with replication privileges using credentials specified by the 'Database Replication Username' and 'Database Replication Password' inputs.

diag-db_security_applevel-v2.png

 

The following scenario explains how iptables and tags are used to configure secure firewall permissions in the cloud.

  1. A master database server (Master-DB) is launched and becomes operational. Since this particular instance is launched in a cloud that supports security groups, it's using a security group ('3tier-db-postgresql') that accepts traffic over the private network on TCP port 5432. In the example below, the database server will accept requests to its private IP address from instances launched with the '3tier-app' and '3tier-db-postgresql' security groups. These permissions allow the Master-DB to accept inbound requests from application servers and slave database servers (Slave-DB).
    screen-SecGrp_DB_PostgreSQL-v1.png
  2. Connectivity between an application server and the Master-DB server is established by using tags. When an application server is launched, the 'db::request_appserver_allow' (boot) script makes a request to the Master-DB to accept its requests. The "master" database server tag ('rs_dbrepl:master_instance_uuid=<RS_UID>') identifies the Master-DB server. If the application server has the appropriate tag ('appserver:active=true') in the same deployment, a script is run on the Master-DB server, which updates its iptables to allow connections on TCP port 5432 over the private network from the application server's private IP address. The Master-DB server will have an audit entry for the 'completed: db::do_appservers_allow' script that confirms the modification to its firewall permissions. In the example below, both application servers are granted access to the Master-DB server.
    screen-Audit_AllowApp-v2.png

    The Master-DB server will automatically look for other application servers within its deployment that have the appropriate "app" server tag and update its iptables accordingly so that as the pool of application servers change over time, each application server will have access to the Master-DB server. By default, the 'db::do_appservers_allow' script is periodically executed every 15 minutes to make sure that all application servers can connect to the Master-DB server. You can see the default setting in the database ServerTemplate's 'Reconverge List' input. 
    screen-DB_Reconverge_List-v1.png
     
  3. Similarly, when you launch a Slave-DB server, the 'db::do_primary_init_slave' operational script will make a similar request to the Master-DB server to allow connections on TCP port 5432 from the Slave-DB server's private IP address for replication purposes. The "slave" database server tag ('rs_dbrepl:slave_instance_uuid=<RS_UID>') identifies the Slave-DB server. The values for the 'Database Replication Username/Password' inputs are used for authentication purposes. You can see details about the Master-DB server's iptables under the 'completed: sys_firewall::setup_rule' audit entry after successfully initializing the Slave-DB with the Master-DB.

 

If you need to open other ports on a database server besides the default TCP ports (22, 80, 443, and 5432), you must create the appropriate rules using the "Firewall" inputs, as described in the Base ServerTemplate for Linux (Chef) documentation.

NoteFor more information about iptables, refer to the Linux supporting documentation for this tool.

Block Devices

The ServerTemplates support the use of multiple block devices where each block device can consist of a single volume or stripe of volumes. Although most users will only use one block device, some will find it useful to use two block devices. For example, you might use the first block device, which consists of a stripe of volumes for the main database, and the second block device for storing transactional log files in a single volume. Be sure to name the inputs accordingly depending on the block device. Related block device inputs will include the block device number in parentheses. (e.g. 'Total Volume Size (1)') By default, the ServerTemplates include inputs for up to two block devices, but if you need additional block devices, see Increase the Number of Block Devices.

Database Dump Retrieval

The ServerTemplate contains scripts that can retrieve a MySQL database dump file from one of the following Remote Object Storage (ROS) locations:

  • Amazon S3
  • Rackspace Cloud Files (US or UK)
  • Google Cloud Storage
  • Microsoft Azure Blob Storage
  • OpenStack Object Storage (swift)
  • SoftLayer Object Storage

Block Devices

The ServerTemplates support the use of multiple block devices where each block device can consist of a single volume or stripe of volumes. Although most users will only use one block device, some will find it useful to use two block devices. For example, you might use the first block device, which consists of a stripe of volumes for the main database, and the second block device for storing transactional log files in a single volume. Be sure to name the inputs accordingly depending on the block device. Related block device inputs will include the block device number in parentheses. (e.g. 'Total Volume Size (1)') By default, the ServerTemplates include inputs for up to two block devices, but if you need additional block devices, see Increase the Number of Block Devices.

Backup and Storage

The Database Manager for PostgreSQL ServerTemplates published by RightScale support the use of primary and secondary storage locations for creating back-ups of databases. If the cloud does not support the use of volumes, the secondary backups can be saved to a different remote object store (ROS) location than the primary backup.

diag-DB_Storage_Options-v1.png

Primary Backup Storage

Primary backups are designed to take the most efficient backup of your database based upon the best storage solution that's supported by the cloud provider in which your server is running. When you launch a database server and run an operational script to initialize it as either a master or slave, the operational script schedules a cron job to save backups as the primary storage type, which is automatically selected for you based on your cloud provider. For clouds that support it, volume-based storage is always used as the primary storage location.

Below is a list of each supported cloud and its default primary backup storage solution. For clouds that do not support volumes, you can use any ROS solution listed under the 'Primary Backup Storage Cloud (default)' input.

  • Amazon EC2 - Amazon EBS volumes (single or striped volumes)
  • Azure - Volumes
  • CloudStack (w/ volumes) - CloudStack volumes (single or striped volumes)
  • CloudStack (w/o volumes) - Remote Object Storage
  • Datapipe - Volumes
  • HP  - Remote Object Storage
  • Google - Google Cloud Storage
  • Rackspace First Generation (legacy) - Rackspace Cloud Files
  • Rackspace Open Cloud (aka Rackspace Next Generation) - Volumes
  • SoftLayer - Remote Object Storage (Swift)

By default, the primary scheduled backup job runs once per hour on the slave server, and once every four hours on the master server.

For clouds that support volumes, use the 'Nickname' input to name the volumes that are attached to the servers. By default, the input is set to "data_storage" so if you have multiple deployments within a RightScale account you should use a unique name for this input for organizational purposes. Otherwise, you will have to filter by tag to find the volumes that are specific to a deployment.

Continuous Primary Backup Policy

By default, a cron job is configured on each database server once it's initialized as either a master or slave to take continuous backups.

  • Primary backups are automatically taken once per hour on all slave database servers.
  • Primary backups are automatically taken once per day on the master database server.


The hour and minute of the backup is randomly generated at launch time, unless you use the following inputs to specify a more convenient time in standard crontab format:

  • Master Backup Cron Hour
  • Master Backup Cron Minute
  • Slave Backup Cron Hour
  • Slave Backup Cron Minute

Secondary Backup Storage

Secondary backup storage is designed to take backups to a remote cloud/region in the case of a cloud outtage or database migration to another cloud provider. The Database Manager for PostgreSQL 9.1 ServerTemplates also let you select a secondary backup location from the following available public-cloud remote object storage (ROS) providers:

  • Amazon S3
  • Rackspace Cloud Files (US)
  • Rackspace Cloud Files (UK)
  • Google
  • Azure
  • HP
  • SoftLayer_Dallas
  • SoftLayer_Singapore
  • SoftLayer_Amsterdam


Data is stored as .gzip files (or objects) in named ROS containers. Files are stored as private or public objects. By default, they will be stored as private objects that are only accessible to servers launched with the same cloud credentials. 

While secondary backups are typically slower and more resource-intensive than volume-based primary backups, they are recommended and useful in disaster-recovery or cloud migration scenarios where you need to launch the database servers in a different cloud/region.

By default, secondary backups are performed manually (as needed) by running the db::do_secondary_backup operational script, as described in the Database Manager for PostgreSQL 9.1 (v13.5 LTS) - Runbook.

PostgreSQL Tuning Parameters

The most common customization of a PostgreSQL database server is tuning the PostgreSQL parameters to be more optimized for your own database. The PostgreSQL ServerTemplate uses a default set of PostgreSQL tuning parameters that is typically sufficient for most common PostgreSQL databases. However, as you become more familiar with the performance characteristics of your database you may want to modify some of these parameters accordingly.

Note: The locations below apply to the following revision of the ServerTemplate: [rev 8]

PostgreSQL Tuning Parameters (Default)

Links to the default PostgreSQL tuning parameters:

 

PostgreSQL Configuration Files

Links to the PostgreSQL configuration files:

Database Synchronous Replication

Slave database servers either replicate with the master database server either synchronously or asynchronously. By default, slave database servers are configured to replicate with the master database server in 'async' mode. However, if data loss is a concern, you may want to change the replication mode to 'sync' mode, which requires transaction confirmation. It's strongly recommended that you read the pros and cons of each replication mode before choosing the option that is ideal for your application and database. See Synchronous Replication.

Logs

Logs are either saved locally on the ephemeral disk (default) or sent to a dedicated logging server or service. See Logging with rsyslog (v13.5 LTS).

You must to post a comment.
Last Modified
09:17, 7 Oct 2014

Tags

Classifications

This page has no classifications.

Announcements

None

Glossary | 用語용어 Site Map | Site Help Community Corporate Site Get Support Dashboard Login
Doc Feedback Product Feedback Resources MultiCloud Marketplace Forums

Dashboard Status


© 2006-2014 RightScale, Inc. All rights reserved.
RightScale is a registered trademark of RightScale, Inc. All other products and services may be trademarks or servicemarks of their respective owners.