[Yandex Cloud documentation](../../../../index.md) > [Yandex Data Transfer](../../../index.md) > [Step-by-step guides](../../index.md) > [Configuring endpoints](../index.md) > Greenplum® > Source

# Transferring data from a Greenplum® source endpoint


Yandex Data Transfer enables you to migrate data from a Greenplum® database and implement various data transfer, processing, and transformation scenarios. To implement a transfer:

1. [Explore possible data transfer scenarios](#scenarios).
1. [Prepare the Greenplum®](#prepare) database for the transfer.
1. [Set up a source endpoint](#endpoint-settings) in Yandex Data Transfer.
1. [Set up one of the supported data targets](#supported-targets).
1. [Create](../../transfer.md#create) a transfer and [start](../../transfer.md#activate) it.
1. Perform required operations with the database and [control the transfer](../../monitoring.md).
1. In case of any issues, [use ready-made solutions](../../../troubleshooting/index.md) to resolve them.

## Scenarios for transferring data from Greenplum® {#scenarios}

1. Migration: Moving data from one storage to another. Migration often means migrating a database from obsolete local databases to managed cloud ones.

    * [Migrating a Greenplum® cluster](../../../tutorials/managed-greenplum.md).

1. Uploading data to data marts is a process of transferring prepared data to storage for subsequent visualization.

    * [Loading data from Greenplum® to the ClickHouse® data mart](../../../tutorials/greenplum-to-clickhouse.md).
    * [Loading data from Greenplum® to the PostgreSQL data mart](../../../tutorials/greenplum-to-postgresql.md).

For a detailed description of possible Yandex Data Transfer scenarios, see [Tutorials](../../../tutorials/index.md).

## Preparing the Greenplum® database {#prepare}

{% note info %}

Data stored in a `MATERIALIZED VIEW` is not transferred. To transfer `MATERIALIZED VIEW` data, create an ordinary `VIEW` that refers to the `MATERIALIZED VIEW` to be transferred.

{% endnote %}

{% list tabs %}

- Yandex MPP Analytics for PostgreSQL

    
    1. Create a user account the transfer will use to connect to the source. To do this, run the following command:
    
        ```pgsql
        CREATE ROLE <username> LOGIN ENCRYPTED PASSWORD '<password>';
        ```

    1. Configure the source cluster to enable the user you created to connect to all the cluster [master hosts](../../../../managed-greenplum/concepts/index.md).
    
    1. If you are going to use [parallel copy](../../../concepts/sharded.md), configure the source cluster to enable the user you created to connect to all the cluster's [segment hosts](../../../../managed-greenplum/concepts/index.md) in utility mode. To do this, make sure that the "Access from Data Transfer" setting is enabled for the cluster.
    
    1. Grant the user you created the `SELECT` privilege for the tables to transfer and the `USAGE` privilege for the schemas these tables are in.
    
       Privileges must be granted to entire tables. Access to certain table columns only is not supported.
    
       Tables without the required privileges are unavailable to Data Transfer. These tables are processed as if they did not exist.
    
       This example issues privileges to all the tables in the selected schema:
    
        ```pgsql
        GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
        GRANT USAGE ON SCHEMA <schema_name> TO <username>;
        ```

- Greenplum®
    
    1. If not planning to use [Cloud Interconnect](../../../../interconnect/concepts/index.md) or [VPN](https://en.wikipedia.org/wiki/Virtual_private_network) for connections to an external cluster, make such cluster accessible from the Internet from [IP addresses used by Data Transfer](../../../../overview/concepts/public-ips.md#virtual-private-cloud).
       
       For details on linking your network up with external resources, see [this concept](../../../concepts/network.md#source-external).
    
    1. Create a user account the transfer will use to connect to the source. To do this, run the following command:
    
        ```pgsql
        CREATE ROLE <username> LOGIN ENCRYPTED PASSWORD '<password>';
        ```

    1. Configure the source cluster to enable the user you created to connect to all the cluster [master hosts](../../../../managed-greenplum/concepts/index.md).
    
    1. If you are going to use [parallel copy](../../../concepts/sharded.md), configure the source cluster to enable the user you created to connect to all the cluster's [segment hosts](../../../../managed-greenplum/concepts/index.md) in utility mode.
    
    1. Grant the user you created the `SELECT` privilege for the tables to transfer and the `USAGE` privilege for the schemas these tables are in.
    
       Privileges must be granted to entire tables. Access to certain table columns only is not supported.
    
       Tables without the required privileges are unavailable to Data Transfer. These tables are processed as if they did not exist.
    
       This example grants privileges to all the database tables:
    
        ```pgsql
        GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
        GRANT USAGE ON SCHEMA <schema_name> TO <username>;
        ```

{% endlist %}

Data Transfer works with Greenplum® differently depending on the transfer configuration and the source cluster contents. Detailed information is available in the section on [Greenplum® source endpoint settings](greenplum.md).

## Configuring the Greenplum® source endpoint {#endpoint-settings}

When [creating](../index.md#create) or [updating](../index.md#update) an endpoint, you can define:

* [Yandex MPP Analytics for PostgreSQL cluster](#managed-service) connection or [custom installation](#on-premise) settings, including those based on Yandex Compute Cloud VMs. These are required parameters.
* [Additional parameters](#additional-settings).


### Yandex MPP Analytics for PostgreSQL cluster {#managed-service}


{% note warning %}

To create or edit an endpoint of a managed database, you will need the [`managed-greenplum.viewer`](../../../../managed-greenplum/security/index.md#mgp-viewer) role or the primitive [`viewer`](../../../../iam/roles-reference.md#viewer) role for the folder the cluster of this managed database resides in.

{% endnote %}


Connection to the database with the cluster specified in Yandex Cloud.

{% list tabs group=instructions %}

- Management console {#console}

    * **Connection type**: Select a database connection option:
    
      * **Self-managed**: Allows you to specify connection settings manually.
    
        Select **Managed Service for Greenplum cluster** as the installation type and configure these settings:
    
          * **Cluster**: Select the cluster to connect to.
          * **Database**: Specify the name of the database in the selected cluster.      
          * **User**: Specify the username Data Transfer will use to connect to the database.
          * **Password**: Enter the user password for access to the database.
    
      * **Connection Manager**: Allows connecting to the cluster via [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md):
    
        * Select the folder with the Yandex MPP Analytics for PostgreSQL cluster.
        * Select **Managed DB cluster** as the installation type and configure these settings:
    
          * **Cluster for Managed DB**: Select the cluster to connect to.      
          * **Connection**: Select or create a connection in Connection Manager.
          * **Database**: Specify the name of the database in the selected cluster.
    
        {% note warning %}
        
        To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher.
        
        {% endnote %}
    
    * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic.
    
      Thus, you will be able to apply the specified security group rules to the VMs and clusters in the selected network without changing the settings of these VMs and clusters. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).

{% endlist %}


### Custom installation {#on-premise}

Connection to the database with explicitly specified network addresses and ports.

{% list tabs group=instructions %}

- Management console {#console}

    * **Connection type**: Select a database connection option:
    
        * **Self-managed**: Allows you to specify connection settings manually:
    
          * **Coordinator host**: Specify the IP or FQDN of the primary master host to connect to.
          * **Coordinator port**: Specify the port for Data Transfer to use to connect to the primary master host.
          * **Coordinator mirror host**: Specify the IP address or FQDN of the standby master host to connect to (leave the field empty if your cluster only has one master host).
          * **Coordinator mirror port**: Specify the port for Data Transfer to use to connect to the standby master host (leave the field empty if there is only one master host in your cluster).
          * **Cluster Segments**: Specify segment host connection information. If you omit these, segment host addresses will be retrieved automatically from the master host housekeeping table.
          * **CA certificate**: Upload the [certificate](../../../../managed-greenplum/operations/connect/index.md#get-ssl-cert) file or add its contents as text if data encryption is required, e.g., to comply with the PCI DSS requirements.
            
            
            {% note warning %}
            
            If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
            
            {% endnote %}
          
          * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the required [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the database.
    
            If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone.
    
          * **Database**: Enter the database name.
          * **User**: Specify the username Data Transfer will use to connect to the database.
          * **Password**: Enter the user password for access to the database.
    
      * **Connection Manager**: Allows connecting to a database using [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md):
    
          * Select the folder where the Connection Manager connection was created.
          * Select **Custom installation** as the installation type and configure these settings:
    
            * **Connection**: Select or create a connection in Connection Manager.
            * **Database**: Specify the database name in the custom installation.
            *  **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the required [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the database.
    
                If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone.
    
            {% note warning %}
            
            To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher.
            
            {% endnote %}        
    
    * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic.
    
      This will allow you to apply the specified security group rules to VMs and DBs in the selected network without reconfiguring these VMs and DBs. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).

{% endlist %}

### Table filter {#filter-settings}

{% list tabs group=instructions %}

- Management console {#console}

    * **Included tables**: Only data from the tables listed here will be transferred.

      If a table is partitioned, you can use this field to specify both the entire table and individual partitions.

      Make sure that, for tables to be included in the list, [all the necessary privileges are granted](../../prepare.md#source-gp) to the user to perform the data transfer.

      When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update).

    * **Excluded tables**: Data from the listed tables is not transferred.

      If a table is partitioned, to exclude it from the list, make sure to list all of its partitions.

      The lists include the name of the [schema](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7/greenplum-database/admin_guide-ddl-ddl-schema.html) that describes the DB contents, structure, and integrity constraints, as well as the table name. Both lists support expressions in the following format:

      * `<schema_name>.<table_name>`: Full table name.
      * `<schema_name>.*`: All tables in the specified schema.
      * `<table_name>`: Table in the default schema.

      Included and excluded table names must meet the ID naming rules in Greenplum®. Double quotes within a table name are not supported. Outer quotes are only used as delimiters and will be deleted when processing paths.

{% endlist %}

### Advanced settings {#additional-settings}

{% list tabs group=instructions %}

- Management console {#console}

    * **Snapshot consistency**: When enabled, Data Transfer will apply additional steps to the source to assure [snapshot consistency](#snapshot-consistency).

    * **Service object schema**: Schema for placing auxiliary objects of the transfer.

      The schema name must meet the ID naming rules in Greenplum®. Double quotes are not supported in schema names.

    * **Use direct reading from segments**: Disables [`gpfdist`](../../../../managed-greenplum/operations/gpfdist/connect.md) for transfers from Greenplum® to Greenplum®.

{% endlist %}

### Specifics of working with the Greenplum source {#advanced}

Data Transfer supports only Greenplum® versions 6.28 and above. Other Greenplum® versions are not supported.

The service performs operations with a Greenplum® cluster in transactions with the `READ COMMITTED` [level of isolation](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7/greenplum-database/ref_guide-sql_commands-SET_TRANSACTION.html).

Data Transfer supports operation with [parallel copy](../../../concepts/sharded.md) enabled for a Greenplum® source.

During operation with parallel copy enabled, Data Transfer maintains an open transaction on the Greenplum® master host. If this transaction is interrupted, a transfer will return an error.

With parallel copy disabled, a transfer will move data from these Greenplum® objects: `TABLE`, `VIEW`, `FOREIGN TABLE`, and `EXTERNAL TABLE`. Data from these objects will be treated as data from ordinary tables and processed by the target accordingly. With parallel copy enabled, a transfer will only move tables (`TABLE` objects). However, tables with the `DISTRIBUTED REPLICATED` [allocation policy](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7/greenplum-database/ref_guide-sql_commands-CREATE_TABLE.html) will not be transferred.

If a Greenplum®-to-Greenplum® transfer does not use direct reads from segments, the number of threads cannot exceed the minimum number of segments in the participating clusters.

You can check the number of segments in the [management console](https://console.yandex.cloud) or by running an SQL query:

```pgsql
SELECT COUNT(*) FROM gp_segment_configuration WHERE role='p' AND content >= 0;
```

The number of workers participating in the transfer is limited to a specified number of threads. Each worker transfers tables one by one, with each table only transferred by one worker.

### Snapshot consistency {#snapshot-consistency}

When you start a transfer with parallel copy off (default), the service interacts only with the Greenplum® cluster's [master host](../../../../managed-greenplum/concepts/index.md) when copying data. The tables being copied are accessed in `ACCESS SHARE` [lock mode](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7/greenplum-database/ref_guide-sql_commands-LOCK.html). Snapshot consistency is achieved through Greenplum® mechanisms.

When you start a transfer with parallel copy on, the service interacts with both the master host and the Greenplum® cluster's [segment hosts](../../../../managed-greenplum/concepts/index.md) in utility mode. The tables being copied are accessed in `ACCESS SHARE` or `SHARE` lock mode, depending on the snapshot consistency setting.

To guarantee snapshot consistency, a transfer with parallel copy on has to ensure the data in the tables remains static. With `ACCESS SHARE` (default), the service is unable to guarantee that the data will remain static, so this must be ensured externally. With `SHARE`, the Greenplum® mechanisms are there to guarantee that data in the source tables remains static.

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


## Configuring the data target {#supported-targets}

Configure one of the supported data targets:

* [PostgreSQL](../target/postgresql.md)
* [ClickHouse®](../target/clickhouse.md)
* [Greenplum®](../target/greenplum.md)
* [YTsaurus](yt.md)

For a complete list of supported sources and targets in Yandex Data Transfer, see [Available transfers](../../../transfer-matrix.md).

After configuring the data source and target, [create and start the transfer](../../transfer.md#create).