Hello World !
分类: Mysql/postgreSQL
2016-11-26 22:18:47
CentOS 7.2安装postgresql95
- 目标
制作自动安装shell脚本。
postgresql95数据库、创建数据库、执行SQL语句、配置ODBC- 环境准备
操作系统:CentOS Linux release 7.2.1511 (Core)
pgdg版本:9.5.5- 数据库在线安装
如需配置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缓存文件
点击(此处)折叠或打开
- [root@localhost ~]# find /var -name *.rpm
- /var/cache/yum/x86_64/7/base/packages/libxslt-1.1.28-5.el7.x86_64.rpm
- /var/cache/yum/x86_64/7/base/packages/unixODBC-2.3.1-11.el7.x86_64.rpm
- /var/cache/yum/x86_64/7/updates/packages/libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm
- /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-9.5.5-1PGDG.rhel7.x86_64.rpm
- /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-libs-9.5.5-1PGDG.rhel7.x86_64.rpm
- /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-server-9.5.5-1PGDG.rhel7.x86_64.rpm
- /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-contrib-9.5.5-1PGDG.rhel7.x86_64.rpm
- /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-odbc-09.05.0400-1PGDG.rhel7.x86_64.rpm
- /var/cache/yum/x86_64/7/pgdg95/packages/postgresql95-devel-9.5.5-1PGDG.rhel7.x86_64.rpm
- /var/tmp/yum-root-mBRLdw/pgdg-redhat95-9.5-2.noarch.rpm
- [root@localhost ~]#
- 数据库离线安装
安装文件准备
- 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
- 数据库初始化
[root@localhost opt]# /usr/pgsql-9.5/bin/postgresql95-setup initdb
Initializing database ... OK
[root@localhost opt]#
- 修改配置文件,供外部地址访问
修改配置文件/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
- 修改开机启动、 启动服务
开机自启动
[root@localhost opt]# systemctl enable postgresql-9.5.service
启动服务
[root@localhost opt]# systemctl start postgresql-9.5.service
- 执行psql的脚本
修改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;\""
- 执行SQL文件(/tmp/cdr.sql)
创建表cdr,将表表cdr拥有者赋予给freeswitch
执行sql文件点击(此处)折叠或打开
- create table cdr (
- id serial primary key,
- local_ip_v4 inet not null,
- caller_id_name varchar,
- caller_id_number varchar,
- destination_number varchar not null,
- context varchar not null,
- start_stamp timestamp with time zone not null,
- answer_stamp timestamp with time zone,
- end_stamp timestamp with time zone not null,
- duration int not null,
- billsec int not null,
- hangup_cause varchar not null,
- uuid uuid not null,
- bleg_uuid uuid,
- accountcode varchar,
- read_codec varchar,
- write_codec varchar,
- sip_hangup_disposition varchar,
- ani varchar
- );
- ALTER TABLE cdr OWNER TO freeswitch;
[root@localhost ~]# su - postgres -c "psql -d freeswitch -f /tmp/cdr.sql"
Note : 如遇到 /root/cdr.sql: Permission denied
postgres用户无权限进入/root目录
- 配置ODBC数据源
创建软链接
[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
点击(此处)折叠或打开
- [freeswitch]
- Driver=PostgreSQL
- Database=freeswitch
- Servername=127.0.0.1
- Port=5432
ODBC数据源测试:
isql freeswitch freeswitch freeswitch
- shell自动安装
文件目录结构,其中odbc.ini内容如上节所示。
postgresql95安装字段安装脚本名 install-pgql95,使用 sh install-pgql95 执行脚本进行安装点击(此处)折叠或打开
- [root@localhost postgresql]# tree
- .
- ├── ini
- │ └── odbc.ini
- ├── install-pgql95
- ├── 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
- ├── readme.txt
- ├── sql
- │ ├── mod_cdr_pg_csv.sql
- │ └── mod_cidlookup.sql
- └── unixODBC-2.3.1-11.el7.x86_64.rpm
- 2 directories, 15 files
- [root@localhost postgresql]#
在线安装脚本(install-pgql95)
点击(此处)折叠或打开
- #!/bin/bash
- DATE=`date +'%Y-%m%d-%H%M'`
- UOUT="/tmp/install-pgql95.out"
- if [ -f $UOUT ];then
- echo "script has already been executed. "
- exit 0
- fi
- hm=$(echo $0 |sed 's/install-pgql95//')
- [[ ! -z $hm ]] && cd $hm
- HOME_DIR=`pwd`
- echo "Entering into $HOME_DIR, use yum install postgresql95, please wait ..."
- yum install http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm >>${UOUT} 2>&1
- yum install -y postgresql95-server postgresql95-contrib postgresql95-devel postgresql95-odbc >>${UOUT} 2>&1
- echo "postgresql95 initdb ..."
- /usr/pgsql-9.5/bin/postgresql95-setup initdb >>${UOUT} 2>&1
- 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
- sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
- sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
- #sed -i 's%#listen_addresses = 'localhost'%listen_addresses = '*'%g' /var/lib/pgsql/9.5/data/postgresql.conf
- ## listen_addresses = '*'
- ln -sf /usr/pgsql-9.5/bin/pg_config /usr/bin/pg_config
- ln -sf /usr/lib64/libodbcpsqlS.so /usr/lib/libodbcpsqlS.so
- ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib64/psqlodbcw.so
- ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib/psqlodbcw.so
- echo "start posrtgresql-9.5 ..."
- systemctl enable postgresql-9.5.service >>${UOUT} 2>&1
- systemctl start postgresql-9.5.service >>${UOUT} 2>&1
- sleep 3
- su - postgres -c "psql -c \" ALTER USER postgres WITH PASSWORD 'postgres';\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\"" >>${UOUT} 2>&1
- for sqlfile in $HOME_DIR/sql/*.sql
- do
- echo "execute sql file : "$sqlfile
- su - postgres -c "psql -d freeswitch -f $sqlfile" >>${UOUT} 2>&1
- done
- if [ -f /etc/odbc.ini ];then
- if [ -f /etc/odbc.ini.bak ];then
- rm -rf /etc/odbc.ini.bak
- fi
- mv /etc/odbc.ini /etc/odbc.ini.bak
- fi
- /bin/cp -rf $HOME_DIR/ini/odbc.ini /etc/odbc.ini
- echo "you can use 'isql freeswitch freeswitch freeswitch' to test odbc"
离线安装脚本(install-pgql95)
点击(此处)折叠或打开
- #!/bin/bash
- DATE=`date +'%Y-%m%d-%H%M'`
- UOUT="/tmp/install-pgql95.out"
- if [ -f $UOUT ];then
- echo "script has already been executed. "
- exit 0
- fi
- hm=$(echo $0 |sed 's/install-pgql95//')
- [[ ! -z $hm ]] && cd $hm
- HOME_DIR=`pwd`
- echo "Entering into $HOME_DIR, install rpms ..."
- rpm_dir=$HOME_DIR/"*.rpm"
- for i in $rpm_dir
- do
- rpm -Uvh --nodeps $i >>${UOUT} 2>&1
- done
- echo "postgresql95 initdb ..."
- /usr/pgsql-9.5/bin/postgresql95-setup initdb >>${UOUT} 2>&1
- 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
- sed -i 's/localhost/*/g' /var/lib/pgsql/9.5/data/postgresql.conf
- sed -i 's/#listen_addresses/listen_addresses/g' /var/lib/pgsql/9.5/data/postgresql.conf
- #sed -i 's%#listen_addresses = 'localhost'%listen_addresses = '*'%g' /var/lib/pgsql/9.5/data/postgresql.conf
- ## listen_addresses = '*'
- ln -sf /usr/pgsql-9.5/bin/pg_config /usr/bin/pg_config
- ln -sf /usr/lib64/libodbcpsqlS.so /usr/lib/libodbcpsqlS.so
- ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib64/psqlodbcw.so
- ln -sf /usr/pgsql-9.5/lib/psqlodbcw.so /usr/lib/psqlodbcw.so
- echo "start posrtgresql-9.5 ..."
- systemctl enable postgresql-9.5.service >>${UOUT} 2>&1
- systemctl start postgresql-9.5.service >>${UOUT} 2>&1
- sleep 3
- su - postgres -c "psql -c \" ALTER USER postgres WITH PASSWORD 'postgres';\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" CREATE USER freeswitch WITH PASSWORD 'freeswitch';\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" CREATE DATABASE freeswitch;\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" ALTER DATABASE freeswitch OWNER TO freeswitch;\"" >>${UOUT} 2>&1
- su - postgres -c "psql -c \" GRANT ALL PRIVILEGES ON DATABASE freeswitch to freeswitch;\"" >>${UOUT} 2>&1
- for sqlfile in $HOME_DIR/sql/*.sql
- do
- echo "execute sql file : "$sqlfile
- su - postgres -c "psql -d freeswitch -f $sqlfile" >>${UOUT} 2>&1
- done
- if [ -f /etc/odbc.ini ];then
- if [ -f /etc/odbc.ini.bak ];then
- rm -rf /etc/odbc.ini.bak
- fi
- mv /etc/odbc.ini /etc/odbc.ini.bak
- fi
- /bin/cp -rf $HOME_DIR/ini/odbc.ini /etc/odbc.ini
- echo "you can use 'isql freeswitch freeswitch freeswitch' to test odbc"