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

Hello World !

文章分类

全部博文(52)

文章存档

2016年(1)

2014年(1)

2013年(8)

2012年(38)

2011年(4)

分类: Mysql/postgreSQL

2016-11-26 22:18:47

CentOS 7.2安装postgresql95
  1. 目标
  2. 制作自动安装shell脚本。
    postgresql95数据库、创建数据库、执行SQL语句、配置ODBC
  3. 环境准备
  4. 操作系统:CentOS Linux release 7.2.1511 (Core) 
    pgdg版本:9.5.5
  5. 数据库在线安装
  6. 如需配置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 ~]#
  7. 数据库离线安装
    • 安装文件准备
  • 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]# 
  3. 修改配置文件,供外部地址访问
  4. 修改配置文件/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
  5. 修改开机启动、 启动服务
  6. 开机自启动
    [root@localhost opt]# systemctl enable postgresql-9.5.service
    启动服务
    [root@localhost opt]# systemctl start postgresql-9.5.service
  7. 执行psql的脚本
  8. 修改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;\""
  9. 执行SQL文件(/tmp/cdr.sql
  10. 创建表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. );

    22. 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目录
  11. 配置ODBC数据源
  12. 创建软链接
    [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

  13. shell自动安装
  14. 文件目录结构,其中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

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

    点击(此处)折叠或打开

    1. #!/bin/bash


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

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

    4. if [ -f $UOUT ];then
    5.   echo "script has already been executed. "
    6.   exit 0
    7. fi


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

    10. HOME_DIR=`pwd`

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

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

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

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

    16. 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
    17. sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
    18. sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
    19. #sed -i 's%#listen_addresses = 'localhost'%listen_addresses = '*'%g' /var/lib/pgsql/9.5/data/postgresql.conf
    20. ## listen_addresses = '*'


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

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

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

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

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

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

    28. sleep 3

    29. su - postgres -c "psql -c \" ALTER USER postgres WITH PASSWORD 'postgres';\"" >>${UOUT} 2>&1
    30. su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" >>${UOUT} 2>&1
    31. su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" >>${UOUT} 2>&1
    32. su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\"" >>${UOUT} 2>&1
    33. su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\"" >>${UOUT} 2>&1

    34. for sqlfile in $HOME_DIR/sql/*.sql
    35. do
    36.   echo "execute sql file : "$sqlfile
    37.   su - postgres -c "psql -d freeswitch -f $sqlfile" >>${UOUT} 2>&1
    38. done

    39. if [ -f /etc/odbc.ini ];then
    40.   if [ -f /etc/odbc.ini.bak ];then
    41.     rm -rf /etc/odbc.ini.bak
    42.   fi
    43.   mv /etc/odbc.ini /etc/odbc.ini.bak
    44. fi

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

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

    离线安装脚本(install-pgql95

    点击(此处)折叠或打开

    1. #!/bin/bash


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

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

    4. if [ -f $UOUT ];then
    5.   echo "script has already been executed. "
    6.   exit 0
    7. fi


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

    10. HOME_DIR=`pwd`

    11. echo "Entering into $HOME_DIR, install rpms ..."
    12. rpm_dir=$HOME_DIR/"*.rpm"
    13. for i in $rpm_dir
    14. do
    15.   rpm -Uvh --nodeps $i >>${UOUT} 2>&1
    16. done

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

    19. 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
    20. sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
    21. sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
    22. #sed -i 's%#listen_addresses = 'localhost'%listen_addresses = '*'%g' /var/lib/pgsql/9.5/data/postgresql.conf
    23. ## listen_addresses = '*'


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

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

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

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

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

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

    31. sleep 3

    32. su - postgres -c "psql -c \" ALTER USER postgres WITH PASSWORD 'postgres';\"" >>${UOUT} 2>&1
    33. su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" >>${UOUT} 2>&1
    34. su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" >>${UOUT} 2>&1
    35. su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\"" >>${UOUT} 2>&1
    36. su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\"" >>${UOUT} 2>&1

    37. for sqlfile in $HOME_DIR/sql/*.sql
    38. do
    39.   echo "execute sql file : "$sqlfile
    40.   su - postgres -c "psql -d freeswitch -f $sqlfile" >>${UOUT} 2>&1
    41. done

    42. if [ -f /etc/odbc.ini ];then
    43.   if [ -f /etc/odbc.ini.bak ];then
    44.     rm -rf /etc/odbc.ini.bak
    45.   fi
    46.   mv /etc/odbc.ini /etc/odbc.ini.bak
    47. fi

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

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


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

上一篇:github生成SSH公钥

下一篇:没有了

给主人留下些什么吧!~~