GreenPlum/PostGreSQL表锁处理

GreenPlum常见问题处理记录

一、GreenPlum执行命令，抛出异常Reason=‘Environment Variable MASTER_DATA_DIRECTORY not set!’

1 问题详情

[gpadmin@zxy_master bin]$ ./gpstate
20230321:12:26:21:019307 gpstate:zxy_master:gpadmin-[INFO]:-Starting gpstate with args:
20230321:12:26:21:019307 gpstate:zxy_master:gpadmin-[CRITICAL]:-gpstate failed. (Reason='Environment Variable MASTER_DATA_DIRECTORY not set!') exiting...
1
2
3

2 解决方案

修改~/.bashrc，添加如下两行即可

[gpadmin@zxy_master bin]$ vim ~/.bashrc

MASTER_DATA_DIRECTORY=/zxy/data/gpdata/master/gpseg-1
export MASTER_DATA_DIRECTORY

[gpadmin@zxy_master bin]$ source ~/.bashrc
1
2
3
4
5
6

3 处理测试

[gpadmin@zxy_master bin]$ ./gpstate
20230321:12:44:38:031337 gpstate:zxy_master:gpadmin-[INFO]:-Starting gpstate with args:
20230321:12:44:38:031337 gpstate:zxy_master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.4.0 build commit:564b89a8c6bef5e329a59f39dac438b13d9cb3fa'
20230321:12:44:38:031337 gpstate:zxy_master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.24 (Greenplum Database 6.4.0 build 
......
1
2
3
4
5

二、GreenPlum执行命令，抛出异常：File “./gpstart”, line 12, in < module> from gppylib.mainUtils import *

1 问题详情

原因是：使用root用户下修改/etc/profile文件，配置GP环境

export GP_HOME=/usr/local/greenplum-db
export GP_MASTER_DATA=/zxy/data/gpdata/master/gpseg-1
export PATH=$PATH:$GP_HOME/bin
1
2
3

再次登录gpadmin用户，启动的时候异常

[gpadmin@zxy_master bin]$ ./gpstart
Traceback (most recent call last):
  File "./gpstart", line 12, in <module>
    from gppylib.mainUtils import *
ImportError: No module named gppylib.mainUtils
1
2
3
4
5

2 问题解决

经过资料查询和检查，刷新一下greenplum_path.sh文件即可

gpadmin@zxy_master bin]$ source /usr/local/greenplum-db/greenplum_path.sh
1

为了避免每次修改都刷新这个文件:

修改~/.bashrc，添加如下一行即可

[gpadmin@zxy_master bin]$ vim ~/.bashrc

source /usr/local/greenplum-db/greenplum_path.sh

[gpadmin@zxy_master bin]$ source ~/.bashrc
1
2
3
4
5

3 处理测试

[gpadmin@zxy_master bin]$ ./gpstart
......
230321:13:28:52:027771 gpstart:zxy_master:gpadmin-[INFO]:-Database successfully started
1
2
3

三、GreenPlum数据库Standby故障处理

1.Standby故障

1.1.检查监控中心数据库状态

1.2.查看master节点数据库状态

su - gpadmin
gpstate -f
1
2

2.重启数据库

2.1.快速关闭数据库

[gpadmin@mdw pg_log]$ gpstop -M fast
...
Continue with Greenplum instance shutdown Yy|Nn (default=N):
> y
...
1
2
3
4
5

2.2.开启数据库

[gpadmin@mdw pg_log]$ gpstart
...
Continue with Greenplum instance startup Yy|Nn (default=N):
> y
...
1
2
3
4
5

2.3.再次查看数据库状态

gpstate -f
1

发现standby status = standby host passive,已经正常

但是最后缺是No entries found，等级是Info,但却是异常情况

3.重做standby

3.1.清除standby配置

在master节点，执行如下:gpinitstandby -f standby主机

[gpadmin@mdw pg_log]$ gpinitstandby -r smdw
...
Do you want to continue with deleting the standby master? Yy|Nn (default=N):
> y
...

1
2
3
4
5
6

3.2.添加standby

在master节点，执行如下:gpinitstandby -s stanby主机 -P port -S 数据路径

[gpadmin@mdw pg_log]$ gpinitstandby -s smdw -P 5432 -S /data/master/gpseg-1
...
Do you want to continue with standby master initialization? Yy|Nn (default=N):
> y
...
1
2
3
4
5

3.3.再次检查数据库状态

gpstate -f，再次检查可以发现No entries found已经不见，如下情况才是正常。

