VMware Tanzu Greenplum

https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/
Admin
VMware Tanzu Greenplum is a massively parallel processing SQL database built on PostgreSQL for...
Tokens:336,669
Snippets:2,972
Trust Score:9.9
Update:1 year ago
Show doc for...
Tokens:
Raw
### gpmemreport Example: Extracting Reports from a Specific Start Time

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpmemreport

Illustrates how to use the `--start` option with `gpmemreport` to generate reports for data collected after a specified timestamp. The example shows the command and the resulting file listing, filtered by the start time.

```bash
$ gpmemreport mdw.ps.out.gz --start='2021-11-19 15:38:00'
>>>21:11:19:15:37:18<<<

>>>21:11:19:15:38:18<<<

>>>21:11:19:15:39:18<<<

```

```bash
$ ls -thrl
-rw-rw-r--. 1 gpadmin gpadmin 1.2K Nov 19 15:50 20211119-153818
-rw-rw-r--. 1 gpadmin gpadmin 1.2K Nov 19 15:50 20211119-153918

```

--------------------------------

### Start Greenplum Master Instance Only

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpstart

This example demonstrates how to start only the Greenplum master instance, useful for maintenance or specific utility connections. It also shows how to connect to the master in utility mode using `psql`.

```bash
gpstart -m 
PGOPTIONS='-c gp_session_role=utility' psql
```

--------------------------------

### Get gpinitsystem Version or Help

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpinitsystem

Displays the version of the gpinitsystem utility or provides help information about its usage and options. This is useful for verifying the installed version or understanding command syntax.

```bash
gpinitsystem -v | --version
```

```bash
gpinitsystem -? | --help
```

--------------------------------

### Example gpmovemirrors Input File (Text)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/best_practices-ha

An example input file for the `gpmovemirrors` utility, demonstrating how to specify three mirror segments to be relocated. This file guides the utility in moving mirrors to their new block mirroring locations.

```text
sdw2|50001|/data2/mirror/gpseg1 sdw3|50001|/data/mirror/gpseg1
sdw2|50001|/data2/mirror/gpseg2 sdw4|50001|/data/mirror/gpseg2
sdw3|50001|/data2/mirror/gpseg3 sdw1|50001|/data/mirror/gpseg3

```

--------------------------------

### Start gpfdist serving files in the background

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpfdist

This example shows how to start the gpfdist utility to serve files from a specified directory on a given port. The '&' symbol indicates that the process should run in the background.

```bash
gpfdist -d /var/load_files -p 8081 &
```

--------------------------------

### Start gpfdist with logging in the background

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpfdist

This example illustrates starting the gpfdist utility in the background, serving files from a directory on a specific port, and redirecting both standard output and error streams to a specified log file.

```bash
gpfdist -d /var/load_files -p 8081 -l /home/gpadmin/log &
```

--------------------------------

### Install Greenplum RPM to Custom Directory

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Installs the Greenplum Database RPM package to a specified directory using the `rpm` command with the `--prefix` option. This method requires manual installation of dependencies. Replace `<version>`, `<platform>`, and `<directory>` with your specific values.

```bash
$ sudo rpm --install ./greenplum-db-<version>-<platform>.rpm --prefix=<directory>

```

--------------------------------

### Example Database Schema and Data Setup

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-view-find-depend

This SQL script sets up example database objects, including tables, functions, and views, to demonstrate the behavior of the queries provided. It defines a base table 't1', a function 'f()', and several views ('v1', 'v2', 'v3', 'v5'). It also creates a schema 'mytest' with its own table 'tm1' and views ('vm1', 'vm2'), including views that reference objects in other schemas.

```sql
CREATE TABLE t1 (
   id integer PRIMARY KEY,
   val text NOT NULL
);

INSERT INTO t1 VALUES
   (1, 'one'), (2, 'two'), (3, 'three');

CREATE FUNCTION f() RETURNS text
   LANGUAGE sql AS 'SELECT ''suffix''::text';

CREATE VIEW v1 AS
  SELECT max(id) AS id
  FROM t1;
 
CREATE VIEW v2 AS
  SELECT t1.val
  FROM t1 JOIN v1 USING (id);
 
CREATE VIEW v3 AS
  SELECT val || f()
  FROM t1;

CREATE VIEW v5 AS
  SELECT f() ;

CREATE SCHEMA mytest ;

CREATE TABLE mytest.tm1 (
   id integer PRIMARY KEY,
   val text NOT NULL
);

INSERT INTO mytest.tm1 VALUES
   (1, 'one'), (2, 'two'), (3, 'three');

CREATE VIEW vm1 AS
  SELECT id FROM mytest.tm1 WHERE id < 3 ;

CREATE VIEW mytest.vm1 AS
  SELECT id FROM public.t1 WHERE id < 3 ;

CREATE VIEW vm2 AS
  SELECT max(id) AS id
  FROM mytest.tm1;

CREATE VIEW mytest.v2a AS
  SELECT t1.val
  FROM public.t1 JOIN public.v1 USING (id);


```

--------------------------------

### PL/Container Command Element Examples

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-plcontainer-configuration

