前面介绍了 PostgreSQL 基于 pgpool 实现读写分离实践、数据库备份与恢复、主从数据目录同步工具 pg_rewind、数据库作业调度工具、性能优化、日志与日常巡检等相关的知识点,今天我将详细的为大家介绍 PostgreSQL 数据库运维管理相关知识,希望大家能够从中收获多多!如有帮助,请点在看、转发支持一波!!!
版本升级
小版本升级pg_upgrade
su - postgres
#将旧的数据库目录重命名
mkdir /usr/local/pgsql.old
chown -R postgres.postgres /usr/local/pgsql.old
mv /usr/local/pgsql/* /usr/local/pgsql.old/
exit #切回root
cd /opt
rz #上传源码包
tar -zxvf postgresql-11.16.tar.gz #解压
cd postgresql-11.16/ #进入到源码目录
./configure --prefix=/usr/local/pgsql --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap
gmake world #gmake包括第三方插件全部编译
gmake install-world #包括第三方插件全部安装
mkdir /usr/local/pgsql/data #创建数据目录
chown -R postgres.postgres /usr/local/pgsql
chown -R postgres.postgres /usr/local/pgsql/data #授权数据目录
su - postgres
#初始化数据库
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 #初始化数据库
#如果有外部extension插件则在这安装插件
#关闭旧数据库
/usr/local/pgsql.old/bin/pg_ctl -D /usr/local/pgsql.old/data/ stop -m fast
#环境变量临时赋值本地ip
export PGHOST=127.0.0.1
#升级前检查
/usr/local/pgsql/bin/pg_upgrade -d /usr/local/pgsql.old/data/ -D /usr/local/pgsql/data/ -b /usr/local/pgsql.old/bin/ -B /usr/local/pgsql/bin/ -c
#升级
/usr/local/pgsql/bin/pg_upgrade -d /usr/local/pgsql.old/data/ -D /usr/local/pgsql/data/ -b /usr/local/pgsql.old/bin/ -B /usr/local/pgsql/bin/
#收集表的统计信息
./analyze_new_cluster.sh
cd $PGDATA
rm -f pg_hba.conf #删除新的pg_hba.conf
rm -f postgresql.conf #删除新的postgresql.conf
#拷贝旧的pg_hba.conf和postgresql.conf到升级的库
cp /usr/local/pgsql.old/data/pg_hba.conf $PGDATA/
cp /usr/local/pgsql.old/data/postgresql.conf $PGDATA/
#启动数据库
pg_ctl -D $PGDATA start
这是个bug,版本升级后,pg_config 改变了,会导致后面装外部extension时没有装到指定目录。
#旧版本
/database/postgres11.old/psql/bin/pg_config
BINDIR = /database/postgres11.old/psql/bin
DOCDIR = /database/postgres11.old/psql/share/doc
HTMLDIR = /database/postgres11.old/psql/share/doc
INCLUDEDIR = /database/postgres11.old/psql/include
PKGINCLUDEDIR = /database/postgres11.old/psql/include
INCLUDEDIR-SERVER = /database/postgres11.old/psql/include/server
LIBDIR = /database/postgres11.old/psql/lib
PKGLIBDIR = /database/postgres11.old/psql/lib
LOCALEDIR = /database/postgres11.old/psql/share/locale
MANDIR = /database/postgres11.old/psql/share/man
SHAREDIR = /database/postgres11.old/psql/share
SYSCONFDIR = /database/postgres11.old/psql/etc
PGXS = /database/postgres11.old/psql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/database/postgres11/psql/' '--with-perl' '--with-python'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/database/postgres11/psql/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 11.6
更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。
#新版本
/database/postgres11/psql/bin/pg_config
BINDIR = /database/postgres11/psql/bin
DOCDIR = /database/postgres11/psql/share/doc/postgresql
HTMLDIR = /database/postgres11/psql/share/doc/postgresql
INCLUDEDIR = /database/postgres11/psql/include
PKGINCLUDEDIR = /database/postgres11/psql/include/postgresql
INCLUDEDIR-SERVER = /database/postgres11/psql/include/postgresql/server
LIBDIR = /database/postgres11/psql/lib
PKGLIBDIR = /database/postgres11/psql/lib/postgresql
LOCALEDIR = /database/postgres11/psql/share/locale
MANDIR = /database/postgres11/psql/share/man
SHAREDIR = /database/postgres11/psql/share/postgresql
SYSCONFDIR = /database/postgres11/psql/etc/postgresql
PGXS = /database/postgres11/psql/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix='
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 11.16
大版本升级
逻辑备份旧数据库
详见:进阶数据库系列(二十):PostgreSQL 数据库备份与恢复
安装新版本数据库
然后创建对应的 role 和 schema 和 database 并且 grant。
在新版本库中进行逻辑恢复
数据库管理
简单的psql命令
[postgres@pg_master ~]$ psql -h 192.168.233.30 -p