Restoring wal-e PostgreSQL database backup


If you have a backup, you must be able to restore it. In the previous post, I wrote about creating scheduled backup using wal-e. Now, we will restore the wal-e backup.

NOTE: This post is intended to restore PostgreSQL base backup that has no user defined tablespace. If you defined that, you can read more in here.

Step 1: Install PostgreSQL and wal-e

I assumed that you already install PostgreSQL and wal-e. You need to install same PostgreSQL major version and all its extensions that you use to create the base backup. FYI, PostgreSQL has a different binary file format for each major version. Postgres major version is determined by the first and second group number of the version number (e.g: 8.4, 9.0, 9.5, etc.).

Before we proceed to the next step, you need to stop the PostgreSQL server and remove the data directory.

POSRTGES_VERSION=9.5
sudo service postgresql stop
sudo rm -rf /var/lib/postgresql/${POSTGRES_VERSION}/main

If you haven’t install wal-e, you can follow this on step 3.

Step 2: Download Base Backup

Now, we can download our base backup:

POSTGRES_VERSION=9.5
sudo -u postgres envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch /var/lib/postgresql/${POSTGRES_VERSION}/main LATEST

The above command will download the latest base backup that you have. You can list your base backup version by running

sudo -u postgres envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list

Run the backup-fecth and replace LATEST argument with the base backup name from backup-list.

Step 3: Configure PostgreSQL recovery mode

Edit your PostgreSQL recovery configuration file:

sudo -u postgres vim /var/lib/postgresql/${POSTGRES_VERSION}/main/recovery.conf

Add the following configuration:

# The command that will be executed while recovering
restore_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'

# You can specify this value with exact time. This will be useful if
# you have incident and you want to recover to a few moments before.
# recovery_target_time = '2016-06-02 06:18:00'

Start the PostgreSQL database again.

sudo service postgresql start

Now you will have a fully running recovery database.

Advertisements

Set up scheduled PostgreSQL backup to AWS S3 using wal-e


In the previous article, I wrote a guide how to set up PostgreSQL master-slave database replication. It is good to have hot standby database server. You can make your slave servers as read-replica to reduce master server load. In case your primary (master) server failed, you still have another server that is ready to be a new master.

Why should I have a cold database backup? Imagine someone accidentally delete your table (let’s say DROP TABLE users;). The drop query is going to be replicated across all hot standby databases. Your data will be gone. To mitigate this issue, we can set up a backup (i.e snapshot) of your database. So in this incident, you can restore your data. You can backup it weekly, daily or hourly, it depends on your RPO/Recovery Point Objective.

There are a few PostgreSQL backup tools. One of them is wal-e. wal-e can perform continuous archiving PostgreSQL WAL files and base backups. The archives are compressed using lzop. The compression is slightly bigger than gzip, but the compressing takes lower CPU usage.

 

UPDATE (2016/06/02): As per bluetreble’s comment below, the backup retention will be done by AWS S3 Lifecycle management and remove IAM user privilege to delete objects in S3 bucket. And also the creating first base backup will be done after configuring archiving first to make sure it can also be restored.

 

Backup on AWS S3

For saving backup files, wal-e support AWS S3, Azure Blob Storage, and OpenStack Swift. Currently, Google Cloud Storage is not supported, but there is a pull request to support it.

I personally prefer to use AWS S3. AWS S3 is a secure, durable and highly-scalable cloud storage managed by Amazon. All objects in AWS S3 are replicated across multiple Availability Zones (AZs) in a region. At the time of writing, AWS ensured the durability of your files up to 99.999999999% (eleven-9s).

Tips using AWS S3 for backup:

  • Choosing region. Prefer to backup on a different region than existing original database. Just in case a failure in primary database region, you still have cold backups in another region.
  • Choosing storage class. AWS S3 has 4 storage classes: Standard, Standard – Infrequent Access (IA), Reduced Redundancy Storage (RRS), and Glacier. I suggest using either Standard or Standard – IA. Both have 99.999999999% durability. Standard has 99.99% availability, but Standard – IA has only 99.9% with lower cost. I do not recommend RRS since it only has 99.99% durability and Glacier may take hours to restore the data.