Examples of the 'command' element within a PL/Container 'runtime' configuration, showing variations for Python 2, Python 3, and R languages. This element specifies the command executed within the container to start the client process.

```xml
<command>/clientdir/pyclient.sh</command>
```

```xml
<command>/clientdir/pyclient3.sh</command>
```

```xml
<command>/clientdir/rclient.sh</command>
```

--------------------------------

### Start Greenplum Database System

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpstart

This snippet shows the basic command to start a Greenplum Database system. It is the most common way to bring the entire cluster online.

```bash
gpstart
```

--------------------------------

### Greenplum Load Control File Example (YAML)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpload

Defines the parameters for a Greenplum data load job, including input source, data format, output table, and pre/post-load SQL commands. This file guides the 'gpload' utility during execution.

```yaml
---
VERSION: 1.0.0.1
DATABASE: ops
USER: gpadmin
HOST: mdw-1
PORT: 5432
GPLOAD:
   INPUT:
    - SOURCE:
         LOCAL_HOSTNAME:
           - etl1-1
           - etl1-2
           - etl1-3
           - etl1-4
         PORT: 8081
         FILE:
           - /var/load/data/*
    - COLUMNS:
           - name: text
           - amount: float4
           - category: text
           - descr: text
           - date: date
    - FORMAT: text
    - DELIMITER: '|'
    - ERROR_LIMIT: 25
    - LOG_ERRORS: true
   OUTPUT:
    - TABLE: payables.expenses
    - MODE: INSERT
   PRELOAD:
    - REUSE_TABLES: true 
    SQL:
    - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
    - AFTER: "INSERT INTO audit VALUES('end', current_timestamp)"

```

--------------------------------

### Start gpfdist File Server for External Tables

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-external-g-creating-external-tables---examples

Starts the gpfdist file server program in the background. This is a prerequisite for creating external tables using the gpfdist protocol. It specifies the port, the directory to serve files from, and the log file location. Ensure gpfdist is running before creating gpfdist external tables.

```shell
gpfdist -p 8081 -d /var/data/staging -l /home/gpadmin/log &

```

--------------------------------

### pg_config - Reproduce Build Configuration (Shell)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-pg_config

This example demonstrates how to reproduce the build configuration of the current Greenplum Database installation using `pg_config`. The `eval` command is necessary because the output of `pg_config --configure` includes shell quotation marks, ensuring correct handling of arguments with spaces.

```shell
eval ./configure 'pg_config --configure'
```

--------------------------------

### Tanzu Greenplum SQL: Example - Create Basic Database

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_DATABASE

A simple example of how to create a new database named 'gpdb' in Tanzu Greenplum. This demonstrates the most basic usage of the CREATE DATABASE command.

```sql
CREATE DATABASE gpdb;

```

--------------------------------

### Example: Show geqo Parameter (Greenplum SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-SHOW

Illustrates retrieving the current value of the 'geqo' configuration parameter in Greenplum Database. This example shows how to query specific parameter values.

```sql
SHOW geqo;
-- Expected Output:
--  geqo 
-- ------
--  off
-- (1 row)
```

--------------------------------

### Start psql in Interactive Mode

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-psql

This example shows how to start the psql client in interactive mode. It specifies the port (54321), username (sally), and the database name (mydatabase) to connect to. Once connected, you can enter SQL commands and psql meta-commands.

```bash
psql -p 54321 -U sally mydatabase
```

--------------------------------

### Create Date Range Partitioned Table (Automatic Generation)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-partition

Creates a sales table partitioned by date, automatically generating daily partitions between specified start and end dates. START is inclusive, END is exclusive.

```sql
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE 
   END (date '2017-01-01') EXCLUSIVE 
   EVERY (INTERVAL '1 day'));
```

--------------------------------

### Example pam_cracklib configuration line

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/security-guide-topics-BestPractices

Provides an example of the full line to be added to `/etc/pam.d/system-auth` to enforce strong password policies using `pam_cracklib`.

```bash
password required pam_cracklib.so try_first_pass retry=3\minlen=14 dcredit=-1 ucredit=-1 ocredit=-1 lcredit=-1

```

--------------------------------

### Install Docker Image on All Hosts

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-plcontainer

Installs a Docker image on all Greenplum Database hosts by loading it from a tar.gz file. The utility shows progress during installation.

```bash
plcontainer image-add -f plc_newr.tar.gz

```

--------------------------------

### Install MADlib as a Database Extension (SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/relnotes-release-notes

Introduces support for installing MADlib as a database extension using the `CREATE EXTENSION` command in Tanzu Greenplum 6.30.0, replacing the older `madpack`-based installation. This also includes support for upgrading from previous MADlib versions. This method simplifies the installation process and integrates MADlib more seamlessly with Greenplum.

```sql
CREATE EXTENSION madlib;

```

--------------------------------

### Install Greenplum Clients on Windows

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/client_tool_guides-client-windows-win_client_install

This snippet demonstrates the typical file name format for the Greenplum Database client and loader tools installer for Windows. It's an MSI file used for package installation.

```text
greenplum-db-clients-<version>-x86_64.msi
```

--------------------------------

