常用查询

1. greenplum常用命令

1.1. 数据导出和导入：

pg_dump --help
-U   用户名
-d   数据库名
-w   不输入密码
-W   输入密码
-s   只转储模式，不转储数据
-a   只转储数据，不转储模式
-t   表名

只导出表结构：
pg_dump -U gpadmin -d datawarehouse -w -s >/tmp/tab.sql

导出指定表的表结构：
pg_dump -U postgres -d postgres -w -s -t test_table

把得到的数据转存到文件可以用重定向：
pg_dump -U postgres -d postgres -w -s -t test_table > 1.txt

导出多张表和数据
pg_dump -U gpadmin -d datawarehouse -w -t tbb_jyjhzbx_jh -t tab2 > tbb_jyjhzbx_jh.sql

表结构和视图、函数
pg_dump -U gpadmin -d datawarehouse -w -s >/tmp/view_func.sql

gpcheckcatgpcheckcat工具测试Greenplum数据库目录表的不一致性。
gpcheckcat -O -v -g /home/gpadmin/gpcheck/ -p 5432 datawarehouse

执行恢复：
psql -d datawarehouse_test -f tab0801.sql

导出、导入的整体思路是：

导出全局对象，如用户、编码、权限等，产生文件global-objs.dmp
导出每个数据库中的对象、结构，如建库语句、用户、权限、编码、表结构、自定义类型等，产生
[库名]-objs.dmp文件，如dxm-objs.dmp
导出每个数据库中的数据，这里分两种来考虑，一个是某库中所有的数据（所有的表），生成单个文件dxm.dmp；
一个是针对某库中每个表分别进行导出备份，每个表一个文件，以[库名]-[表名].dmp命名，如dxm-all_types.dmp
在目标实例上建立全局对象，即导入global-objs.dmp 导入对象，即dxm-objs.dmp文件中的內容
导入数据，根据导出的不同，分为一次导入dxm.dmp，和分别导入每个表的文件，如dxm-all_types.dmp

1.2. 常用工具

1.2.1. analyzedb

一个提供对表的递增和并发ANALYZE操作的工具。对追加优化表来说， analyzedb只在统计数据不是最新的时候才更新统计信息。
概要

analyzedb -d dbname
        { -s schema  | 
        { -t schema.table 
        [ -i col1[, col2, ...] | 
        -x col1[, col2, ...] ] } |
        { -f | --file} config-file }
        [ -l | --list ]
        [ --gen_profile_only ]   
        [ -p parallel-level ]
        [ --full ]
        [ -v | --verbose ]
        [ --debug ]
        [ -a ]
        
        analyzedb { --clean_last | --clean_all }
        analyzedb --version
        analyzedb { -? | -h | --help }

1.2.1.1. 描述

analyzedb工具递增并发地更新Greenplum数据库中指定表的表数据的统计信息。

在执行ANALYZE操作时，analyzedb创建了表元数据的快照并将它存储在Master主机的磁盘上。只有在表格被修改的情况下才执行ANALYZE操作。如果表或者分区自从上次分区依赖没有被修改过，则analyzedb会自动跳过表或分区，因为它已经包含了最新的统计信息。

对追加优化表来说，analyzedb如果统计数据数据不是最新的，则会逐渐分析统计数据。例如，如果表数据在表的统计数据收集之后改变了。如果没有该表的统计数据，则收集统计数据。
对于堆表，统计信息总是被更新。
即使表格统计信息是新的，也可以指定--full选项来更新追加优化表的统计信息。

默认情况下，analyzedb最多创建5个并发会话来并行分析表。对于每个会话，analyzedb发出一个ANALYZE命令到数据库并指定不同的表名。-p选项控制最大并发会话数。

分区追加优化表
对于一个分区的追加优化表analyzedb检查分区表的根分区和叶子分区。如果需要，该工具将更新非当前分区和根分区的统计信息。

GPORCA需要根分区统计信息。默认情况下，如果统计信息不存在，那么analyzedb 工具将收集分区表的根分区的统计信息。如果任何叶子节点有陈旧的统计信息，那么,analyzedb 也会刷新根分区的统计信息。刷新根节点的统计信息的成本和分析一个叶子分区相当。用户可以指定 --skip_root_stats来禁用分区表的根分区的统计信息的收集。

1.2.1.2. 注意

如果表已经由DML或DDL命令（包括INSERT, DELETE, UPDATE, CREATE TABLE, ALTER TABLE 和 TRUNCATE）修改，则analyzedb工具会更新追加优化表的统计信息。该工具通过比较表的元数据和之前analyzedb操作中所保存的快照目录元数据来确定表是否已经被修改。表元数据的快照作为状态文件存储在Greenplum数据库主数据目录中的db_analyze/<db_name>/ 目录中。

