es、pg、mongoDB批量导入导出

一. es导入导出测试

1. 创建snapshot文件夹

mkdir /mnt/ssd1

chown -R es:es /mnt/ssd1/snapshot

2. master节点elasticsearch.yml添加

path.repo: ["/mnt/ssd1/snapshot"]

3. 创建repository

curl -XPUT -H 'content-type:application/json' http://hdh149:9200/_snapshot/my_repository -d '{"type": "fs", "settings": {"location": "/mnt/ssd1/snapshot", "max_snapshot_bytes_per_sec":"60MB", "max_restore_byte_per_sec": "60MB" }}'

4. 创建快照

curl -XPUT -H 'content-type:application/json' http://hdh149:9200/_snapshot/my_repository/snapshot_50101?wait_for_completion=true -d '{"indices": "test0410050101"}'

5. 拷贝快照文件到新集群

/mnt/ssd1/snapshot，添加运行快照的配置

6. 目标集群创建repository

curl -XPUT -H 'content-type:application/json' http://hdh149:9200/_snapshot/my_repository -d '{"type": "fs", "settings": {"location": "/mnt/ssd1/snapshot"}}'

7. 查看快照信息并从快照恢复数据

curl -XGET http://hdh149:9200/_snapshot/my_repository/_all

curl -XPOST -H 'content-type:application/json' http://hdh149:9200/_snapshot/my_repository/snapshot_50101/_restore?wait_for_completion=true -d '{ "indices": "test0410050101", "rename_replacement": "test0410050101"}'

常见问题：

（1）es重启：su - elasticsearch -c “/usr/lib/cluster001/SERVICE-ELASTICSEARCH-retro-1/bin/elasticsearch -d -p /var/run/cluster001/SERVICE-ELASTICSEARCH-retro-1/elasticsearch.pid”

修改es使用root用户启动：es 5之后不能使用root启动

（2）启动报错：

Exception in thread "main" java.nio.file.AccessDeniedException: /mnt/ssd6/elasticsearch-7.5.1/config/jvm.options

（3）创建mapping

curl -X PUT "10.3.73.149:9200/test0410050102?pretty" -H 'Content-Type: application/json' -d '{"settings":{"number_of_shards":6,"number_of_replicas":0, "index.refresh_interval": "1s"},"mappings":{"properties":{"tid": {"type": "integer", "index": false, "doc_values": false},"time1": {"type": "text", "index": false, "doc_values": false},"time2": {"type": "date", "doc_values": false},"str1": {"type": "keyword", "index": false},"str2": {"type": "text", "doc_values": false}, "str3": {"type": "text", "index": false, "doc_values": false},"str4": {"type": "text", "index": false, "doc_values": false},"str5": {"type": "text", "index": false, "doc_values": false},"str6": {"type": "text", "index": false, "doc_values": false},"num1": {"type": "integer", "doc_values": false},"num2": {"type": "integer", "index": false, "doc_values": false},"num3": {"type": "integer", "index": false, "doc_values": false},"num4": {"type": "long", "index": false, "doc_values": false},"flo1": {"type": "float", "doc_values": false},"flo2": {"type": "float", "index": false, "doc_values": false}}}}'

将es目录权限授权给启动用户（chown -R dictionary）

（4）关闭索引

curl -XPOST -H 'content-type:application/json' http://hdh149:9200/test0410053003/_close

二. mongoDB导入导出

数据备份：mongodump --collection COLLECTON --db DB_NAME -o path

数据还原：mongorestore -h <hostname><:port> -d dbname <path>

例子：

/opt/HDB/mongodb-linux-x86_64-rhel70-4.2.5/bin/mongodump --collection test0410101002 --db perftest -o /mnt/ssd1/mongo

/opt/HDB/mongodb-linux-x86_64-rhel70-4.2.5/bin/mongorestore -d perftest /mnt/ssd1/mongo

/opt/HDB/mongodb-linux-x86_64-rhel70-4.2.5/bin/mongorestore -h hdh149:27017 -d perftest --drop --collection $table /mnt/ssd1/mongo/perftest/${table}.bson

常用命令：

连接：/opt/HDB/mongodb-linux-x86_64-rhel70-4.2.5/bin/mongo

切换数据库：use perftest

查看表集合：show collections

三. postgresql导入导出测试

数据备份：pg_dump -U postgres perftest -t public.test0410103003 >outfile-test0410103003

psql -U postgres -d perftest -c "drop table test0410103003"

数据还原：

psql -U postgres perftest < outfile-test0410103003

直接从一个服务器转储到另一个服务器：pg_dump -h host1 dbname | psql -h host2 dbname

pg常用命令：

连接数据库：psql -U postgres -d postgres

连接后，切换数据库：\c dbname

列举数据库：\l

列举表：\dt

查看表结构：\d tablename

查看索引：\di tablename

创建数据库：create database dbname;

创建表：create table company(id int primary key not null, name text not null, age int not null, address char(50), salary real);

断开连接：\q

最后编辑于：2020.05.07 20:16:27