Please take a note, if you put your data to a different region, there will be additional AWS data transfer charge.

Step 1: Create AWS S3 bucket and set up its lifecycle

First, we need to create a new S3 bucket to store your database backup.

  1. Open AWS S3 Management Console.
  2. Click Create Bucket.
  3. Fill the bucket name and the region where you want to keep the backup. I assumed that the S3 bucket name is pgbackup.
  4. Click Create.

Then, we will enable the versioning and setup lifecycle of the bucket:

  1. Open AWS S3 Management Console.
  2. Right click on your bucket name, and click Properties.
  3. In the Versioning menu, click on Enable Versioning button and click OK for the confirmation dialog.
  4. In the Lifecycle menu,
    1. Click Add rule and a new dialog will appear.
    2. In the Step 1, choose Whole Bucket and click Configure Rule.
    3. In the Step 2, check on Expire and fill its value to 8 days. You can adjust the value based on your requirement. In my case, I want to keep my backup for 7 days and I will create base backup daily, so I just use 7 + 1 = 8 days as the value.
    4. Still on the Step 2, check on Permanently Delete and fill its value to 1 day. This will keep you backup for extra 1 day after it marked as expired.
    5. Click Review. You can name this rule with intuitive name like `Database retention rule`
    6. Click Create and Activate Rule.

Step 2: Create AWS IAM user

Create a new IAM user. Take a note of the generated access key and secret key. After that, set inline policy to your new user with the following policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": ["arn:aws:s3:::pgbackup"]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:PutObject"
      ],
      "Resource": ["arn:aws:s3:::pgbackup/*"]
    }
  ]
}

Don’t forget to adjust the bucket name. The above policy is to allow the principal (user) to CRUD the backup bucket.

If you host your database server on AWS EC2 instances and an IAM role has been attached to it, you can skip creating IAM user, just add a new inline policy. Using IAM role is more recommended, but you can only attach it to a new EC2 instance.

Step 3: Install and configure wal-e

You can install wal-e to any servers that you want to backup. Run the following commands to install wal-e:

sudo apt-get -y update
sudo apt-get -y install daemontools python-dev lzop pv
curl -O https://bootstrap.pypa.io/get-pip.py
sudo python get-pip.py
export LC_ALL=C
sudo pip install -U six
sudo pip install -U requests
sudo pip install wal-e

Configure environment variable for wal-e

umask u=rwx,g=rx,o=
sudo mkdir -p /etc/wal-e.d/env
echo '<AWS_REGION>' | sudo tee /etc/wal-e.d/env/AWS_REGION
echo '<AWS_ACCESS_KEY_ID>' | sudo tee /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo '<AWS_SECRET_ACCESS_KEY>' | sudo tee /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
echo 's3://pgbackup/<NODE_NAME>' | sudo tee /etc/wal-e.d/env/WALE_S3_PREFIX 
sudo chown -R root:postgres /etc/wal-e.d

Change <AWS_REGION> to your S3 bucket region (e.g: us-east-1). Adjust <AWS_SECRET_ACCESS_KEY> and <AWS_ACCESS_KEY_ID> to the key pairs, you git after creating a new IAM user. <NODE_NAME> parameter is optional, use it whenever you put your backups from multiple nodes to a single bucket.

If you use IAM role, you do not need to put <AWS_SECRET_ACCESS_KEY> and <AWS_ACCESS_KEY_ID>. wal-e can use your instance’s IAM role. Just append --aws-instance-profile to all wal-e commands below.

Step 4: Configure PostgreSQL

Not only create a base backup, wal-e can archive PostgreSQL WAL too. We need to configure PostgreSQL to trigger wal-e whenever there is a new WAL archive. Edit your PostgreSQL configuration using your favorite text editor:

POSTGRES_VERSION=9.5
sudo vim /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf

Add/update the following configuration:

# wal_level determines how much information is written to the WAL.
# For backup, you need to set this value as 'hot_standby' or 'archive'
# The 'archive' adds logging required for WAL archiving,
# and the 'hot_standby' further adds information required to run read-only queries on a standby server.
wal_level = 'hot_standby'

