Database Manager for PostgreSQL 9.1 (v13.5 LTS) - Tutorial

Upload the Database Dump File

The ServerTemplate contains scripts that can retrieve a database dump file from a container in one of the supported Remote Object Storage (ROS) providers (e.g. Amazon S3, Rackspace Cloud Files). See Database Dump Retrieval.

Create a new bucket/container and upload your database dump file. The file can remain a 'private' object because your cloud credentials can be used (as inputs) for authentication purposes to retrieve the file. Make sure the uploaded file maintains the .gz file extension.

Warning! The filename of the PostgreSQL dump file cannot contain a dash (-) in its prefix name. For example, if your dump file is named, 'my-app-201205030022.gz', you must manually rename it to be 'my_app-201205030022.gz' where you use an underscore (_) to replace the dash, otherwise the script (do::do_dump_import) that imports the database dump file into the instance will fail.

• Upload Files to an Amazon S3 Bucket
• Upload Files to a Rackspace Cloud Files Container
• Upload Files to an OpenStack (Swift) Container
• Upload Files to a Google Cloud Storage Bucket
• Upload Files to a Windows Azure Blob Container
• Upload Files to a SoftLayer Object Storage Container

If you are setting up a database server for testing purposes or if you do not have your own dump file, you can use the following sample PostgreSQL dump file to complete the tutorial. The sample is a gzip (.gz) file.

app_test-20121603072614.gz

Create a Database Server

Follow these steps to add a database server to the deployment.

1. Go to the MultiCloud Marketplace (Design > MultiCloud Marketplace > ServerTemplates) and import the most recently published revision of the Database Manager for PostgreSQL 9.1 (v13.5.x LTS) ServerTemplate into your RightScale account.
2. From the imported ServerTemplate's show page, click the Add Server button.
3. Select the cloud for which you will configure a server. 
4. Select the deployment for the new server.
5. Next, the Add Server Assistant wizard will walk you through the remaining steps that are required to create a server based on the selected cloud.
   • Server Name - Provide a nickname for your new database server (e.g., postgresql-db1). Do not include "master" or "slave" in the name, because a database server's role can change in the future.
   • Select the appropriate cloud-specific resources (e.g. SSH Key, Security Group, etc.) that are required in order to launch a server into the chosen cloud. The required cloud resources may differ depending on the type of cloud infrastructure. If the cloud supports multiple datacenters/zones, select a specific zone. Later, when you create the other database server you will use a different datacenter/zone to ensure high-availability. For more information, see Add Server Assistant.
   • Important! If you are not using volumes to store the database, you must select an instance type that has disk space that's at least twice as large as your database because LVM snapshots are performed locally on the instance before they are gzipped and saved to the specified ROS location. Also, although these ServerTemplates will work with any instance size, you may experience degraded performance with small instance sizes (such as EC2 micro, Rackspace 256MB etc) due to lack of system resources. We do not recommend smaller instance types for production use.
6. Click Confirm, review the server's configuration and click Finish to create the server.

Configure Inputs

The next step is to define the properties of your database server or servers by entering values for inputs. It is simplest and best to do this at the deployment level. For a detailed explanation of how inputs are defined and used in Chef recipes and RightScripts, see Understanding Inputs.

The inputs that you need to provide values for will depend on which options you're going to use. The ServerTemplate is very flexible and supports a variety of different configurations. You must provide values for the required inputs based on the chosen options.

• Where will the contents of the database be stored?
  • On volumes attached to the instance.
  • On the local/ephemeral drive.
• If you're using volumes, are you going to use a stripe?
  • Yes - Use a stripe of multiple volumes.
  • No - Use a single volume. 
  • INPUTS: Block Device Mount Directory (1), Nickname (1), Number of Volumes in the Stripe (1), Total Volume Size (1)
• Are you building a master-slave database setup?
  • Yes
  • No
  • INPUTS: Database Master FQDN, Database Master DNS Record ID, Database Slave FQDN, Database Slave DNS Record ID, Database Replication Password, Database Replication Username
• Will there be replication between master-slave database servers across different clouds/regions? If yes, you should set up SSL.
  • Yes
  • No
  • INPUTS: CA SSL Certificate, Master SSL Certificate, Master SSL Key, Slave SSL Certificate, Slave SSL Key
• What are you going to use to take "primary" backups of the database?
  • Volume Snapshots
  • Binary Dumps to an ROS container (e.g. S3 bucket or Cloud Files container)
  • INPUTS: Backup Primary Secret (default), Backup Primary User (default), Primary Backup Storage Cloud (default),
    Primary Backup Storage Cloud Endpoint URL (default)
• Are you going to take "secondary" backups of the database? If yes, which ROS provider will you use?
  • Amazon S3, Rackspace Cloud Files (US or UK), Google Cloud Storage, Azure Blob Storage, Swift-based Storage, SoftLayer Object Storage
  • INPUTS: Secondary Backup Storage Cloud (default), Secondary Backup Secret (default), Secondary Backup User (default), Secondary Backup Storage Container (1), Secondary Backup Storage Cloud Endpoint URL (default)
• Which DNS provider are you using for dynamic DNS at the database level?
  • DNS Made Easy
  • DynDNS
  • Amazon Route 53
  • Rackspace Cloud DNS
  • INPUTS: DNS Service Provider, DNS Password, DNS User, Database Master FQDN, Database Master DNS Record ID, Database Slave DNS Record ID, Cloud DNS region

Launch the Master Database Server

