In the public cloud Marketplaces, you can scale compute and memory in your cluster independently of storage with gpcompute. This automates the process to increase or decrease the instance type size to increase/decrease the amount of CPU and RAM for each VM.

By scaling up, you will be able to handle higher concurrency. Scaling down, you can save money. So it is a great utility to handle the Monday morning traffic in the database and then scale down for the remaining part of the week. Or, you may have a new cluster with few users. As you get more adoption, you can scale up to handle the increase of demand.

For storage, you can scale up your storage independently of compute with gpgrow. This automates the commands to increase the storage size of your cluster. For AWS and GCP, this command is an online command which means the database is up and running while you grow your storage. For Azure, the cluster is temporarily paused while it grows the storage.

For on-premise installations, you typically scale by adding more nodes and then use the gpexpand utility. This is the most practical way to expand as you just rack more nodes and then expand the cluster to take advantage of the new nodes.

In the cloud, you can provision a new cluster rather quickly so quickly that it makes using gpcopy the better way to add more nodes to you cluster.

Using gpcopy

I have a small cluster in AWS with 8 nodes (64 segment cores) and now have decided to double the cluster to 16 nodes (128 segment cores). For my test, I first loaded 1TB of TPC-H data into my old cluster. This is the Source cluster.

Next, I deployed the new AWS cluster with 16 nodes using the AWS Marketplace which only took 17 minutes. This is the Target cluster.

gpcopy Configuration Steps

• Deploy new cluster in a new VPC which requires configuring VPC peering. Note: If you are deploying the cluster to the same VPC, you won’t have to do these steps.
  • Update the Route Tables so the two VPCs could route to one another.
  • Update the Security Groups on the Target cluster so the Source cluster could communicate to it. Allow all traffic on UDP and TCP from the Source Security Groups to the Target Security Groups.
• Install gpcopy on both clusters.
• Stop Command Center on Target cluster.
• On Target cluster, update gpadmin and gpmon users’ passwords to match the Source cluster.
• On the Source cluster, update the gpadmin .pgpass file to have an entry for the private ip address of the Target cluster. Remember to use port 6432. Example: 10.1.0.248:6432:*:gpadmin:VZBQCFDxzZuWm
• Run gpcopy on the Source cluster.
• Run analyzedb on Target cluster.

gpcopy Command

gpcopy --full \
--source-host mdw \
--source-port 6432 \
--dest-host 10.1.0.248 \
--dest-port 6432 \
--drop

gpcopy Results

[INFO]:-Total elapsed time: 15m10.61753093s
[INFO]:-Total transferred data 1.2TB, transfer rate 9.2TB/h
[INFO]:-Copied 2 databases
[INFO]:-Database gpperfmon: successfully copied 34 tables, skipped 0 tables, failed 0 tables
[INFO]:-Database dev: successfully copied 187 tables, skipped 0 tables, failed 0 tables
[INFO]:-Copy completed successfully

Validation

The Source cluster ran one of the TPC-H queries in 1 minute and 15 seconds. The same query on the new cluster ran the query in 25 seconds. Another example, a query ran in 1 minute and 59 seconds and now runs in 33 seconds.

Benefits of gpcopy

• Validation that the new cluster performs as expected.
• Automatic Rollback plan. If I don’t like it, I delete the new cluster and keep using the old one.
• Fast.
• Secure. The data transfers between VMs routed through your private network.

Final Thoughts

The elasticity of the cloud provides new solutions in solving old problems. How can I scale Greenplum? The old way is to add more nodes and run gpexpand. With the cloud, there are three answers.

1. Scale compute with gpcompute
2. Scale storage with gpgrow
3. Scale nodes with gpcopy

Register now for the Greenplum Summit!

https://connect.tanzu.vmware.com/greenplum-summit.html

The Greenplum Summit is virtual this year with sessions every Wednesday from 9AM-11AM PST. This Wednesday on July 29, I will be discussing ways to save money in the public cloud when deploying Greenplum database.

Cloud deployments need special attention because of the cloud environment itself and each cloud vendor needs unique attention. AWS is the most popular environment so this post will discuss some of the additional configurations that are needed and available in the Greenplum on AWS Marketplace product offerings.

Accelerated Networking

To enable Accelerated Networking, AWS requires either an Intel or Elastic Network Adapter (ENA) driver installed in your VMs depending on the Instance Type you are using. Not only is the driver needed, but you must modify the VM to enable the driver.

After the driver has been installed, you then must stop the VM and execute a CLI command to enable Accelerated Networking. After the VM starts again, you should verify that the drivers have been installed.

# Intel Driver
modinfo ixgbevf

#ENA Driver
modinfo ena

After this is done, you can create an Amazon Machine Image (AMI) so that new VMs you create have both the drivers and Accelerated Networking.

Elastic Block Storage (EBS) Timeout

Did you know that your VM may timeout when trying to communicate with a mounted EBS volume? When it does timeout, the operating system doesn’t know what to do either. The disk can’t be unmounted and you can’t use the disk at all. It isn’t a good situation to be in.

EBS may recover the disk properly but not your operating system may corrupt the disk. You can try to reboot but there isn’t a guarantee that the files will be intact. EBS didn’t technically fail either. Your VM just timed out communicating with EBS.

The default timeout is 30 seconds but can be increased up to 255 seconds. AWS recommends using the largest timeout possible to avoid this scenario too.

To alter this, you have to add nvme_core.io_timeout=255 to your kernel boot options and then restart the VM. Ideally, this setting is part of your AMI so that all VMs automatically get this setting so that you can avoid an EBS timeout.

Disk Settings on Reboot

Did you know that when you reboot a VM in AWS, the blockdev readahead and disk scheduler will get set back to the defaults? The consequence of this means much slower disk performance.

Greenplum on AWS Marketplace

The Greenplum on AWS Marketplace products have Accelerated Networking enabled, EBS Timeout at the max 255 seconds, and service on startup that automatically sets the optimal disk settings.

By using the Greenplum on AWS, Azure, or GCP Marketplace products, you can focus on using the database without having to worry with the tuning and configuration that is required for each cloud environment.

Backing up the Greenplum database was commonly performed by the gpcrondump utility but starting with Greenplum 5, this utility has been deprecated. You can still use it but it won’t be included or supported after Greenplum 5. Instead, you should use gpbackup to create database backups.

Greenplum 5

You still can find gpbackup with Greenplum 5 but it is not the latest version available. Instead, you should install the latest version of gpbackup to get the latest features and bug fixes.

Greenplum 6

The backup utility is no longer included with the database installer. This method of software delivery enables the Backup/Restore team to deliver software faster than if were bundled with the database installer.

https://network.pivotal.io/products/pivotal-gpdb-backup-restore