# Enable archiving
archive_mode = on

# Command that will be triggered after the WAL archive segment ready
archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'

# The archive_timeout setting will force to swicth WAL archive segment
# if the WAL segments is not completed within this periods.
archive_timeout = 60 # seconds

Restart PostgreSQL server:

sudo service postgresql restart

 

Now, you can test you wal-e configuration to create a base backup:

POSTGRES_VERSION=9.5
sudo -u postgres /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/${POSTGRES_VERSION}/main

 

The output might look like:

wal_e.main INFO MSG: starting WAL-E
 DETAIL: The subcommand is "backup-push".
 STRUCTURED: time=2016-05-24T14:34:51.764264-00 pid=26204
wal_e.operator.backup INFO MSG: start upload postgres version metadata
 DETAIL: Uploading to s3://pgbackup/node1/basebackups_005/base_000000010000000000000004_00000040/extended_version.txt.
 STRUCTURED: time=2016-05-24T14:34:52.490033-00 pid=26204
wal_e.operator.backup INFO MSG: postgres version metadata upload complete
 STRUCTURED: time=2016-05-24T14:34:52.580803-00 pid=26204
wal_e.worker.upload INFO MSG: beginning volume compression
 DETAIL: Building volume 0.
 STRUCTURED: time=2016-05-24T14:34:52.664456-00 pid=26204
wal_e.worker.upload INFO MSG: begin uploading a base backup volume
 DETAIL: Uploading to "s3://pgbackup/node1/basebackups_005/base_000000010000000000000004_00000040/tar_partitions/part_00000000.tar.lzo".
 STRUCTURED: time=2016-05-24T14:34:53.282628-00 pid=26204
wal_e.worker.upload INFO MSG: finish uploading a base backup volume
 DETAIL: Uploading to "s3://pgbackup/node1/basebackups_005/base_000000010000000000000004_00000040/tar_partitions/part_00000000.tar.lzo" complete at 7570.78KiB/s. 
 STRUCTURED: time=2016-05-24T14:34:53.787137-00 pid=26204
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

 

Step 6: Create scheduled base backup cronjob

Create a new cronjob to create base backup for you:

(sudo -u postgres crontab -l 2>/dev/null; echo "0 2 * * * POSTGRES_VERSION=9.5 /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/${POSTGRES_VERSION}/main") | sudo -u postgres crontab -

The above command will append a new cronjob that will be triggered daily at 2 am.

In the first step, we already setup S3 lifecycle, so we do not have to worry to delete the old backup files. It will be automatically deleted by AWS.

What’s next?

Do you need encryption for your data? wal-e can encrypt your data on the client side (on the database server) using gpg. You can follow the documentation.

In the next article, I will show you how to restore the wal-e backup.

Enable HTTPS and HTTP-Redirect on AWS Elastic Beanstalk


AWS Elastic Beanstalk is powerful deployment tools on AWS. It allows users to create applications and push them to a definable set of AWS services, including Amazon EC2, Amazon RDS, Amazon Simple Notification Service (SNS), Amazon CloudWatch, Auto Scaling Group, and Elastic Load Balancer (ELB). The problem is: Elastic Beanstalk Web Console is not so powerful. But, it can be extended using script (.ebextensions) and CLI.

Problem: HTTP and HTTPS Configuration on Elastic Beanstalk (Web Console)

AWS Elastic Beanstalk Configuration: Load Balancer

From the Elastic Beanstalk Web Console, you can configure a web application to listen HTTP and HTTPS port using Elastic Load Balancer (ELB). But, the ELB will forward/proxy the request into a single HTTP port. It means that HTTP and HTTPS will serve same response from user point-of-view. HTTPS connection is terminated (HTTPS-termination) in ELB.

Some users (at least me and some users in stackoverflow.com/serverfault.com) want:

  • HTTP request is replied with HTTP redirection (3xx status code) to HTTPS.
  • HTTPS request is replied by actual web app.
  • No HTTPS-termination in ELB.

Solution: Configure Elastic Load Balancer using .ebextentions

