linux安装postgreSql数据库

介绍

  本文主要介绍linux系统上安装postgreSql数据库的一般思路，并提供了一个可执行脚本，在下载pg安装包后，修改脚本的部分配置，执行脚本，就可以完成数据库的安装、配置和启动，脚本在文末提供。

下载安装包

脚本的执行依赖现有安装包，不支持使用命令获取pg安装包，可以手动下载后，与脚本放置到统一目录下

解压安装包

这个步骤是把安装包解压到指定目录下

# unzip pg 
function pg_untar(){
    if [ -d ${pg_deploy_path} ];then
      echo "pg has been installed"
    else
      if [ ! -e ${pg_name} ];then
        echo "Missing pg installation package"
      else
         mkdir -p ${pg_deploy_path}
         tar -zxf ${pg_name} -C ${pg_deploy_path}
      fi
    fi
}

 

新增postgres用户

为数据库创建默认的postgres用户，用于登录并操作数据库

备注：对用户的密码进行了加密，具体介绍参见：Linux下利用glibc2库和crypt()函数生成用户密码

# Create a Linux user to log in to the PG database
function pg_create_user(){
#  user_exist=`cat /etc/passwd | grep '^${user_name}:' -c`
  egrep "^${user_name}" /etc/passwd >/dev/null
  if [ $? -eq 1 ]; then
    pass=$(perl -e 'print crypt($ARGV[0], "password")' $user_name)
    useradd -m -p $pass ${user_name}
    #useradd -m -p 'postgres' ${user_name}
    [ $? -eq 0 ] && echo "user [${user_name}] has been added to system!" || echo "Failed to add user [${user_name}]"
  else
    echo "user [${user_name}] exists"
  fi
}

 

初始化数据库

创建用户后，需要对数据库进行初始化，主要是初始化数据库的数据存储目录。

备注：

  1. 把数据库的数据存储目录拥有者改为postgres用户  

  2. 脚本的执行需要root权限，所以对数据库的初始化，需要切换到postgres用户。

#init pg
function pg_init(){
    #Create a data directory
    mkdir -p ${pg_data_path}
    touch ${pg_deploy_path}/logfile
    #Grant user access
    chown -R ${user_name} ${pg_data_path}
    chown ${user_name} ${pg_deploy_path}/logfile
    
    # init database 
    su - ${user_name} -c "${pg_deploy_path}/pgsql/bin/initdb -D ${pg_data_path}"
    
}

 

更改配置文件

1. 修改数据库的绑定ip,默认绑定的是localhost,不能使用部署服务器ip访问

2. 修改对客户端ip的限制，允许任意客户端连接

