PostgreSQL pg_upgrade 9.3 improve and example
背景
E.1.3.10.1. pg_upgrade
Allow pg_upgrade --jobs to do parallelism (Bruce Momjian)
This allows parallel schema dump/restore of databases, as well as parallel copy/link of data files per tablespace.
Have pg_upgrade create unix-domain sockets in the current directory (Bruce Momjian, Tom Lane)
This reduces the possibility that someone will accidentally connect during the upgrade.
Have pg_upgrade --check mode properly detect the location of non-default socket directories (Bruce Momjian, Tom Lane)
Improve performance of pg_upgrade for databases with many tables (Bruce Momjian)
Increase pg_upgrade logging content by showing executed command (?lvaro Herrera)
Improve pg_upgrade's status display during copy/link (Bruce Momjian)
pg_upgrade使用介绍
测试环境 :
CentOS 5.x 64bit
PostgreSQL 9.0 beta2
postgis
升级到 PostgreSQL 9.3 beta1
一. 安装PostgreSQL 9.0 beta2测试环境
[root@db-172-16-3-33 soft_bak]# useradd uptest
su - uptest
vi .bash_profile
# add by digoal
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1099
export PGDATA=/pgdata1099
export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.0beta2
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
alias rm='rm -i'
alias ll='ls -lh'
vi /etc/sysctl.conf
# add by digoal
# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536
# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
net.ipv4.ip_conntrack_max = 655360
fs.aio-max-nr = 1048576
net.ipv4.tcp_timestamps = 0
vm.overcommit_memory = 0
sysctl -p
vi /etc/security/limits.conf
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000
[root@db-172-16-3-33 soft_bak]# cd flex-2.5.35
./configure
make
make install
[root@db-172-16-3-33 soft_bak]# tar -jxvf postgresql-9.0beta2.tar.bz2
[root@db-172-16-3-33 soft_bak]# cd postgresql-9.0beta2
./configure --prefix=/opt/pgsql9.0beta2 --with-pgport=1099 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
gmake world
gmake install-world
初始化数据库 :
[root@db-172-16-3-33 postgresql-9.0beta2]# mkdir /pgdata1099
[root@db-172-16-3-33 postgresql-9.0beta2]# chown uptest:uptest /pgdata1099
[root@db-172-16-3-33 postgresql-9.0beta2]# su - uptest
uptest@db-172-16-3-33-> initdb -D $PGDATA -E UTF8 --locale=C -W -U postgres
配置postgresql.conf
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
port = 1099 # (change requires restart)
max_connections = 100 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directory = '.' # (change requires restart)
unix_socket_permissions = 0700 # begin with 0 to use octal notation
shared_buffers = 1024MB # min 128kB
maintenance_work_mem = 512MB # min 1MB
max_stack_depth = 8MB # min 100kB
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
wal_level = hot_standby # minimal, archive, or hot_standby
synchronous_commit = off # immediate fsync at commit
wal_sync_method = fsync # the default is the first option
full_page_writes = on # recover from partial page writes
wal_buffers = 16384kB # min 32kB
wal_writer_delay = 10ms # 1-10000 milliseconds
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
archive_mode = on # allows archiving to be done
archive_command = '/bin/date' # command to use to archive a logfile segment
hot_standby = on # allows queries during recovery
max_wal_senders = 16 # max number of walsender processes
wal_sender_delay = 10ms # 1-10000 milliseconds
wal_keep_segments = 256 # in logfile segments, 16MB each; 0 disables
random_page_cost = 2.0 # same scale as above
effective_cache_size = 8192MB
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file of the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_lock_waits = on # log lock waits >= deadlock_timeout
log_statement = 'ddl' # none, ddl, mod, all
autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all
启动数据库 :
uptest@db-172-16-3-33-> pg_ctl start
server starting
uptest@db-172-16-3-33-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1201
Not safe to send CSV data
安装模块 :
pgfincore
[root@db-172-16-3-33 soft_bak]# tar -zxvf pgfincore-v1.1.1.tar.gz
[root@db-172-16-3-33 soft_bak]# mv pgfincore-1.1.1 postgresql-9.0beta2/contrib/
[root@db-172-16-3-33 soft_bak]# cd postgresql-9.0beta2/contrib/pgfincore-1.1.1/
[root@db-172-16-3-33 pgfincore-1.1.1]# export PATH=/opt/pgsql9.0beta2/bin:$PATH
[root@db-172-16-3-33 pgfincore-1.1.1]# which pg_config
/opt/pgsql9.0beta2/bin/pg_config
[root@db-172-16-3-33 pgfincore-1.1.1]# gmake clean
[root@db-172-16-3-33 pgfincore-1.1.1]# gmake
[root@db-172-16-3-33 pgfincore-1.1.1]# gmake install
uptest@db-172-16-3-33-> psql -f /opt/pgsql9.0beta2/share/contrib/pgfincore.sql
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
pg_stat_statements
uptest@db-172-16-3-33-> psql
psql (9.0beta2)
Type "help" for help.
postgres=# \q
uptest@db-172-16-3-33-> psql -f /opt/pgsql9.0beta2/share/contrib/pg_stat_statements.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
GRANT
REVOKE
安装模块 postgis：
安装参考 :
下载安装包 :
http://download.osgeo.org/gdal/1.10.0/gdal-1.10.0.tar.gz
http://download.osgeo.org/geos/geos-3.3.8.tar.bz2
http://download.osgeo.org/proj/proj-4.8.0.tar.gz
https://github.com/json-c/json-c
http://download.osgeo.org/postgis/source/postgis-2.0.3.tar.gz
tar -zxvf gdal-1.10.0.tar.gz
cd gdal-1.10.0
./configure --prefix=/opt/gdal-1.10.0
make
make install
tar -jxvf geos-3.3.8.tar.bz2
cd geos-3.3.8
./configure --prefix=/opt/geos-3.3.8
make
make install
tar -zxvf proj-4.8.0.tar.gz
cd proj-4.8.0
./configure --prefix=/opt/proj-4.8.0
make
make install
tar -zxvf unzip json-c-master.zip
cd json-c-master
./autogen.sh
./configure --prefix=/opt/json-c-git20130520
make
make install
使用, add to Makefile
JSON_C_DIR=/opt/json-c-git20130520
CFLAGS += -I$(JSON_C_DIR)/include/json-c
LDFLAGS+= -L$(JSON_C_DIR)/lib -ljson-c
su - uptest
vi .bash_profile
export LD_LIBRARY_PATH=/opt/gdal-1.10.0/lib:/opt/geos-3.3.8/lib:/opt/json-c-git20130520/lib:/opt/proj-4.8.0/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
tar -zxvf postgis-2.0.3.tar.gz
cd postgis-2.0.3
./configure --prefix=/opt/postgis-2.0.3 --with-pgconfig=/opt/pgsql9.0beta2/bin/pg_config --with-gdalconfig=/opt/gdal-1.10.0/bin/gdal-config --with-geosconfig=/opt/geos-3.3.8/bin/geos-config --with-xml2config=/usr/bin/xml2-config --with-projdir=/opt/proj-4.8.0 --with-libiconv=/usr/bin --with-jsondir=/opt/json-c-git20130520 --with-gui --with-raster --with-topology --with-gettext=no
gmake
gmake install
重启数据库.
pg_ctl restart -m fast
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/postgis.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/spatial_ref_sys.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/postgis_comments.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/rtpostgis.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/raster_comments.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/topology.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/topology_comments.sql
psql -f /opt/pgsql9.0beta2/share/contrib/postgis-2.0/legacy.sql
测试数据 :
uptest@db-172-16-3-33-> psql
psql (9.0beta2)
Type "help" for help.
postgres=# create table test (id int primary key, info text);;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
postgres=# insert into test select generate_series(1,10000);
INSERT 0 10000
二. 升级准备
1. 检查老版本configure选项
uptest@db-172-16-3-33-> pg_config --pkgincludedir
/opt/pgsql9.0beta2/include
uptest@db-172-16-3-33-> pg_config --cflags
-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
uptest@db-172-16-3-33-> pg_config --cflags_sl
-fpic
uptest@db-172-16-3-33-> pg_config --ldflags
-Wl,-rpath,'/opt/pgsql9.0beta2/lib',--enable-new-dtags
uptest@db-172-16-3-33-> pg_config --libs
-lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lz -lreadline -ltermcap -lcrypt -ldl -lm
uptest@db-172-16-3-33-> pg_config --configure
'--prefix=/opt/pgsql9.0beta2' '--with-pgport=1099' '--with-segsize=8' '--with-wal-segsize=64' '--with-wal-blocksize=64' '--with-perl' '--with-python' '--with-openssl' '--with-pam' '--with-ldap' '--with-libxml' '--with-libxslt' '--enable-thread-safety'
或者查看老版本的config.log
[root@db-172-16-3-33 soft_bak]# cd postgresql-9.0beta2
[root@db-172-16-3-33 postgresql-9.0beta2]# less config.log
$ ./configure --prefix=/opt/pgsql9.0beta2 --with-pgport=1099 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
2. 检查老版本initdb选项
uptest@db-172-16-3-33-> psql
psql (9.0beta2)
Type "help" for help.
postgres=# \l+
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-----------+-------+-----------------------+---------+------------+---------------------------
postgres | postgres | UTF8 | C | C | | 11 MB | pg_default |
template0 | postgres | UTF8 | C | C | =c/postgres +| 5273 kB | pg_default |
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 5273 kB | pg_default | default template database
| | | | | postgres=CTc/postgres | | |
(3 rows)
3. 根据老版本的configure选项以及其他环境变量编译postgresql 9.3beta1
wget http://ftp.postgresql.org/pub/source/v9.3beta1/postgresql-9.3beta1.tar.bz2
tar -jxvf postgresql-9.3beta1.tar.bz2
cd postgresql-9.3beta1
注意端口不要和老版本重复.
./configure --prefix=/opt/pgsql9.3beta1 --with-pgport=2099 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
gmake world
gmake install-world
4. 初始化数据库集群
[root@db-172-16-3-33 postgresql-9.3beta1]# cd /
[root@db-172-16-3-33 /]# mkdir pgdata2099
[root@db-172-16-3-33 /]# chown -R uptest:uptest pgdata2099
[root@db-172-16-3-33 /]# su - uptest
uptest@db-172-16-3-33-> LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib /opt/pgsql9.3beta1/bin/initdb -D /pgdata2099 -E UTF8 --locale=C -U postgres -W
配置postgresql.conf, 注意端口不要和老的重复.
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
port = 2099 # (change requires restart)
max_connections = 100 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
unix_socket_permissions = 0700 # begin with 0 to use octal notation
shared_buffers = 1024MB # min 128kB
maintenance_work_mem = 1024MB # min 1MB
max_stack_depth = 8MB # min 100kB
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
wal_level = hot_standby # minimal, archive, or hot_standby
synchronous_commit = off # synchronization level;
wal_sync_method = fdatasync # the default is the first option
wal_buffers = 16384kB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
archive_mode = on # allows archiving to be done
archive_command = '/bin/date' # command to use to archive a logfile segment
max_wal_senders = 32 # max number of walsender processes
wal_keep_segments = 128 # in logfile segments, 16MB each; 0 disables
hot_standby = on # "on" allows queries during recovery
wal_receiver_status_interval = 1s # send replies at least this often
hot_standby_feedback = off # send info from standby to prevent
random_page_cost = 2.0 # same scale as above
effective_cache_size = 8192MB
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600 # creation mode for log files,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
pg_stat_statements.max = 1000
pg_stat_statements.track = all
5. 启动新集群
uptest@db-172-16-3-33-> LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib /opt/pgsql9.3beta1/bin/pg_ctl start -D /pgdata2099
server starting
6. 安装老版本上一致的加载模块的so动态链接库. 本例 : pgfincore, postgis, pg_stat_statements(自带)
[root@db-172-16-3-33 /]# cd /opt/soft_bak/
[root@db-172-16-3-33 soft_bak]# tar -zxvf pgfincore-v1.1.1.tar.gz
[root@db-172-16-3-33 soft_bak]# mv pgfincore-1.1.1 postgresql-9.3beta1/contrib/
[root@db-172-16-3-33 soft_bak]# cd postgresql-9.3beta1/contrib/pgfincore-1.1.1/
[root@db-172-16-3-33 pgfincore-1.1.1]# export PATH=/opt/pgsql9.3beta1/bin:$PATH
[root@db-172-16-3-33 pgfincore-1.1.1]# which pg_config
/opt/pgsql9.3beta1/bin/pg_config
确保当前是postgresq9.3beta1的pg_config
[root@db-172-16-3-33 pgfincore-1.1.1]# gmake clean
[root@db-172-16-3-33 pgfincore-1.1.1]# gmake
[root@db-172-16-3-33 pgfincore-1.1.1]# gmake install
[root@db-172-16-3-33 soft_bak]# cd /opt/soft_bak/
[root@db-172-16-3-33 soft_bak]# mv postgis-2.0.3 postgis-2.0.3_for_9.0beta2
[root@db-172-16-3-33 soft_bak]# tar -zxvf postgis-2.0.3.tar.gz
[root@db-172-16-3-33 pgfincore-1.1.1]# export PATH=/opt/pgsql9.3beta1/bin:$PATH
[root@db-172-16-3-33 pgfincore-1.1.1]# which pg_config
/opt/pgsql9.3beta1/bin/pg_config
确保当前是postgresq9.3beta1的pg_config
[root@db-172-16-3-33 postgis-2.0.3]# cd /opt/soft_bak/postgis-2.0.3
注意修改pgsql路径,postgis路径.
./configure --prefix=/opt/postgis-2.0.3-for9.3 --with-pgconfig=/opt/pgsql9.3beta1/bin/pg_config --with-gdalconfig=/opt/gdal-1.10.0/bin/gdal-config --with-geosconfig=/opt/geos-3.3.8/bin/geos-config --with-xml2config=/usr/bin/xml2-config --with-projdir=/opt/proj-4.8.0 --with-libiconv=/usr/bin --with-jsondir=/opt/json-c-git20130520 --with-gui --with-raster --with-topology --with-gettext=no
gmake
gmake install
7. 调整新老版本数据库本地无密码认证
cd /pgdata1099
vi pg_hba.conf
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
cd /pgdata2099
vi pg_hba.conf
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
LD_LIBRARY_PATH=/opt/pgsql9.0beta2/lib /opt/pgsql9.0beta2/bin/pg_ctl restart -m fast -D /pgdata1099
LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib /opt/pgsql9.3beta1/bin/pg_ctl restart -m fast -D /pgdata2099
8. 停库
LD_LIBRARY_PATH=/opt/pgsql9.0beta2/lib /opt/pgsql9.0beta2/bin/pg_ctl stop -m fast -D /pgdata1099
LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib /opt/pgsql9.3beta1/bin/pg_ctl stop -m fast -D /pgdata2099
9. 运行pg_upgrade(注意必须运行新版的pg_upgrade)
9.1 检查, 但不执行更新. pg_upgrade需要在当前目录存放日志文件, 所以当前目录要有写权限
mkdir upgrade_test
cd upgrade_test
uptest@db-172-16-3-33-> LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib /opt/pgsql9.3beta1/bin/pg_upgrade -c -b /opt/pgsql9.0beta2/bin -B /opt/pgsql9.3beta1/bin -d /pgdata1099 -D /pgdata2099 -p 1099 -P 2099 -u postgres -v
报错 :
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
Failure, exiting
"/opt/pgsql9.3beta1/bin/pg_ctl" -w -D "/pgdata2099" -o "" -m fast stop >> "pg_upgrade_server.log" 2>&1
查看导致错误的模块详细信息 :
uptest@db-172-16-3-33-> less loadable_libraries.txt
Could not load library "$libdir/rtpostgis-2.0"
ERROR: could not load library "/opt/pgsql9.3beta1/lib/rtpostgis-2.0.so": libgdal.so.1: cannot open shared object file: No such file or directory
Could not load library "$libdir/postgis-2.0"
ERROR: could not load library "/opt/pgsql9.3beta1/lib/postgis-2.0.so": libgeos_c.so.1: cannot open shared object file: No such file or directory
指定LD_LIBRARY_PATH后, 报错变了:
LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib:/opt/postgis-2.0.3-for9.3/lib:/opt/gdal-1.10.0/lib:/opt/geos-3.3.8/lib:/opt/json-c-git20130520/lib:/opt/proj-4.8.0/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib /opt/pgsql9.3beta1/bin/pg_upgrade -c -b /opt/pgsql9.0beta2/bin -B /opt/pgsql9.3beta1/bin -d /pgdata1099 -D /pgdata2099 -p 1099 -P 2099 -u postgres -v
依旧报错 :
uptest@db-172-16-3-33-> less loadable_libraries.txt
Could not load library "$libdir/postgis-2.0"
ERROR: could not load library "/opt/pgsql9.3beta1/lib/postgis-2.0.so": /opt/pgsql9.3beta1/lib/postgis-2.0.so: undefined symbol: GETSTRUCT
发现这个函数GETSTRUCT在geometry_estimate.c中调用.由于9.3修改了对应的头文件, 所以重新包含以下.
vi /opt/soft_bak/postgis-2.0.3/postgis/geometry_estimate.c
#if POSTGIS_PGSQL_VERSION >= 93
#include "access/htup_details.h"
#endif
PostgreSQL 9.3的GETSTRUCT macro定义如下 :
src/include/access/htup_details.h
/*
* GETSTRUCT - given a HeapTuple pointer, return address of the user data
*/
#define GETSTRUCT(TUP) ((char *) ((TUP)->t_data) + (TUP)->t_data->t_hoff)
重新编译即可.
cd /opt/soft_bak/postgis-2.0.3
gmake
gmake install
重新执行
LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib:/opt/postgis-2.0.3-for9.3/lib:/opt/gdal-1.10.0/lib:/opt/geos-3.3.8/lib:/opt/json-c-git20130520/lib:/opt/proj-4.8.0/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib /opt/pgsql9.3beta1/bin/pg_upgrade -c -b /opt/pgsql9.0beta2/bin -B /opt/pgsql9.3beta1/bin -d /pgdata1099 -D /pgdata2099 -p 1099 -P 2099 -u postgres -v
通过.
*Clusters are compatible*
三. 升级
建议执行升级前, 先备份一下老版本的数据库, 表空间以及xlog. 便于回滚. 可以打开归档来备份, 减少停机时间.
升级把上面的-c参数去掉就可以了.
如果数据库巨大, 建议使用-k做硬链接, 但是$PGDATA必须在同一个文件系统中.
详见 :
下面使用硬链接进行升级. 同时9.3新增了并行的功能. -j参数控制并行度.
su - uptest
LD_LIBRARY_PATH=/opt/pgsql9.3beta1/lib:/opt/postgis-2.0.3-for9.3/lib:/opt/gdal-1.10.0/lib:/opt/geos-3.3.8/lib:/opt/json-c-git20130520/lib:/opt/proj-4.8.0/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib /opt/pgsql9.3beta1/bin/pg_upgrade -b /opt/pgsql9.0beta2/bin -B /opt/pgsql9.3beta1/bin -d /pgdata1099 -D /pgdata2099 -p 1099 -P 2099 -u postgres -v -k -j 16
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
delete_old_cluster.sh
使用硬链接, 实际上是拷贝了inode number. 所以新老$PGDATA里面的自定义对象对应的文件(除了initdb初始化集群文件以外的文件)对应的inode number一样.
新版本集群 :
cd /pgdata2099/base/12815
ll -i
3042182 -rw------- 2 uptest uptest 3.2M May 20 15:57 16637
3042187 -rw------- 2 uptest uptest 24K May 20 15:57 16637_fsm
3042183 -rw------- 2 uptest uptest 0 May 20 15:55 16641
3042184 -rw------- 2 uptest uptest 8.0K May 20 15:55 16643
3042185 -rw------- 2 uptest uptest 144K May 20 15:57 16644
3042188 -rw------- 2 uptest uptest 8.0K May 20 15:57 17513
3042189 -rw------- 2 uptest uptest 0 May 20 15:55 17515
3042190 -rw------- 2 uptest uptest 0 May 20 15:55 17520
3042191 -rw------- 2 uptest uptest 8.0K May 20 15:55 17522
3042192 -rw------- 2 uptest uptest 8.0K May 20 15:55 17523
3042193 -rw------- 2 uptest uptest 8.0K May 20 15:55 17525
3042194 -rw------- 2 uptest uptest 0 May 20 15:55 17528
3042195 -rw------- 2 uptest uptest 0 May 20 15:55 17532
3042196 -rw------- 2 uptest uptest 8.0K May 20 15:55 17534
3042197 -rw------- 2 uptest uptest 8.0K May 20 15:55 17535
3042198 -rw------- 2 uptest uptest 8.0K May 20 15:55 17537
3042199 -rw------- 2 uptest uptest 360K May 20 15:57 17915
3041504 -rw------- 2 uptest uptest 24K May 20 15:56 17915_fsm
3042200 -rw------- 2 uptest uptest 0 May 20 15:56 17918
3042201 -rw------- 2 uptest uptest 8.0K May 20 15:56 17920
3042202 -rw------- 2 uptest uptest 240K May 20 15:57 17921
老版本集群抽样 :
对应的inode number一致.
cd /pgdata1099/base/11874
uptest@db-172-16-3-33-> ll -i 17915_fsm
3041504 -rw------- 2 uptest uptest 24K May 20 15:56 17915_fsm
uptest@db-172-16-3-33-> stat 17915_fsm
File: `17915_fsm'
Size: 24576 Blocks: 48 IO Block: 4096 regular file
Device: 6801h/26625d Inode: 3041504 Links: 2
Access: (0600/-rw-------) Uid: ( 512/ uptest) Gid: ( 512/ uptest)
Access: 2013-05-20 15:56:06.000000000 +0800
Modify: 2013-05-20 15:56:06.000000000 +0800
Change: 2013-05-20 15:58:39.000000000 +0800
link数为2.
最后执行这两个脚本 :
因为pg_upgrade启动时的监听指定了当前目录, 所以要加两个变量.
uptest@db-172-16-3-33-> PGHOST=/home/uptest/upgrade_test PGPORT=2099 ./analyze_new_cluster.sh
uptest@db-172-16-3-33-> PGHOST=/home/uptest/upgrade_test PGPORT=2099 ./delete_old_cluster.sh
rm: cannot remove directory `/pgdata1099': Permission denied
无法删除因为放在根目录.
最后, 修改uptest用户的环境变量, 重启数据库.
vi /home/uptest/.bash_profile
# add by digoal
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=2099
export PGDATA=/pgdata2099
export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.3beta1
export LD_LIBRARY_PATH=/opt/gdal-1.10.0/lib:/opt/geos-3.3.8/lib:/opt/json-c-git20130520/lib:/opt/proj-4.8.0/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
alias rm='rm -i'
alias ll='ls -lh'
重启数据库
su - uptest
pg_ctl stop -m fast
pg_ctl start
四. 其他
1. 安装postgis for postgresql 9.0beta2时遇到问题 :
lwgeom_accum.c:316:66: error: macro "InitFunctionCallInfoData" passed 6 arguments, but takes just 5
lwgeom_accum.c: In function ‘PGISDirectFunctionCall1’:
lwgeom_accum.c:316: error: ‘InitFunctionCallInfoData’ undeclared (first use in this function)
lwgeom_accum.c:316: error: (Each undeclared identifier is reported only once
lwgeom_accum.c:316: error: for each function it appears in.)
gmake[1]: *** [lwgeom_accum.o] Error 1
gmake[1]: Leaving directory `/opt/soft_bak/postgis-2.0.3/postgis'
gmake: *** [all] Error 1
解决 :
vi configure
修改如下, 改成当前版本 :
#POSTGIS_PGSQL_VERSION="$PGSQL_MAJOR_VERSION$PGSQL_MINOR_VERSION"
POSTGIS_PGSQL_VERSION="90"
2. 调用不同版本的bin时, 最好加上LD_LIBRARY_PATH路径. 否则会有问题.
3. 升级到9.3后, 外部模块不是extension管理的.
4. 如果是没有数据的模块, 尽量在老版本中删除. 在升级完后, 新版本中再使用extension加载.
5. 一些注意事项参考 :
升级前一定要做好功课.
Notes
pg_upgrade does not support upgrading of databases containing these reg* OID-referencing system data types: regproc, regprocedure, regoper, regoperator, regconfig, and regdictionary. (regtype can be upgraded.)
All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically.
For deployment testing, create a schema-only copy of the old cluster, insert dummy data, and upgrade that.
If you are upgrading a pre-PostgreSQL 9.2 cluster that uses a configuration-file-only directory, you must pass the real data directory location to pg_upgrade, and pass the configuration directory location to the server, e.g. -d /real-data-directory -o '-D /configuration-directory'.
If using a pre-9.1 old server that is using a non-default Unix-domain socket directory or a default that differs from the default of the new cluster, set PGHOST to point to the old server's socket location. (This is not relevant on Windows.)
A Log-Shipping Standby Server (Section 25.2) cannot be upgraded because the server must allow writes. The simplest way is to upgrade the primary and use rsync to rebuild the standbys. You can run rsync while the primary is down, or as part of a base backup (Section 24.3.2) which overwrites the old standby cluster.
If you want to use link mode and you do not want your old cluster to be modified when the new cluster is started, make a copy of the old cluster and upgrade that in link mode. To make a valid copy of the old cluster, use rsync to create a dirty copy of the old cluster while the server is running, then shut down the old server and run rsync again to update the copy with any changes to make it consistent. You might want to exclude some files, e.g. postmaster.pid, as documented in Section 24.3.3.
Limitations in Upgrading from PostgreSQL 8.3
Upgrading from PostgreSQL 8.3 has additional restrictions not present when upgrading from later PostgreSQL releases. For example, pg_upgrade will not work for upgrading from 8.3 if a user column is defined as:
a tsquery data type
data type name and is not the first column
You must drop any such columns and upgrade them manually.
pg_upgrade will not work if the ltree contrib module is installed in a database.
pg_upgrade will require a table rebuild if:
a user column is of data type tsvector
pg_upgrade will require a reindex if:
an index is of type hash or GIN
an index uses bpchar_pattern_ops
Also, the default datetime storage format changed to integer after PostgreSQL 8.3. pg_upgrade will check that the datetime storage format used by the old and new clusters match. Make sure your new cluster is built with the configure flag --disable-integer-datetimes.
For Windows users, note that due to different integer datetimes settings used by the graphical installer and the MSI installer, it is only possible to upgrade from version 8.3 of the installer distribution to version 8.4 or later of the installer distribution. It is not possible to upgrade from the MSI installer to the new graphical installer.
参考
1. http://www.postgresql.org/docs/devel/static/pgupgrade.html
2. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a89c46f9bc314ed549245d888da09b8c5cace104
3. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6f1b9e4efd94fc644f5de5377829d42e48c3c758