Installation

This is so easy! Installation is done via a Greenplum package. For example:

gppkg -i pivotal_greenplum_backup_restore-1.18.2-1-gp6-rhel-x86_64.gppkg

Greenplum can read and write data to Azure blob store with PXF just like it can with AWS S3. Here is a quick demo on using Azure.

Step 1 – Start PXF

Run this as gpadmin on the Master (mdw) host.

pxf cluster start

Step 2 – Create Azure Storage Account

Log into Azure and navigate to Storage Accounts and create a new one.

The Storage Account name must be unique within Azure. I picked “greenplumguru” for this demo but your name must be different.

Step 3 – Retrieve the Storage Account Key

After the Storage Account has been created, go to the resource and click on “Access Keys” to copy it.

Step 4 – Configure PXF

On the master node (mdw), execute the following commands.

mkdir $PXF_CONF/servers/demo
cp $PXF_CONF/templates/wasbs-site.xml $PXF_CONF/servers/demo/
vi $PXF_CONF/servers/demo/wasbs-site.xml

Change YOUR_AZURE_BLOB_STORAGE_ACCOUNT_NAME to the name of your Storage Account from Step 2. Change YOUR_AZURE_BLOB_STORAGE_ACCOUNT_KEY to the key value from Step 3.

Step 5 – Sync PXF

pxf cluster sync

Step 6 – Create a Container in your Storage Account

Step 7 – Create Writable External Table

CREATE WRITABLE EXTERNAL TABLE ext_write
(id int, description text)
LOCATION ('pxf://demo-container@greenplumguru.blob.core.windows.net/path/to/blob/file?PROFILE=wasbs:text&SERVER=demo')
FORMAT 'csv';

In the above example, I used the following:

• “greenplumguru” for the Storage Account from step 2
• “demo” as the name of the server configuration from step 4
• “demo-container” which is the container name from step 6

Step 8 – Insert Some Data

INSERT INTO ext_write SELECT i, 'foo_' || i 
FROM generate_series(1,100000) as i;

Step 9 – Create Readable External Table

CREATE EXTERNAL TABLE ext_read
(id int, description text)
LOCATION ('pxf://demo-container@greenplumguru.blob.core.windows.net/path/to/blob/file?PROFILE=wasbs:text&SERVER=demo')
FORMAT 'csv';

Step 10 – Select from External Table

SELECT * FROM ext_read LIMIT 10;

Greenplum has always used External Tables to access external data for query access. You can load this data into Greenplum formatted tables or even use as partitions in a partitioned table. The “location” of the External Table can be many things such as gpfdist (for files) or S3 or PXF.

PXF can access many formats like HDFS, HBase, Hive, JDBC, Google Cloud Storage, Minio, and AWS S3. This article will cover S3 but you can learn more about PXF here.

When you deploy Greenplum in AWS, an IAM::Role resource is created which enables many AWS features in the virtual machines deployed. You see this warning message bout the Role when you deploy.

Because you have the Role by default in AWS, you can leverage these credentials immediately to access S3 via PXF. Here is how you do it.

Step 1 – Start PXF

Run this as gpadmin on the Master (mdw) host.

pxf cluster start

Step 2 – Create s3 bucket

Note: The bucket name must be unique.

Step 3 – Create Writable External Table

CREATE WRITABLE EXTERNAL TABLE ext_write
(id int, description text)
LOCATION ('pxf://my-demo-bucket-greenplum/?PROFILE=s3:text&SERVER=s3')
FORMAT 'csv';

Step 4 – Insert Some Data

INSERT INTO ext_write SELECT i, 'foo_' || i 
FROM generate_series(1,100000) as i;

Step 5 – Create Readable External Table

CREATE EXTERNAL TABLE ext_read
(id int, description text)
LOCATION ('pxf://my-demo-bucket-greenplum/?PROFILE=s3:text&SERVER=s3')
FORMAT 'csv';

Step 6 – Select from External Table

SELECT * FROM ext_read LIMIT 10;

As you can see, with relative ease, you can create readable and writable external tables using PXF in AWS by leveraging the existing credentials that are included with your Greenplum on AWS deployment. Enjoy!

Greenplum supports many different languages that can be executed in-database and in parallel. SQL and pgSQL are the most common but Perl, R, Python, and Java are also available.

Some of these languages provide means to interact with the operating system directly which can bypass security measures in the database. When these languages are created in the database, they are considered to be “Untrusted” where “Trusted” languages have certain commands removed so that the language can’t interact with the operating system.

Python is a very popular language and code can be written in Python using PL/PythonU in Greenplum. There isn’t a Trusted version of Python for PostgreSQL.

The “U” at the end indicates that it is in fact an Untrusted language. Again, this means a function written in this language can do nasty things to the operating system so be careful allowing this language to be used.

CREATE OR REPLACE FUNCTION fn_test() RETURNS SETOF text AS
$$
        import os
        return os.listdir('/home/gpadmin/')
$$
LANGUAGE plpythonu;

This simple function can return data about the operating system but nastier functions can be created to do all sorts of things as a privileged user (gpadmin).

PL/Container

PL/Container is a language extension in Greenplum that allows the database to interact with Docker containers to execute code. This means you can use Untrusted languages such as Python, and have that code execute in containers rather than on the host operating system. This isolation makes the use of Untrusted languages safe to be used.

CREATE OR REPLACE FUNCTION fn_test2() RETURNS SETOF text AS
$$
        # container: plc_python_shared
        import os
        return os.listdir('/')
$$
LANGUAGE plcontainer;

Running this function will list the directory contents but of the container. This isolation protects the database cluster from being exploited by the use of the popular Python language.

So have fun with Python in Greenplum knowing you can use PL/Container to protect the cluster from “Untrusted” code.

I’ve been working in the cloud for a while now and have learned a lot about optimizing the disk performance. This quick block post will cover some of the things I’ve learned in the cloud.

Blockdev

On-premise installations are typically up 24×7 but in the cloud, you may frequently want to pause your cluster to save money. Greenplum on the cloud has gppower which automates the pause and resume of your cluster so you won’t have to mess with the blockdev command.

The installation guide for Greenplum will advise you to set the read-ahead value to 16384 for each data volume in your cluster.

/sbin/blockdev --setra 16384 $device

Did you know that on reboot, your operating system will reset this back to the default? So, you will need to set the read-ahead after every reboot. This is done automatically for you in the cloud but if you build your own, be sure to add some automation to do this after starting the nodes in your cluster.

UUID

In your /etc/fstab file, you can define the mounts like so:

/dev/sdb /data1 xfs rw,noatime,nobarrier,nodev,inode64 0 2
/dev/sdc /data2 xfs rw,noatime,nobarrier,nodev,inode64 0 2
/dev/sdd /data3 xfs rw,noatime,nobarrier,nodev,inode64 0 2

