postgres安装及实现mysql到pg的数据迁移
1. Postgres 资料参考:
- pg操作手册:http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
- psql命令
:https://ywnz.com/linux/psql/
2. 消息推送用户标签迁移测试准备工作
2.1. 前提:
- 线上sql已经开发完成
- 测试数据库环境搭建完成
2.2. 主要工作:
2.2.1. mysql2udw工具暂时无法指定字段,源表和目标表表字段不一致报错。
- ods表结构:从源库导入
- 中间表表结构:从线上pg库导入
2.2.2. 准备mysql2udw.sh测试调度任务脚本:
- 保留用户标签需要的源表和数仓中间表:
文档底部列出用户标签的相关的源表和中间表清单:
2.2.3. 准备ods表抽数配置文件yaml,修改环境信息和抽数方式
- 只导表数据,全量抽取,抽取前清空数据
supress_data: false
supress_ddl: true
force_truncate: true
2.2.4. 备注
-
开发负责的表均替换成正式命名的表:
tag_user_temp
tag_member_temp
tag_user
-
中间表:f_order_item,d_user,f_user_list有调整(删除跟用户标签取值不相关的源表),其余中间表etl脚本与线上一致。
etl脚本需要单独从git_lab:bi_etl_dev/etl
导到测试服务器。
2.2.5. 中间表抽数方式
- tag_user表:1 会员属性的标签全量更新。2 普通用户的标签必须增量更新
- 其余中间表全量更新。
2.2.6. 相关脚本上传路径:
-
测试调度任务脚本mysql2udw_dev.sh:
/data/scripts/mysql2udw_dev.sh -
源数据抽取配置文件yaml:
/home/hjmrunning/bi_etl_dev/ods_mysql2udw/
维度表:/home/hjmrunning/bi_etl_dev/dw_sqldesign/dw_demension -
事实表:/home/hjmrunning/bi_etl_dev/dw_sqldesign/dw_fact
git_lab:bi_etl_dev/mysql2udw_dev.sh >> 10.10.184.217:/data/scripts/
维度表2张:
d_product
d_user
路径:
git_lab:bi_etl_dev/etl >> 10.10.184.217:/home/hjmrunning/bi_etl_dev/dw_sqldesign/dw_demension/
事实表3张:
f_user_list
f_order_itme
f_app_log_item
路径:
git_lab:bi_etl_dev/etl/ >> 10.10.184.217:/home/hjmrunning/bi_etl_dev/dw_sqldesign/dw_fact/
2.3. 测试
2.3.1. 测试 ods源表配置是否正确配置且能成功抽数
mysql2udw -v -f /home/hjmrunning/bi_etl_dev/ods_mysql2udw/h_goods.yaml
2.3.2. 测试中间ETL处理的脚本是否成功调用
/usr/bin/psql -U admin -d ahaschool_udw -h 127.0.0.1 -p 5432
passwd: WZgNV0Pcp0
2.3.3. 待所有脚本验证成功,测试调度任务脚本:mysql2udw.sh
nohup /data/scripts/mysql2udw_dev.sh >> /data/logs/cron_day.log 2>&1 &
3. 数仓测试环境:
- IP:10.10.184.217
- 用户名:hjmrunning
- 通过dev:120.132.70.125 跳转
ssh hjmrunning@120.132.70.125
ssh hjmrunning@10.10.184.217
3.1. test2-basics (测二微信项目mysql\redis\kafka)
host:10.10.82.107
database:hjm_dev
password:hjm_dev
3.2. 测试pg数据库信息
数据库:
数据库用户名:admin
密码:WZgNV0Pcp0
主机:10.10.184.217
端口:5432
数据库:postgres
步骤一:本地:
scp -r /Users/cheney/git_work/bi_etl_product/* hjmrunning@120.132.70.125:/home/hjmrunning/bi_etl_dev/
步骤二:dev120.132.70.125:
scp -r /home/hjmrunning/bi_etl_dev/* hjmrunning@10.10.184.217:/home/hjmrunning/bi_etl_dev/
scp hjmrunning@120.132.70.125:/home/hjmrunning/bi_etl_dev/* hjmrunning@120.132.70.125:/home/hjmrunning/bi_etl_dev2/
scp -r /home/hjmrunning/bi_etl_dev/* hjmrunning@120.132.70.125:/home/hjmrunning/bi_etl_dev2/
4. pg库连接命令
登录控制台:psql postgres
退出: \q
- 命令行连接数据库控制台:
psql -h 127.0.0.1 -d ahaschool_udw -U admin
psql -h 10.10.184.217 -d ahaschool_udw -U admin
- 查询所连接数据库的IP地址与端口
SELECT inet_server_addr(), inet_server_port();
4.1. 创建pg数据库
- 连接:psql postgres
这时相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。
- 第一件事是使用\password命令,为postgres用户设置一个密码:
\password postgres
- 第二件事是创建数据库用户dbuser(刚才创建的是Linux系统用户),并设置密码:
CREATE USER admin WITH PASSWORD 'WZgNV0Pcp0';
- 第三件事是创建用户数据库,这里为exampledb,并指定所有者为dbuser:
create database ahaschool_udw owner admin;
- 第四件事是将exampledb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限:
GRANT ALL PRIVILEGES ON DATABASE ahaschool_udw to admin;
卸载和重装pg库
- 查看pg服务器版本: psql --version
启动停止服务命令:
停止服务:
/data/postgresql/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/server.log stop
启动服务:
/data/postgresql/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/server.log start
查看数据库状态:
/data/postgresql/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/server.log status
卸载
- 卸载的话,直接删除/data/下的postgresql文件夹
- 然后把/etc/profile文件里的,postgresql的export HOME注释掉就可以了
安装
- 源码安装方式:
下载source的包
wget https://ftp.postgresql.org/pub/source/v8.4.20/postgresql-8.4.20.tar.gz
scp -r postgresql-8.4.20.tar.gz hjmrunning@120.132.70.125:/home/hjmrunning/
scp -r /home/hjmrunning/postgresql-8.4.20.tar.gz hjmrunning@10.10.184.217:/data/
路径要在/data下并解压:
tar -zxvf postgresql-8.4.20.tar.gz /data/
进入/data/postgresql-8.4.20
- 指定安装路径
指定安装路径:
./configure --help
--prefix=dir可以指定安装目录
./configure --prefix=/data/postgresql
编译并安装
执行make,从Makefile中读取指令,编译源码
make
执行make install,将软件安装至指定目录
make install
显示:PostgreSQL installation complete.
安装成功
创建数据文件存储路径并初始化数据库
mkdir /data/postgresql/data
初始化数据库并生成:data/postgres/data/postgresql.conf等pg等配置文件
/data/postgresql/bin/initdb -D /data/postgresql/data
修改监听地址和添加数据库服务器端口
listen_addresses配置监听地址范围,改为*则为所有:
listen_addresses = '*'
port = 5432
配置数据库允许访问的IP(远程访问认证机制):
vim /data/postgresql/data/pg_hba.conf
TYPE | DATABASE | USER | ADDRES | METHOD |
---|---|---|---|---|
host | all | all | 0.0.0.0/0 | trust |
需要所有IP都可以访问则对应为0.0.0.0/0
设置postgresql服务开机自启动
chkconfig --add postgresql
启动pg服务器
报错:
postgres cannot access the server configuration file "/data/postgresql/data/postgresql.conf": No such file or directory
- 原因分析:未初始化数据库
查看指定文件路径
sudo find / -name postgresql.conf