Blog of Yang
0%

sftp_sql_1

Posted on Edited on In
Symbols count in article: Reading time ≈ NaN:aN

2022/3/20 发现sftp连接不上，显示connection closed

查看 sshd_config 配置文件， 发现没有/usr/libexec/sftp-server可执行程序

override default of no subsystems

Subsystem sftp /usr/libexec/opensshj/sftp-server
可以修改如下：

override default of no subsystems

Subsystem sftp internal-sftp
并且发现里头sftp路径为：usr/libexec/opensshj/sftp-server

修改j为好，但仍然connection closed

sftp-server 与 internal-sftp 的区别可看

https://serverfault.com/questions/660160/openssh-difference-between-internal-sftp-and-sftp-server

ssh可以使用但sftp不能使用

http://bbs.chinaunix.net/thread-4252902-1-1.html

ssh可以使用sftp不可以使用

Vim /etc/passwd
把 bbscgl:500:500::/kssftp:/bin/false
改为 bbscgl:500:500::/kssftp:/bin/bash

但是Vi /etc/passwd 失败

尝试重启服务sshd.service

systemctl restart sshd.service

防火墙

sudo systemctl status wall fired

查看防火墙状态：sudo systemctl status firewalld
关闭防火墙:sudo systemctl stop firewalld

sftp -P 5432 root@127.0.0.1

ssh -p 5432 root@127.0.0.1

1
chown jay:fefjay a.txt #修改文件所属用户为jay，所属用户组为fefjay
1

chown gpadmin:gpadmin tel.csv

使用Fillzilla连接centos7：
1.关闭防火墙
查看防火墙状态：sudo systemctl status firewalld
关闭防火墙:sudo systemctl stop firewalld
2.开启网卡
centos默认不开启网卡，使用ip addr 命令查看是否含有ens33属性，如果没有ens33属性，说明没有开启网卡，参考centos7如何查看ip信息
3.检查ssh是否安装
命令：yum list installed | grep openssh-server
表示已安装，否则执行命令：yum install openssh-serve
进行安装
4.更改SSH配置文件（很重要）
编辑SSH配置文件：vi /etc/ssh/sshd_config
如何编辑：
通过vi命令进入编辑模式以后，键盘输入“I”后即可开始插入或者修改内容

编辑完成以后通过“ESC”键即可退出编辑模式，在所有的操作完成以后，键盘输入“：wq”保存后回车

关于vi使用可以参考vi编辑与保存

将监听端口和端口地址打开

放开root权限：

开启使用用户名密码来作为连接验证：

5.配置FillZilla连接（最重要的一步）
在第四步我们已经开启了22端口，ip地址一定要用第二步enss33属性里边的ip，不能使用本机的ipconfig命令查看ip地址，否则会报连接拒绝connect refused错误，连接帐号root，密码就是登陆密码。

正确配置完成以后，即可连接上。撒花！

wget –header ‘X-GP-PROTO:0’ mdw:8081/tel.csv

%s/“//g

导入stif。csvcsv
显示
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL 错误 [22021]: ERROR: invalid byte sequence for encoding "UTF8": 0xb3  (seg1 slice1 127.0.0.1:6001 pid=2640)

SQL 错误 [22021]: ERROR: invalid byte sequence for encoding "UTF8": 0xb3  (seg0 slice1 127.0.0.1:6000 pid=3852)
  在位置：External table stif, line 1 of file gpfdist://mdw:8081/stif.csv

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [22021]: ERROR: invalid byte sequence for encoding "UTF8": 0xb3  (seg0 slice1 127.0.0.1:6000 pid=3852)
  在位置：External table stif, line 1 of file gpfdist://mdw:8081/stif.csv
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.model.impl.jdbc.struct.JDBCTable.readData(JDBCTable.java:190)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4821)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0xb3  (seg0 slice1 127.0.0.1:6000 pid=3852)
  在位置：External table stif, line 1 of file gpfdist://mdw:8081/stif.csv
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
	... 7 more

SQL 错误 [22021]: ERROR: invalid byte sequence for encoding “UTF8”: 0xb3 (seg1 slice1 127.0.0.1:6001 pid=4145)
在位置：External table stif, line 1 of file gpfdist://mdw:8081/stif.csv

解决办法1:转换成txt再导入

无法转换成txt，python显示