Unfortunately, you may run into problems with this configuration. The device order may change on reboot so suddenly, the disk that is supposed to be data1 is now data2. This will prevent the database from starting properly.

Instead, you should use the UUID which can be found in /dev/disk/by-uuid/ on each host.

UUID=46c6e872-20ca-4d50-9ed9-d98c4f662437 /data1 xfs rw,noatime,nobarrier,nodev,inode64 0 2
UUID=ed8b5c09-7427-455c-b132-f601d4f0b0d2 /data2 xfs rw,noatime,nobarrier,nodev,inode64 0 2
UUID=e92a5f17-3903-4c14-8e22-ebe218ab403f /data3 xfs rw,noatime,nobarrier,nodev,inode64 0 2

By using UUID, the device path may change but the UUID will persist. This ensures no problems in starting your database after the cluster was restarted.

noatime

This simple setting which is found in the /etc/fstab file, indicates to the operating system not to update the timestamp on a file if it is modified or accessed. There is considerable overhead added to using the database if you don’t add this mount option. In other words, it is faster to use noatime.

nobarrier

This mount option which is available in CentOS/Redhat 6 and 7 improves performance by not using write barriers. The overall increase in performance depends on your disk configuration but be advised that the disks need a battery backed disk write cache to ensure no data loss.

xfs

XFS has been the recommended disk format for over 10 years but did you know about the cool utility xfs_growfs that is included with this filesystem? This nifty tool will grow an existing xfs filesystem that is mounted. It is actually used in the Greenplum cloud utility gpgrow to grow the disk storage for your database in the cloud. It also can be executed online so you don’t have to stop the database to grow your storage.

Scheduler

The disk scheduler recommended for Greenplum is deadline. If you ever replace a disk for Greenplum, be sure to set the scheduler back to deadline. The default is likely cfq but this needs to be updated.

In the cloud, the gpsnap utility actually replaces your data volumes during a restore and will automatically set the scheduler properly but if you do a disk restore manually, be sure to set this for all disks replaced.

echo "deadline" > /sys/block/$devname/queue/scheduler

Number of Disks

Typically, when you add more disks to a node, you will get greater throughput but you will eventually reach the limit of the controller so adding more disks won’t mean more throughput.

For the cloud, things get a little bit more interesting. Each cloud vendor puts performance limits on each VM and compared to a bare metal installation, these limits are considerably lower.

So you may read about how a cloud vendor’s disk can provide 500MB/s of read performance so you might think with 4 disks, I could achieve 2000MB/s. However, that same cloud vendor may put a speed limit on each VM of only 500MB/s. So no matter how many disks you add to your cluster, you won’t get more throughput!

It is often better to use 2x to 8x more virtual machines that are smaller to get more overall disk throughput rather than using the larger instance types in the clouds. BTW, all of this work has already been figured out for the Greenplum cloud products. 🙂

Greenplum on AWS, GCP and Azure have so many features that make it easier to run and manage Greenplum in the cloud.

gppower

gppower is a new utility that integrates with each cloud provider to automate the pause and resume of the cluster.

The pause command will stop Greenplum, Command Center, and even PXF before it then suspends the segment virtual machines. This is designed to save you money because the IaaS providers do not charge for the compute time of stopped VMs.

The resume command reverses this by starting the VMs and then starting Greenplum, Command Center and PXF.

gpgrow

This utility also integrates with the IaaS and allows you grow storage independently of compute. Each IaaS has different attributes in regards to this feature worth mentioning.

Growing the disk size is online for AWS and GCP. The database doesn’t even need to be stopped. For Azure, gpgrow will automatically stop and deallocate the VMs so that the disks can be modified. It will then start the VMs and Greenplum before expanding the disks.

gpmaintain & gpcronmaintain

This utility automates the common maintenance routines needed for Greenplum. The “cron” version makes it easier to schedule your maintenance. The configuration file is in /usr/local/greenplum-cloud/conf/.

The utility automates the execution of analyze and vacuum. It checks for bloated tables too.

gpoptional

This utility makes it easier to install optional components like Command Center. Just run it at the command line to see the list of optional components.

bouncer

This utility automates the start, stop, pause, and resume of pgBouncer. It is intended to make it easier to use the load balancer.

gpsnap & gpcronsnap

This has been a popular utility! It automates the backup of the data volumes in your cluster for extremely fast backups. The backups are executed in parallel and can be used for disaster recovery.

Instead of copying the database to backup files, gpsnap relies on the IaaS disk snapshots to create a consistent backup of your database.

The “cron” version of the utility makes it easier to schedule the execution of gpsnap. The configuration file is in /usr/local/greenplum-cloud/conf.

gprelease & gpcronrelease

This utility automates software upgrades and when upgrades are available, the Message of the Day (the information displayed when you ssh to the master host) gets updated to let you know there is a new version available.

The utility not only upgrades the database but also upgrades dependent packages like MADlib and PostGIS.

Summary

Running Greenplum in the cloud is getting easier and easier because of utilities and automation that is available with Greenplum in the cloud. So be sure to check us out in your favorite cloud provider’s marketplace!

I recently worked with Amazon to publish a blog post covering the Pivotal Greenplum on AWS Marketplace product offerings. Check it out!

https://aws.amazon.com/blogs/apn/pivotal-greenplum-on-aws-parallel-postgres-for-enterprise-analytics-at-scale/

These tools replace the older gpcrondump and gpdbrestore utilities. A great feature for these newer tools is the ability to use S3 directly. This post will give a quick example on how to do this.

First, create a cluster and create a table to demonstrate the backup and restore.

psql -c "create table foo as select i from generate_series(1,1000000) as i distributed by (i);"

Next, create an s3_config.yaml file. You’ll need an Access key ID from AWS as well as the Secret Access key. This file should have the following:

executablepath: $GPHOME/bin/gpbackup_s3_plugin
options: 
  region: <your AWS region>
  aws_access_key_id: <your access key>
  aws_secret_access_key: <your secret access key>
  bucket: <S3 Bucket name>
  folder: <Path to store your backups in S3>

Next, you can execute a backup.

gpbackup --dbname gpadmin --plugin-config /home/gpadmin/s3_config.yaml 

Pretty simple, right?

To restore, you can follow these simple steps. (You will need to update the timestamp value for the backup you wish to restore.)

dropdb gpadmin
gprestore --plugin-config /home/gpadmin/s3_config.yaml --timestamp 20190823142028

Pivotal Greenplum 5.x has two main installers. One is the older “binary” installer and the other is an rpm installer. With Greenplum 6.x, there will only be a single rpm installer. So, you need to get used to installing with rpm. This post will cover installing the Greenplum software on a cluster using rpm.