### Start PXF on Each Host (Bash)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/pxf-migrate_5to6

Start the PXF service on each host in the Greenplum Database cluster. This command should be executed after registering the PXF cluster configuration.

```bash
gpadmin@gp6master$ pxf cluster start
```

--------------------------------

### Start Existing Standby Master (Greenplum)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpinitstandby

Starts an already configured but stopped standby master instance. This action synchronizes data with the current primary master.

```bash
gpinitstandby -n
```

--------------------------------

### Run gpinitsystem Utility (Basic)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This command initiates the Greenplum Database system creation process using the specified configuration and host files. It requires the paths to the gpinitsystem_config file and the hostfile_gpinitsystem.

```bash
$ cd ~ 
$ gpinitsystem -c gpconfigs/gpinitsystem_config -h gpconfigs/hostfile_gpinitsystem
```

--------------------------------

### Install Greenplum Database with Ansible Playbook

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_guide

This snippet demonstrates an example Ansible playbook for installing a Greenplum Database software release onto hosts. It assumes prior setup of passwordless SSH for the 'gpadmin' user and requires appropriate inventory and variable configurations.

```yaml
- hosts: greenplum_hosts
  become: yes
  vars:
    gpdb_version: "6.x.x"
    gpdb_install_dir: "/opt/greenplum-db"
  tasks:
    - name: Download Greenplum Database installer
      get_url:
        url: "https://example.com/downloads/greenplum-db-{{ gpdb_version }}-{{ ansible_os_family }}.tar.gz"
        dest: "/tmp/greenplum-db-{{ gpdb_version }}.tar.gz"

    - name: Extract Greenplum Database installer
      unarchive:
        src: "/tmp/greenplum-db-{{ gpdb_version }}.tar.gz"
        dest: "/tmp/"
        remote_src: yes

    - name: Install Greenplum Database software
      command: "/tmp/greenplum-db-{{ gpdb_version }}/install-gpdb-platform --prefix={{ gpdb_install_dir }} --with-openssl"
      args:
        chdir: "/tmp/greenplum-db-{{ gpdb_version }}"
      creates: "{{ gpdb_install_dir }}/greenplum_path"

    - name: Ensure gpadmin user ownership of install directory
      file:
        path: "{{ gpdb_install_dir }}"
        owner: gpadmin
        group: gpadmin
        state: directory
        recurse: yes

    - name: Remove installer archive
      file:
        path: "/tmp/greenplum-db-{{ gpdb_version }}.tar.gz"
        state: absent

    - name: Remove extracted installer directory
      file:
        path: "/tmp/greenplum-db-{{ gpdb_version }}"
        state: absent
```

--------------------------------

### Start gpfdist Protocol for External Table Access

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-partition

Initiates the 'gpfdist' protocol, which is required for Greenplum Database to interact with external data sources. This command is a prerequisite for creating and accessing external tables.

```bash
 $ gpfdist

```

--------------------------------

### Redo SSH Key Exchange for Passwordless Access

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

This command is used to re-establish passwordless SSH access between hosts if the initial setup failed or if prompted for a password during `gpssh` execution. It uses the provided host file.

```bash
gpssh-exkeys -f hostfile_exkeys

```

--------------------------------

### Add Partition to Tanzu Greenplum Table

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-partition

Adds a new partition to an existing table in Tanzu Greenplum. This example demonstrates adding a partition without a subpartition template. The 'START' and 'END' clauses define the range for the new partition.

```sql
ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE;
```

--------------------------------

### pg_dump Synopsis and Help

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-pg_dump

Provides the basic command-line synopsis for the pg_dump utility, including options for connection, dump configuration, and accessing help or version information. This is the entry point for using the tool.

```shell
pg_dump [<connection-option> ...] [<dump_option> ...] [<dbname>]

pg_dump -? | --help

pg_dump -V | --version
```

--------------------------------

### Install Tanzu Greenplum Clients with Zypper (SLES 12)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/client_tool_guides-client-unix-unix_client_install

Installs the Tanzu Greenplum client and loader tools package on a host running SLES 12 using the `zypper` package manager. This command requires operating system superuser privileges or `sudo` access. It automatically installs any necessary dependent packages.

```bash
root@clientsys$ zypper install greenplum-db-clients-6.24.3-sles12-x86_64.rpm
```

--------------------------------

### Default Installation Directory for Greenplum Clients

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/client_tool_guides-client-windows-win_client_install

This snippet shows the default installation path for the Greenplum Database client and loader tools on a Windows system. Users can modify this during the installation process.

```plaintext
C:\Program Files\Greenplum\greenplum-clients\
```

--------------------------------

### Change Ownership of Greenplum Installation Files

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Changes the owner and group of the Greenplum Database installation files to 'gpadmin'. This is a crucial step after installing to a non-default directory to ensure proper permissions. Replace `<directory>` with your chosen installation path.

```bash
$ sudo chown -R gpadmin:gpadmin <directory>/greenplum*

```

--------------------------------

### Verify Greenplum Installation on All Hosts

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Uses `gpssh` to check passwordless SSH access and confirm the Greenplum software is installed consistently on all hosts. It verifies the contents and ownership of the specified installation directory.

