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
posted @ 2019-07-22 02:42  MrCheney  阅读(...)  评论(... 编辑 收藏