PostgreSQL standby in 64bit to 32bit or reverse enviroment

64和32位系统上安装的PostgreSQL能不能做流复制搭建standby.

今天问同事借了一台32位的机器测试一下.

测试环境 : 

CentOS 5.x 32bit 和64bit 各一台.

PostgreSQL 9.1.4源码

32bit 作为primary

64bit 作为standby

结果是可行的, 只是需要在64bit上使用32bit的pgsql程序.

而反过来则不行(64bit primary , 32bit standby).

说白了, 程序要兼容. 在这种环境中建standby, 程序必须都是32bit的. 因为OS 64bit可以跑32bit的程序, 而OS 32bit 不能跑64bit的程序.

1. 配置OS

yum install -y lrzsz
yum install -y sysstat
yum install -y e4fsprogs
yum install -y ntp
yum install -y readline-devel
yum install -y zlib
yum install -y zlib-devel
yum install -y openssl
yum install -y openssl-devel
yum install -y pam-devel
yum install -y libxml2-devel
yum install -y libxslt-devel

/usr/sbin/ntpdate asia.pool.ntp.org && /sbin/hwclock --systohc

-- 临时关闭防火墙, 生产中则配置一下, 开放互访监听端口
service iptables stop

vi /etc/sysctl.conf
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
vm.overcommit_memory = 0
net.ipv4.ip_conntrack_max = 655360
fs.aio-max-nr = 1048576
net.ipv4.tcp_timestamps = 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

vi /etc/sysconfig/selinux
SELINUX=disabled

cd /data03/soft_bak
tar -jxvf flex-2.5.35.tar.bz2
cd flex-2.5.35
./configure && make && make install

32位机器上

useradd platform32
vi /home/platform32/.bash_profile
export PGPORT=1616
export PGDATA=/home/platform32/pg_root
export LANG=en_US.utf8
export PGHOME=/home/platform32/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'

64位机器上

useradd platform64
vi /home/platform64/.bash_profile
export PGPORT=1616
export PGDATA=/home/platform64/pg_root
export LANG=en_US.utf8
export PGHOME=/home/platform64/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'

2. 编译安装PostgreSQL 9.1.4

64位机器
./configure --prefix=/home/platform64/pgsql --with-pgport=1616 --with-perl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 && gmake world
sudo gmake install-world

32位机器
./configure --prefix=/home/platform32/pgsql --with-pgport=1616 --with-perl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 && gmake world
sudo gmake install-world

3. 在32bit 机器上初始化数据库

initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W
vi pg_hba.conf
host    replication     replica        192.168.101.35/32                 md5
host all all 0.0.0.0/0 md5

vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 1616
max_connections = 100
shared_buffers = 1024MB
maintenance_work_mem = 512MB
max_stack_depth = 8MB
wal_level = hot_standby
checkpoint_segments = 32
max_wal_senders = 32
wal_sender_delay = 10ms
wal_keep_segments = 64
hot_standby = on

pg_ctl start
psql -h 127.0.0.1 postgres postgres
create role replica nosuperuser nocreatedb nocreaterole noinherit login replication connection limit 32 encrypted password 'REPLICA';

vi /home/platform32/.pgpass
192.168.101.66:1616:replication:replica:REPLICA
192.168.101.35:1616:replication:replica:REPLICA
chmod 400 /home/platform32/.pgpass

4. 配置standby

vi /home/platform64/.pgpass
192.168.101.66:1616:replication:replica:REPLICA
192.168.101.35:1616:replication:replica:REPLICA
chmod 400 /home/platform64/.pgpass

vi $PGDATA/recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.101.66 port=1616 user=replica keepalives_idle=60'
trigger_file = '/home/platform64/pg_root/.1616.PG.trigger'

5. 初始化standby

pg_basebackup -D $PGDATA -F p -x -l base -P -v -h 192.168.101.66 -p 1616 -U replica

6. 启动standby

pg_ctl start
报错
FATAL:  incorrect checksum in control file

7. 修改pg_controlfile的checksum源码, 不check.

vi src/backend/access/transam/xlog.c
注释checksum部分
        /* if (!EQ_CRC32(crc, ControlFile->crc))
                ereport(FATAL,
                                (errmsg("incorrect checksum in control file")));
        */

8. 重新编译standby端的pgsql

略

9. 重新启动standby, 报另一个错误

FATAL:  database files are incompatible with server
DETAIL:  The database cluster was initialized with MAXALIGN 1093850759, but the server was compiled with MAXALIGN 8.
HINT:  It looks like you need to initdb.

在控制文件差别 : 

32 位机器的控制文件信息 :
Maximum data alignment:               4
Maximum size of a TOAST chunk:        2000

64 位机器(initdb 后)的控制文件信息 :
Maximum data alignment:               8
Maximum size of a TOAST chunk:        1996

在src/backend/access/transam/xlog.c这里面有很多的check, 不一一列出.
要在64 bit的机器上使用到底应该怎么办呢?
其实和程序有关系, 只要把32bit 上编译的pgsql程序拷贝过来就可以了

10. 在64bit的机器上使用32bit的pgsql做standby .

scp -r root@32bit:/home/platform32/pgsql /home/platform64/
pg_ctl start

在primary上插入测试数据 :

[platform32@192_168_101_66 pg_root]$ psql postgres postgres -h 127.0.0.1 psql (9.1.4) Type "help" for help.  postgres=# \dt         List of relations  Schema | Name | Type  |  Owner    --------+------+-------+----------  public | t    | table | postgres (1 row)  postgres=# create table test (id int); CREATE TABLE postgres=# insert into test select generate_series (1,10000); INSERT 0 10000

在standby上验证 :

[platform64@db-192-168-101-35 ~]$ psql -h 127.0.0.1 postgres postgres
psql (9.1.4)
Type "help" for help.

postgres=# select count(*) from test;
 count
-------
 10000
(1 row)

11. 那么反过来行不行呢? 在32bit的机器上允许64bit的程序?

显然是不行的

[platform32@192_168_101_66 ~]$ pg_ctl --help -bash: /home/platform32/pgsql/bin/pg_ctl: cannot execute binary file
[platform32@192_168_101_66 ~]$ postgres --help -bash: /home/platform32/pgsql/bin/postgres: cannot execute binary file