```bash
gpssh -f hostfile_exkeys -e 'ls -l /usr/local/greenplum-db-<version>'

```

--------------------------------

### Access Example Data Set Information (R Console)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-greenplum_r_client

Demonstrates how to access help documentation for example datasets ('abalone' and 'null.data') included with the Greenplum R Client using R's built-in help function.

```r
> help( abalone )
> help( null.data )

```

--------------------------------

### Source Greenplum Environment Variables

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Sources the Greenplum Database environment variables by executing the `greenplum_path.sh` script. This command is typically run by the 'gpadmin' user to set up the necessary paths for Greenplum utilities. It's recommended to add this to the '.bashrc' file for persistent environment setup.

```bash
$ source /usr/local/greenplum-db-<version>/greenplum_path.sh

```

--------------------------------

### Execute Greenplum ODBC Driver Installer on Linux

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/datadirect-datadirect_ODBC_71

This command executes the installer for the Progress DataDirect ODBC driver on Linux. It requires root privileges to install system-wide. The installer will guide through the installation process, including license key entry and product registration.

```shell
$ ./PROGRESS_DATADIRECT_ODBC_LINUX_64_INSTALL.bin
```

--------------------------------

### Drop Database - Basic Usage Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-dropdb

This example demonstrates the simplest way to use the `dropdb` command to destroy a database named 'demo' using default connection parameters.

```bash
dropdb demo

```

--------------------------------

### Install Greenplum Database DEB on Ubuntu

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Installs the Greenplum Database software from a DEB package on Ubuntu systems. This command requires sudo or root privileges and automatically handles software dependencies and installation to a default directory.

```shell
$ sudo apt install ./greenplum-db-<version>-<platform>.deb

```

--------------------------------

### psql Connection Examples

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-psql

Demonstrates how to connect to Greenplum Database using `psql` with different connection parameter specifications, including service names and URIs. These examples show flexible ways to establish a connection.

```bash
$ psql "service=myservice sslmode=require"
$ psql postgresql://gpmaster:5433/mydb?sslmode=require

```

--------------------------------

### Installing PL/R Extension

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/relnotes-release-notes-6-20

Install the PL/R procedural language using the `CREATE EXTENSION` command. This is the recommended method, as the deprecated `createlang` utility may fail.

```sql
CREATE EXTENSION plr;
```

--------------------------------

### gpmovemirrors Example: Moving Mirrors to New Hosts

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpmovemirrors

An example demonstrating how to use the gpmovemirrors utility with a configuration file to move mirror segments to different hosts in a Greenplum Database system.

```bash
$ gpmovemirrors -i move_config_file
```

--------------------------------

### Initialize Greenplum Host File Example (Text)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This example shows the format of the 'hostfile_gpinitsystem' file used by the gpinitsystem utility. It lists the host address names for segment host interfaces, one per line. This file is crucial for gpinitsystem to identify and configure all segment instances.

```text
sdw1-1
sdw1-2
sdw2-1
sdw2-2
sdw3-1
sdw3-2
sdw4-1
sdw4-2

```

--------------------------------

### Install Greenplum Dependencies with YUM

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Installs necessary dependencies for Greenplum Database on RHEL/CentOS systems using the YUM package manager. Ensure you have sudo or root access. This command lists the packages required for a successful Greenplum installation.

```bash
$ sudo yum install apr apr-util bash bzip2 curl krb5 libcurl libevent \
libxml2 libyaml zlib openldap openssh openssl openssl-libs perl readline rsync R sed tar zip

```

--------------------------------

### Enable and Start iptables Service

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-enable_iptables

Commands to enable the iptables service to start on boot and to start the service immediately. These commands are run as the root user.

```bash
# chkconfig iptables on
# service iptables start
```

--------------------------------

### Change Ownership of Greenplum Installation

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Changes the owner and group of the installed Greenplum Database files to 'gpadmin'. This is a crucial post-installation step to ensure the `gpadmin` user has the necessary permissions to manage the Greenplum system.

```shell
$ sudo chown -R gpadmin:gpadmin /usr/local/greenplum*
$ sudo chgrp -R gpadmin /usr/local/greenplum*

```

--------------------------------

### gppkg Query Installed Packages

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gppkg

Shows how to query information about installed Greenplum Database packages using the gppkg utility. Options include checking for a specific package, getting detailed information, listing package contents, or listing all installed packages.

```bash
gppkg -q <package_file>
gppkg --query <package_file>

gppkg --query --info <package_file>
gppkg --query --list <package_file>
gppkg --query --all
```

--------------------------------

### Greenplum EXPLAIN Query Plan Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-query-topics-query-profiling

Demonstrates a basic EXPLAIN query plan in Greenplum, showing how to read the plan from bottom to top to understand the execution flow, costs, and estimated rows. It highlights sequential scans and gather motion operations.

```sql
EXPLAIN SELECT * FROM names WHERE name = 'Joelle';

QUERY PLAN
------------------------------------------------------------
Gather Motion 2:1 (slice1) (cost=0.00..20.88 rows=1 width=13)

   -> Seq Scan on 'names' (cost=0.00..20.88 rows=1 width=13)
         Filter: name::text ~~ 'Joelle'::text

```

