postgresql安装
[TOC]
二进制安装 postgresql-10.12
- 二进制安装 postgresql 10.12-1（目前没有更高版本的二进制包），示例 数据目录
/data/pgsql_1921，端口1921
#.下载安装
cd /opt/
wget -c http://iso.sqlfans.cn/postgresql/postgresql-10.12-1-linux-x64-binaries.tar.gz
wget -c http://iso.sqlfans.cn/postgresql/install_pgsql_1012.sh
wget -c http://iso.sqlfans.cn/postgresql/my_pgsql.conf
bash install_pgsql_1012.sh /opt /data 1921
#.登陆测试
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "SELECT * FROM pg_shadow;"
- 停止与卸载
ps aux | fgrep 'postgres' | fgrep "bin" | awk '{print $2}' | xargs kill -9
rm -rf /data/pgsql*
rm -rf /opt/pgsql
userdel -r postgres
yum安装 postgresql-16-latest
- 在 RHEL-8.6 上安装 postgresql-16-latest，默认 安装目录
/usr/pgsql-16/bin，数据目录/var/lib/pgsql/16/data
注：CentOS 7 已于 2020-08-06 停止更新，于 2024-06-30 停止维护，而Posgres官方从2023年8月不再提供RHEL7上的rpm包更新，包括PostgreSQL 16及更高版本（公告在此）。若要在Centos7上安装Postgres16，可通过手动编译源码来安装，否则执行
yum install postgresql16-server会提示No package postgresql16-server available.
#.在redhat 8.6上安装
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
sudo /usr/pgsql-16/bin/psql --version
#.优化默认参数-先取消注释
cp /var/lib/pgsql/16/data/postgresql.conf /var/lib/pgsql/16/data/postgresql.conf.$(date +%Y%m%d%H%M%S)
sed -i "s/^#port/port/" /var/lib/pgsql/16/data/postgresql.conf
sed -i "s/^#listen_addresses/listen_addresses/" /var/lib/pgsql/16/data/postgresql.conf
sed -i "s/^#max_connections/max_connections/" /var/lib/pgsql/16/data/postgresql.conf
sed -i "s/^#shared_buffers/shared_buffers/" /var/lib/pgsql/16/data/postgresql.conf
#.优化默认参数-再修改数值，示例端口改为 1921
sed -i "s/^port.*$/port=1921/g" /var/lib/pgsql/16/data/postgresql.conf
sed -i "s/^listen_addresses.*$/listen_addresses='*'/g" /var/lib/pgsql/16/data/postgresql.conf
sed -i "s/^max_connections.*$/max_connections=3000/g" /var/lib/pgsql/16/data/postgresql.conf
sed -i "s/^shared_buffers.*$/shared_buffers=512MB/g" /var/lib/pgsql/16/data/postgresql.conf
cat /var/lib/pgsql/16/data/postgresql.conf | egrep "(^port|^listen_addresses|^max_connections|^shared_buffers)"
#.允许所有ip访问
cp /var/lib/pgsql/16/data/pg_hba.conf /var/lib/pgsql/16/data/pg_hba.conf.$(date +%Y%m%d%H%M%S)
echo "host all all 0.0.0.0/0 md5" >> /var/lib/pgsql/16/data/pg_hba.conf
#.重启服务生效
sudo systemctl restart postgresql-16
sudo systemctl status postgresql-16
#.重置postgres密码，示例改为 Admin_147
[root@redhat8 ~]# sudo -u postgres psql -p 1921
postgres=# ALTER USER postgres WITH PASSWORD 'Admin_147';
postgres=# \q
#.登陆测试，密码请用 Admin_147
/usr/pgsql-16/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "SELECT * FROM pg_shadow;"
- 停止与卸载
sudo systemctl stop postgresql-16
sudo dnf remove -y postgresql16-server
rm -rf /usr/pgsql-16
rm -rf /var/lib/pgsql
docker安装 postgresql-16.6
- docker方式安装 postgresql-16.6，示例 端口
1921
#.安装docker-24.0.1
curl -sL 'http://iso.sqlfans.cn/docker/install_docker_2401.sh' | bash
docker --version
#.创建数据目录
mkdir -p /data/docker/pgsql_1921
#.启动容器，示例改为 Admin_147
docker pull postgres:16.6
docker run -d --name postgres_1921 -p 1921:5432 -e POSTGRES_PASSWORD=Admin_147 -e TZ=Asia/Shanghai -v /data/docker/pgsql_1921:/var/lib/postgresql/data postgres:16
docker exec -it postgres_1921 psql -U postgres
- 停止与卸载
docker stop $(docker ps -a | grep postgres_1921 | awk '{print $1}')
docker rm $(docker ps -a | grep postgres_1921 | awk '{print $1}')
docker rmi -f $(docker images | grep postgres | awk '{print $3}')
rm -rf /data/docker/pgsql_1921
编译安装 postgresql-16.6
- 编译安装 postgresql-16.6，示例 安装目录
/opt/pgsql，端口1921
#.安装依赖
# dnf install -y gcc libicu-devel zlib-devel readline-devel perl-ExtUtils-Embed openssl-devel tcl tcl-devel
yum install -y gcc libicu-devel zlib-devel readline-devel perl-ExtUtils-Embed openssl openssl-devel tcl tcl-devel
#.编译安装，示例端口 1921
cd /opt/
wget -c http://iso.sqlfans.cn/postgresql/postgresql-16.6.tar.gz
tar xvf postgresql-16.6.tar.gz
cd postgresql-16.6
./configure --prefix=/opt/pgsql --with-pgport=1921 --with-perl --with-tcl --with-openssl --without-readline
make -j4 && make install
- 启动实例，示例 安装目录
/data/pgsql_1921
#.创建启动用户
groupadd postgres
useradd -g postgres postgres
echo "Admin_147" | passwd postgres --stdin
#.创建数据目录
mkdir -p /data/pgsql_1921
chown -R postgres:postgres /data/pgsql_1921
chmod -R 775 /data/pgsql_1921
#.初始化及启动
su - postgres -c "/opt/pgsql/bin/initdb -E UNICODE -D /data/pgsql_1921"
su - postgres -c '/opt/pgsql/bin/pg_ctl -D /data/pgsql_1921 -l logfile start'
#.优化默认参数-先取消注释
cp /data/pgsql_1921/postgresql.conf /data/pgsql_1921/postgresql.conf.$(date +%Y%m%d%H%M%S)
sed -i "s/^#port/port/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#listen_addresses/listen_addresses/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#max_connections/max_connections/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#shared_buffers/shared_buffers/" /data/pgsql_1921/postgresql.conf
#.优化默认参数-再修改数值，示例端口改为 1921
sed -i "s/^port.*$/port=1921/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^listen_addresses.*$/listen_addresses='*'/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^max_connections.*$/max_connections=3000/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^shared_buffers.*$/shared_buffers=512MB/g" /data/pgsql_1921/postgresql.conf
cat /data/pgsql_1921/postgresql.conf | egrep "(^port|^listen_addresses|^max_connections|^shared_buffers)"
#.允许所有ip访问
cp /data/pgsql_1921/pg_hba.conf /data/pgsql_1921/pg_hba.conf.$(date +%Y%m%d%H%M%S)
echo "host all all 0.0.0.0/0 md5" >> /data/pgsql_1921/pg_hba.conf
#.重启服务生效
su - postgres -c '/opt/pgsql/bin/pg_ctl -D /data/pgsql_1921 -l logfile restart'
#.重置postgres密码，示例改为 Admin_147
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "ALTER USER postgres WITH PASSWORD 'Admin_147';"
#.登陆测试，密码请用 Admin_147
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "SELECT * FROM pg_shadow;"
- 停止与卸载
ps aux | fgrep 'postgres' | fgrep "bin" | awk '{print $2}' | xargs kill -9
rm -rf /data/pgsql*
rm -rf /opt/pgsql
userdel -r postgres