/
test_timescaledb.sh
155 lines (106 loc) · 4.56 KB
/
test_timescaledb.sh
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
## 安装 TimescaleDB
# add PostgreSQL's third party repository
sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -c -s`-pgdg main' >> /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Add our PPA
add-apt-repository ppa:timescale/timescaledb-ppa
apt-get update
# To install for PG 10.2+
apt install timescaledb-postgresql-10
# 移动数据目录
cp -rp /var/lib/postgresql /data/postgresql
rm -rf /var/lib/postgresql
ln -s /data/postgresql /var/lib/postgresql
chown -h postgres:postgres /var/lib/postgresql
ls -ld /var/lib/postgresql
# 编辑配置文件
/etc/postgresql/10/main/postgresql.conf
# 修改访问权限
/etc/postgresql/10/main/pg_hba.conf
# local all all peer
# host all all 0.0.0.0/0 md5
# 控制
service postgresql start
service postgresql status
service postgresql restart
service postgresql stop
systemctl stop postgresql.service
systemctl status postgresql.service
ps -efl | grep postgres
free -h
# 修改 postgres 用户密码
su postgres
psql
postgres=# \password
Enter new password:
Enter it again:
postgres=#
# 卸载
apt remove --purge timescaledb-postgresql-10
apt remove --purge postgresql-10
apt remove timescaledb-postgresql-10
apt remove postgresql-10
apt autoremove
# 日志
tail -f /var/log/postgresql/postgresql-10-main.log
## 导入小数据集并计时
# 导入 devices
timescaledb-parallel-copy \
--workers 12 \
--reporting-period 5s \
--copy-options "CSV" \
--connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" \
--db-name test \
--table device_info \
--file /data/devices/csv/devices_big_device_info.csv
# 导入 readings
timescaledb-parallel-copy \
--workers 12 \
--reporting-period 5s \
--copy-options "CSV" \
--connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" \
--db-name test \
--table readings \
--batch-size 10000 \
--file /data/devices/csv/devices_big_readings.csv
# 6m 30s
# 导出数据并计时
chown postgres:postgres /data/devices
su postgres
cd ~
time psql -d test -c "\COPY (SELECT * FROM readings) TO /data/devices/devices_dump.csv DELIMITER ',' CSV"
# 占用空间
du -sh /mnt/data/postgresql/10/main
# 15 GB
## 导入大数据集并计时
for f in /data/TAQ/csv/*.csv ; do
tail -n +2 $f | timescaledb-parallel-copy \
--workers 12 \
--reporting-period 1s \
--copy-options "CSV" \
--connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" \
--db-name test \
--table taq \
--batch-size 200000
echo "文件 $f 导入完成"
done
tail -n +2 /data/TAQ/csv/TAQ20070802.csv | timescaledb-parallel-copy --db-name test --table taq --copy-options "CSV" --workers 3 --reporting-period 1s --connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" --batch-size 200000
tail -n +2 /data/TAQ/csv/TAQ20070803.csv | timescaledb-parallel-copy --db-name test --table taq --copy-options "CSV" --workers 3 --reporting-period 1s --connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" --batch-size 200000
tail -n +2 /data/TAQ/csv/TAQ20070806.csv | timescaledb-parallel-copy --db-name test --table taq --copy-options "CSV" --workers 3 --reporting-period 1s --connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" --batch-size 200000
tail -n +2 /data/TAQ/csv/TAQ20070807.csv | timescaledb-parallel-copy --db-name test --table taq --copy-options "CSV" --workers 3 --reporting-period 1s --connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" --batch-size 200000
tail -n +2 $f | timescaledb-parallel-copy \
--workers 4 \
--reporting-period 5s \
--copy-options "CSV" \
--connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable" \
--db-name test \
--table taq \
--batch-size 15000
# at 17h14m38s, row rate 0.000000/sec (period), row rate 4813.130307/sec (overall), 2.987895E+08 total rows
# COPY 298789504
# at 20h32m9s, row rate 0.000000/sec (period), row rate 4462.411737/sec (overall), 3.299016E+08 total rows
# COPY 329901638
# at 20h40m27s, row rate 0.000000/sec (period), row rate 4525.943276/sec (overall), 3.368524E+08 total rows
# COPY 336852381
# at 20h35m51s, row rate 0.000000/sec (period), row rate 4427.001260/sec (overall), 3.282666E+08 total rows
# COPY 328266571