PostgreSQL Multi-Master 雙向複寫

安裝 PostgreSQL BDR 版本

What's BDR?
http://2ndquadrant.com/en-us/resources/bdr/

Yum Install

1. yum install http://packages.2ndquadrant.com/postgresql-bdr94-2ndquadrant/yum-repo-rpms/postgresql-bdr94-2ndquadrant-redhat-1.0-2.noarch.rpm
2. 1. 1. yum install postgresql-bdr94-bdr
      3. 
         

設定PostgreSQL lib

     vi /etc/profile  增加下方資訊

export PATH=/usr/pgsql-9.4/bin:$PATH

Init DB

1. service postgresql-9.4 initdb -E UTF8  --no-locale --locale=C LC_COLLATE='C' LC_CTYPE='C' -A trust -U postgres
2. cd /var/lib/pgsql/9.4-bdr/
3. vi data/pg_hba.conf
4. vi data/postgresql.conf
5. service postgresql-9.4 start
6. chkconfig postgresql-9.4 on

Create DB

• CREATE ROLE message LOGIN ENCRYPTED PASSWORD 'yourpassword md5' VALID UNTIL 'infinity';
• CREATE DATABASE message WITH ENCODING='UTF8' OWNER=message TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C' CONNECTION LIMIT=-1;

message DB, BDR Setting

##first node

    CREATE EXTENSION btree_gist;

    CREATE EXTENSION bdr;

    SELECT bdr.bdr_group_create( 

        local_node_name := 'centos1', 

        node_external_dsn := 'host=centos1 dbname=message' 

    );

##second node

    CREATE EXTENSION btree_gist;

    CREATE EXTENSION bdr;

    SELECT bdr.bdr_group_join( 

        local_node_name := 'centos2', 

        node_external_dsn := 'host=centos2 dbname=message', 

        join_using_dsn := 'host=centos1 dbname=message' 

    );

更多設定可參考quictstart

http://bdr-project.org/docs/0.9.0/quickstart.html

PostgreSQL 9.3 HA Script

PostgreSQL 9.3 已經內建 Hot Standby 的機制

這時只要在加上 Linux Virtual Host, 就能達到 High Availability DB

寫了一個 shell script 在 git 上
https://github.com/yulinliu/postgres_ha

在加上網路、DB 的基本設定就可正常運作

fedora10 安裝PostgreSQL8.3.7

建立群組與使用者

     groupadd postgres
     useradd -g postgres postgres

安裝RPM source安裝方式可參考這裡

     rpm --import http://yum.pgsqlrpms.org/RPM-GPG-KEY-PGDG

     rpm -ivh postgresql-libs-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-debuginfo-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-docs-8.3.7-1PGDG.fc10.i386.rpm

     rpm -ivh postgresql-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-devel-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-server-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-plperl-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-plpython-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh postgresql-test-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh uuid-1.6.1-3.fc9.i386.rpm
     rpm -ivh uuid-pgsql-1.6.1-3.fc9.i386.rpm
     rpm -ivh postgresql-contrib-8.3.7-1PGDG.fc10.i386.rpm
     rpm -ivh tcl-8.5.3-1.fc10.i386.rpm
     rpm -ivh postgresql-pltcl-8.3.7-1PGDG.fc10.i386.rpm

---------------------------------------------------------------------------
初始化DB

     service postgresql initdb -D /var/lib/pgsql/data -E UTF-8 --locale=zh_TW

修改設定檔

     cd /var/lib/pgsql/data

     vi postgresql.conf

          listen_addresses = '*'
           port=5432

     vi pg_hba.conf

          host all all 192.168.0.0/16 trust

修改iptables，開放5432 port連線
     vi /etc/sysconfig/iptables

           -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
     service iptables restart

     service postgresql start
     ps auxww | grep ^postgres


設定開機時啟動

     chkconfig postgresql on

Solaris10 安裝PostgreSQL 8.3.7

1.下載postgresql-8.3.7-S10.i386-32.tar.bz2

2.到postgres的目錄解壓縮檔案
     cd /usr/postgres
     bunzip2 < postgresql-8.3.7-S10.i386-32.tar.bz2 | tar xpf -

3.修改/etc/profile，增加設定
     PATH=/usr/postgres/8.3-community/bin:$PATH
     MANPATH=/usr/postgres/8.3-community/man:${MANPATH}
     PGDATA=/var/postgres/8.3/data
     export PATH MANPATH PGDATA

4.修改postgresql_83.xml內容，更新至最新版路徑
     檔案路徑/var/svc/manifest/application/database/postgresql_83.xml

5.匯入設定
     svccfg import /var/svc/manifest/application/database/postgresql_83.xml
     svcs postgresql_83

6.初始化DB
     su - postgres
     /usr/postgres/8.3-community/bin/initdb -D /var/postgres/8.3/data -E UTF-8 --locale=zh_TW

7.啟動DB
     /usr/sbin/svcadm enable postgresql_83:default_32bit