PostgreSQL9.2 升级至 PostgreSQL 9.3

前端之家收集整理的这篇文章主要介绍了PostgreSQL9.2 升级至 PostgreSQL 9.3前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

1)备份9.2的数据

cp -r pgdata /home/postgres/pgdata.bak


2)修改环境变量至新的Postgresql路径

vi ~/.bash_profile


/opt/postgres/pgsql/bin/psql


3)创建新数据库目录

mkdir /drbd/pgdata93


4)初始化数据库

/opt/postgres/pgsql/bin/initdb -E UTF-8 --local=en_US.UTF-8 -D /drbd/pgdata93/


如果升级时报如下错误,--local记得保持一致

lc_collate cluster values do not match: old "en_US.UTF-8",new "zh_CN.utf8"


5)执行升级(pg_upgrade 需要在contrib扩展里面安装,make 并且 make install即自动安装)

/opt/postgres/pgsql/bin/pg_upgrade -d /drbd/pgdata/ -D /drbd/pgdata93/ -b /usr/local/pgsql/bin/ -B /opt/postgres/pgsql/bin/



6)如果报自定义函数库找不到,需要重新编译并拷贝至 $libdir


more loadable_libraries.txt

Could not load library "$libdir/tablefunc"

ERROR: could not access file "$libdir/tablefunc": No such file or directory


Could not load library "$libdir/pgpool-recovery"

ERROR: could not access file "$libdir/pgpool-recovery": No such file or directory

Could not load library "$libdir/uuid-ossp"

ERROR: could not access file "$libdir/uuid-ossp": No such file or directory

Could not load library "$libdir/pg_stat_statements"

ERROR: could not access file "$libdir/pg_stat_statements": No such file or directory


7)注意

可能需要重新安装9.3的pgpool(即用源码在9.3的Postgresql环境下重新安装pgpool)

pgpool-II-3.3.3]$ ./configure --prefix=/home/postgres/opt/pgpool --with-pgsql=/opt/postgres/pgsql/


pgpool安装文件sql目录下面的 pgpool-recovery目录编译,才能出现如下.so,编译后拷贝至 $libdir

pgpool-recovery.so



8)启动数据库后,可以执行

/home/postgres/analyze_new_cluster.sh

原文链接:https://www.f2er.com/postgresql/195488.html

猜你在找的Postgre SQL相关文章