该工具不会自动删除旧的快照信息。时间久了，这些快照会占用大量的磁盘空间。为了回收磁盘空间，您可以保留最近的快照信息，将旧的快照删掉。用户可以指定--clean_last或者--clean_all选项来删除由 analyzedb生成的状态文件。
如果用户未指定表，一组表或模式，那么analyzedb工具会根据需要收集所有系统目录表和用户定义的表中的统计信息。

外部表不受analyzedb的影响。

包含空格的表名是不支持的。

在表上运行ANALYZE命令，（不使用analyzedb工具），不会更新表的元数据， analyzedb工具通常使用这些元数据通常来确定表的统计信息是否是最新的表的元数据。

1.2.1.3. gpconfig

用以在Greenplum数据库系统中所有的Segment上设置服务器配置参数。

描述
gpconfig工具允许用户在Greenplum数据库系统中所有实例（Master、Segment和镜像）的postgresql.conf文件中设置、复原或查看配置参数。设置参数时，如果需要，还可以为Master指定一个不同的值。例如，诸如max_connections之类的参数要求Master的设置不同于Segment的设置。如果要设置或复原全局参数或仅可对Master设置的参数，请使用 --masteronly选项。

gpconfig只能用来管理某些参数。例如，用户不能使用它来设置port等参数，这些参数对每个Segment实例都不同。使用-l （list）选项查看gpconfig支持的配置参数的完整列表。

当gpconfig在Segment的postgresql.conf文件中设置配置参数时，新的参数设置将总是显示在该文件的底部。当用户使用gpconfig移除配置参数时，gpconfig会在所有Segment的postgresql.conf 文件中把该参数注释掉，从而恢复系统默认设置。例如，如果使用gpconfig删除（注释掉）一个参数，并且稍后把它添加回来（设置新值），则该参数会有两个实例，一个被注释掉，另一个被启用并添加到postgresql.conf文件的底部。

设置参数之后，用户必须重新启动其Greenplum数据库系统，或者重新加载postgresql.conf 文件以使得更改生效。是否需要重新启动或者加载取决于被设置的参数。

gpconfig使用以下环境变量连接到Greenplum数据库的Master实例并获取系统配置信息：

PGHOST
PGPORT
PGUSER
PGPASSWORD
PGDATABASE

选项

-c：通过在postgresql.conf 文件的底部添加新的设置来改变配置参数的设置。
-v ：用于由-c选项指定的配置参数的值。默认情况下，此值将应用于所有Segment及其镜像、Master和后备Master。
-m：用于由-c选项指定的配置参数的Master值。如果指定，则该值仅适用于Master和后备Master。该选项只能与-v一起使用。
--masteronly：当被指定时，gpconfig 将仅编辑Master的postgresql.conf文件。
-r：通过注释掉postgresql.conf文件中的项删除配置参数。
-l：列出所有被gpconfig工具支持的配置参数。
-s：显示在Greenplum数据库系统中所有实例（Master和Segment）上使用的配置参数的值。如果实例中参数值存在差异，则工具将显示错误消息。使用-s选项运行gpconfig将直接从数据库中读取参数值，而不是从postgresql.conf文件中读取。如果用户使用gpconfig 在所有Segment中设置配置参数，然后运行gpconfig -s来验证更改，用户仍可能会看到以前的（旧）值。用户必须重新加载配置文件（gpstop -u）或重新启动系统（gpstop -r）以使更改生效。
--file：对于配置参数，显示在Greenplum数据库系统中的所有Segment（Master和Segment）上的postgresql.conf文件中的值。如果实例中的参数值存在差异，则工具会显示一个消息。必须与-s选项一起指定。
例如，使用ALTER ROLE为用户设置配置参数statement_mem为64MB，而postgresql.conf文件中的值为128MB。运行命令 gpconfig -s statement_mem --file显示 128MB。用户运行的命令gpconfig -s statement_mem显示64MB。

另：
参数访问设置有多种情况，有些参数改变后必须重启服务器才能生效，有些参数用户可以直接改变，还有些参数需要超级用户才能改表。
在postgresql中，把参数分为以下几类。
1.internal：这类参数是只读参数，不能更改。且这类参数值不能配置在postgresql.conf中，因为它们是由postgres程序在初始化实例时写死的。
2.postmaster：改变这些参数的值需要重启postgresql实例。在postgresql.conf中改变这些参数后，需要重启postgresql实例才生效。
3.sighup：在postgresql.conf文件中可以该表这些参数的值，不需要重启数据库，只需要向postmaster进程发送sighup信号，让其重新配置新的参数值即可。运行pg_ctl reload，对于greenplum，运行gpstop -u。
4.backend：同sighup，不同的是新的配置值只会出现在这之后的新的连接中，在已有的连接中，这些参数的值不会改变。
5.superuser：这类参数可以由超级用户使用set来改变。超级用户改变此参数值时，只会影响自身的session配置，不会影响其他用户。
6.user：可由普通用户使用set来改变此类参数在本连接中的配置。