--------------------------------

### Get PXF Version Information (Bash)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/pxf-migrate_5to6

Retrieve and display the version information of the installed PXF software. This command is useful for verifying the PXF version after installation or upgrade.

```bash
gpadmin@gp6master$ pxf version
```

--------------------------------

### Install Tanzu Greenplum Clients with Apt (Ubuntu 18.04)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/client_tool_guides-client-unix-unix_client_install

Installs the Tanzu Greenplum client and loader tools package on a host running Ubuntu 18.04 using the `apt` package manager. This command requires operating system superuser privileges or `sudo` access. It automatically installs any necessary dependent packages.

```bash
root@clientsys$ apt install greenplum-db-clients-6.24.3-ubuntu18.04-amd64.deb
```

--------------------------------

### COPY Command Syntax Example (SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-COPY

A basic example of the `COPY` command syntax in SQL, showing how to specify the source table and the destination for the data. This command is fundamental for bulk data operations in Greenplum.

```sql
COPY table_name TO stdout

```

--------------------------------

### Start PXF Cluster Services

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/pxf-upgrade_pxf_6x

This command starts the PXF services on all segment hosts within the Greenplum Database cluster. It is a necessary step after PXF has been registered, installed, or its configuration has been synchronized.

```shell
gpadmin@gpmaster$ $GPHOME/pxf/bin/pxf cluster start
```

--------------------------------

### GPMT Synopsis and Options

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpmt

Displays the general syntax for using the GPMT tool, including options for specifying a host file, requesting help, and enabling verbose output. These are fundamental for initiating GPMT operations.

```bash
gpmt <tool> [<tool_options> ...]

gpmt -hostfile <file>

gpmt -help

gpmt -verbose
```

--------------------------------

### Install and Load R Packages (R)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/analytics-pl_r

Provides commands to install new R packages, optionally with dependencies, and to load them into the R session for use. Uses `install.packages()` and `library()`.

```R
> install.packages("<package_name>")
> install.packages("mypkg", dependencies = TRUE, type="source")

```

```R
> library(" <package_name> ") 

```

--------------------------------

### Run gpinitsystem to Output Configuration

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This command runs the gpinitsystem utility with the `-O` option to generate and save a cluster configuration template file. This file can be later used with the `-I` option to create a new cluster or recover from a backup.

```bash
$ gpinitsystem -c gpconfigs/gpinitsystem_config -O gpconfigs/config_template
```

--------------------------------

### SQL UNION DISTINCT Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-SELECT

An example of using the `UNION` operator to combine the results of two `SELECT` statements, retrieving distinct names from `distributors` and `actors` tables that start with 'W'.

```sql
SELECT distributors.name FROM distributors WHERE 
distributors.name LIKE 'W%' UNION SELECT actors.name FROM 
actors WHERE actors.name LIKE 'W%';

```

--------------------------------

### psql tableattr HTML Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-psql

Provides an example of setting HTML table attributes for the `table` tag in psql's HTML output format. This allows customization of table appearance.

```psql
\pset tableattr 'cellpadding=2 bgcolor=lightblue'
```

--------------------------------

### Example: Create User Mapping (SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_USER_MAPPING

Provides a concrete example of how to create a user mapping for a specific user and server, including setting the user and password options. This is useful for establishing connections to external data sources.

```sql
CREATE USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'secret');

```

--------------------------------

### gpreload Example Command

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpreload

This snippet provides a practical example of how to use the gpreload utility to reload tables listed in a specified file within a given database.

```bash
gpreload -d mytest --table-file data-tables.txt

```

--------------------------------

### Start Greenplum Database System (gpstart)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpstart

The `gpstart` utility starts the Greenplum Database server, including master and segment instances in parallel. It performs consistency checks on heap checksum settings and handles standby master detection. Requires prior initialization with `gpinitsystem`.

```bash
gpstart [-d <master_data_directory>] [-B <parallel_processes>] [-R]
        [-m] [-y] [-a] [-t <timeout_seconds>] [-l <logfile_directory>]
        [--skip-heap-checksum-validation] [--enable-restricted-mode-resmgmt]
        [-v | -q]

gpstart -? | -h | --help

gpstart --version
```

--------------------------------

### Drop Database - Advanced Usage Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-dropdb

This example shows how to use `dropdb` with specific connection options (port, host), enables interactive verification, and echoes the generated SQL command before executing it to destroy the 'demo' database.

```bash
dropdb -p 54321 -h masterhost -i -e demo

```

--------------------------------

### Initialize Greenplum System with Hostfile

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpinitsystem

Initializes a Greenplum Database system using a configuration file and a segment host address file. It demonstrates setting up spread mirroring. Ensure the hostfile is correctly formatted with hostnames or IP addresses.

```bash
gpinitsystem -c gpinitsystem_config -h hostfile_gpinitsystem --mirror-mode=spread
```

--------------------------------

### SQL - Example of Dropping a Domain

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-DROP_DOMAIN