1
2
3
4
5
6
7
8
Traceback (most recent call last):
  File "/Users/roy/PycharmProjects/data_transfer/main.py", line 11, in <module>
    [
  File "/Users/roy/PycharmProjects/data_transfer/main.py", line 11, in <listcomp>
    [
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/codecs.py", line 322, in decode
    (result, consumed) = self._buffer_decode(data, self.errors, final)
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb3 in position 20: invalid start byte
解决办法2:切换encoding

format ‘csv’, delimiter ‘, encoding ‘ISO-8859-1’)” $dbname

psql -c “copy $schemaname.$tbname from ‘$dirname/$filename’ with(format ‘csv’, delimiter ‘, encoding ‘ISO-8859-1’)” $dbname

可以导入，但是导入了以后还是乱码

解决办法3:使用工具切换成txt

使用wps另存为txt并，解决了大数据量的

导入relation。csv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL 错误 [22021]: ERROR: invalid byte sequence for encoding "UTF8": 0xc8 0xfd  (seg1 slice1 127.0.0.1:6001 pid=5441)
  在位置：External table relation, line 1 of file gpfdist://mdw:8081/relation.csv



org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [22021]: ERROR: invalid byte sequence for encoding "UTF8": 0xc8 0xfd  (seg1 slice1 127.0.0.1:6001 pid=5441)
  在位置：External table relation, line 1 of file gpfdist://mdw:8081/relation.csv
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.model.impl.jdbc.struct.JDBCTable.readData(JDBCTable.java:190)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4821)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0xc8 0xfd  (seg1 slice1 127.0.0.1:6001 pid=5441)
  在位置：External table relation, line 1 of file gpfdist://mdw:8081/relation.csv
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
	... 7 more

centos更改文件所有者

su - root

cd /home/gpadmin/gpfdist/

chown zzx:zzxGroup a.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
ssh -p 50009 admin@agilec.gicp.net

密码：adminADMIN

ssh -p 22 admin@192.168.0.253

密码：admin 

sftp -P 22 hcc@192.168.0.9  

密码：ccfCCF@2022

roy@yangzonyoudeMBP ~ % ssh -p 50009 admin@agilec.gicp.net

The authenticity of host '[agilec.gicp.net]:50009 ([222.128.178.75]:50009)' can't be established.

ECDSA key fingerprint is SHA256:xOPh2JzO597wKLvgc7q73C4AYjzCeYBZR7hTuNk+uT4.

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Warning: Permanently added '[agilec.gicp.net]:50009,[222.128.178.75]:50009' (ECDSA) to the list of known hosts.

admin@agilec.gicp.net's password: 

Last login: Tue May 17 00:31:42 2022 from 119.166.234.126

[admin@cdh01 /home/admin]$ ssh -p 22 admin@192.168.0.253

admin@192.168.0.253's password: 

Permission denied, please try again.

admin@192.168.0.253's password: 

Last failed login: Tue May 17 01:22:15 CST 2022 from 192.168.0.21 on ssh:notty

There was 1 failed login attempt since the last successful login.

Last login: Tue May 17 00:35:48 2022 from 192.168.0.21

[admin@master /home/admin]$

在外先登录跳板机，ssh admin@agilec.gicp.net 密码：adminADMIN
然后通过内网ssh登录 ssh admin@192.168.0.253 密码：admin 部署程序的用户，你们自己定义就行，做好软件安装记录文档。
如果需要使用root用户，则sudo su - root 输入admin的密码，就可以切换了。

跳板机ssh端口号50009

5台虚拟机，8核32GB500GB磁盘 都可以通过admin / admin登录，可疑通过admin切换到root账户。
192.168.0.170
192.168.0.171
192.168.0.172
192.168.0.173
192.168.0.174

一共六台服务器，192.168.0.253是主节点

SFTP
外网：agilec.gicp.net 端口：20210
内网：192.168.0.9 端口：22
用户：hcc 密码：ccfCCF@2022
路径：/CCF

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ssh -p 50009 admin@agilec.gicp.net

密码：adminADMIN

ssh -p 22 admin@192.168.0.253

密码：admin 

sftp -P 22 hcc@192.168.0.9  

密码：ccfCCF@2022

sftp -P 20210 admin@agilec.gicp.net
sftp -P 20210 hcc@agilec.gicp.net
sftp -P 22 hcc@192.168.0.9  

密码：ccfCCF@2022

linux解压zip

1
2
3
4
5
6
7
8
9
10
11
12
13
14
cd zip
ls
unzip abc.zip
#出现inflating即为成功
unzip 2022-03-12_1.zip
unzip 2022-03-13_1.zip
unzip 2022-03-14_1.zip
unzip 2022-03-15_1.zip
unzip 2022-03-16_1.zip
unzip 2022-03-17_1.zip
unzip 2022-03-18_1.zip
unzip 2022-03-19_1.zip
unzip 2022-03-20_1.zip
unzip 2022-03-21_1.zip

vi正则

1
%s/“//g

vim正则

1
rpm -qa|grep vim
1
2
3
4
5
6
7
8
9
10
11
:%s/foo/bar/g    会在全局范围(%)查找foo并替换为bar，所有出现都会被替换（g）
:%s/&#@/,/g  会在全局范围(%)查找&#@并替换为","，所有出现都会被替换（g）

