postgresql13 rpm方式安装(推荐)+主从部署
环境:
OS:Centos 7
DB:13.8
1.介质下载
可以到官网下载相应版本的rpm介质
下载地址:
https://yum.postgresql.org/rpmchart/
https://yum.postgresql.org/13/redhat/rhel-7-x86_64/repoview/postgresqldbserver13.group.html
我这里下载的如下介质
[root@localhost pg]# ls -lrt
total 7968
-rw-r--r--. 1 root root 1481300 Oct 31 05:29 postgresql13-13.8-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root 628384 Oct 31 05:30 postgresql13-contrib-13.8-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root 392816 Oct 31 05:30 postgresql13-libs-13.8-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root 5649556 Oct 31 05:30 postgresql13-server-13.8-1PGDG.rhel7.x86_64.rpm
2.创建用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres
3.修改系统配置
关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
修改vi /etc/selinux/config
SELINUX=disabled
vi /etc/security/limits.conf ,最后面增加两行
* hard nofile 65535
* soft nofile 65535
#sysctl -p
然后退出重新登录
[mysql@localhost logfile]# ulimit -n
65535
3.安装系统依赖包
yum install libxslt
yum install perl
这些包在操作系统的介质里可以找得到,修改yum指向本地光盘挂载目录即可安装
4.安装pg
rpm -ivh postgresql13-libs-13.8-1PGDG.rhel7.x86_64.rpm
rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm
rpm -ivh postgresql13-13.8-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql13-server-13.8-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql13-contrib-13.8-1PGDG.rhel7.x86_64.rpm
libicu下载地址为:
http://mirror.centos.org/centos/7/os/x86_64/Packages/libicu-50.2-4.el7_7.x86_64.rpm
默认postgreql 安装在/usr/pgsql-13
数据存储目录:/var/lib/pgsql/版本号/data,
在实际生产中/var可能存在硬盘空间不足的问题,我们一般将数据存储目录放在挂载的硬盘如/data下
可以使用rpm -qpl postgresql13-libs-13.8-1PGDG.rhel7.x86_64.rpm 查看安装包的路径
这些安装包上不能自定义安装路径的
[root@host134 pg14]# rpm -qpi postgresql14-libs-14.11-1PGDG.rhel7.x86_64.rpm|grep Relocations
warning: postgresql14-libs-14.11-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Relocations : (not relocatable)
[root@host134 pg14]# rpm -qpi postgresql14-14.11-1PGDG.rhel7.x86_64.rpm|grep Relocations
warning: postgresql14-14.11-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Relocations : (not relocatable)
[root@host134 pg14]# rpm -qpi postgresql14-server-14.11-1PGDG.rhel7.x86_64.rpm|grep Relocations
warning: postgresql14-server-14.11-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Relocations : (not relocatable)
[root@host134 pg14]# rpm -qpi postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm|grep Relocations
warning: postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Relocations : (not relocatable)
pg14安装会报如下依赖包错误
报错:
[root@dsc1 pg]# rpm -ivh postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
error: Failed dependencies:
libpython3.6m.so.1.0()(64bit) is needed by postgresql14-contrib-14.11-1PGDG.rhel7.x86_64
ipimport.ZipImportError: can't decompress data; zlib not available
make: *** [install] Error 1
yum install zlib
yum install zlib-devel
解决办法:
yum install libxslt libicu python36-libs python2-libs libperl.so
5.创建数据存储目录
[root@localhost bin]#mkdir -p /opt/pg13/data
[root@localhost bin]#mkdir -p /opt/pg13/log
[root@localhost bin]#mkdir -p /opt/pg13/archivelog
[root@localhost bin]#chown -R postgres:postgres /opt/pg13
[root@localhost bin]#chmod 0700 /opt/pg13/data
6.初始化数据库
su - postgres
/usr/pgsql-13/bin/initdb -D /opt/pg13/data
7.修改启动参数
su - root
[root@localhost postgresql-13.service.d]# ls -al /usr/lib/systemd/system/postgresql-13.service
-rw-r--r--. 1 root root 1764 Aug 10 06:06 /usr/lib/systemd/system/postgresql-13.service
vi /usr/lib/systemd/system/postgresql-13.service
修改为Environment=PGDATA=/opt/pg13/data/
8.修改配置参数postgresql.conf
su - postgres
vi /opt/pg13/data/postgresql.conf
修改的内容如下:
[root@dsc1 data]# more postgresql.conf
listen_addresses='*'
port=5432
max_connections=1000
unix_socket_directories = '/tmp'
####内存相关#########
work_mem=10MB
maintenance_work_mem=2GB
max_locks_per_transaction=1024
max_wal_size=64GB
checkpoint_timeout=30min
checkpoint_completion_target=0.9
shared_buffers=2GB ##RAM*0.25GB
effective_cache_size=4GB ##RAM*0.5GB #RAM指内存(free -g)
wal_level=replica
archive_mode=on
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg14/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'
######慢查询部分#####################
logging_collector=on
log_directory = '/opt/pg14/log' ##指定具体目录,否则会默认在pgdata目录下创建log目录
log_destination='stderr'
log_min_duration_statement=1000 ##超过1秒的慢查询都会记录
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = off ##是否覆盖
log_rotation_age = 1d ##每天生成
log_rotation_size = 10MB ##每个日志大小
#log_statement = all 和 log_min_duration_statement = 5000
#根据需要两者设置其一即可
#如果两个都进行了配置默认所有SQL全部打印，log_min_duration_statement设置失效
##log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息
log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'
wal_log_hints=on
full_page_writes=on
##时区
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
wal_keep_size = 1024MB ##为standby从库保留的wal日志大小
9.修改配置参数pg_hba.conf
su - postgres
vi /opt/pg13/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
10.设置postgres用户环境变量
su - postgres
[postgres@localhost ~]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PGHOME=/usr/pgsql-13
PGDATA=/opt/pg13/data
PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export PGHOST=/tmp ##因为参数unix_socket_directories = '/tmp' 设置在tmp目录
11.启动数据库
[root@localhost pgsql-13]#systemctl daemon-reload
[root@localhost pgsql-13]#systemctl start postgresql-13
[root@localhost pgsql-13]#systemctl enable postgresql-13
10.登录并修改postgres账号的密码
su - postgres
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
客户端连接需要关闭服务器防火墙
[root@localhost log]# systemctl stop firewalld.service
12.检查常用的扩展是否已经安装
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# create extension dblink;
CREATE EXTENSION
说明这两个默认扩展是已经安装了的.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers
(3 rows)
安装连接mysql的扩展
[root@localhost]#yum install epel-release
[root@localhost]#yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@localhost extension]#yum list mysql_fdw*
[root@localhost extension]#yum install mysql_fdw_13.x86_64
执行完该命令后会在扩展目录生成如下文件
[root@host134 extension]# pwd
/usr/pgsql-13/share/extension
[root@host134 extension]# ls -al *mysql*
-rwxr-xr-x 1 root root 156 Jul 21 2023 mysql_fdw--1.0--1.1.sql
-rwxr-xr-x 1 root root 722 Jul 21 2023 mysql_fdw--1.0.sql
-rwxr-xr-x 1 root root 282 Jul 21 2023 mysql_fdw--1.1--1.2.sql
-rwxr-xr-x 1 root root 838 Jul 21 2023 mysql_fdw--1.1.sql
-rwxr-xr-x 1 root root 1007 Jul 21 2023 mysql_fdw--1.2.sql
-rwxr-xr-x 1 root root 546 Jul 21 2023 mysql_fdw.control
-rwxr-xr-x 1 root root 11489 Jul 21 2023 mysql_fdw_pushdown.config
-rwxr-xr-x 1 root root 11153 Jul 21 2023 README-mysql_fdw
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
postgres=# create extension mysql_fdw;
CREATE EXTENSION
13.尝试使用命令行启动关闭数据库
yum安装的默认启动和关闭数据库可以使用systemctl操作的,下面我们尝试使用命令的方式启动和关闭
[root@localhost log]# systemctl stop postgresql-13
方法1:
su - postgres
[postgres@localhost ~]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
[postgres@localhost ~]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
方法2:
su - postgres
后台启动
/usr/pgsql-13/bin/postmaster -D /opt/pg13/data/ &
关闭
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
################################安装postgis扩展#########################
1.安装相关依赖yum
[root@localhost]#yum install epel-release
[root@localhost]#yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2.查看postgis个版本
yum list postgis*
3.我们这里安装32版本
这里可以查看pg版本可以安装那个postgis的版本对应关系
https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
我这里选择安装32-13版本,32代表的是postgis版本,13代表的是pg版本
yum install postgis32_13.x86_64
安装过程可以看到安装很多的依赖包:
Install 1 Package (+81 Dependent packages)
4.查看安装版本信息
[root@localhost yum.repos.d]# rpm -qi postgis32_13
Name : postgis32_13
Version : 3.2.3
Release : 1.rhel7
Architecture: x86_64
Install Date: Mon 31 Oct 2022 10:39:51 PM EDT
Group : Unspecified
Size : 36912353
License : GPLv2+
Signature : DSA/SHA1, Mon 22 Aug 2022 03:50:51 AM EDT, Key ID 1f16d2e1442df0f8
Source RPM : postgis32_13-3.2.3-1.rhel7.src.rpm
Build Date : Mon 22 Aug 2022 03:50:46 AM EDT
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://www.postgis.net/
Summary : Geographic Information Systems Extensions to PostgreSQL
Description :
PostGIS adds support for geographic objects to the PostgreSQL object-relational
database. In effect, PostGIS "spatially enables" the PostgreSQL server,
allowing it to be used as a backend spatial database for geographic information
systems (GIS), much like ESRI's SDE or Oracle's Spatial extension. PostGIS
follows the OpenGIS "Simple Features Specification for SQL" and has been
certified as compliant with the "Types and Functions" profile.
[root@localhost yum.repos.d]#
5.安装扩展
su - postgres
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help
postgres=# create extension postgis;
CREATE EXTENSION
postgres=# create extension postgis_raster;
CREATE EXTENSION
postgres=# create extension postgis_topology;
CREATE EXTENSION
postgres=# create extension postgis_sfcgal;
CREATE EXTENSION
postgres=# create extension fuzzystrmatch;
CREATE EXTENSION
postgres=# create extension address_standardizer;
CREATE EXTENSION
postgres=# create extension address_standardizer_data_us;
CREATE EXTENSION
postgres=# create extension postgis_tiger_geocoder;
CREATE EXTENSION
6.验证是否安装成功
##查看版本,验证安装是否成功
postgres=# SELECT PostGIS_full_version();
7.查看当前数据库安装的组件
postgres=# \dx
List of installed extensions
Name | Version | Schema | Descriptio
n
------------------------------+---------+------------+---------------------------------------------------------------
------------------------------------------------------
address_standardizer | 3.2.3 | public | Used to parse an address into constituent elements. Generally
used to support geocoding address normalization step.
address_standardizer_data_us | 3.2.3 | public | Address Standardizer US dataset example
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
fuzzystrmatch | 1.1 | public | determine similarities and distance between strings
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.2.3 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.2.3 | public | PostGIS raster types and functions
postgis_sfcgal | 3.2.3 | public | PostGIS SFCGAL functions
postgis_tiger_geocoder | 3.2.3 | tiger | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 3.2.3 | topology | PostGIS topology spatial types and functions
postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
(11 rows)
########################主从部署###########################
1.主库创建流复制的用户
su - postgres
[postgres@hmaster ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.
postgres=# CREATE ROLE replica login replication encrypted password 'replica';
CREATE ROLE
2.主库修改pg_hba.conf文件,允许备库IP通过复制用户访问数据库
vi /opt/pg13/data/pg_hba.conf
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host replication replica 192.168.1.0/24 md5 ## 新增的,我这里整个网段开放
192.168.1.0/24是开放整个网段,也可以指定具体的ip
host replication replica 192.168.1.102/32 md5 ## 具体指定ip
host replication replica 192.168.1.103/32 md5 ## 具体指定ip
需要重新reload,否则报错连接不了
[postgres@hmaster ~]$ pg_ctl -D /opt/pg13/data reload
server signaled
3.停掉从库
[root@hslavea Python-3.6.5]# systemctl stop postgresql-13
4.从库准备data目录
从库安装完成后，不初始化，若已经初始化，删除其data目录
若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录
su - postgres
[postgres@host135 ~]$ cd /opt/pg13
[postgres@host135 pg13]$ mv data bakdata
[postgres@host135 pg13]$ mkdir data
创建归档目录(若已经存在的可以忽略)，保持与主库一致
[postgres@host135 pg13]$mkdir -p /opt/pg13/archivelog
注意权限要正确,不对的话需要进行修改,root用户下修改权限
[root@host135 ~]# chown -R postgres:postgres /opt/pg13
[root@host135 ~]# chmod 0700 /opt/pg13/data
5.备库上执行对于主库的基础备份
su - postgres
[postgres@host135 pg13]$pg_basebackup -h 192.168.1.100 -p 5432 -U replica --password -X stream -Fp --progress -D /opt/pg13/data -R -c fast
注意,备份选项上带有-R选项.
执行了pg_basebackup命令,从库会把主库的 postgresql.conf,pg_hba.conf文件也拷贝过来了的
复制信息已经在postgresql.auto.conf文件
[root@hslavea data]# more postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replica password=replica channel_binding=prefer host=192.168.1.100 port=5432 sslmode=prefer
sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
[root@hslavea data]#
6.启动
[root@hslavea data]#systemctl start postgresql-13
7.验证
主库上创建用户、库和表
创建用户
[postgres@hmaster ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.
postgres=# create user hxl with password 'postgres';
CREATE ROLE
创建数据库
postgres=# create database db_test encoding = 'utf8' owner hxl; -- 创建数据库指定字符集和属主
CREATE DATABASE
[postgres@hmaster ~]$ psql -h 192.168.1.100 -U hxl -d db_test
Password for user hxl:
psql (13.8)
Type "help" for help.
create table tb_test
(
id bigserial primary key not null,
name varchar(20),
createtime timestamp default current_timestamp,
modifytime timestamp default current_timestamp
);
insert into tb_test(name) values('name1');
insert into tb_test(name) values('name2');
insert into tb_test(name) values('name3');
insert into tb_test(name) values('name4');
insert into tb_test(name) values('name5');
db_test=> select * from tb_test;
id | name | createtime | modifytime
----+-------+----------------------------+----------------------------
1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901
2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863
3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182
4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843
5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502
(5 rows)
从库验证
[postgres@hslavea ~]$ psql -h 192.168.1.100 -U hxl -d db_test
Password for user hxl:
psql (13.8)
Type "help" for help.
db_test=> select * from tb_test;
id | name | createtime | modifytime
----+-------+----------------------------+----------------------------
1 | name1 | 2022-11-09 17:18:39.973171 | 2022-11-09 17:18:39.973171
2 | name2 | 2022-11-09 17:18:39.982155 | 2022-11-09 17:18:39.982155
3 | name3 | 2022-11-09 17:18:39.99649 | 2022-11-09 17:18:39.99649
4 | name4 | 2022-11-09 17:18:40.061437 | 2022-11-09 17:18:40.061437
5 | name5 | 2022-11-09 17:18:40.709742 | 2022-11-09 17:18:40.709742
(5 rows)
查看主从关系视图:
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
