!/bin/bash
获取当前目录的绝对路径
current_directory=$(readlink -f "$PWD")
echo '-------------------------pgsql安装开始-----------------------'
创建文件仓库配置
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
导入仓库签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
更新软件包列表
sudo apt update
安装 PostgreSQL 13
sudo apt -y install postgresql-13
设置 PostgreSQL 用户密码
echo postgres:123456 | chpasswd
备份 PostgreSQL 数据目录
sudo rsync -av /var/lib/postgresql /db
移动 PostgreSQL 数据目录
sudo mv /var/lib/postgresql/13/main /var/lib/postgresql/13/main.bak
修改 PostgreSQL 配置文件
sed -i 's#/var/lib/postgresql/13/main#/db/postgresql/13/main#g' /etc/postgresql/13/main/postgresql.conf
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/postgresql/13/main/postgresql.conf
修改 PostgreSQL 访问控制
sudo sed -i "s/host\sall\sall\s127.0.0.1/32\smd5/host all all 0.0.0.0/0 md5/g" /etc/postgresql/{version}/main/pg_hba.conf
允许防火墙访问 PostgreSQL 端口
sudo ufw allow 5432
启动 PostgreSQL 服务
sudo systemctl start postgresql
查看 PostgreSQL 服务状态
sudo systemctl status postgresql
删除备份的 PostgreSQL 数据目录
sudo rm -Rf /var/lib/postgresql/13/main.bak
更改 PostgreSQL 用户家目录
cd /
mkdir datadisk
sudo usermod --home '/datadisk' postgres
sudo chown postgres:postgres /datadisk
sudo systemctl restart postgresql
sudo systemctl status postgresql
cd /datadisk
创建 PostgreSQL 命令行配置文件
cat <
\pset null 'NULL'
\pset border 2
\set PROMPT1 '%n@%/#'
\timing on
EOF
echo '-------------------------pgsql安装结束-----------------------'
echo '-------------------------pgsql插件安装开始-----------------------'
安装 Python3 pip
sudo apt -y install python3-pip
安装 pgxnclient
sudo -H pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple pgxnclient
安装 PostgreSQL 开发依赖
sudo apt -y install postgresql-server-dev-13
安装必要的库
sudo apt -y install libicu-dev
创建工具目录
cd /db
mkdir tools
cd tools
复制 pg_bigm 源码
cp $current_directory/pg_bigm-1.2-20200228.tar.gz /db/tools
解压 pg_bigm
cd /db/tools
tar zxf pg_bigm-1.2-20200228.tar.gz
cd pg_bigm-1.2-20200228
make USE_PGXS=1 PG_CONFIG=/usr/bin/pg_config
make USE_PGXS=1 PG_CONFIG=/usr/bin/pg_config install
安装依赖
sudo apt install libssl-dev
sudo apt install zlib1g-dev
sudo apt install libreadline-dev
安装 pg_repack
sudo pgxn install pg_repack
sudo apt install postgresql-13-repack
安装 clickhouse_fdw
sudo apt install libcurl4-openssl-dev
sudo apt install uuid-dev
cd /db/tools
sudo git clone https://github.com/adjust/clickhouse_fdw.git
cd clickhouse_fdw
mkdir build && cd build
sudo apt-get install pkg-config
sudo apt-get install cmake
cmake ..
make && make install
安装 scws
sudo apt-get install build-essential
cd /db/tools
wget -q -O - http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2 | tar xjf -
cd scws-1.2.3
./configure
sudo make install
安装 zhparser
cd /db/tools
git clone https://github.com/amutu/zhparser.git
cd zhparser
make && sudo make install
安装 rum
cd /db/tools
git clone https://github.com/postgrespro/rum
cd rum
make USE_PGXS=1 && sudo make USE_PGXS=1 install
echo '-------------------------pgsql插件安装结束-----------------------'
echo '-----注意事项：进入数据库 执行语句创建extention-------：
CREATE EXTENSION IF NOT EXISTS pg_bigm;
CREATE EXTENSION pg_repack;
-- 启用扩展
CREATE EXTENSION clickhouse_fdw;
-- 创建数据库链接
CREATE SERVER clickhouse_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'default');
-- 用户映射
CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr OPTIONS (user 'default', password '9dje3RtgO7eeHb');
-- 创建外部表
CREATE FOREIGN TABLE dwd_user_info
(
user_infoid text,
code text,
name text,
createdon timestamp
)
SERVER clickhouse_svr;
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION zh(PARSER = zhparser); -- 添加配置
ALTER TEXT SEARCH CONFIGURATION zh ADD MAPPING FOR n,v,a,i,e,l,j WITH simple; --设置分词规则 （n 名词 v 动词等）
alter system set zhparser.dict_in_memory = 'on';
CREATE EXTENSION rum;
create index odp_accountbase_name_rum_idx on odp_accountbase using rum(to_tsvector('zh', name));
'