1.2.1.4. gpstart

启动一个Greenplum数据库系统。

描述
gpstart工具用于启动Greenplum数据库服务器进程。当用户启动一个Greenplum数据库系统时，用户实际上是同时启动了几个postgres数据库服务器监听器进程（Master和所有的Segment实例）。gpstart工具处理各个实例的启动。每个实例都是并行启动的。

管理员次运行gpstart时，该工具将在用户的主目录中创建一个名为.gphostcache的主缓存文件。随后，该工具使用此主机列表更有效地启动系统。如果将新主机添加到系统中，则必须手动从gpadmin用户的主目录中删除此文件。该工具将在下次启动时创建一个新的主机缓存文件。

在启动Greenplum数据库系统之前，用户必须首先使用gpinitsystem初始化系统。

选项

-a：不要提示用户确认。
-m：可选。仅启动Master实例，这可能对维护任务有用。
-R：以受限模式启动Greenplum数据库（只允许数据库超级用户连接）。

1.2.1.5. gpstate

显示正在运行的Greenplum数据库系统的状态。

描述
gpstate工具显示有关正在运行的Greenplum数据库实例的信息。由于Greenplum数据库系统由跨多台机器的多个PostgreSQL数据库实例（Segment）组成，因此用户可能需要了解关于Greenplum数据库系统的额外信息。gpstate工具为Greenplum数据库系统提供了额外的状态信息。

选项

-c （显示主Segment到镜像Segment的映射）
可选。显示主Segment到镜像Segment的映射。
-e （显示镜像状态出问题的Segment）
显示具有潜在问题的主要/镜像Master对的详细信息，例如1）活动Segment正在Change Tracking模式下运行，表示Segment处于关闭状态；2）活动Segment处于重新同步模式，这意味着它正在同步对镜像的更改；3）一个Segment不是它的首-选角色，例如在系统初始化时作为一个主Segment的现在作为一个镜像Segment，这意味着用户可能有一台或多台Segment主机的处理负载不平衡。
-f （显示后备Master的详情）
显示后备Master主机的详细信息（如果配置）。
-i （显示Greenplum数据库的版本）
显示每个实例的Greenplum数据库软件版本信息。
-m （列出镜像）
可选。列出系统中的镜像Segment实例及其当前角色和同步状态。
-s （详细状态）
可选。显示Greenplum数据库系统的详细状态信息。



-b（简要状态）

可选。显示Greenplum数据库系统状态的简要概述。这是默认选项。

-B parallel_processes

并行检查的Segment数。如果未指定，则工具将根据需要检查多少个Segment实例启动最多60个并行进程。

-c（显示主Segment到镜像Segment的映射）

可选。显示主Segment到镜像Segment的映射。

-d master_data_directory

可选。Master的数据目录。如果未指定，则使用为$MASTER_DATA_DIRECTORY 设置的值。

-e（显示镜像状态出问题的Segment）

显示具有潜在问题的主要/镜像Master对的详细信息，例如1）活动Segment正在Change Tracking模式下运行， 表示Segment处于关闭状态；2）活动Segment处于重新同步模式，这意味着它正在赶上对镜像的更改；3） 一个Segment不是它的首选角色，例如在系统初始化时作为一个主Segment的现在作为一个镜像Segment， 这意味着用户可能有一台或多台Segment主机的处理负载不平衡。

-f（显示后备Master的详情）

显示后备Master主机的详细信息（如果配置）。

-i（显示Greenplum数据库的版本）

显示每个实例的Greenplum数据库软件版本信息。

-l logfile_directory

写入日志文件的目录。默认为~/gpAdminLogs。

-m（列出镜像）

可选。列出系统中的镜像Segment实例及其当前角色和同步状态。

-p（显示端口）

列出整个Greenplum数据库系统使用的端口号。

-q（没有屏幕输出）

可选。以静默模式运行。除了警告信息之外，屏幕上不显示命令输出。但是， 这些信息仍然写入到日志文件中。

-Q（快速状态）

可选。在Master主机上的系统目录中检查Segment的状态。不直接向Segment征询状态。

-s（详细状态）

可选。显示Greenplum数据库系统的详细状态信息.

-v（详细输出）

可选。显示错误消息并输出详细的状态和进度信息。