An example demonstrating how to drop a domain named 'zipcode' using the DROP DOMAIN command. This showcases the basic usage for domain removal.

```sql
DROP DOMAIN zipcode;
```

--------------------------------

### Concatenate Text Strings Example (C/C++)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-extensions-gppc

An example demonstrating how to concatenate two TEXT arguments using the GPPC API. It involves getting the lengths of the input strings, allocating memory for the result, and using memcpy to combine the content.

```c
GppcText first_textstr = GPPC_GETARG_TEXT(0);
GppcText second_textstr = GPPC_GETARG_TEXT(1);

// determine the size of the concatenated string and allocate
// text memory of this size
size_t arg0_len = GppcGetTextLength(first_textstr);
size_t arg1_len = GppcGetTextLength(second_textstr);
GppcText retstring = GppcAllocText(arg0_len + arg1_len);

// construct the concatenated return string; copying each string
// individually
memcpy(GppcGetTextPointer(retstring), GppcGetTextPointer(first_textstr), arg0_len);
memcpy(GppcGetTextPointer(retstring) + arg0_len, GppcGetTextPointer(second_textstr), arg1_len);
```

--------------------------------

### Run gpinitsystem Utility (Redundant System)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This command initializes a fully redundant Greenplum system with a standby master and a spread mirror configuration. It includes the `-s` option for the standby master hostname and `--mirror-mode=spread` for mirror distribution.

```bash
$ gpinitsystem -c gpconfigs/gpinitsystem_config -h gpconfigs/hostfile_gpinitsystem \
  -s <standby_master_hostname> --mirror-mode=spread
```

--------------------------------

### PSQL Editor Line Number Argument Examples

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-psql

Provides examples of setting the PSQL_EDITOR_LINENUMBER_ARG environment variable to specify how the starting line number is passed to the editor when using \e or \ef commands in psql. This allows editors like Emacs or vi to open directly to a specific line.

```bash
PSQL_EDITOR_LINENUMBER_ARG='+';
PSQL_EDITOR_LINENUMBER_ARG='--line ';
```

--------------------------------

### pg_restore Command Synopsis and Options

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-pg_restore

Provides the basic command structure for pg_restore, including connection and restore options. It also details how to access help and version information.

```bash
pg_restore [<connection-option> ...] [<restore_option> ...] <filename>

pg_restore -? | --help

pg_restore -V | --version
```

--------------------------------

### SQL - Basic VALUES Command Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-VALUES

An example of a standalone VALUES command that generates a table with specified rows and columns. It demonstrates how to create a constant table.

```sql
VALUES (1, 'one'), (2, 'two'), (3, 'three');
```

--------------------------------

### Create Greenplum Database (SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-query-topics-gp-mapreduce

This SQL command creates a new database named 'mapredex_db' specifically for the MapReduce example. This isolates the example data and configurations.

```sql
createdb mapredex_db

```

--------------------------------

### Log in to Greenplum Master Host

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

This command logs the user into the Greenplum master host as the 'gpadmin' user. It is a prerequisite for running subsequent confirmation steps.

```bash
su - gpadmin

```

--------------------------------

### tsquery Prefix Matching in SQL

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-datatype-textsearch

Shows how to use the asterisk (*) for prefix matching in tsquery. This example demonstrates a query that matches any word starting with 'super'.

```sql
SELECT 'super:*'::tsquery;
  tsquery  
-----------
 'super':*
```

--------------------------------

### Tanzu Greenplum `gpv` Utility Synopsis

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpv

Provides a synopsis of the `gpv` utility commands, including options for base configuration, cluster deployment, and version information. This utility automates the setup and management of Greenplum Database clusters on virtual platforms.

```shell
gpv base <base_options>
gpv config <config_options>
gpv greenplum <greenplum_options>
gpv version
gpv -h | --help
```

--------------------------------

### Host File Example for gpchecknet

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpcheckperf

Provides an example of a host file content for the gpchecknet utility, where all host addresses must be within the same subnet for network performance testing.

```text
sdw1-1
sdw2-1
sdw3-1
```

--------------------------------

### Synonym Dictionary File Format Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-textsearch-dictionaries

Illustrates the required format for a synonym dictionary configuration file. Each line should contain a word followed by its synonym, separated by whitespace. This example includes entries for basic synonyms, prefix synonyms, and variations.

```text
postgres pgsql postgresql pgsql postgre pgsql 
gogle googl 
indices index*
```

--------------------------------

### Stop and Disable Greenplum-postmaster Service

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/vsphere-greenplum-ha-service-installation

Stops the user-level greenplum-postmaster service and disables it from starting automatically on boot. This is part of the upgrade process to prepare for a new version.

```shell
systemctl --user stop greenplum-postmaster
systemctl --user disable greenplum-postmaster

```

--------------------------------

### Example gpinitsystem_config Parameters

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This snippet shows essential parameters for the gpinitsystem_config file, including segment prefix, port base, data directories, master hostname and directory, master port, trusted shell, checkpoint segments, and encoding. These settings define the basic structure of the Greenplum cluster.

