PG cluster configuration http://keyvalue.net/linux%E5%B9%B3%E5%8F%B0%E6%8A%80%E6%9C%AF/%E8%BD%ACpg%E9%9B%86%E7%BE%A4%E

= 1. Machine =

PL/Proxy
hostname:h08-vm08.corp.cnb.yahoo.com
inet addr:10.62.245.152
Bcast:10.62.245.255
Mask:255.255.254.0
OS: Linux  2.6.9-42.ELsmp
CPU:Intel(R) Xeon(R) CPU  L5320  @ 1.86GHz
MemTotal: 254772 kB
Pg_Dir: /home/y/pgsql/data/
port = 5432

Node1:
hostname:h08-vm08.corp.cnb.yahoo.com
inet addr:10.62.245.152
Bcast:10.62.245.255
Mask:255.255.254.0
OS: Linux  2.6.9-42.ELsmp
CPU:Intel(R) Xeon(R) CPU  L5320  @ 1.86GHz
MemTotal: 254772 kB
Pg_Dir: /usr/local/pgsql/data/
port = 5433

Node2:
hostname:h07-vm08.corp.cnb.yahoo.com
inet addr:10.62.245.136
Bcast:10.62.245.255
Mask:255.255.254.0
OS: Linux  2.6.9-42.ELsmp
CPU:Intel(R) Xeon(R) CPU  L5320  @ 1.86GHz
MemTotal: 514440 kB
Pg_Dir: /usr/local/pgsql/data/
port = 5433

= 2. Install =

# Run the following commands on PL/proxy, node1, and node2:

Sudo rpm-IVH postgres_4e_alone-8.3.1.0.rpm
Sudo rpm-IVH postgres_4e_contrib-8.3.0.1.rpm
Sudo rpm-IVH pgbouncer_4e-1.0.0.0.rpm

= 3. init =

# Run the following commands on PL/proxy, node1, and node2:

# Add UNIX user
Sudo adduser Postgres
Sudo mkdir/usr/local/pgsql/Data
Sudo chown Postgres/usr/local/pgsql/Data
Sudo shortdo
** Add "devel all = (S) All" in the last line
** Apend "hebing" to "user_alias devel"

# Init dB and start service
Sudo-u Postgres/usr/local/pgsql/bin/initdb-D/usr/local/pgsql/Data
Sudo-u Postgres/usr/local/pgsql/bin/Postgres-D/usr/local/pgsql/data> logfile 2> & 1 &

# Create dB and use local connection
Sudo-u Postgres/usr/local/pgsql/bin/createdb URT

# Check whether the database has been created
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D URT
Sudo-u Postgres/usr/local/pgsql/bin/pg_ctl-D/usr/local/pgsql/Data stop

# Continue to execute the following command on the PL/Proxy node (install the plproxy node ):
Sudo mkdir/home/y/pgsql/Data
Sudo chown S/home/y/pgsql/Data
Sudo-u Postgres/usr/local/pgsql/bin/initdb-D/home/y/pgsql/Data
Sudo-u Postgres/usr/local/pgsql/bin/Postgres-D/home/y/pgsql/data> logfile_1 2> & 1 &
Sudo-u Postgres/usr/local/pgsql/bin/createdb URT
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D URT
Sudo-u Postgres/usr/local/pgsql/bin/pg_ctl-D/home/y/pgsql/Data stop

= 4. Config =

# Run the following commands on PL/proxy, node1, and node2:

# Check whether the TCP connection port is configured. The default installation is configured.
Sudo Vim/usr/local/pgsql/data/PostgreSQL. conf
Listen_addresses = '*'
Port = 5433

# Pgsql is host-based authentication (HbA: "host-based authentication ")
Sudo Vim/usr/local/pgsql/data/pg_hba.conf
# Add IP addresses, databases, and role names as needed. The IP address is generally the frontend IP address and the local IP address. The role has been added in the previous step.
Host URT ipvs 10.62.0.1/16 Trust

# Start the server
Sudo-u Postgres/usr/local/pgsql/bin/Postgres-D/usr/local/pgsql/data> logfile 2> & 1 &
Sudo-u Postgres/usr/local/pgsql/bin/pg_ctl-D/usr/local/pgsql/Data reload

# Run the following commands on PL/proxy, node1, and node2:
Sudo Vim/home/y/pgsql/data/PostgreSQL. conf
Listen_addresses = '*'
Port = 5432

Sudo Vim/home/y/pgsql/data/pg_assist.conf
# Add IP addresses, databases, and role names as needed. The IP address is generally the frontend IP address and the local IP address. The role has been added in the previous step.
Host URT ipvs 10.62.0.1/16 Trust

Sudo-u Postgres/usr/local/pgsql/bin/Postgres-D/home/y/pgsql/data> logfile_1 2> & 1 &
Sudo-u Postgres/usr/local/pgsql/bin/pg_ctl-D/home/y/pgsql/Data reload