-x (expand)

Optional. Displays detailed information about the progress and state of a Greenplum system expansion.

-? | -h | —help (help)

显示在线帮助。

常用的gpstate命令是 gpstate –s，其中的Mirror Statu显示出主/镜像Segment对的状态：
Synchronized = 两者上的数据都是新
Resynchronization = 数据当前正被从一个拷贝到另一个
Change Tracking = Segment停止并且活动Segment正在记录更改

字段释义：

1.2.1.6. gpstop

停止或者重启Greenplum数据库系统。

建议：

gpstop -M fast

描述
gpstop工具用于停止构成Greenplum数据库系统的数据库服务器。当用户停止一个Greenplum数据库系统时，用户实际上是一次停止几个postgres数据库服务器进程（Master和所有的Segment实例）。gpstop工具处理个别实例的关闭。每个实例被并行地关闭。

默认情况下，如果有任何客户端连接到数据库，则不允许关闭Greenplum数据库。使用-M fast选项来回滚所有正在进行的事务，并在关闭之前终止所有连接。如果有任何事务正在进行，则默认行为是在关闭之前等待它们提交。

使用-u选项时, 该工具会上传对主pg_hba.conf文件所做的更改，或者在postgresql.conf文件中将运行时配置参数上载到服务中。请注意，任何活动的会话在重新连接到数据库之前都不会获取更改。

选项

-m：可选。关闭在维护模式下启动的Greenplum主实例。
-M fast：快速关闭。任何正在进行的事务都会中断并回滚。
-M immediate：立即关闭。任何正在进行的事务都会中止。（不推荐使用）
该模式杀死所有postgres进程，而不允许数据库服务器完成事务处理或清理任何临时或进程内工作文件。
-M smart：智能关闭。如果存在活动连接，则此命令将失败并显示警告。这是默认的关闭模式
-r：关机完成后重新启动。
-u：此选项将重新加载Master和Segment的pg_hba.conf文件以及postgresql.conf文件的运行时参数，但不会关闭数据库阵列。编辑postgresql.conf或pg_hba.conf之后，使用此选项可使新的配置设置处于活动状态。请注意，这仅适用于设计为运行时的配置参数

1.2.2. gpssh

一次提供对多台主机的SSH访问。

概要

gpssh { -f hostfile_gpssh | - h hostname [-h hostname ...] } [-s] [-e]
                [-d seconds] [-t multiplier] [-v]
                [bash_command]
                
                gpssh -? 
                
                gpssh --version

选项
bash_command
在此会话中涉及的所有主机上执行的bash shell命令（可选择封闭在引号中）。 如果未指定，则gpssh启动交互式会话。
-d（延迟）seconds
可选。指定用ssh开始gpssh交互开始时等待 的时间（以秒为单位）。默认值是0.05。此选项将覆盖gpssh.conf 配置文件中指定的delaybeforesend的值。
增加此值可能会导致在gpssh启动过程中等待很长时间。
-e（回显）
可选。以非交互模式运行时，回显传递给每个主机的命令及其结果输出。
-f hostfile_gpssh
指定包含将参与此SSH会话的主机列表的文件的名称。主机文件的语法是每行一个主机。
-h hostname
指定将参与此SSH会话的单个主机名。用户可以多次使用-h 选项来指定多个主机名。
-s
可选。如果指定，则在执行目标主机上的任何命令之前，gpssh将在 $GPHOME环境变量指定的目录中引用文件greenplum_path.sh。
该选项对交互模式和单命令模式都有效。
-t multiplier
可选。大于0的十进制数，它是gpssh在验证ssh 提示时使用的超时的倍数。默认值为1。此选项将覆盖gpssh.conf 配置文件中的prompt_validation_timeout值。
增加此值对gpssh启动过程影响不大。
-v（详细模式）
可选。在非交互模式下运行时，除了输出命令外，还会报告其他消息。
--version
显示此工具的版本。
-?（帮助）
显示在线帮助。

pssh工具允许用户使用SSH（安全shell）一次在多台主机上运行bash shell命令。用户可以通过在命令行上指定一个命令来执行单个命令，也可以省略该命令以进入交互式命令行会话。

要指定参与SSH会话的主机，请使用-f选项指定包含主机名列表的文件，或使用 -h选项在命令行上指明单个主机名。至少需要一个主机名（-h）或主机文件（-f）。请注意，当前主机默认不包含在会话中 — 要包含本地主机，用户必须在会话中涉及的主机列表中明确声明它。

在使用gpssh之前，用户必须在涉及SSH会话的主机之间建立可信的主机设置。用户可以使用工具gpssh-exkeys更新已知的主机文件并在主机之间交换公钥（如果尚未这样做的话）。