:w 保存不退出
:w 新文件名 把文件另存为新文件
:q 不保存退出
:wq 保存退出
:! 强制
:q! 强制不保存退出，用于修改文件之后，不保存数据退出
:wq! 强制保存退出，当文件的所有者或 root 用户，对文件没有写权限的时候，强制写入数据使用
ctrl+c强制中断

sed

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sed -i "s/&#@/,/g" stif_2022-03-12.csv
sed -i "s/&#@/,/g" address_2022-03-12.csv
sed -i "s/&#@/,/g" bact_2022-03-12.csv
sed -i "s/&#@/,/g" cert_2022-03-12.csv
sed -i "s/&#@/,/g" org_2022-03-12.csv
sed -i "s/&#@/,/g" pact_2022-03-12.csv
sed -i "s/&#@/,/g" person_2022-03-12.csv
sed -i "s/&#@/,/g" relation_2022-03-12.csv
sed -i "s/&#@/,/g" tel_2022-03-12.csv


sed -i "s/&#@/,/g" stif_2022-03-19.csv
sed -i "s/&#@/,/g" stif_2022-03-20.csv
sed -i "s/&#@/,/g" stif_2022-03-21.csv



gpfdist导入

1
2
3
4
5
ps -ef | grep gpfdist
gpfdist -d /home/admin/data/ -p 8081 -l /home/admin/data.log &



time导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#插入
INSERT INTO stif (select * from ext_stif);
INSERT INTO address (select * from ext_address);
INSERT INTO bact (select * from ext_bact);
INSERT INTO cert (select * from ext_cert);
INSERT INTO org (select * from ext_org);
INSERT INTO pact (select * from ext_pact);
INSERT INTO person (select * from ext_address);
INSERT INTO relation (select * from ext_relation);
INSERT INTO tel (select * from ext_tel);

time psql gpdb -c "INSERT INTO stif (select * from ext_stif)";
time psql gpdb -c "INSERT INTO address (select * from ext_address)";
time psql gpdb -c "INSERT INTO bact (select * from ext_bact)";
time psql gpdb -c "INSERT INTO cert (select * from ext_cert)";
time psql gpdb -c "INSERT INTO org (select * from ext_org)";
time psql gpdb -c "INSERT INTO pact (select * from ext_pact)";
time psql gpdb -c "INSERT INTO person (select * from ext_person)";
time psql gpdb -c "INSERT INTO relation (select * from ext_relation)";
time psql gpdb -c "INSERT INTO tel (select * from ext_tel)";


linux给文件改名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
sudo mv test.txt new.txt

sudo mv address_2022-03-13.csv address_2022-03-12.csv
sudo mv bact_2022-03-13.csv bact_2022-03-12.csv
sudo mv cert_2022-03-13.csv cert_2022-03-12.csv
sudo mv org_2022-03-13.csv org_2022-03-12.csv
sudo mv pact_2022-03-13.csv pact_2022-03-12.csv
sudo mv person_2022-03-13.csv person_2022-03-12.csv
sudo mv relation_2022-03-13.csv relation_2022-03-12.csv
sudo mv stif_2022-03-13.csv stif_2022-03-12.csv
sudo mv tel_2022-03-13.csv tel_2022-03-12.csv

sudo mv address_2022-03-14.csv address_2022-03-12.csv
sudo mv bact_2022-03-14.csv bact_2022-03-12.csv
sudo mv cert_2022-03-14.csv cert_2022-03-12.csv
sudo mv org_2022-03-14.csv org_2022-03-12.csv
sudo mv pact_2022-03-14.csv pact_2022-03-12.csv
sudo mv person_2022-03-14.csv person_2022-03-12.csv
sudo mv relation_2022-03-14.csv relation_2022-03-12.csv
sudo mv stif_2022-03-14.csv stif_2022-03-12.csv
sudo mv tel_2022-03-14.csv tel_2022-03-12.csv

sudo mv address_2022-03-15.csv address_2022-03-12.csv
sudo mv bact_2022-03-15.csv bact_2022-03-12.csv
sudo mv cert_2022-03-15.csv cert_2022-03-12.csv
sudo mv org_2022-03-15.csv org_2022-03-12.csv
sudo mv pact_2022-03-15.csv pact_2022-03-12.csv
sudo mv person_2022-03-15.csv person_2022-03-12.csv
sudo mv relation_2022-03-15.csv relation_2022-03-12.csv
sudo mv stif_2022-03-15.csv stif_2022-03-12.csv
sudo mv tel_2022-03-15.csv tel_2022-03-12.csv