Step 1

First you need to install the binaries on your Master node. This is pretty simple. Copy the rpm file to you Master node, connect to the Master node as gpadmin and then install the software.

scp -i private_key greenplum-db.rpm gpadmin@master_node:/home/gpadmin/
ssh -i private_key gpadmin@master_node
sudo rpm -i greenplum-db.rpm
sudo chown -R gpadmin:gpadmin /usr/local/greenplum-db*
echo "source /usr/local/greenplum-db/greenplum_path.sh" >> /home/gpadmin/.bashrc
source /usr/local/greenplum-db/greenplum_path.sh

Step 2

Note: This step assumes you have already exchanged keys across your cluster either manually or with gpssh-exkeys.

Copy the rpm file to all other nodes in the cluster and then run the rpm -i command again. You’ll need a file that lists all nodes in the cluster except the master.

for i in $(cat all_hosts_except_master.txt); do scp greenplum-db.rpm gpadmin@$i:/home/gpadmin/; done
gpssh -f all_hosts_except_master.txt "sudo rpm -i /home/gpadmin/greenplum-db.rpm"
gpssh -f all_hosts_except_master.txt "sudo chown -R gpadmin:gpadmin /usr/local/greenplum-db*"

Done

There are lots of ways of achieving this with automation tools but this is one easy way to install the rpm on all nodes in your cluster.

You might have missed the addition of Resource Groups to Greenplum Database version 5 but it is something worth looking at. It is an alternative to Resource Queues for managing resources but because of how it works, it makes the database faster!

Resource Queues prioritize and allocate resources and it does this at the process level in the operating system.

Resource Groups, on the other hand, rely on the OS Kernel to manage and prioritize resources. This alone, removes overhead of managing resources thus making it faster!

Resource Groups have more control over managing resources too so you can better control your resources. So not only is it faster, it gives you greater control over the cluster.

Implementing Resource Groups is easy too.

gpconfig -c gp_resource_manager -v group
gpstop -a 
gpstart -a

More information on Resource Groups are in the Admin Guide: https://gpdb.docs.pivotal.io/5170/admin_guide/workload_mgmt_resgroups.html

Greenplum includes several optional modules from PostgreSQL. One of these is dblink which enables connecting to a remote PostgreSQL or Greenplum database with SQL.

First, run this command as gpadmin:

psql -f /usr/local/greenplum-db/share/postgresql/contrib/dblink.sql

Doc: https://gpdb.docs.pivotal.io/5180/utility_guide/dblink.html

If you are superuser like gpadmin, you can use the connect function to a database in the same cluster without specifying the password like this:

PERFORM dblink_connect('blog_test', 'dbname=blog_db');

So now you can execute some SQL in the other database. It is also in a separate transaction which gives you the ability to have an autonomous transaction inside of a Function.

v_sql := 'CREATE TABLE foo (id int) DISTRIBUTED BY (id)';
PERFORM dblink_exec('blog_test', v_sql, true);
PERFORM dblink_disconnect('pws_test');

If you do all of this as a superuser, the function will execute the dblink successfully but if you grant EXECUTE on the function to a non-superuser, it will fail. Why? Because by default, the Function executes as the user that is executing the Function, not the owner.

The easy fix for this is to use SECURITY DEFINER when creating the function. This means the Function executes as the owner of the Function. You could have all sorts of code in your Function that requires a superuser to execute and grant it to a non-superuser. This includes dblink!

Doc:https://gpdb.docs.pivotal.io/5180/ref_guide/sql_commands/CREATE_FUNCTION.html#topic1

Have fun!

I will be presenting at the Greenplum Summit which is held in New York City March 18-22. I’m really looking forward to the event and speaking with customers. If you attend, be sure to look for me and let me know you use this site!

• March 18-22 in New York City
• Use code 2019_GREENPLUM to get a 25% discount off any pass type when you Register
• Hands On Training Pre-Conference

I recently wrote a blog post for greenplum.org and I thought I would provide the link to it here too.

Master Node
The Greenplum architecture uses a Master node for client connections. The Master also generates and dispatches the query plan to the Segments and then returns results back to the clients. For the most part, the Master node is relatively idle because the data is distributed across multiple segments on other hosts.

Standby-Master
If the Master node were to fail, the database goes offline so to mitigate this risk and provide high-availability, Greenplum has a Standby-Master process that runs on a separate host. Just like the Master, the Standby-Master is usually pretty idle. Sometimes it is also used as an ETL node so you aren’t wasting money by having an extra, idle machine.

In a physical data center, a node replacement may take days so having a physical standby-master is typically a requirement. This isn’t a fault with Greenplum but a fault of the realities in replacing physical hardware.

Cloud
Deployments in the Cloud, however, can replace a bad node in minutes. So do you really need a VM just for the Standby-Master process? Furthermore, what happens when you automate the node replacement so that the cluster “Self-Heals”?

Cloud Marketplaces
In the AWS, Azure, and GCP Marketplaces, Pivotal Greenplum is deployed with a single Master node and the Standby-Master process runs on the first Segment host. If any node in the cluster fails, the cluster Self-Heals. The process is different in AWS versus GCP and Azure but the concept is the same. A new VM is created and the commands to recover the cluster back to 100% are executed automatically.

You get to save money by not having a dedicated Standby-Master node and you still get the high-availability you want with Greenplum.

gpsnap is a utility currently available for Greenplum in AWS and Azure. It automates backup and recovery with AWS EBS snapshots and Azure Disk snapshots. For AWS, you can also copy these snapshots to a different AWS Region which is an ideal solution for Disaster Recovery.

Check it out!

It took a couple of tries but after help from Craig Sylvester, I think I got it now. It now handles both Greenplum version 4.x and 5.x.

Greenplum Skew Post

I recently found out that one of my assumptions in Greenplum was completely wrong! After all of these years, I didn’t understand how pg_stat_activity worked!

I always thought that each row in this system table equated to a single session so, if I wanted to get the number of sessions logged in, I could do this:

select count(*) from pg_stat_activity;

But, this is wrong! One row actually means one server process and not one session.

When a session submits a query, pg_stat_activity may report multiple rows for that single session as it execute the query. Why? A single query can spawn multiple backend processes and these backend processes can show up in pg_stat_activity.

Example – Query 22 from TPC-DS
More Information on TPC-DS

select i_product_name
,i_brand
,i_class
,i_category
,avg(inv_quantity_on_hand) qoh
from inventory
,date_dim
,item
where inv_date_sk=d_date_sk
and inv_item_sk=i_item_sk
and d_month_seq between 1212 and 1212 + 11
group by rollup(i_product_name
,i_brand
,i_class
,i_category)
order by qoh, i_product_name, i_brand, i_class, i_category
limit 100;