Some of Elastic Beanstalk resources can be customized using .ebextenstions script (see: Customize Containers and Environment Resources). Now, we will configure the ELB to proxy HTTP and HTTPS request to different EC2 instance’s ports.

  1. Create .ebextensions directory inside your app root path.
  2. Create a file (e.g: 00-load-balancer.config) inside .ebextensions directory.
  3. Write the following configuration into the file (.ebextensions/00-load-balancer.config).
{
  "Resources": {
    "AWSEBSecurityGroup": {
      "Type": "AWS::EC2::SecurityGroup",
      "Properties": {
        "GroupDescription": "Allow HTTP and HTTPS",
        "SecurityGroupIngress": [
          {
            "IpProtocol": "tcp",
            "FromPort": 80,
            "ToPort": 80,
            "CidrIp": "0.0.0.0/0"
          },
          {
            "IpProtocol": "tcp",
            "FromPort": 443,
            "ToPort": 443,
            "CidrIp": "0.0.0.0/0"
          }
        ]
      }
    },
    "AWSEBLoadBalancerSecurityGroup": {
      "Type": "AWS::EC2::SecurityGroup",
      "Properties": {
        "GroupDescription": "Allow HTTP and HTTPS",
        "SecurityGroupIngress": [
          {
            "IpProtocol": "tcp",
            "FromPort": 80,
            "ToPort": 80,
            "CidrIp": "0.0.0.0/0"
          },
          {
            "IpProtocol": "tcp",
            "FromPort": 443,
            "ToPort": 443,
            "CidrIp": "0.0.0.0/0"
          }
        ],
        "SecurityGroupEgress": [
          {
            "IpProtocol": "tcp",
            "FromPort": 80,
            "ToPort": 80,
            "CidrIp": "0.0.0.0/0"
          },
          {
            "IpProtocol": "tcp",
            "FromPort": 443,
            "ToPort": 443,
            "CidrIp": "0.0.0.0/0"
          }
        ]
      }
    },
    "AWSEBLoadBalancer": {
      "Type": "AWS::ElasticLoadBalancing::LoadBalancer",
      "Properties": {
        "HealthCheck": {
          "HealthyThreshold": "3",
          "Interval": "30",
          "Target": "HTTP:80/status.html",
          "Timeout": "5",
          "UnhealthyThreshold": "5"
        },
        "Listeners": [
          {
            "LoadBalancerPort": 80,
            "Protocol": "HTTP",
            "InstancePort": 80,
            "InstanceProtocol": "HTTP"
          },
          {
            "LoadBalancerPort": 443,
            "Protocol": "HTTPS",
            "InstancePort": 443,
            "InstanceProtocol": "HTTPS",
            "SSLCertificateId": "arn:aws:iam::123456789012:server-certificate/YourSSLCertificate"
          }
        ],
        "SecurityGroups": [
          { "Fn::GetAtt": [ "AWSEBLoadBalancerSecurityGroup", "GroupId" ] }
        ]
      }
    }
  }
}

In the above config, we modified 3 resources:

  • EC2 Instance Security Group, allow to listen on port HTTP (80) and HTTPS (443).
  • ELB Security Group, allow to listen on port HTTP (80) and HTTPS (443).
  • ELB, we modified ELB to:
    • Do health check EC2 instances on port 80 by HTTP request to /status.html. So, we need to create a hole in port HTTP to allow access the page (will be described later). Elastic Beanstalk doesn’t allow us to do health check using HTTPS request. If you want to do health check by checking TCP port 80, just remove this config section.
    • Make ELB listen to port 80 and forward it to EC2 instance’s port 80.
    • Make ELB listen to port 443 and forward it to EC2 instance’s port 443.

Now the ELB configuration is ready. But, we need to configure web server inside EC2 instances.

Elastic Beanstalk provides some different type of environment (e.g: Java, Python, Ruby, Docker, etc.). Each environment might have different configuration. You can check it on Supported Platforms. At the time of writing this post, they use some web proxy/server (i.e. Apache 2.2, Apache 2.4, Nginx 1.6.2 and IIS 8.5) to listen at port 80 (HTTP).