```bash
SEG_PREFIX=gpseg
PORT_BASE=6000 
declare -a DATA_DIRECTORY=(/data1/primary /data1/primary /data1/primary /data2/primary /data2/primary /data2/primary)
MASTER_HOSTNAME=mdw 
MASTER_DIRECTORY=/data/master 
MASTER_PORT=5432 
TRUSTED SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE
```

--------------------------------

### Create Date Range Partitioned Table (Individual Declaration)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-partition

Creates a sales table partitioned by date, declaring each month as an individual partition with explicit start dates. The end date is specified for the last partition.

```sql
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( PARTITION Jan16 START (date '2016-01-01') INCLUSIVE , 
  PARTITION Feb16 START (date '2016-02-01') INCLUSIVE , 
  PARTITION Mar16 START (date '2016-03-01') INCLUSIVE , 
  PARTITION Apr16 START (date '2016-04-01') INCLUSIVE , 
  PARTITION May16 START (date '2016-05-01') INCLUSIVE , 
  PARTITION Jun16 START (date '2016-06-01') INCLUSIVE , 
  PARTITION Jul16 START (date '2016-07-01') INCLUSIVE , 
  PARTITION Aug16 START (date '2016-08-01') INCLUSIVE , 
  PARTITION Sep16 START (date '2016-09-01') INCLUSIVE , 
  PARTITION Oct16 START (date '2016-10-01') INCLUSIVE , 
  PARTITION Nov16 START (date '2016-11-01') INCLUSIVE , 
  PARTITION Dec16 START (date '2016-12-01') INCLUSIVE 
     END (date '2017-01-01') EXCLUSIVE );
```

--------------------------------

### Start gpfdist in Current Directory (Bash)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-external-g-using-the-greenplum-parallel-file-server--gpfdist-

Starts the gpfdist utility in the current directory. It listens on the default port 8080 and runs in the background. No external dependencies are required beyond the gpfdist executable.

```bash
gpfdist &

```

--------------------------------

### Get gp_fts_probe_interval Configuration

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/vsphere-greenplum-ha-service-installation

Retrieves the current configuration value for the gp_fts_probe_interval parameter using the gpconfig command. This is useful for setting an appropriate custom restart interval for the postmaster service.

```shell
gpconfig -s gp_fts_probe_interval

```

--------------------------------

### gpstart Interactive Mode Prompts

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpstart

Illustrates the interactive prompts displayed by `gpstart` when the standby master is unreachable and the `-a` option is not used. The user is prompted to confirm startup.

```bash
Standby host is unreachable, cannot determine whether the standby is currently acting as the master. Received error: <error>
Continue only if you are certain that the standby is not acting as the master.

Continue with startup Yy|Nn (default=N):
```

--------------------------------

### Enable multi-threaded data transfer with gpfdist

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpfdist

This example demonstrates how to start gpfdist with multi-threaded data transfer enabled, utilizing a specified number of threads. Implicit compression is also enabled by default when multi-threading is used.

```bash
gpfdist -d /var/load_files -p 8081 --multi_thread 4
```

--------------------------------

### Start gpfdist File Server Utility (Bash)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-external-g-using-the-greenplum-parallel-file-server--gpfdist-

This command starts the gpfdist file server utility in the background. It specifies the listen port and the directory for serving files. Ensure the specified directory exists and has the necessary read/write permissions.

```bash
$ gpfdist -p 8801 -d /home/gpadmin/external_files &
```

--------------------------------

### Reload Databases from Dump File using psql

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-pg_dumpall

This example shows how to reload databases from a previously created dump file ('db.out') using the psql client. It targets the 'template1' database, which is often used as a starting point for restoration.

```bash
psql template1 -f db.out
```

--------------------------------

### gpfdist Command Synopsis and Options

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpfdist

This snippet shows the command-line synopsis for the gpfdist utility, detailing various options for directory, port, logging, timeout, SSL, compression, and threading. It also includes commands for displaying help and version information.

```bash
gpfdist [-d <directory>] [-p <http_port>] [-P <last_http_port>] [-l <log_file>]
   [-t <timeout>] [-k <clean_up_timeout>] [-S] [-w <time>] [-v | -V] [-s] [-m <max_length>]
   [--ssl <certificate_path> [--sslclean <wait_time>] ]
   [--compress] [--multi_thread <num_threads>]
   [-c <config.yml>]

gpfdist -? | --help 

gpfdist --version

```

--------------------------------

### Create Database with createdb Client

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ddl-ddl-database

The `createdb` client program provides an alternative method to create a database from a command-line terminal. This example demonstrates connecting to a Greenplum Database instance using a specified host and port, and then creating a database named 'mydatabase'. Ensure the host and port match your Greenplum system configuration.

```bash
$ createdb -h masterhost -p 5432 mydatabase
```

--------------------------------

### Enable Passwordless SSH with sshpass

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

An alternative method to enable passwordless SSH using `sshpass` to avoid interactive password prompts. This command provides the password directly, which may be less secure. Replace `<password>` with the actual password.

```bash
$ SSHPASS=<password> sshpass -e ssh-copy-id smdw

```

--------------------------------

### Register pg_cron Extension in Greenplum Database

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-pg_cron

