Exporting Greenplum® data to a cold storage in Yandex Object Storage

In a Yandex Managed Service for 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 from such tables in the 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 the 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 the cluster storage.

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

Getting startedGetting started

1. Create a Managed Service for 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. Check that 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 VMware, Inc. in the United States and/or other countries.