# Check whether the PL/Proxy node can access the databases on node1 and node2, and whether the PL/Proxy node can be accessed
[Hebing @ h08-vm08 ~] $ Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-H h08-vm08.corp.cnb.yahoo.com-P 5433
[Hebing @ h08-vm08 ~] $ Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-H h07-vm08.corp.cnb.yahoo.com-P 5433
[Hebing @ h08-vm08 ~] $ Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-H h08-vm08.corp.cnb.yahoo.com-P 5432

= 5. Install plpgsql and plproxy =

# Install plpgsql on plproxy, node1, and node2. the package is already installed.
# If plpgsql is not installed, follow these steps:
Sudo-u Postgres/usr/local/pgsql/bin/createlang plpgsql urt-P 5433

# Install plproxy on the plproxy, node1, and node2. the package is already installed.
# If plporxy is not installed, follow these steps:
# Check whether the/usr/local/pgsql/bin directory exists. If not, modify your. bash_profile file and add/usr/local/pgsql/bin to path.
Echo $ path

Gunzip plproxy-2.0.4.tar.gz
Tar xf plproxy-2.0.4.tar
CD plproxy-2.0.4
Gmake
Sudo gmake install
Sudo-u Postgres/usr/local/pgsql/bin/Psql-F/usr/local/pgsql/share/contrib/plproxy. SQL URT

= 6. config cluster in plproxy =

# Create a plproxy schema and configure the cluster in plproxy. Be sure to connect to pgbouncer and put it in an SQL script;
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-P 5432-F cluster. init. SQL

= 7. config dB node =

# Add operation functions on node1 and node2 nodes (only a few simple functions are written here, And URT business logic functions need to be added)
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-P 5433-F node. SQL

= 8. config plproxy =

# Add operation functions on the plproxy node (only a few simple functions are written here, And the URT business logic functions need to be added)
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-P 5432-F plproxy. SQL

= 9. config pgbouncer =

# Perform the following operations on the plproxy Node

# Install pgbouncer on the plproxy node, which is already installed in the package.
# If pgbouncer is not installed, follow these steps:
./Configure -- prefix =/usr/local -- With-libevent =/prefix
Make
Sudo make install

# Modify the pgbouncer. ini file
Sudo Vim/usr/local/pgsql/share/doc/pgbouncer. ini
# Add content
#######################
Node1 = host = 10.62.245.152 Port = 5433 user = s dbname = URT
Node2 = host = 10.62.245.136 Port = 5433 user = s dbname = URT
Listen_addr = 127.0.0.1
Listen_port= 6543
Auth_file =/usr/local/pgsql/share/doc/pgbouncer/users.txt
Logfile =/usr/local/pgsql/share/doc/pgbouncer. Log
Pidfile =/usr/local/pgsql/share/doc/pgbouncer. PID
Admin_users = user2, someadmin, otheradmin, Postgres
Stats_users = stats, root, Postgres
#########################

Create the users.txt File
Sudo Vim/usr/local/pgsql/share/doc/pgbouncer/users.txt
# Add "s """

# Start pgbouncer
Sudo/usr/local/pgsql/bin/pgbouncer-D/usr/local/pgsql/share/doc/pgbouncer. ini

# Test
Sudo-u Postgres/usr/local/pgsql/bin/Psql-P 6543-D pgbouncer-u Postgres
Pgbouncer = # Show databases;

Sudo-u Postgres/usr/local/pgsql/bin/Psql-H 127.0.0.1-P 6543-D node1-u Postgres
Sudo-u Postgres/usr/local/pgsql/bin/Psql-H 127.0.0.1-P 6543-D node2-u Postgres

= 10. Import ltree test data =

# Install the ltree type on plproxy, node1, and node2. the package is already installed.
# If not, refer to the following steps.
# First install the postgresql-8.3.0, the installation directory will be saved in the configuration file. Then the ltree module:
CD postgresql-8.3.0/contrib/ltree/
Make
Sudo make install
# Install the ltree type
Sudo-u Postgres/home/y/pgsql/bin/Psql-D urtcluster-f/home/y/pgsql/Sha
Re/contrib/ltree. SQL
# Re-load configuration
Sudo-u Postgres/home/y/pgsql/bin/pg_ctl-D/home/y/pgsql/Data reload

# Import data on node1 and node2
Wget http://www.sai.msu.su /~ Megera/ipvs/gist/ltree/dmozltree-eng. SQL .gz
Gunzip dmozltree-eng. SQL .gz
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-F dmozltree-eng. SQL-P 5433

= 11. test =

# Execute on the plproxy Node
Sudo-u Postgres/usr/local/pgsql/bin/Psql-D urt-P 5432

URT = # select * from public. XQuery ('select * From dmoz limit 1 offset 10; ') as dmoz (ID int4, Name text, path ltree );

URT = # select * from public. XQuery ('select path from dmoz where path ~ ''Top. Adult. arts. animation. * {1} ''; ') as dmoz (ID int4, Name text, path ltree );