First, look at pg_stat_activity and see that I have two sessions open. One is executing the simple query from pg_stat_activity (sess_id=1013) and the other is idle (sess_id=1011).

gpadmin=# select procpid, sess_id, substring(current_query, 1, 20) from pg_stat_activity;
 procpid | sess_id |      substring       
---------+---------+----------------------
   31027 |    1013 | select procpid, sess
   30804 |    1011 | <idle>
(2 rows)

Now, execute Query 22 from sess_id=1011.

gpadmin=# select procpid, sess_id, substring(current_query, 1, 20) from pg_stat_activity;
 procpid | sess_id |      substring       
---------+---------+----------------------
   31027 |    1013 | select procpid, sess
   30804 |    1011 | select i_product_nam
   31137 |    1011 | select i_product_nam
(3 rows)

I now see that sess_id=1011 has started a second process to complete the query. It is still a single database session but pg_stat_activity now shows two rows for this session.

Even after the query is over, I can still see the idle process created for sess_id=1011.

gpadmin=# select procpid, sess_id, substring(current_query, 1, 20) from pg_stat_activity;
 procpid | sess_id |      substring       
---------+---------+----------------------
   31027 |    1013 | select procpid, sess
   30804 |    1011 | <idle>
   31137 |    1011 | <idle>
(3 rows)

So how do you get the number of sessions from pg_stat_activity? With this query:

