Data Export and Import

Greenplum provides several ways to export and import data using standard PostgreSQL tools like pg_dump and psql. Here are some common commands:

• Export only table structure:``` pg_dump -U gpadmin -d datawarehouse -w -s > /tmp/tab.sql

• Export specific table structure:``` pg_dump -U postgres -d postgres -w -s -t test_table > 1.txt

• Export multiple tables and their data:``` pg_dump -U gpadmin -d datawarehouse -w -t tbb_jyjhzbx_jh -t tab2 > tbb_jyjhzbx_jh.sql

• Export schema objects like views and functions:``` pg_dump -U gpadmin -d datawarehouse -w -s > /tmp/view_func.sql

Restore:

psql -d datawarehouse_test -f tab0801.sql

Common Tools

analyzedb

The analyzedb utility is used to update statistics for tables in Greenplum in a concurrent and incremental manner. It only updates statistics when necessary, based on changes to the table metadata.

Command Syntax

analyzedb -d dbname [options]

Key Features

• Skips tables or partitions that have not been modified since the last analysis.
• Supports full analysis with --full flag.
• Can analyze specific tables or schemas.
• Uses multiple concurrent sessions (default 5) to speed up analysis.

Partitioned Tables

• Analyzes root and leaf partitions.
• Collects root statistics by default unless --skip_root_stats is used.

gpconfig

Used to configure Greanplum system-wide parameters across all segments.

Usage

• -c Set a configuration parameter.
• -v Value to set.
• -m Master-specific value.
• --masteronly Apply only to master.
• -r Remove a parameter.
• -l List supported parameters.

gpstart

Starts the Greenplum database system.

Options

• -a Don't prompt for confirmation.
• -m Start only the master instance.
• -R Start in restricted mode (only superusers can connect).

gpstate

Displays the current status of Greenplum instances.

Common Options

• -s Detailed status.
• -b Brief status (default).
• -c Show mirror mappings.
• -e Show problematic segments.
• -f Show standby master details.
• -m List mirrors.
• -i Show version info.
• -p Show port numbers.
• -Q Quick status check without polling segments.

Mirror Status Interpretation

• Synchronized: Data is up to date.
• Resynchronization: Syncing data between primary and mirror.
• Change Tracking: Mirror is offline, primary is tracking changes.

gpstop

Stops or restarts the Greenplum database system.

Recommended Command

gpstop -M fast

Options

• -M fast Rollback transactions and terminate all connections.
• -M immediate Forcefully kill all processes (not recommended).
• -M smart Default; fails if there are active connections.
• -r Restart after shutdown.
• -u Reload configuration files without stopping the system.

Session Management

Viewing and Killing Sessions

List active sessions:

SELECT procpid, now() - start AS lap, current_query, rolname
FROM (
    SELECT backendid,
           pg_stat_get_backend_pid(backendid) AS procpid,
           pg_stat_get_backend_activity_start(backendid) AS start,
           pg_stat_get_backend_activity(backendid) AS current_query,
           pg_stat_get_backend_userid(backendid) AS uid
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s
) AS t1
JOIN pg_authid t2 ON t1.uid = t2.oid
WHERE current_query != '<IDLE>'
ORDER BY lap DESC;

Kill a session:

SELECT pg_terminate_backend(32004);

Stop a Segment on a Specific Host

gpstop --host n3.gp.test
gpstart --host n2.gp.test
gpstart --host n3.gp.test

Slow Query Monitoring

Query slow queries that have executed for more than 10 minutes:

SELECT username, db, cost, tfinish, tstart, status, query_text,
       skew_cpu, skew_rows, memory,
       ROUND(CAST(date_part('epoch', to_timestamp(tfinish::text,'yyyy-mm-dd hh24:MI:SS') - to_timestamp(tstart::text,'yyyy-mm-dd hh24:MI:SS')) / 60 AS numeric), 1) AS minutes
FROM gpmetrics.gpcc_queries_history
WHERE ROUND(CAST(date_part('epoch', to_timestamp(tfinish::text,'yyyy-mm-dd hh24:MI:SS') - to_timestamp(tstart::text,'yyyy-mm-dd hh24:MI:SS')) / 60 AS numeric), 1) > 10
  AND tstart >= '2023-03-01 14:40:35.847344';

Index Usage Enforcement

To force the query planner to use an index:

SET enable_seqscan TO off;
EXPLAIN ANALYZE
SELECT s.*
FROM bc_in_storage_order s
INNER JOIN bc_in_storage_order_detail b ON s.ID = b.order_id
WHERE s.TYPE = 28
  AND s.update_time >= '2024-11-01 00:00:00'::timestamp
  AND s.dr = 0
  AND b.dr = 0;