Exporting Greenplum® data to cold storage in Yandex Object Storage

In a Greenplum® cluster, you can enable hybrid storage for append-optimized (AO) and append-optimized column-oriented (AOCO) tables. With this done, the Yezzey extension can transfer data in such tables from a cluster storage to a cold storage.

Cold storage is a convenient option if you need to store your data for a long time without using it much. This will make data storage less costly.

To transfer your data from a cluster storage to a cold storage:

1. Export the Greenplum® table to a cold storage.
2. Check the result.

You can also transfer your data back to a cluster storage.

If you no longer need the resources you created, delete them.

Required paid resourcesRequired paid resources

The support cost for this solution includes:

• Greenplum® cluster fee: Using computing resources allocated to hosts and disk space (see Greenplum® pricing).
• Fee for public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Getting startedGetting started

1. Create a Greenplum® cluster. When creating a cluster, make sure to enable Hybrid storage.

   Note

   You cannot disable this option after you save your cluster settings.

2. Get an SSL certificate to connect to the Greenplum® database:

   Linux (Bash)/macOS (Zsh)

   Windows (PowerShell)

   mkdir -p ~/.postgresql && \
   wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
        --output-document ~/.postgresql/root.crt && \
   chmod 0655 ~/.postgresql/root.crt
   

   The certificate will be saved to the ~/.postgresql/root.crt file.

   mkdir $HOME\.postgresql; curl.exe -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem
   

   The certificate will be saved to the $HOME\.postgresql\root.crt file.

   Corporate policies and antivirus software can block the download of certificates. For more information, see FAQ.

   To use graphical IDEs, save a certificate to a local folder and specify the path to it in the connection settings.

Export the Greenplum® table to a cold storageExport the Greenplum® table to a cold storage

1. Connect to the cluster:

   psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \
         port=6432 \
         sslmode=verify-full \
         dbname=postgres \
         user=<username> \
         target_session_attrs=read-write"
   

2. Create a database named db_with_yezzey:

   CREATE DATABASE db_with_yezzey;
   

   The new DB will be granted permissions to install extensions. These permissions are not granted for the default DB.

3. Connect to the new DB:

   \connect db_with_yezzey
   

4. Create a Yezzey extension:

   CREATE EXTENSION yezzey;
   

5. Create an AO table named ao_table:

   CREATE TABLE ao_table (a int)
       WITH (appendoptimized=true)
       DISTRIBUTED BY (a);
   

6. Populate the table with a series of integers from 1 through 10,000:

   INSERT INTO ao_table SELECT * FROM GENERATE_SERIES(1, 10000);
   

7. Transfer the ao_table data to a cold storage:

   SELECT yezzey_define_offload_policy('ao_table');
   

Check the resultCheck the result

1. Check how much of the cluster local cache and cold storage is used by:

   • ao_table:

     SELECT * FROM yezzey_offload_relation_status('ao_table');
     

   • Each segment file in ao_table:

     SELECT * FROM yezzey_offload_relation_status_per_filesegment('ao_table');
     

   If there are non-zero values in each external_bytes column in the command output, the table was successfully transferred to a cold storage.

2. Check which table segment files are now in the cold storage:

   SELECT * FROM yezzey_relation_describe_external_storage_structure('ao_table');
   

3. Make sure you can read data from the transferred table:

   SELECT AVG(a) FROM ao_table;
   

   Result:

             avg          
   -----------------------
    5000.5000000000000000
   (1 row)
   

4. Make sure you can write data to the transferred table:

   1. Add a series of integers 1 through 10,000 to ao_table:

      INSERT INTO ao_table SELECT * FROM GENERATE_SERIES(1, 10000);
      

   2. Make sure the number of rows has doubled:

      SELECT COUNT(1) FROM ao_table;
      

      Result:

       count
      -------
       20000
      (1 row)
      

Move the table from the cold storage to the cluster storageMove the table from the cold storage to the cluster storage

To move ao_table from the cold storage back to the cluster storage, run this command:

SELECT yezzey_load_relation('ao_table');

Delete the resources you createdDelete the resources you created

If you no longer need the cluster you created, delete it.

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.