gpadmin=# select count(*) from (
gpadmin(#     select sess_id from pg_stat_activity group by sess_id
gpadmin(# ) as sub;
 count 
-------
     2
(1 row)

When you create a table in Greenplum, the default storage options make the table heap (appendonly=false) which also means the table is row oriented and not compressed. But, you can change the default with the GUC gp_default_storage_options.

Here are the defaults:

gpadmin=# show gp_default_storage_options;
                            gp_default_storage_options                            
----------------------------------------------------------------------------------
 appendonly=false,blocksize=32768,compresstype=none,checksum=true,orientation=row

And a quick test, shows how you can default to something else.

gpadmin=# set gp_default_storage_options='appendonly=true';                                                                                                                                               SET
gpadmin=# create table foo as select i from generate_series(1, 1000) as i distributed by (i);
SELECT 1000
gpadmin=# \d foo
Append-Only Table "public.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 i      | integer | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (i)

You can make the changes permanent with gpconfig.

gpconfig -c 'gp_default_storage_options' -v 'appendonly=true, orientation=column'

And you can make it limited to a single database.

ALTER DATABASE mytest SET gp_default_storage_options = 'appendonly=true, orientation=column';

gpsnap and gpcronsnap are new utilities for Pivotal Greenplum on AWS. These utilities leverage Amazon EBS Snapshots to take very quick snapshot backups.

EBS Snapshot
Elastic Block Storage (EBS) is the preferred storage in Amazon AWS for many reasons like persistence, performance, and high availability. A great feature of EBS is the ability to take a “Snapshot”. A snapshot can be taken of a disk volume and to get a consistent backup, you need to stop the database, take a snapshot of every volume, and then restart the database.

EBS Restore
The restore process requires stopping the database, detaching and deleting the existing volumes, creating new volumes from the snapshots, attaching the volumes to the right nodes, mounting the new volumes to the right directories, and then starting the database. If you don’t delete the existing volumes and just create new ones, you will incur more storage cost in AWS and you may hit your disk quota. So take a snapshot before you execute a restore so you can revert back if needed.

gpsnap makes this entire process easy. It does all of the tasks needed to take a consistent snapshot of the database. It also automates the tasks for restoring from a snapshot.

The commands include:
– gpsnap list: lists snapshots
– gpsnap create: creates a new snapshot
– gpsnap delete : deletes a specific snapshot
– gpsnap delete all: deletes all snapshots
– gpsnap restore : restores a specific snapshot

gpcronsnap is the second utility and you can probably guess from the name that it is the scheduling portion of the utility. By default, a snapshot backup will be created weekly and retain up to four snapshots. This is all configurable in a configuration file too.

So you can see, it is now a snap for Pivotal Greenplum on AWS to take snapshot backups!

You can either Bring Your Own License (BYOL) or pay Hourly for Greenplum on AWS. Here are the links:
Pivotal Greenplum on AWS (BYOL)
Pivotal Greenplum on AWS (Hourly)

I haven’t posted much here lately because I’ve been working on deploying GemFire and Greenplum in the AWS Marketplace. It has been interesting work so I thought I would make a post about it here.

Amazon Web Services (AWS)
Amazon has the most popular cloud in the US with many products available which now includes Pivotal Greenplum and Pivotal GemFire. Both products are available as Bring Your Own License (BYOL) or Billed Hourly. BYOL simply means you already own a license and you wish to apply this to a deployment in AWS.

If you are not familiar with AWS, there are many ways to configure resources which can be overwhelming to learn and optimize. I have done extensive testing in these product offerings to ensure stability, security, repeatability, and performance. With just a few clicks, you can have an on-demand cluster built that rivals a full rack of on-premise hardware.

Greenplum and GemFire on AWS
Greenplum Billed Hourly
Greenplum Bring Your Own License (BYOL)
GemFire Billed Hourly
GemFire BYOL

S3 is Amazon’s Simple Storage Service which is an inexpensive cloud storage solution and has quickly become a solution for cold data and backups. Greenplum now has External Tables that can read and write data to S3 so you can leverage this popular storage service with Greenplum. Here is how you do it!

Configuration
1. You will need an Amazon account with your Access Key ID and Secret Access Key. If you have the aws CLI installed and configured, just cat ~/.aws/credentials

2. Get your default region. This is in ~/.aws/config

3. You’ll need a bucket in this region and this can be done with the AWS web interface.

4. You’ll need a configuration file. Here is an example (be sure to change the secret and accessid).

[default]
secret = <secret>
accessid = <accesssid>
threadnum = 4
chunksize = 67108864
low_speed_limit = 10240
low_speed_time = 60
encryption = true

5. Copy the configuration to every Segment Host in your cluster.

for i in $(cat segment_hosts.txt); do scp s3_demo.conf $i:/home/gpadmin; done

Writing to S3

1. Create the Writable External Table with the S3 protocol, AWS URL that has the correct region, and the configuration file that is found on every Segment Host.

CREATE WRITABLE EXTERNAL TABLE public.demo_write
(id int, fname text, lname text)
LOCATION ('s3://s3-us-east-1.amazonaws.com/pivotalguru/demo config=/home/gpadmin/s3_demo.conf')
FORMAT 'TEXT' (DELIMITER '|' NULL AS '');

2. Execute an INSERT statement:

INSERT INTO demo_write 
SELECT i, 'Jon_' || i, 'Roberts_' || i
FROM generate_series(1,10000) as i;

Note: Each Segment will create a file in S3 in your bucket with the prefix you specify in the location specified in the Writable External Table. In this demo, each file is prefixed with “demo”. An example filename is “demo9767abbb3.txt”.

Reading from S3
1. Create the External Table with the same location and configuration as before.

CREATE EXTERNAL TABLE public.demo_read
(id int, fname text, lname text)
LOCATION ('s3://s3-us-east-1.amazonaws.com/pivotalguru/demo config=/home/gpadmin/s3_demo.conf')
FORMAT 'TEXT' (DELIMITER '|' NULL AS '');

2. Select the data.

gpadmin=# select * from demo_read limit 10;
 id  |  fname  |    lname    
-----+---------+-------------
  58 | Jon_58  | Roberts_58
  90 | Jon_90  | Roberts_90
 122 | Jon_122 | Roberts_122
 191 | Jon_191 | Roberts_191
 207 | Jon_207 | Roberts_207
 239 | Jon_239 | Roberts_239
 271 | Jon_271 | Roberts_271
 319 | Jon_319 | Roberts_319
 335 | Jon_335 | Roberts_335
 351 | Jon_351 | Roberts_351
(10 rows)

Time: 1139.538 ms

Tips
1. An S3 External Table that references a single file will only use a single Segment to read the data. Instead, try to have at least 1 file per Segment for an S3 External Table.
2. S3 External Tables supports gzip compression only.
3. Use S3 External Tables for cold storage or to create a backup of a table or query using a Writable External Table.

Greenplum has two major types of table storage techniques. The first is inherited from PostgreSQL so it uses Multi-Version Concurrency Control (MVCC) and is referred to as HEAP storage. The second is more efficient technique called Append Optimized (AO). This blog post discusses the performance benefits of AO over HEAP tables.

HEAP
This is the default storage technique when you create a table in Greenplum. It handles UPDATE and DELETE commands just like PostgreSQL in terms of marking the old row invalid and inserting a new record into the table. This hides the deleted or stale row for new queries but provides read consistency to sessions that may have started querying the table prior to the UPDATE or DELETE.

Here is an example of a HEAP table.

CREATE TABLE heap_table
(id int,
 fname text,
 lname text,
 address1 text,
 address2 text,
 city text,
 state text,
 zip text)
DISTRIBUTED BY (id);

But how does this HEAP table store data? How much space does it use?

INSERT INTO heap_table (id, fname, lname, address1, address2, city, state, zip) 
SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text
FROM generate_series(1, 10000) AS i;

ANALYZE heap_table;

SELECT sotdsize
FROM gp_toolkit.gp_size_of_table_disk
WHERE sotdschemaname = 'public'
AND sotdtablename = 'heap_table';

1114112

So it uses 1,114,112 bytes in my small single node test for this generated data of 10,000 records.

AO
AO storage was originally designed to be Append Only but starting with Greenplum version 4.3, these tables became Append Optimized because the tables now allow UPDATE and INSERT like a HEAP table but retain the efficient storage.

Create the same table and data but as an AO table.

CREATE TABLE ao_table
(id int,
 fname text,
 lname text,
 address1 text,
 address2 text,
 city text,
 state text,
 zip text)
WITH (appendonly=true)
DISTRIBUTED BY (id);


INSERT INTO ao_table (id, fname, lname, address1, address2, city, state, zip) 
SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text
FROM generate_series(1, 10000) AS i;

ANALYZE ao_table;

SELECT sotdsize
FROM gp_toolkit.gp_size_of_table_disk
WHERE sotdschemaname = 'public'
AND sotdtablename = 'ao_table';

973016

This is now only using 973,016 bytes or a 12.6% reduction in disk space being used. For large tables, the performance difference will be more noticeable because it will take less time to scan your disks.

AO tables also let you compress it where a HEAP table does not. What does that look like?

CREATE TABLE ao_compressed_table
(id int,
 fname text,
 lname text,
 address1 text,
 address2 text,
 city text,
 state text,
 zip text)
WITH (appendonly=true, compresstype=quicklz)
DISTRIBUTED BY (id);


INSERT INTO ao_compressed_table (id, fname, lname, address1, address2, city, state, zip) 
SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text
FROM generate_series(1, 10000) AS i;

ANALYZE ao_compressed_table;

SELECT sotdsize
FROM gp_toolkit.gp_size_of_table_disk
WHERE sotdschemaname = 'public'
AND sotdtablename = 'ao_compressed_table';

234344

Dropped more to 234,344 bytes. This is a huge decrease in disk space being used and measures at another 75.9% reduction! This is trading CPU cycles for IO scans which in most cases, will provide better query times.

In the last test, make the table also column oriented. This is also a feature only available for AO tables.

CREATE TABLE ao_co_compressed_table
(id int,
 fname text,
 lname text,
 address1 text,
 address2 text,
 city text,
 state text,
 zip text)
WITH (appendonly=true, compresstype=quicklz, orientation=column)
DISTRIBUTED BY (id);


INSERT INTO ao_co_compressed_table (id, fname, lname, address1, address2, city, state, zip) 
SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text
FROM generate_series(1, 10000) AS i;

ANALYZE ao_co_compressed_table;

SELECT sotdsize
FROM gp_toolkit.gp_size_of_table_disk
WHERE sotdschemaname = 'public'
AND sotdtablename = 'ao_co_compressed_table';

196840

It dropped again! This time to 196,840 bytes or another 16%.

The total percentage difference between a HEAP table and the AO table that is column oriented and compressed is 82.33%!

Summary
Use AO tables. These are faster than HEAP tables and also let you compress and column orient your larger tables for even better performance.

Occasionally, I see the request to get the filename added to a file read by gpfdist. Here is a way to do it!

First, create a YML file named “transform_config.yml” with the following:

---
VERSION: 1.0.0.1
TRANSFORMATIONS:
  transformation_input:
     TYPE: input 
     CONTENT: data
     COMMAND: /bin/bash transform.sh

Next, create that “transform.sh” file. This is just a simple example that gets all txt files but you can also pass in a parameter to this script. The filename in the external table gets passed to the script.

#!/bin/bash
set -e
for i in $(ls *.txt); do awk '{print FILENAME"|"$0}' $i; done

Create two test files (test_file_1.txt and test_file_2.txt).

cat test_file_1.txt 
1|foo1
2|foo2
3|foo3
4|foo4
5|foo5
6|foo6
7|foo7
8|foo8
9|foo9
10|foo10

cat test_file_2.txt 
11|foo11
12|foo12
13|foo13
14|foo14
15|foo15
16|foo16
17|foo17
18|foo18
19|foo19
20|foo20

Start gpfdist in the background.

gpfdist -p 8999 -c transform_config.yml > mylog 2>&1 < mylog &

Create the External Table but be sure to change the hostname. Note that "foo" in the LOCATION is the filename. I'm ignoring it for this example but this is how you can pass parameters to the script. You add %filename% to the YML file as the parameter to the script.

CREATE EXTERNAL TABLE ext_transform_table 
(filename text, id int, descrption text) 
LOCATION ('gpfdist://gpdbsne:8999/foo#transform=transformation_input') 
FORMAT 'text' (DELIMITER '|')

Now select from the External Table.

select * from ext_transform_table;
    filename     | id | descrption 
-----------------+----+------------
 test_file_1.txt |  1 | foo1
 test_file_1.txt |  2 | foo2
 test_file_1.txt |  3 | foo3
 test_file_1.txt |  4 | foo4
 test_file_1.txt |  5 | foo5
 test_file_1.txt |  6 | foo6
 test_file_1.txt |  7 | foo7
 test_file_1.txt |  8 | foo8
 test_file_1.txt |  9 | foo9
 test_file_1.txt | 10 | foo10
 test_file_2.txt | 11 | foo11
 test_file_2.txt | 12 | foo12
 test_file_2.txt | 13 | foo13
 test_file_2.txt | 14 | foo14
 test_file_2.txt | 15 | foo15
 test_file_2.txt | 16 | foo16
 test_file_2.txt | 17 | foo17
 test_file_2.txt | 18 | foo18
 test_file_2.txt | 19 | foo19
 test_file_2.txt | 20 | foo20
(20 rows)

Step 1
Download a CentOS 6 VM from http://virtual-machine.org/.

Step 2
Download the latest Greenplum binaries for RedHat Enterprise Linux 6 from http://network.pivotal.io

Step 3
Start the Virtual Machine with VMWare Fusion or something similar.
Memory: 8GB
Cores: 4
Disk: 50GB
You can use less memory and cores but the more you provide the VM, the better it will perform. You might have to expand the VM disk space when using the VirtualMachine.org VM.

Step 4
Configure the operating system.

hostname gpdbsne

Add the hostname to /etc/sysconfig/network too.

Turn off firewalls.

chkconfig iptables off
service iptables stop
echo 0 >/selinux/enforce
vi /etc/selinux/config
SELINUX=disabled
setenforce 0

Edit the /etc/hosts file.

echo "127.0.0.1 gpdbsne gpdbsne.localdomain" >> /etc/hosts

I also like to get the ip address for this host and add it to my local /etc/hosts file.

ifconfig

Install unzip, ed, ntp and ssh.

yum install ntp
yum install unzip
yum install openssh-clients
yum install ed
chkconfig ntpd on
ntpdate pool.ntp.org
/etc/init.d/ntpd start

Add the following to the end of your /etc/sysctl.conf file.

kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 250 512000 100 2048
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.overcommit_memory = 0

Add this to your /etc/security/limits.conf file.

* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

Remove all lines from /etc/security/limits.d/90-nproc.conf

echo "" > /etc/security/limits.d/90-nproc.conf

There are some other configuration changes you make on a real cluster that involves XFS filesystem but for a SNE, this can be skipped. This is just intended for Development and Testing purposes.

Restart the VM so these changes take affect.

shutdown -r now

Step 5
Copy the installer to the VM.

scp greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.zip root@gpdbsne:/root/

Step 6
ssh to the VM and run the installer.

ssh root@gpdbsne
unzip greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.zip
/bin/bash greenplum-db-4.3.6.2-build-1-RHEL5-x86_64.bin

--Accept the license agreement
--Accept default installation directory

Step 7
For a multi-node cluster, the next step is to use gpseginstall but this isn’t needed with a single node installation. Instead, you have to manually create the gpadmin account and get the cluster ready for the next step.

useradd gpadmin
passwd gpadmin
chown -R gpadmin:gpadmin /usr/local/greenplum-db-4.3.6.2/
mkdir -p /data/master
mkdir /data/primary
chown -R gpadmin:gpadmin /data
su - gpadmin
echo "source /usr/local/greenplum-db/greenplum_path.sh" >> .bashrc
echo "export MASTER_DATA_DIRECTORY=/data/master/gpseg-1" >> .bashrc
source .bashrc
echo "gpdbsne" > hostfile
gpssh-exkeys -f hostfile

Step 8
Create an initialize file called gp_init_config so you can initialize the database.

ARRAY_NAME="Greenplum"
MACHINE_LIST_FILE=./hostfile
SEG_PREFIX=gpseg
PORT_BASE=50000
declare -a DATA_DIRECTORY=(/data/primary /data/primary )
MASTER_HOSTNAME=gpdbsne
MASTER_DIRECTORY=/data/master
MASTER_PORT=5432
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE

Step 9
Initialize the database.

gpinitsystem -c ~/gp_init_config
--select Y to continue

Now create the default database and configure the database to allow external connections.

psql -c "create database gpadmin" template1
psql -c "alter user gpadmin password 'changeme'"
echo "host all all 0.0.0.0/0 md5" >> /data/master/gpseg-1/pg_hba.conf
gpstop -u

Complete!
Now you can connect to Greenplum with the gpadmin account and the password is changeme. The default port is 5432. A great client tool is pgAdmin III v1.14.3.
http://www.postgresql.org/ftp/pgadmin3/release/v1.14.3/

It is official! Greenplum is now open source!
http://greenplum.org/

Download
Documentation

There are lots of new features in this maintenance release of Greenplum database.

1. External Tables can now read Parquet and Avro files in HDFS.
2. Partitioned Tables can now have External Partitions. This would be useful to have a Greenplum table with some data residing external to the database in something like Hadoop.
3. More enhancements to the Query Optimizer.
4. Native connection pooling.
5. COPY commands can be controlled by Resource Queues.
6. Utility analyzedb can now analyze catalog tables.
7. Utility gptransfer has enhancements.
8. Improvements in resynchronization of segment mirrors.
9. Utilities gpmigrator and gpmigrator_mirror have enhancements.
10. Improvements in gpsupport utility.

Note: This post is for Greenplum only and I will make a subsequent post for HAWQ.

What Is Analyze?
Analyze is a pretty simple command that gets statistics on tables so the cost based optimizer can make the best plan possible. Greenplum has a mechanism to do this automatically for you but in some circumstances it won’t so you will have to run it manually.

gp_autostats_mode=on_no_stats
This is the default which simply means the database will automatically gather stats when data is inserted into the table for the first time. When you do a Create Table As Select (CTAS), it will gather the statics for you. If you create a table and then INSERT data, it will gather stats for you. But, if you INSERT data to a table that already has stats, Greenplum will not gather stats for you again.

Example

CREATE TABLE foo (id int, fname text) DISTRIBUTED BY (id);
INSERT INTO foo VALUES (1, 'jon');
INSERT INTO foo select i, 'jon_' || i from generate_series(1,1000000) as i;

In the above example, the first INSERT statement will cause the database to gather the stats and it will record that foo only has 1 row in it. The next INSERT of 1 million records won’t trigger another ANALYZE. Do you see where you could get into trouble? You need to run ANALYZE on foo.

ANALYZE foo;

gp_autostats_mode=on_change
This sounds like a good idea. If you insert more data into a table, it will automatically gather stats for you. But, there is another GUC that is important here. gp_autostats_on_change_threshold determines how many rows need to be added to a table before an ANALYZE is run automatically again. The default value is 2,147,483,647!

So in our first example of adding 1 million rows to an existing table, ANALYZE will not run automatically if we were to set gp_autostats_mode=none.

If you want to use on_change, I suggest reducing the threshold to a lower number.

gp_autostats_mode=none
This is the last value you can set for this GUC and it does exactly what is says. Nothing.

Partitioned Tables
If you insert data into an empty partitioned table, an ANALYZE will never automatically be executed! You will have to execute ANALYZE manually.

This is really important. Inserting data into partitioned tables will not cause an ANALYZE to be executed. The stats will be incorrect and you will need to run ANALYZE on the table.

Root Partition
The new Query Optimizer, code named Orca, must have statistics on the “rootpartition”. This simply is a rollup of all of the statistics from each partition at the table level.

When enabling Orca, you will need to add this which is documented in this blog post.:

gpconfig -c optimizer_analyze_root_partition -v on --masteronly

This will tell Greenplum to gather the table level statistics on a table when you ANALYZE it. If you don’t do this, the table will appear to be empty to Orca and it will most certainly have a poor query plan.

You can also run ANALYZE on just the rootpartition.

ANALYZE ROOTPARTITION foo;

Please Make this Easy! analyzedb
We did! There is a new utility called “analyzedb” which tracks what tables and partitions need to be analyzed and does it for you automatically. It also does the work in parallel so it completes the task much, much faster.

You can specify an entire database:

analyzedb -d gpdb

single schema:

analyzedb -d gpdb -s myschema

or a single table.

analyzedb -d gpdb -t public.foo

analyzedb is the the program to add to your maintenance scripts to ensure you have the correct statistics in the database. It is another example of the continual development and enhancements of Greenplum database.

Greenplum Database has a feature called “incremental” backup but really it is a incremental differential backup. In this post, I will explain how it works and give you a working example.

AO Tables
In version 4.2 and earlier, AO tables are Append Only but starting in 4.3, the tables are Append Optimized. Everyone using 4.3 should be using AO tables as it uses less space, supports this incremental feature, and executes faster. Incremental backup requires AO tables.

Example:

CREATE TABLE foo (id integer, bar text) WITH (appendonly=true) DISTRIBUTED BY (id);

Using the incremental option on a regular heap table will not make any difference. It will backup all heap tables with every backup.

Demo

First create a database.

gpdb=# drop database poc;
DROP DATABASE
gpdb=# CREATE DATABASE poc;
CREATE DATABASE
gpdb=# \c poc
You are now connected to database "poc" as user "gpadmin".

Now create an AO table and insert some data.

poc=# CREATE TABLE foo (id integer, bar text) WITH (appendonly=true) DISTRIBUTED BY (id);
CREATE TABLE
poc=# INSERT INTO foo VALUES (1, 'bar_1');
INSERT 0 1
poc=# INSERT INTO foo VALUES (2, 'bar_2');
INSERT 0 1
poc=# 

I’m next going to execute a full backup. For the demo, I’m going to turn off compression so it is easier to view the backup files.

I’m using gpcrondump with these switches:
-x specifies the database to backup
-a means automatic and don’t prompt
-z turns off compression

bigmac:~ gpadmin$ gpcrondump -x poc -a -z
20140909:13:39:19:006156 gpcrondump:bigmac:gpadmin-[INFO]:-Starting gpcrondump with args: -x poc -a -z
...

Just to see what is in the backup file, I’m going to cat one of the segment’s backup files. It shows one of the two records in this tiny table.

bigmac:~ gpadmin$ cat /data/primary/gpseg0/db_dumps/20140909/gp_dump_0_2_20140909133919 
--
-- Greenplum Database database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_with_oids = false;

--
-- Data for Name: foo; Type: TABLE DATA; Schema: public; Owner: gpadmin
--

COPY foo (id, bar) FROM stdin;
1	bar_1
\.


--
-- Greenplum Database database dump complete
--

Now I immediately run an incremental backup. Nothing changed in my database so it should be an empty backup set.

bigmac:~ gpadmin$ gpcrondump -x poc -a -z --incremental
20140909:13:41:19:006395 gpcrondump:bigmac:gpadmin-[INFO]:-Starting gpcrondump with args: -x poc -a -z --incremental
...

To verify:

bigmac:~ gpadmin$ cat /data/primary/gpseg0/db_dumps/20140909/gp_dump_0_2_20140909134119 
--
-- Greenplum Database database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Greenplum Database database dump complete
--

So my incremental backup doesn’t have any data in it. If I had deleted or inserted any data to my one table, the entire table would be part of the backup set. That is why it is an incremental differential backup. If there is a difference in the AO table (or partition), the entire table/partition will be included in the incremental backup.

Restore
So now that I have one full backup and one incremental, let’s drop the table and then restore it.

bigmac:~ gpadmin$ psql poc
psql (8.2.15)
Type "help" for help.

poc=# DROP TABLE foo;
DROP TABLE
poc=# \q

And now the restore using gpdbrestore. I’m using the following switches:
-a means automatic and don’t prompt
-e will drop the target database and then recreate it
-s uses the latest backup set

bigmac:~ gpadmin$ gpdbrestore -a -e -s poc
20140909:13:42:58:006720 gpdbrestore:bigmac:gpadmin-[INFO]:-Starting gpdbrestore with args: -a -e -s poc
...

Verify the restore worked:

bigmac:~ gpadmin$ psql poc
psql (8.2.15)
Type "help" for help.

poc=# SELECT * FROM foo;
 id |  bar  
----+-------
  1 | bar_1
  2 | bar_2
(2 rows)

poc=# 

Conclusion
Both commands used here, gpcrondump and gpdbrestore, have lots of options to backup and restore respectively. Using the incremental switch on gpcrondump will save you disk space and execution time while still giving you the ability to do a full system restore or even a selective table restore.