yum安装

安装PostgreSQL 12

  1. yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  2. yum install postgresql12 postgresql12-server -y

安装路径在 /usr/pgsql-12/
安装完之后，初始化数据库并设置开机启动：

  1. /usr/pgsql-12/bin/postgresql-12-setup initdb 
  2. systemctl enable postgresql-12 
  3. systemctl start postgresql-12

安装PostgreSQL 9.6

  1. sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  2. sudo yum install postgresql96 postgresql96-server -y

安装路径在 /usr/pgsql-9/
安装完之后，初始化数据库并设置开机启动：

  1. /usr/pgsql-9.6/bin/postgresql96-setup initdb
  2. systemctl enable postgresql-9.6
  3. systemctl start postgresql-9.6

配置数据库

查找路径：

  1. find / -name pg_hba.conf /var/lib/pgsql/12/data/pg_hba.conf

修改 pg_hba.conf，设置远程用户访问权限：

  1. sed -i "s/127.0.0.1\/32/0.0.0.0\/0/" /var/lib/pgsql/12/data/pg_hba.conf
  2. sed -i "s/ident/trust/" /var/lib/pgsql/12/data/pg_hba.conf
  3. sed -i "s/peer/trust/" /var/lib/pgsql/12/data/pg_hba.conf
  • md5并提供加密的密码验证
  • trust意思不用密码验证

修改postgresql.conf，配置监听地址：

  1. CONF_FILE=/var/lib/pgsql/12/data/postgresql.conf
  2. sudo sed -i "s/#port = 5432/port = 5432/" $CONF_FILE
  3. sudo sed -i "s/max_connections = 100/max_connections = 600/" $CONF_FILE
  4. sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" $CONF_FILE
  5. sudo sed -i "s/shared_buffers = 32MB/shared_buffers = 256MB/" $CONF_FILE

重启数据库：

  1. systemctl restart postgresql-12

创建数据库

  1. CREATE USER test WITH PASSWORD 'test'; 
  2. CREATE DATABASE dataware owner=test; 
  3. GRANT ALL privileges ON DATABASE dataware TO test;

自动化运行脚本

以下是按照hive时，配置hive元数据的数据库时的自动化安装脚本，仅供参考：

  1. #!/bin/bash
  2. readonly PROGNAME=$(basename $0)
  3. readonly PROGDIR=$(readlink -m $(dirname $0))
  4. readonly ARGS="$@"
  5. if [ `id -u` -ne 0 ]; then
  6.     echo "must run as root"
  7.     exit 1
  8. fi
  9. get_postgresql_major_version(){
  10.     local psql_output=`psql --version`
  11.     local regex="^psql \(PostgreSQL\) ([[:digit:]]+)\..*"
  12.     if [[ $psql_output =~ $regex ]]; then
  13.       echo ${BASH_REMATCH[1]}
  14.     fi
  15. }
  16. get_standard_conforming_strings(){
  17.     local psql_version=$(get_postgresql_major_version)
  18.     if [[ $psql_version -gt 8 ]]; then
  19.         echo "# This is needed to make Hive work with Postgresql 9.1 and above"
  20.         echo "# See OPSAPS-11795"
  21.         echo "standard_conforming_strings=off"
  22.     fi
  23. }
  24. check_postgresql_installed(){
  25.   echo -e "Install postgresql-server"
  26.     if ! rpm -q postgresql-server >/dev/null ; then
  27.         yum install postgresql-server postgresql-jdbc -y >/dev/null 2>&1
  28.         ln -s /usr/share/java/postgresql-jdbc.jar /usr/lib/hive/lib/postgresql-jdbc.jar
  29.         chkconfig postgresql on
  30.     fi
  31.     pkill -9 postgres
  32.     rm -rf /var/lib/pgsql/data /var/run/postgresql/.s.PGSQL.$DB_PORT
  33.   echo -e "Init postgresql database"
  34.     postgresql-setup initdb
  35. }
  36. configure_postgresql_conf(){
  37.   echo -e "Configure postgresql conf"
  38.     sed -i "s/#port = 5432/port = $DB_PORT/" $CONF_FILE
  39.     sed -i "s/max_connections = 100/max_connections = 600/" $CONF_FILE
  40.     sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" $CONF_FILE
  41.     sed -i "s/shared_buffers = 32MB/shared_buffers = 256MB/" $CONF_FILE
  42.     local SCS=$(get_standard_conforming_strings)
  43.     if [ "$SCS" != "" ]; then
  44.         echo -e $SCS
  45.         sed -i "s/#standard_conforming_strings = on/standard_conforming_strings = off/" $CONF_FILE
  46.     fi
  47. }
  48. enable_remote_connections(){
  49.   echo -e "Enable remote connections"
  50.     sed -i "s/127.0.0.1\/32/0.0.0.0\/0/" /var/lib/pgsql/data/pg_hba.conf
  51.     sed -i "s/ident/trust/" /var/lib/pgsql/data/pg_hba.conf
  52.   sed -i "s/peer/trust/" /var/lib/pgsql/data/pg_hba.conf
  53. }
  54. create_db(){
  55.     DB_NAME=$1
  56.     DB_USER=$2
  57.     DB_PASSWORD=$3
  58.   echo -e "Create database $DB_NAME"
  59.   cd /var/lib/pgsql/data
  60.     su -c "/usr/bin/pg_ctl start -w -m fast -D /var/lib/pgsql/data" postgres
  61.     su -c "/usr/bin/psql --command \"CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD'; \" " postgres
  62.     su -c "/usr/bin/psql --command \"CREATE DATABASE $DB_NAME owner=$DB_USER;\" " postgres
  63.     su -c "/usr/bin/psql --command \"GRANT ALL privileges ON DATABASE $DB_NAME TO $DB_USER;\" " postgres
  64. }
  65. init_hive_metastore(){
  66.     DB_NAME=$1
  67.     DB_USER=$2
  68.     DB_FILE=$3
  69.   echo -e "Init hive metastore using $DB_FILE"
  70.     su -c "/usr/bin/psql -U $DB_USER -d $DB_NAME -f $DB_FILE" postgres
  71. }
  72. manager_db(){
  73.   echo -e "$1 postgres"
  74.     su -c "/usr/bin/pg_ctl $1 -w -m fast -D /var/lib/pgsql/data" postgres
  75. }
  76. readonly DB_HOST=$(hostname -f)
  77. readonly DB_PORT=${DB_PORT:-5432}
  78. readonly DB_HOSTPORT="$DB_HOST:$DB_PORT"
  79. CONF_FILE="/var/lib/pgsql/data/postgresql.conf"
  80. check_postgresql_installed
  81. configure_postgresql_conf
  82. enable_remote_connections
  83. create_db metastore hive hive
  84. manager_db restart
  85. init_hive_metastore metastore hive `ls /usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-* |tail -n 1`

Docker安装

使用docker-compose安装：
postgresql.yaml

  1. version: "3"
  2. services:
  3.   db:
  4.     image: postgres:13
  5.     restart: always
  6.     ports:
  7.       - 5432:5432
  8.     environment:
  9.       - POSTGRES_USER=chenzj
  10.       - POSTGRES_PASSWORD=chenzj@vps2021
  11.     volumes:
  12.       - /data/postgres:/var/lib/postgresql/data

运行容器：

  1. docker-compose -f postgresql.yaml up -d