3.4.监控中心数据库状态

问题报错

在安装greenplum的时候做权限互通出现问题

[gpadmin@hadoop_zxy ~]$ gpssh-exkeys -f all_host_file
[ERROR]: Failed to ssh to zxy. No ECDSA host key is known for zxy and you have requested strict checking.
Host key verification failed.

1
2
3
4

四、GreenPlum安装遇见免密错误

1.问题解决

在进入gpadmin用户下进行免密操作

1. ssh-keygen

1.1生成密钥

[gpadmin@hadoop_zxy ~]$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/gpdata/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/gpdata/.ssh/id_rsa.
Your public key has been saved in /home/gpdata/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:WIZ6lg5LsX0NN6wFxQN/k7l8Zqujpu2hTyltV25xYT4 gpadmin@hadoop_zxy
The key's randomart image is:
+---[RSA 2048]----+
|        o+.      |
|       . +o  o   |
|    . . + *.=  o |
|     = = * + oo .|
|    = * S . o *E.|
|   . * . . . * +.|
|    . . . * . +  |
|         *.o.o   |
|        o==...   |
+----[SHA256]-----+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.2密钥拷贝

[gpadmin@hadoop_zxy ~]$ ssh-copy-id ****.**.**.****
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/gpdata/.ssh/id_rsa.pub"
The authenticity of host '****.**.**.**** (****.**.**.****)' can't be established.
ECDSA key fingerprint is SHA256:C/+BHxQ00jfgmaqAKHrxx2HBl27GHjCcerelvB9tH3s.
ECDSA key fingerprint is MD5:5e:08:d4:36:ad:29:cd:96:f9:ea:cb:69:61:7c:80:32.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
gpadmin@****.**.**.****'s password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '****.**.**.****'"
and check to make sure that only the key(s) you wanted were added.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. 再次执行权限互通

[gpadmin@hadoop_zxy ~]$ gpssh-exkeys -f all_host_file
[STEP 1 of 5] create local ID and authorize on local host
  ... /home/gpdata/.ssh/id_rsa file exists ... key generation skipped

[STEP 2 of 5] keyscan all hosts and update known_hosts file

[STEP 3 of 5] retrieving credentials from remote hosts
  ... send to ****.**.**.****

[STEP 4 of 5] determine common authentication file content

[STEP 5 of 5] copy authentication files to all remote hosts
  ... finished key exchange with ****.**.**.****

[INFO] completed successfully

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

五、GreenPlum/PostGreSQL表锁处理

数据库中遇到表锁的情况，可以通过select * from pg_stat_activity;查看表锁的进程及进程ID，从而取消进程，解锁。

1.模拟表锁

1.1 模拟表数据

创建lock_test表，并随意插入一条数据，用于后续模拟表锁使用。

postgres=# create table lock_test(id int , name text );
CREATE TABLE
postgres=# insert into lock_test values(1,'zxy');
INSERT 0 1
1
2
3
4

1.2 会话A：

在数据库可视化工具中，一般会自动提交事务。这里通过在服务端打开会话，通过begin和commit命令，开始事务和提交事务。

为模拟锁操作，这里开始了事务，并模拟插入一条数据，不提交。

postgres=# begin;
BEGIN
postgres=# insert into lock_test values(2,'zzz');
INSERT 0 1
postgres=# select * from lock_test;
 id | name
----+------
  1 | zxy
  2 | zzz
(2 rows)
1
2
3
4
5
6
7
8
9
10

1.3 会话B：

打开一个新的会话，通过查看表数据可知，会话A未提交的事务操作(插入一条新的数据)，在这里查看不到。

postgres=# select * from lock_test;
 id | name
----+------
  1 | zxy
(1 row)
1
2
3
4
5

2.查看锁进程

为方便查看查询结果，通过可视化工具执行select * form pg_stat_activity;命令来查看锁进程。

通过查看state字段下数据，发现进程pid = 23584的进程处于idle in transaction状态，这个就是我们未提交的事务，也就是会话A执行的内容。

3.处理表锁：

3.1 中断session

# 取消后台操作，回滚未提交事务
select pg_cancel_backend(pid);

# 中断session，回滚未提交事务
select pg_terminate_backend(pid);

1
2
3
4
5
6

执行pg_terminate_backend后，我们回到会话A，发现再去查询表数据的时候，会提示你，该会话已经被管理员关闭。自此已经解决了锁问题。

3.2 会话A:

postgres=# select * from lock_test;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
1
2
3
4
5
6