After configuring your inputs, launch your newly configured master database server.

1. Go to the deployment's Servers tab and launch the database server. When you view the input confirmation page, there should not be any missing values (highlighted in red) for inputs that are required by any of the server's boot scripts. If there are any inputs highlighted in red, cancel the launch and add the missing values at the deployment level before launching the server again. Refer to the instructions in Launch a Server if you are not familiar with this process. Click the Launch (not 'Save and Launch') button at the bottom of the input confirmation page.

Initialize the Master Database Server

Wait for the server to reach the "operational" state before you run a script to initialize the database server.

1. Go to the "current" server's Scripts tab and run the db::do_init_and_become_master operational script to initialize it as the "Master" database server.
2. (Optional) You can go to the "current" server's Audit Entries tab to track the status of the operation.

The script performs the following actions:

• Registers it as the "master" database server and assign appropriate replication privileges and machine tags. (e.g. rs_dbrepl:master_instance_uuid=01-AVMV4MFHJQOK0 and rs_dbrepl:master_active=20130313200931-bob)
• For cloud providers with volume support, it creates and mounts either a single volume or group of striped volumes for data storage, based on the inputs configured for your primary database backups.
• Creates a database backup to primary storage.

• Schedules a cron job to run backups to primary storage once every four hours on the server. (For information on modifying the default backup schedule, see the Database Manager for PostgreSQL 9.1 (v13.5 LTS) - Runbook.
• Updates the dynamic DNS record for the "Master" database with the DNS provider. The DNS record is updated with the server's IP address. By default, it will use the instance's private IP address ('Database Replication Network Interface' = private). The default TTL of the "master" DNS record must also be set to 60 seconds or less ('Database DNS TTL Limit' = 60) unless you are using Rackspace's Cloud DNS service, where the lowest allowable TTL is 300 seconds.

Disable Scheduled Primary Backups

Since you have not loaded an actual database onto the server there is no reason to create a primary backup of the database.

Go to the "current" server's Scripts tab and run the db::do_primary_backup_schedule_disable operational script to disable your scheduled backups (cron jobs). 

Later, once you have imported your database you will reverse this action and enable continuous backups.

Set Up the Database

After initializing the master database server and disabling scheduled backups, you will need to add your database (or databases) and records to it.

1. Go to the "current" server's Scripts tab and run the db::do_dump_import operational script to import a PostgreSQL dump file from an ROS location.

Note: If you use a previous backup snapshot instead of a PostgreSQL dump file or initialize a blank PostgreSQL database, refer to the Database Manager for PostgreSQL 9.1 (v13.5 LTS) - Runbook for instructions. 

Create a Primary Backup

You are now ready to create the first primary backup of the database. You will need a completed backup in order to initialize a slave database server.

1. Go to the "current" server's Scripts tab and run the db::do_primary_backup operational script to manually generate a primary backup of your database server.

Enable Scheduled Primary Backups

It is now safe to enable continuous backups of the database server.

1. Go to the "current" server's Scripts tab and run the db::do_primary_backup_schedule_enable operational script.

For more information about configuring and modifying your scheduled backup policy, see the Database Manager for PostgreSQL 9.1 (v13.5 LTS) - Runbook.

Add a Slave Database Server

Although you can run PostgreSQL in single-server mode and having a separate slave server for replication purposes is not required, this is strongly recommended for failover purposes. Create a slave server in your deployment.

1. Clone the Master-DB server. See Clone a Server.
2. Rename the server accordingly. (e.g. postgresql-db2) Remember, you do not want to include the word "slave" in the nickname because this server may become the "master" server during a failover scenario. You don't want the server's nickname to potentially cause any confusion.
3. Under the server's Info tab, click Edit and change the server's availability zone. In order to ensure high availability, it's strongly recommended that you launch the Slave-DB server in a different availability zone as the Master-DB.  Note: Cross-zone data transfer costs may apply.

Launch the Slave Database Server

Make sure the following conditions are true before you launch the second database server.

• The master database server state is "operational."
• The initial primary backup of the master database server is 100% complete. If you are using a cloud that supports snapshots for backups, you can track the status in the dashboard (Clouds > region > Snapshots). The time required to complete the initial primary backup will vary based on factors such as storage type, volume size, etc.

You are now ready to launch a "slave" database server for failover and redundancy purposes. 

1. Go to the deployment's Servers tab and launch the server that will be the slave database server. 
2. When you view the input confirmation page, change the value for the following input because you have an operational master database server an a completed database backup. 

DB (advanced)

3. If there are any required inputs that are missing values (highlighted in red), cancel the launch and add the missing values at the deployment level before launching the server again. Refer to the instructions in Launch a Server if you are not familiar with this process.
4. Click the Launch (not Save and Launch) button at the bottom of the input confirmation page because you do not want to override this input at the server level. You may not want this server to become a slave the next time it is launched or relaunched.

The scripts perform the following actions:

• Assigns the "slave" role to the server.
• Uses the most recently completed database backup (default) to initially populate the database in order to reduce the time needed for the slave to become in-sync with the master.
• Sends a request to the master server to allow connections from the slave server's private IP address and opens the default PostgreSQL client port (TCP port 5432) on the master server's firewall (i.e., iptables) for this purpose.
• Schedules a cron job to run primary backups of the database once per hour (default). 

(Optional) Update the DNS Record for the Slave Database Server

If you created a DNS record for the slave database server, you can set a value for the Database Slave DNS Record ID input and run the db::do_set_dns_slave operational script.