Chinaunix首页 | 论坛 | 博客

zhoulo1289.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

likeilinux

  • 博客访问： 441578
  • 博文数量： 52
  • 博客积分： 1076
  • 博客等级： 准尉
  • 技术积分： 821
  • 用 户 组： 普通用户
  • 注册时间： 2011-08-18 20:05
个人简介

Hello World !

文章分类

全部博文（52）

文章存档

2016年（1）

2014年（1）

2013年（8）

2012年（38）

2011年（4）

我的朋友
最近访客
推荐博文
相关博文
CentOS 7.2 安装postgresql95

分类： Mysql/postgreSQL

2016-11-26 22:18:47

CentOS 7.2安装postgresql95
  1. 目标
    2. 制作自动安装shell脚本。
    postgresql95数据库、创建数据库、执行SQL语句、配置ODBC
  2. 环境准备
    3. 操作系统：CentOS Linux release 7.2.1511 (Core) 
    pgdg版本：9.5.5
  3. 数据库在线安装
    4. 如需配置yum缓存，修改/etc/yum.conf，keepcache=0为keepcache=1，如在rhel-server-7.2上安装另还需修改plugins=1为plugins=0

    安装PostgreSQL95 Yum Repository
    [root@localhost ~]# yum install
    安装postgresql95-server
    [root@localhost ~]# yum install postgresql95-server postgresql95-contrib postgresql95-devel
    安装postgresql95的odbc
    [root@localhost ~]# yum install postgresql95-odbc

    备份yum缓存文件

    点击(此处)折叠或打开

    1. [root@localhost ~]# find /var -name *.rpm
    2. /var/cache/yum/x86_64/7/base/packages/libxslt-1.1.28-5.el7.x86_64.rpm
    3. /var/cache/yum/x86_64/7/base/packages/unixODBC-2.3.1-11.el7.x86_64.rpm
    4. /var/cache/yum/x86_64/7/updates/packages/libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm
    5. /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-9.5.5-1PGDG.rhel7.x86_64.rpm
    6. /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-libs-9.5.5-1PGDG.rhel7.x86_64.rpm
    7. /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-server-9.5.5-1PGDG.rhel7.x86_64.rpm
    8. /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-contrib-9.5.5-1PGDG.rhel7.x86_64.rpm
    9. /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-odbc-09.05.0400-1PGDG.rhel7.x86_64.rpm
    10. /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-devel-9.5.5-1PGDG.rhel7.x86_64.rpm
    11. /var/tmp/yum-root-mBRLdw/pgdg-redhat95-9.5-2.noarch.rpm
    12. [root@localhost ~]#
  4. 数据库离线安装
      5. 安装文件准备
  • libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm
  • libxslt-1.1.28-5.el7.x86_64.rpm
  • pgdg-redhat95-9.5-2.noarch.rpm
  • postgresql95-9.5.5-1PGDG.rhel7.x86_64.rpm
  • postgresql95-contrib-9.5.5-1PGDG.rhel7.x86_64.rpm
  • postgresql95-devel-9.5.5-1PGDG.rhel7.x86_64.rpm
  • postgresql95-libs-9.5.5-1PGDG.rhel7.x86_64.rpm
  • postgresql95-odbc-09.05.0400-1PGDG.rhel7.x86_64.rpm
  • postgresql95-server-9.5.5-1PGDG.rhel7.x86_64.rpm
  • unixODBC-2.3.1-11.el7.x86_64.rpm
      使用rpm -Uvh *.rpm 或rpm -Uvh --nodeps *.rpm
  1. 数据库初始化
    2. [root@localhost opt]# /usr/pgsql-9.5/bin/postgresql95-setup initdb
    Initializing database ... OK
    [root@localhost opt]# 
  2. 修改配置文件，供外部地址访问
    3. 修改配置文件/var/lib/pgsql/9.5/data/pg_hba.conf
    [root@localhost opt]# sed -i 's/127.0.0.1\/32            ident/0.0.0.0\/0            md5/g' /var/lib/pgsql/9.5/data/pg_hba.conf
    修改配置文件/var/lib/pgsql/9.5/data/postgresql.conf
    [root@localhost opt]# sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
    [root@localhost opt]# sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
  3. 修改开机启动、 启动服务
    4. 开机自启动
    [root@localhost opt]# systemctl enable postgresql-9.5.service
    启动服务
    [root@localhost opt]# systemctl start postgresql-9.5.service
  4. 执行psql的脚本
    5. 修改postgres密码为postgres
    [root@localhost ~]# su - postgres -c "psql -c \" ALTER  USER postgres WITH PASSWORD 'postgres';\"" 
    创建用户：freeswitch，密码：freeswitch
    [root@localhost ~]# su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" 
    创建数据库：freeswitch
    [root@localhost ~]# su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" 
    赋予数据库freeswitch，拥有者为freeswitch
    [root@localhost ~]# su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\""
    赋予freeswitch用户，数据库freeswitch所有权限
    [root@localhost ~]# su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\""
  5. 执行SQL文件（/tmp/cdr.sql）
    6. 创建表cdr，将表表cdr拥有者赋予给freeswitch

    点击(此处)折叠或打开

    1. create table cdr (
    2.     id serial primary key,
    3.     local_ip_v4 inet not null,
    4.     caller_id_name varchar,
    5.     caller_id_number varchar,
    6.     destination_number varchar not null,
    7.     context varchar not null,
    8.     start_stamp timestamp with time zone not null,
    9.     answer_stamp timestamp with time zone,
    10.     end_stamp timestamp with time zone not null,
    11.     duration int not null,
    12.     billsec int not null,
    13.     hangup_cause varchar not null,
    14.     uuid uuid not null,
    15.     bleg_uuid uuid,
    16.     accountcode varchar,
    17.     read_codec varchar,
    18.     write_codec varchar,
    19.     sip_hangup_disposition varchar,
    20.     ani varchar
    21. );

    23. ALTER TABLE cdr OWNER TO freeswitch;
    执行sql文件
    [root@localhost ~]# su - postgres -c "psql -d freeswitch -f /tmp/cdr.sql"

    Note : 如遇到 /root/cdr.sql: Permission denied
    postgres用户无权限进入/root目录
  6. 配置ODBC数据源
    7. 创建软链接
    [root@localhost ~]# ln -sf /usr/pgsql-9.5/bin/pg_config /usr/bin/pg_config
    [root@localhost ~]# ln -sf /usr/lib64/libodbcpsqlS.so /usr/lib/libodbcpsqlS.so
    [root@localhost ~]# ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib64/psqlodbcw.so
    [root@localhost ~]# ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib/psqlodbcw.so

    创建/etc/odbc.ini

    点击(此处)折叠或打开

    1. [freeswitch]
    2. Driver=PostgreSQL
    3. Database=freeswitch
    4. Servername=127.0.0.1
    5. Port=5432

    ODBC数据源测试：
        isql freeswitch freeswitch freeswitch
  7. shell自动安装
    8. 文件目录结构，其中odbc.ini内容如上节所示。

    点击(此处)折叠或打开

    1. [root@localhost postgresql]# tree
    2. .
    3. ├── ini
    4. │   └── odbc.ini
    5. ├── install-pgql95
    6. ├── libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm
    7. ├── libxslt-1.1.28-5.el7.x86_64.rpm
    8. ├── pgdg-redhat95-9.5-2.noarch.rpm
    9. ├── postgresql95-9.5.5-1PGDG.rhel7.x86_64.rpm
    10. ├── postgresql95-contrib-9.5.5-1PGDG.rhel7.x86_64.rpm
    11. ├── postgresql95-devel-9.5.5-1PGDG.rhel7.x86_64.rpm
    12. ├── postgresql95-libs-9.5.5-1PGDG.rhel7.x86_64.rpm
    13. ├── postgresql95-odbc-09.05.0400-1PGDG.rhel7.x86_64.rpm
    14. ├── postgresql95-server-9.5.5-1PGDG.rhel7.x86_64.rpm
    15. ├── readme.txt
    16. ├── sql
    17. │   ├── mod_cdr_pg_csv.sql
    18. │   └── mod_cidlookup.sql
    19. └── unixODBC-2.3.1-11.el7.x86_64.rpm

    21. 2 directories, 15 files
    22. [root@localhost postgresql]#
    postgresql95安装字段安装脚本名 install-pgql95，使用 sh install-pgql95 执行脚本进行安装
    在线安装脚本（install-pgql95）

    点击(此处)折叠或打开

    1. #!/bin/bash


    4. DATE=`date +'%Y-%m%d-%H%M'`

    6. UOUT="/tmp/install-pgql95.out"

    8. if [ -f $UOUT ];then
    9.   echo "script has already been executed. "
    10.   exit 0
    11. fi


    14. hm=$(echo $0 |sed 's/install-pgql95//')
    15. [[ ! -z $hm ]] && cd $hm

    17. HOME_DIR=`pwd`

    19. echo "Entering into $HOME_DIR, use yum install postgresql95, please wait ..."

    21. yum install http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm >>${UOUT} 2>&1

    23. yum install -y postgresql95-server postgresql95-contrib postgresql95-devel postgresql95-odbc >>${UOUT} 2>&1

    25. echo "postgresql95 initdb ..."
    26. /usr/pgsql-9.5/bin/postgresql95-setup initdb >>${UOUT} 2>&1

    28. sed -i 's/127.0.0.1\/32 ident/0.0.0.0\/0 md5/g' /var/lib/pgsql/9.5/data/pg_hba.conf
    29. sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
    30. sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
    31. #sed -i 's%#listen_addresses = 'localhost'%listen_addresses = '*'%g' /var/lib/pgsql/9.5/data/postgresql.conf
    32. ## listen_addresses = '*'


    35. ln -sf /usr/pgsql-9.5/bin/pg_config /usr/bin/pg_config

    37. ln -sf /usr/lib64/libodbcpsqlS.so /usr/lib/libodbcpsqlS.so

    39. ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib64/psqlodbcw.so

    41. ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib/psqlodbcw.so

    43. echo "start posrtgresql-9.5 ..."
    44. systemctl enable postgresql-9.5.service >>${UOUT} 2>&1

    46. systemctl start postgresql-9.5.service >>${UOUT} 2>&1

    48. sleep 3

    50. su - postgres -c "psql -c \" ALTER USER postgres WITH PASSWORD 'postgres';\"" >>${UOUT} 2>&1
    51. su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" >>${UOUT} 2>&1
    52. su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" >>${UOUT} 2>&1
    53. su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\"" >>${UOUT} 2>&1
    54. su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\"" >>${UOUT} 2>&1

    56. for sqlfile in $HOME_DIR/sql/*.sql
    57. do
    58.   echo "execute sql file : "$sqlfile
    59.   su - postgres -c "psql -d freeswitch -f $sqlfile" >>${UOUT} 2>&1
    60. done

    62. if [ -f /etc/odbc.ini ];then
    63.   if [ -f /etc/odbc.ini.bak ];then
    64.     rm -rf /etc/odbc.ini.bak
    65.   fi
    66.   mv /etc/odbc.ini /etc/odbc.ini.bak
    67. fi

    69. /bin/cp -rf $HOME_DIR/ini/odbc.ini /etc/odbc.ini

    71. echo "you can use 'isql freeswitch freeswitch freeswitch' to test odbc"

    离线安装脚本（install-pgql95）

    点击(此处)折叠或打开

    1. #!/bin/bash


    4. DATE=`date +'%Y-%m%d-%H%M'`

    6. UOUT="/tmp/install-pgql95.out"

    8. if [ -f $UOUT ];then
    9.   echo "script has already been executed. "
    10.   exit 0
    11. fi


    14. hm=$(echo $0 |sed 's/install-pgql95//')
    15. [[ ! -z $hm ]] && cd $hm

    17. HOME_DIR=`pwd`

    19. echo "Entering into $HOME_DIR, install rpms ..."
    20. rpm_dir=$HOME_DIR/"*.rpm"
    21. for i in $rpm_dir
    22. do
    23.   rpm -Uvh --nodeps $i >>${UOUT} 2>&1
    24. done

    26. echo "postgresql95 initdb ..."
    27. /usr/pgsql-9.5/bin/postgresql95-setup initdb >>${UOUT} 2>&1

    29. sed -i 's/127.0.0.1\/32 ident/0.0.0.0\/0 md5/g' /var/lib/pgsql/9.5/data/pg_hba.conf
    30. sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
    31. sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
    32. #sed -i 's%#listen_addresses = 'localhost'%listen_addresses = '*'%g' /var/lib/pgsql/9.5/data/postgresql.conf
    33. ## listen_addresses = '*'


    36. ln -sf /usr/pgsql-9.5/bin/pg_config /usr/bin/pg_config

    38. ln -sf /usr/lib64/libodbcpsqlS.so /usr/lib/libodbcpsqlS.so

    40. ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib64/psqlodbcw.so

    42. ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib/psqlodbcw.so

    44. echo "start posrtgresql-9.5 ..."
    45. systemctl enable postgresql-9.5.service >>${UOUT} 2>&1

    47. systemctl start postgresql-9.5.service >>${UOUT} 2>&1

    49. sleep 3

    51. su - postgres -c "psql -c \" ALTER USER postgres WITH PASSWORD 'postgres';\"" >>${UOUT} 2>&1
    52. su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" >>${UOUT} 2>&1
    53. su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" >>${UOUT} 2>&1
    54. su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\"" >>${UOUT} 2>&1
    55. su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\"" >>${UOUT} 2>&1

    57. for sqlfile in $HOME_DIR/sql/*.sql
    58. do
    59.   echo "execute sql file : "$sqlfile
    60.   su - postgres -c "psql -d freeswitch -f $sqlfile" >>${UOUT} 2>&1
    61. done

    63. if [ -f /etc/odbc.ini ];then
    64.   if [ -f /etc/odbc.ini.bak ];then
    65.     rm -rf /etc/odbc.ini.bak
    66.   fi
    67.   mv /etc/odbc.ini /etc/odbc.ini.bak
    68. fi

    70. /bin/cp -rf $HOME_DIR/ini/odbc.ini /etc/odbc.ini

    72. echo "you can use 'isql freeswitch freeswitch freeswitch' to test odbc"


阅读(1870) | 评论(0) | 转发(0) |
0

上一篇：github生成SSH公钥

下一篇：没有了

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6