In this post, I only tell you how to configure Single Docker Container Elastic Beanstalk, which is using Nginx 1.6.2 as proxy. Basically, Single Docker Container Elastic Beanstalk use Nginx to proxy the request to a Docker container. Each time you deploy a new update, Elastic Beanstalk agent inside EC2 instance will update Docker upstream in /etc/nginx/conf.d/elasticbeanstalk-nginx-docker-upstream.conf. Another environment can be configured slightly same.

  1. Create a file (e.g: 01-nginx-proxy.config) inside .ebextensions directory.
  2. Write the following configuration into the file (.ebextensions/01-nginx-proxy.config). Don’t forget to adjust some config (e.g: domain name, SSL certificate, etc.).
files:
  "/etc/nginx/sites-available/000-default.conf":
    mode: "000644"
    owner: root
    group: root
    content: |
      map $http_upgrade $connection_upgrade {
        default   "upgrade";
        ""        "";
      }
 
      server {
        listen         80;
        server_name    your-domain.com;
 
        location = /status.html {
          proxy_pass          http://docker;
          proxy_http_version  1.1;
 
          proxy_set_header    Connection          $connection_upgrade;
          proxy_set_header    Upgrade             $http_upgrade;
          proxy_set_header    Host                $host;
          proxy_set_header    X-Real-IP           $remote_addr;
          proxy_set_header    X-Forwarded-For     $proxy_add_x_forwarded_for;
          proxy_set_header    X-Forwarded-Host    $host;
          proxy_set_header    X-Forwarded-Server  $host;
        }
 
        location / {
          return        301 https://$host$request_uri;
        }
      }
 
      server {
        listen 443;
 
        ssl                  on;
        ssl_session_timeout  5m;
        ssl_protocols        TLSv1 TLSv1.1 TLSv1.2;
        ssl_certificate      /opt/ssl/default-ssl.crt;
        ssl_certificate_key  /opt/ssl/default-ssl.pem;
        ssl_session_cache    shared:SSL:10m;
 
        location / {
          proxy_pass          http://docker;
          proxy_http_version  1.1;
 
          proxy_set_header    Connection          $connection_upgrade;
          proxy_set_header    Upgrade             $http_upgrade;
          proxy_set_header    Host                $host;
          proxy_set_header    X-Real-IP           $remote_addr;
          proxy_set_header    X-Forwarded-For     $proxy_add_x_forwarded_for;
          proxy_set_header    X-Forwarded-Host    $host;
          proxy_set_header    X-Forwarded-Server  $host;
        }
      }
 
  "/opt/ssl/default-ssl.crt":
    mode: "000400"
    owner: root
    group: root
    content: |
      -----BEGIN CERTIFICATE-----
      *
      * YOUR-CHAINED-SSL-CERTIFICATE-HERE
      *
      -----END CERTIFICATE-----
 
 
  "/opt/ssl/default-ssl.pem":
    mode: "000400"
    owner: root
    group: root
    content: |
      -----BEGIN RSA PRIVATE KEY-----
      *
      * YOUR-SSL-PRIVATE-KEY-HERE
      *
      -----END RSA PRIVATE KEY-----
 
commands:
   00_enable_site:
    command: 'rm -f /etc/nginx/sites-enabled/* && ln -s /etc/nginx/sites-available/000-default.conf /etc/nginx/sites-enabled/000-default.conf'

In the above config, we:

  • Create SSL certificate and key file.
  • Create Nginx site config:
    • Listen port 80 (HTTP) and redirect all request to HTTPS, except for /status.html. We create a hole here to allow load balancer do health check.
    • Listen port 443 (HTTPS) and proxy the request to actual web server (in this case, Docker container upstream, http://docker).
  • Remove all enabled-sites config and create symlink for the new Nginx config.

After that, you can zip your app directory and deploy it to Elastic Beanstalk via Web Console or CLI.

AWS CloudFormation: VPC with Public and Private Subnets


This is an AWS CloudFormation template to create a VPC environment with public and private subnets. The subnets will be located at two different availability zones:

  • Availability Zone 1
    • Public Subnet 1
    • Private Subnet 1
  • Availability Zone 2
    • Public Subnet 2
    • Private Subnet 2

This template also create an NAT instance inside public subnet 1.