如果用户没有在命令行上指定命令，gpssh将进入交互模式。在gpssh 命令提示符（=>）处，用户可以像在常规bash终端命令行中那样输入命令，并且该命令将在会话涉及的所有主机上执行。要结束交互式会话，请按键盘上的CTRL+D 或键入exit或quit。

如果主机文件中没有指定用户名，则gpssh将以当前登录的用户身份执行命令。要确定当前登录的用户，请执行whoami命令。默认情况下，登录后 gpssh将转到远程主机上的会话用户的$HOME。为确保所有远程主机上的命令都能正确执行，应始终输入绝对路径。

如果在使用gpssh时遇到网络超时问题，可以使用-d和 -t选项或者在gpssh.conf文件中设置参数来控制 gpssh在验证初始ssh连接时使用的时间。有关配置文件的信息，请参阅gpssh配置文件gpssh配置文件。

1.2.2.1. 示例

gpssh -f all_hosts -v -e 'cat gp.log'

与文件hostfile_gpssh中列出的所有主机启动交互式SSH会话组：
$ gpssh -f hostfile_gpssh

在gpssh交互式命令提示符处，在此会话中涉及的所有主机上运行shell命令。

=> ls -a /data/primary/*

退出交互式会话：

=> exit
                => quit
使用名为sdw1和sdw2的主机启动非交互式SSH会话组， 并将包含多个名为command_file的命令的文件传递给gpssh：

$ gpssh -h sdw1 -h sdw2 -v -e < command_file
在主机sdw2和localhost上以非交互模式执行单个命令：

$ gpssh -h sdw2 -h localhost -v -e 'ls -a /data/primary/*'
                $ gpssh -h sdw2 -h localhost -v -e 'echo $GPHOME'
                $ gpssh -h sdw2 -h localhost -v -e 'ls -1 | wc -l'

1.2.2.2. gpssh-exkeys

在主机之间交换SSH公钥。

描述：
gpssh-exkeys工具在指定的主机名（或主机地址）之间交换SSH密钥。这允许Greenplum主机和网络接口之间的SSH连接，而不需要口令提示。该工具用于初将Greenplum数据库系统准备好用于无口令的SSH访问，以及在扩展Greenplum数据库系统时添加额外的ssh密钥。

要指定参与初始SSH密钥交换的主机，请使用-f 选项指定包含主机名列表的文件（推荐），或使用-h选项在命令行上指出单个主机名。至少需要一个主机名（-h）或主机文件。请注意，本地主机默认包含在密钥交换中。

指定要添加到现有Greenplum数据库系统的新扩展主机，请使用-e和-x选项。-e选项指定一个文件，其中包含系统中已有SSH密钥的现有主机列表。-x 选项指定一个文件，其中包含需要参与SSH密钥交换的新主机列表

密钥要作为当前登录的用户交换。用户应该执行两次密钥交换过程：一次作为root用户，一次作为gpadmin用户（拥有Greenplum数据库安装的用户）。Greenplum数据库管理要求在Greenplum数据库系统的所有主机上创建相同的非root用户，并且这些工具必须能够以该用户的身份连接到所有主机而无需口令。

gpssh-exkeys工具使用以下步骤执行密钥交换：

为当前用户创建一个RSA标识密钥对（如果尚不存在）。该密钥对中的公钥被添加到当前用户的authorized_keys文件中。
使用-h、-f、-e以及-x选项指定的每台主机的主机密钥更新当前用户的known_hosts文件。
使用ssh连接到每个主机，并获取authorized_keys、known_hosts和id_rsa.pub文件以设置无口令访问。
将从每个主机获取的id_rsa.pub 文件中的密钥添加到当前用户的authorized_keys文件中。
使用新的主机信息（如果有）更新所有主机上的authorized_keys、known_hosts和id_rsa.pub文件。

示例
在文件hostfile_exkeys中列出的所有主机名和地址之间交换SSH密钥：

$ gpssh-exkeys -f hostfile_exkeys

在主机sdw1、sdw2和sdw3之间交换SSH密钥：

$ gpssh-exkeys -h sdw1 -h sdw2 -h sdw3

1.3. 获取gp慢查询

查询执行时间大于10分的慢查询

gpperfmon 库下的gpmetrics对象

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 minitues 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'


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 minitues 
FROM
	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'

2. 会话相关：

2.1. 查看和杀掉当前会话

SELECT
    procpid,
    START,
    now() - START AS lap,
    current_query,
 	 -- count() over() count_num,
  	t2.rolname,t3.rsqname,
  	ip
FROM
    (
        SELECT
            backendid,
            pg_stat_get_backend_userid(S.backendid) as uid,
            pg_stat_get_backend_client_addr(S.backendid) as ip,
            pg_stat_get_backend_pid (S.backendid) AS procpid,
            pg_stat_get_backend_activity_start (S.backendid) AS START,
            pg_stat_get_backend_activity (S.backendid) AS current_query
        FROM
            (
                SELECT
                    pg_stat_get_backend_idset () AS backendid
            ) AS S
    ) AS t1 left join pg_authid  t2 on t1.uid=t2.oid
    left join pg_resqueue t3 on t2.rolresqueue=t3.oid
WHERE
    current_query!= '<IDLE>' and rolname = 'gpadmin'
ORDER BY lap DESC;

select * from gp_toolkit.gp_locks_on_relation ;

--杀死查询进程
select pg_terminate_backend(32004);

# 取消该后台查询进程
--假如PG_TERMINATE_BACKEND(进程ID)不能杀死session的情况下，我们可以使用kill -9 pid强制杀死session
SELECT PG_CANCEL_BACKEND(进程ID); --取消后台操作，回滚未提交事物don't kill当前session
SELECT PG_TERMINATE_BACKEND(进程ID); --取消后台操作，回滚未提交事物，kill当前查询的session



查询当前会话中未提交事物超过30秒的
SELECT
	pid,
	usename,
	application_name,
	client_addr,
	STATE,
	query,
	waiting_reason,
	EXTRACT ( epoch FROM ( now( ) - query_start ) ) waiting_seconds 
FROM
	pg_stat_activity 
WHERE
	STATE IN ( 'idle in transaction', 'idle in transaction(aborted)' ) 
	AND EXTRACT ( epoch FROM ( now( ) - query_start ) ) > 30

2.2. gp只停止一个主机上的segment

清除master standy
gpinitstandby -r n2.gp.test

重新添加
 gpinitstandby -s n2.gp.test
 gpstate -f
 
 
gpstop --host n3.gp.test
gpstart --host n2.gp.test


gpstart --host n3.gp.test

2.3. analyzed

SELECT
	'vacuum full ' || SCHEMA || '.' || TABLE_NAME || ';  select pg_sleep(10);' 
FROM
	gpmetrics.gpcc_table_info 
WHERE
	(
		last_del > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		OR last_upd > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		OR last_ins > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		or last_idx_scan > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
	) 
	AND SIZE / 1024 / 1024 > 100 
	AND SCHEMA <> 'pg_catalog';

2.4. gpstate 命令详解

gpstate显示正在运行的Greenplum数据库系统的状态。

2.4.1. 概要

gpstate [-d master_data_directory] [-B parallel_processes] 
2          [-s | -b | -Q | -e] [-m | -c] [-p] [-i] [-f] [-v | -q] | -x 
3          [-l log_directory]
4gpstate -? | -h | --help

2.4.2. 描述

gpstate工具显示有关正在运行的Greenplum数据库实例的信息。由于Greenplum数据库系统由跨多台机器的多个PostgreSQL数据库实例（Segment）组成，因此用户可能需要了解关于Greenplum数据库系统的额外信息。gpstate 工具为Greenplum数据库系统提供了额外的状态信息，例如：

哪台Segment主机已被关闭？
Master和Segment配置信息（主机、数据目录等）。
系统使用的端口。
主Segment到其相应的镜像Segment的映射。

2.4.3. 选项

-b（简要状态）

可选。显示Greenplum数据库系统状态的简要概述。这是默认选项。

-B parallel_processes

并行检查的Segment数。如果未指定，则工具将根据需要检查多少个Segment实例启动最多60个并行进程。

-c（显示主Segment到镜像Segment的映射）

可选。显示主Segment到镜像Segment的映射。

-d master_data_directory

可选。Master的数据目录。如果未指定，则使用为$MASTER_DATA_DIRECTORY 设置的值。

-e（显示镜像状态出问题的Segment）

显示具有潜在问题的主要/镜像Master对的详细信息，例如
1）活动Segment正在Change Tracking模式下运行， 表示Segment处于关闭状态；
2）活动Segment处于重新同步模式，这意味着它正在赶上对镜像的更改；
3） 一个Segment不是它的首选角色，例如在系统初始化时作为一个主Segment的现在作为一个镜像Segment， 这意味着用户可能有一台或多台Segment主机的处理负载不平衡。

-f（显示后备Master的详情）

显示后备Master主机的详细信息（如果配置）。

-i（显示Greenplum数据库的版本）

显示每个实例的Greenplum数据库软件版本信息。

-l logfile_directory

写入日志文件的目录。默认为~/gpAdminLogs。

-m（列出镜像）

可选。列出系统中的镜像Segment实例及其当前角色和同步状态。

-p（显示端口）

列出整个Greenplum数据库系统使用的端口号。

-q（没有屏幕输出）

可选。以静默模式运行。除了警告信息之外，屏幕上不显示命令输出。但是， 这些信息仍然写入到日志文件中。

-Q（快速状态）

可选。在Master主机上的系统目录中检查Segment的状态。不直接向Segment征询状态。

-s（详细状态）

可选。显示Greenplum数据库系统的详细状态信息.

-v（详细输出）

可选。显示错误消息并输出详细的状态和进度信息。

-x (expand)

Optional. Displays detailed information about the progress and state of a Greenplum system expansion.

-? | -h | —help (help)

显示在线帮助。

2.4.4. 输出字段定义

以下输出字段由主机的gpstate -s报告：


输出数据	描述
Master host	Master的主机名
Master postgres process ID	主数据库侦听进程的PID
Master data directory	主数据目录的文件系统位置
Master port	Master上postgres数据库监听器进程的端口
Master current role	dispatch = 常规操作模式utility = 维护模式
Greenplum array configuration type	Standard = 每台主机一个NICMulti-Home = 每台主机多个NIC
Greenplum initsystem version	系统第一次初始化时的Greenplum数据库版本
Greenplum current version	Greenplum数据库当前的版本
Postgres version	Greenplum数据库基于的PostgreSQL版本
Greenplum mirroring status	物理镜像或无镜像
Master standby	后备Master的主机名
Standby master state	后备Master的状态：活跃或被动

以下输出字段由gpstate -s为每个Segment报告：

输出数据	描述
Hostname	系统配置的主机名
Address	网络地址主机名（NIC名称）
Datadir	Segment数据目录的文件系统位置
Port	Segment的postgres数据库监听器进程的端口号
Current Role	Segment的当前角色：Mirror或 Primary
Preferred Role	系统初始化时的角色：Mirror或 Primary
Mirror Status	主/镜像Segment对的状态：Synchronized = 两者上的数据都是最新Resynchronization = 数据当前正被从一个拷贝到另一个Change Tracking = Segment停止并且活动Segment正在记录更改
Change tracking data size	当处于Change Tracking模式中时， 更改日志文件的尺寸（如果应用压缩，尺寸可能增长或者收缩）
Estimated total data to synchronize	当处于Resynchronization模式中时， 剩下要同步的数据的估计尺寸
Data synchronized	当处于Resynchronization模式中时， 已经被同步的数据的估计尺寸
Estimated resync progress with mirror	当处于Resynchronization模式中时， 完成百分比的估计值
Estimated resync end time	当处于Resynchronization模式中时， 完成时间的估计
File postmaster.pid	postmaster.pid锁文件的状态： Found或Missing
PID from postmaster.pid file	postmaster.pid文件中找到的PID
Lock files in tmp	为Segment的postgres会在 tmp中创建Segment端口锁文件（当Segment关闭时会移除该文件）
Active PID	Segment的活动进程ID
Master reports status as	系统目录中报告的Segment状态： Up或Down
Database status	Greenplum数据库对进入请求的状态： Up、Down或Suspended。 Suspended状态意味着当Segment正从一种状态转移到另一种状态时， 数据库活动被临时暂停。

以下输出字段由gpstate -f为每个后备Master复制状态报告：

输出数据	描述
Standby address	后备Master的主机名
Standby data dir	后备Master的数据目录的文件系统位置
Standby port	后备Master的postgres数据库监听器进程的端口
Standby PID	后备Master的进程ID
Standby status	后备Master的状态：Standby host passive
WAL Sender State	预写式日志（WAL）流状态： streaming、 startup、backup、catchup
Sync state	WAL发送者同步状态：sync
Sent Location	WAL发送者事务日志（xlog）记录发送位置
Flush Location	WAL接收者xlog记录刷入位置
Replay Location	后备上xlog记录重放位置

2.4.5. 命令示例

显示Greenplum数据库系统的详细信息：

查询更新和删除频繁的表和对应操作的数据行数

(select a.table_name,a.owner,(a.n_tup_del-b.n_tup_del) delete_count  from gpmetrics.gpcc_table_info a join gpmetrics.gpcc_table_info b on a.table_name=b.table_name
WHERE
	a.last_del > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
	AND a.SIZE / 1024 / 1024 > 100 
	AND a.SCHEMA <> 'pg_catalog' 
  and b.last_del > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
	and (a.n_tup_del-b.n_tup_del) > 50000
	order by (a.n_tup_del-b.n_tup_del)  desc)
	union
(select a.table_name,a.owner,(a.n_tup_upd-b.n_tup_upd) delete_count  from gpmetrics.gpcc_table_info a join gpmetrics.gpcc_table_info b on a.table_name=b.table_name
WHERE
	a.last_upd > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
	AND a.SIZE / 1024 / 1024 > 100 
	AND a.SCHEMA <> 'pg_catalog' 
  and b.last_upd > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
	and (a.n_tup_upd-b.n_tup_upd) > 50000
	order by (a.n_tup_upd-b.n_tup_upd)  desc)
	






( SELECT
	'vacuum full ' || A.SCHEMA || '.' || A.TABLE_NAME || ';  select pg_sleep(10);' 
	FROM
		gpmetrics.gpcc_table_info
		A JOIN gpmetrics.gpcc_table_info b ON A.TABLE_NAME = b.TABLE_NAME 
	WHERE
		A.last_del > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		AND A.SIZE / 1024 / 1024 > 100 
		AND A.SCHEMA <> 'pg_catalog' 
		AND b.last_del > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
		AND ( A.n_tup_del - b.n_tup_del ) > 50000 
	ORDER BY
		( A.n_tup_del - b.n_tup_del ) DESC 
	) UNION
	(
	SELECT
		'vacuum full ' || A.SCHEMA || '.' || A.TABLE_NAME || ';  select pg_sleep(10);' 
	FROM
		gpmetrics.gpcc_table_info
		A JOIN gpmetrics.gpcc_table_info b ON A.TABLE_NAME = b.TABLE_NAME 
	WHERE
		A.last_upd > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		AND A.SIZE / 1024 / 1024 > 100 
		AND A.SCHEMA <> 'pg_catalog' 
		AND b.last_upd > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
		AND ( A.n_tup_upd - b.n_tup_upd ) > 50000 
	ORDER BY
		( A.n_tup_upd - b.n_tup_upd ) DESC 
	) UNION
		(
	SELECT
		'vacuum full ' || A.SCHEMA || '.' || A.TABLE_NAME || ';  select pg_sleep(10);' 
	FROM
		gpmetrics.gpcc_table_info
		A JOIN gpmetrics.gpcc_table_info b ON A.TABLE_NAME = b.TABLE_NAME 
	WHERE
		A.last_ins > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		AND A.SIZE / 1024 / 1024 > 100 
		AND A.SCHEMA <> 'pg_catalog' 
		AND b.last_ins > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
		AND ( A.n_tup_ins - b.n_tup_ins ) > 10000 
	ORDER BY
		( A.n_tup_ins - b.n_tup_ins ) DESC 
	)  



指定数据库：
( SELECT
	'vacuum full ' || A.SCHEMA || '.' || A.TABLE_NAME || ';  select pg_sleep(10);' 
	FROM
		gpmetrics.gpcc_table_info
		A JOIN gpmetrics.gpcc_table_info b ON A.TABLE_NAME = b.TABLE_NAME 
		join pg_database c on a.dbid=c.oid
	WHERE
		A.last_del > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		AND A.SIZE / 1024 / 1024 > 100 
		AND A.SCHEMA <> 'pg_catalog' 
		AND b.last_del > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
		AND ( A.n_tup_del - b.n_tup_del ) > 50000 
		AND c.datname='datawarehouse'
	ORDER BY
		( A.n_tup_del - b.n_tup_del ) DESC 
	) UNION
	(
	SELECT
		'vacuum full ' || A.SCHEMA || '.' || A.TABLE_NAME || ';  select pg_sleep(10);' 
	FROM
		gpmetrics.gpcc_table_info
		A JOIN gpmetrics.gpcc_table_info b ON A.TABLE_NAME = b.TABLE_NAME 
		join pg_database c on a.dbid=c.oid
	WHERE
		A.last_upd > ( NOW( ) - INTERVAL '1 day' ) :: TIMESTAMP 
		AND A.SIZE / 1024 / 1024 > 100 
		AND A.SCHEMA <> 'pg_catalog' 
		AND b.last_upd > ( NOW( ) - INTERVAL '2 day' ) :: TIMESTAMP 
		AND ( A.n_tup_upd - b.n_tup_upd ) > 50000 
		AND c.datname='datawarehouse'
	ORDER BY
		( A.n_tup_upd - b.n_tup_upd ) DESC 
	);

强制走索引

使用 SET enable_seqscan TO off 强制使用索引
如果你确定索引是有效的，并且查询的执行计划不合理，可以尝试强制查询使用索引。通过设置 enable_seqscan 为 off，Greenplum 会优先考虑使用索引而非全表扫描。

解决方案：

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;

posted @ 2025-06-26 16:04 数据库小白（专注）阅读(54) 评论(0) 收藏举报

刷新页面返回顶部

数据库小白（专注）