#Modify config of the pg database
function pg_modify_config(){
    id_path=${pg_data_path}/postgresql.conf
    local_path=${pg_data_path}/pg_hba.conf
    localip=$(/sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:")
    if [ -d ${pg_data_path} ];then
      chown -R 'root' ${pg_data_path}
      #Modify listening ip
      sed -i "s@#listen_addresses = 'localhost'@listen_addresses = \'${localip}\'@g" $id_path
      #Modify Trust client ip
      #sed -i "s@#listen_addresses = 'localhost'@listen_addresses = \'${localip}\'@g" $id_path
      sed '86 ahost   all   all   0.0.0.0/0   trust' -i $local_path
      chown -R ${user_name} ${pg_data_path}
    else
      echo "You need to initialize the database with the command:\n\t  ${pg_deploy_path}/pgsql/bin/initdb -D ${pg_data_path} "
    fi
}

 

启动数据库

数据库的操作都需要切换到postgres用户

# start pg database
function pg_start(){
 su - ${user_name} -c "${pg_deploy_path}/pgsql/bin/pg_ctl -D ${pg_data_path} -l ${pg_deploy_path}/logfile start"
}

 

开放接口

一般数据库部署后，本机能调试操作，其他服务器无法连接，需要开放端口

#Open 5432 port
function pg_open_port(){
  isopen=$(firewall-cmd --query-port=5432/tcp)
  if [ 'no' == $isopen ];then
   firewall-cmd --add-port=5432/tcp --permanent>/dev/null
   firewall-cmd --reload>/dev/null
  else
    echo "port 5432 already opened"
  fi
}

以上是所有的安装步骤解释，下面是完整脚本：

#!/bin/bash
# install postgre

# Pg database installation package name
pg_name='postgresql-9.5.16-1-linux-x64-binaries.tar.gz'
# Pg database installation path
pg_deploy_path='/opt/ops/pg'
# Directory of data stored in the pg database
pg_data_path="${pg_deploy_path}/data"
# Directory of data stored in the pg database
user_name='postgres'


# unzip pg 
function pg_untar(){
    if [ -d ${pg_deploy_path} ];then
      echo "pg has been installed"
    else
      if [ ! -e ${pg_name} ];then
        echo "Missing pg installation package"
      else
         mkdir -p ${pg_deploy_path}
         tar -zxf ${pg_name} -C ${pg_deploy_path}
      fi
    fi
}



# Create a Linux user to log in to the PG database
function pg_create_user(){
#  user_exist=`cat /etc/passwd | grep '^${user_name}:' -c`
  egrep "^${user_name}" /etc/passwd >/dev/null
  if [ $? -eq 1 ]; then
    pass=$(perl -e 'print crypt($ARGV[0], "password")' $user_name)
    useradd -m -p $pass ${user_name}
    #useradd -m -p 'postgres' ${user_name}
    [ $? -eq 0 ] && echo "user [${user_name}] has been added to system!" || echo "Failed to add user [${user_name}]"
  else
    echo "user [${user_name}] exists"
  fi
}

#init pg
function pg_init(){
    #Create a data directory
    mkdir -p ${pg_data_path}
    touch ${pg_deploy_path}/logfile
    #Grant user access
    chown -R ${user_name} ${pg_data_path}
    chown ${user_name} ${pg_deploy_path}/logfile
    
    # init database 
    su - ${user_name} -c "${pg_deploy_path}/pgsql/bin/initdb -D ${pg_data_path}"
    
}

#Modify config of the pg database
function pg_modify_config(){
    id_path=${pg_data_path}/postgresql.conf
    local_path=${pg_data_path}/pg_hba.conf
    localip=$(/sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:")
    if [ -d ${pg_data_path} ];then
      chown -R 'root' ${pg_data_path}
      #Modify listening ip
      sed -i "s@#listen_addresses = 'localhost'@listen_addresses = \'${localip}\'@g" $id_path
      #Modify Trust client ip
      #sed -i "s@#listen_addresses = 'localhost'@listen_addresses = \'${localip}\'@g" $id_path
      sed '86 ahost   all   all   0.0.0.0/0   trust' -i $local_path
      chown -R ${user_name} ${pg_data_path}
    else
      echo "You need to initialize the database with the command:\n\t  ${pg_deploy_path}/pgsql/bin/initdb -D ${pg_data_path} "
    fi
}

# start pg database
function pg_start(){
 su - ${user_name} -c "${pg_deploy_path}/pgsql/bin/pg_ctl -D ${pg_data_path} -l ${pg_deploy_path}/logfile start"
}

#Open 5432 port
function pg_open_port(){
  isopen=$(firewall-cmd --query-port=5432/tcp)
  if [ 'no' == $isopen ];then
   firewall-cmd --add-port=5432/tcp --permanent>/dev/null
   firewall-cmd --reload>/dev/null
  else
    echo "port 5432 already opened"
  fi
}

echo "============== start install pg =============="
if [ -d ${pg_deploy_path} ];then
  echo "pg has been installed"
else
  pg_untar
  pg_create_user
  pg_init
  pg_modify_config
  pg_start
  pg_open_port
fi
echo "============== finish installing pg =============="