Registers the pg_cron extension within a specific Greenplum database. This SQL command allows you to start using the cron job scheduling functionality. Note that pg_cron can only be installed in one database per cluster.

```sql
CREATE EXTENSION pg_cron;

```

--------------------------------

### Example Optional Mirror Parameters in gpinitsystem_config

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This example demonstrates optional parameters for configuring mirror segments in the gpinitsystem_config file. It includes the mirror port base and mirror data directories. These settings are used for creating redundant data storage for the Greenplum cluster.

```bash
MIRROR_PORT_BASE=7000
declare -a MIRROR_DATA_DIRECTORY=(/data1/mirror /data1/mirror /data1/mirror /data2/mirror /data2/mirror /data2/mirror)
```

--------------------------------

### Generate N-to-N Passwordless SSH with gpssh-exkeys

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-install_gpdb

Enables passwordless SSH between all hosts in the Greenplum cluster for the 'gpadmin' user. This utility uses a host file (`hostfile_exkeys`) containing all hostnames and interface names to configure SSH keys across the entire cluster.

```bash
$ gpssh-exkeys -f hostfile_exkeys

```

--------------------------------

### GPMT - Show Tool Help

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpmt

Provides instructions on how to display detailed help information for a specific GPMT tool, such as 'gp_log_collector'. This is crucial for understanding the options and usage of individual utilities.

```bash
gpmt gp_log_collector -help
```

--------------------------------

### Fetch Tuple Descriptor Attributes (C)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-extensions-gppc

Provides C code examples for retrieving information about attributes within a tuple descriptor. Functions are used to get the number of attributes, attribute names, types, lengths, and type modifiers.

```C
int natts = GppcTupleDescNattrs(tupdesc);
const char *name = GppcTupleDescAttrName(tupdesc, attno);
GppcOid type = GppcTupleDescAttrType(tupdesc, attno);
int16_t len = GppcTupleDescAttrLen(tupdesc, attno);
int32_t typmod = GppcTupleDescAttrTypmod(tupdesc, attno);

```

--------------------------------

### Set Up Greenplum Environment Variables (Shell)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/install_guide-init_gpdb

This snippet demonstrates how to switch to the 'gpadmin' user and source the 'greenplum_path.sh' script to set up the necessary environment variables for running Greenplum Database utilities. This is a prerequisite for most Greenplum administrative tasks.

```shell
$ su - gpadmin
$ source /usr/local/greenplum-db/greenplum_path.sh

```

--------------------------------

### Get Greenplum Database Version (SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/pxf-migrate_5to6

This SQL query retrieves the version information of the Greenplum Database installation. It is executed using `psql` connected to the `postgres` database. This helps in verifying the Greenplum version before proceeding with the migration, ensuring it meets the minimum requirements.

```sql
SELECT version();
```

--------------------------------

### Access gp_sparse_vector Functions with Specific Schema

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-modules-gp_sparse_vector

This SQL example shows how to access functions from the gp_sparse_vector module when they are installed in the 'sparse_vector' schema, which is the default for Greenplum Database versions 6.16 and later. It illustrates querying data using the array_agg function, prefixed with the schema name.

```sql
SELECT sparse_vector.array_agg( col1 ) FROM table1;

```

--------------------------------

### Show All System Configuration Parameters (Greenplum SQL)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/ref_guide-sql_commands-SHOW

Displays the current settings of all Greenplum Database system configuration parameters. This command provides a comprehensive overview of the database's configuration. It is useful for auditing and understanding the overall state of the system. No external dependencies are required.

```sql
SHOW ALL;
```

--------------------------------

### Greenplum INSERT Load Mode Example

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/utility_guide-ref-gpload

This code snippet demonstrates the basic syntax for loading data into a target table using the INSERT mode in Greenplum Database. It specifies the target table and the input data source.

```SQL
INSERT INTO <target_table> SELECT * FROM <input_data>;

```

--------------------------------

### Greenplum LDAP Authentication (TLS/SSL - LDAPS)

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/admin_guide-ldap

Configures LDAP authentication using a secure connection with the TLS/SSL protocol (LDAPS) between Greenplum Database and the LDAP server. This example specifies the LDAPS protocol directly in the ldapserver parameter, ensuring an encrypted connection from the start.

```postgresql
host all ldapsuser 0.0.0.0/0 ldap ldapserver=ldaps://myldap.com ldapprefix="uid=" ldapsuffix=",ou=People,dc=example,dc=com"
```

--------------------------------

### Enhance gpstart Output for Segment Failures

Source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/6/greenplum-database/relnotes-release-notes-6-10

The `gpstart` utility now provides enhanced output to clearly indicate when segment instances fail to start. It displays warnings about downed segments and guides users to use `gprecoverseg` for recovery. Previously, it incorrectly reported 0 skipped segments.

```shell
[WARNING]:-****************************************************************************
[WARNING]:-There are 1 segment(s) marked down in the database
[WARNING]:-To recover from this current state, review usage of the gprecoverseg
[WARNING]:-management utility which will recover failed segment instance databases.
[WARNING]:-****************************************************************************

```
Tokens:
Raw