sudo mv address_2022-03-16.csv address_2022-03-12.csv
sudo mv bact_2022-03-16.csv bact_2022-03-12.csv
sudo mv cert_2022-03-16.csv cert_2022-03-12.csv
sudo mv org_2022-03-16.csv org_2022-03-12.csv
sudo mv pact_2022-03-16.csv pact_2022-03-12.csv
sudo mv person_2022-03-16.csv person_2022-03-12.csv
sudo mv relation_2022-03-16.csv relation_2022-03-12.csv
sudo mv stif_2022-03-16.csv stif_2022-03-12.csv
sudo mv tel_2022-03-16.csv tel_2022-03-12.csv

sudo mv address_2022-03-17.csv address_2022-03-12.csv
sudo mv bact_2022-03-17.csv bact_2022-03-12.csv
sudo mv cert_2022-03-17.csv cert_2022-03-12.csv
sudo mv org_2022-03-17.csv org_2022-03-12.csv
sudo mv pact_2022-03-17.csv pact_2022-03-12.csv
sudo mv person_2022-03-17.csv person_2022-03-12.csv
sudo mv relation_2022-03-17.csv relation_2022-03-12.csv
sudo mv stif_2022-03-17.csv stif_2022-03-12.csv
sudo mv tel_2022-03-17.csv tel_2022-03-12.csv

sudo mv address_2022-03-18.csv address_2022-03-12.csv
sudo mv bact_2022-03-18.csv bact_2022-03-12.csv
sudo mv cert_2022-03-18.csv cert_2022-03-12.csv
sudo mv org_2022-03-18.csv org_2022-03-12.csv
sudo mv pact_2022-03-18.csv pact_2022-03-12.csv
sudo mv person_2022-03-18.csv person_2022-03-12.csv
sudo mv relation_2022-03-18.csv relation_2022-03-12.csv
sudo mv stif_2022-03-18.csv stif_2022-03-12.csv
sudo mv tel_2022-03-18.csv tel_2022-03-12.csv

sudo mv address_2022-03-19.csv address_2022-03-12.csv
sudo mv bact_2022-03-19.csv bact_2022-03-12.csv
sudo mv cert_2022-03-19.csv cert_2022-03-12.csv
sudo mv org_2022-03-19.csv org_2022-03-12.csv
sudo mv pact_2022-03-19.csv pact_2022-03-12.csv
sudo mv person_2022-03-19.csv person_2022-03-12.csv
sudo mv relation_2022-03-19.csv relation_2022-03-12.csv
sudo mv stif_2022-03-19.csv stif_2022-03-12.csv
sudo mv tel_2022-03-19.csv tel_2022-03-12.csv

sudo mv address_2022-03-20.csv address_2022-03-12.csv
sudo mv bact_2022-03-20.csv bact_2022-03-12.csv
sudo mv cert_2022-03-20.csv cert_2022-03-12.csv
sudo mv org_2022-03-20.csv org_2022-03-12.csv
sudo mv pact_2022-03-20.csv pact_2022-03-12.csv
sudo mv person_2022-03-20.csv person_2022-03-12.csv
sudo mv relation_2022-03-20.csv relation_2022-03-12.csv
sudo mv stif_2022-03-20.csv stif_2022-03-12.csv
sudo mv tel_2022-03-20.csv tel_2022-03-12.csv

sudo mv address_2022-03-21.csv address_2022-03-12.csv
sudo mv bact_2022-03-21.csv bact_2022-03-12.csv
sudo mv cert_2022-03-21.csv cert_2022-03-12.csv
sudo mv org_2022-03-21.csv org_2022-03-12.csv
sudo mv pact_2022-03-21.csv pact_2022-03-12.csv
sudo mv person_2022-03-21.csv person_2022-03-12.csv
sudo mv relation_2022-03-21.csv relation_2022-03-12.csv
sudo mv stif_2022-03-21.csv stif_2022-03-12.csv
sudo mv tel_2022-03-21.csv tel_2022-03-12.csv

rm -f + 文件名
eg：rm -f  2018_12_26.stderrout.log.060121612 --执行完成即将这个文件删除
rm -f address_2022-03-12.csv
rm -f bact_2022-03-12.csv
rm -f cert_2022-03-12.csv
rm -f org_2022-03-12.csv
rm -f pact_2022-03-12.csv
rm -f person_2022-03-12.csv
rm -f relation_2022-03-12.csv
rm -f stif_2022-03-12.csv
rm -f tel_2022-03-12.csv


rm -f address_2022-03-13.csv
rm -f bact_2022-03-13.csv
rm -f cert_2022-03-13.csv
rm -f org_2022-03-13.csv
rm -f pact_2022-03-13.csv
rm -f person_2022-03-13.csv
rm -f relation_2022-03-13.csv
rm -f stif_2022-03-13.csv
rm -f tel_2022-03-13.csv