pg集群配置 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%E9%85%8D%E7%BD%AE.html

最新推荐文章于 2025-01-02 12:35:13 发布

kc87654321

最新推荐文章于 2025-01-02 12:35:13 发布

阅读量3.7w

点赞数 1

文章标签：集群 c vim 数据库 authentication path

本文详细介绍如何在PL/Proxy、Node1及Node2节点上安装配置PostgreSQL集群，并完成数据库初始化、配置、安装扩展插件等步骤，最终实现跨节点数据库查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

== 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 ==

#在PL/Proxy、Node1、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 ==

#在PL/Proxy、Node1、Node2节点上执行以下命令：



## Add Unix User

sudo adduser postgres

sudo mkdir /usr/local/pgsql/data

sudo chown postgres /usr/local/pgsql/data

sudo visudo

**Add "DEVEL ALL=(postgres) 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



##检查数据库是否已经创建

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



#继续在PL/Proxy节点上执行以下命令(安装plproxy节点)：

sudo mkdir /home/y/pgsql/data

sudo chown postgres /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 ==

#在PL/Proxy、Node1、Node2节点上执行以下命令：



## 检查tcp连接端口是否已经配置，默认安装已经配置好

sudo vim /usr/local/pgsql/data/postgresql.conf

listen_addresses = '*'

port = 5433



## PgSQL是基于主机的认证(HBA："host-based authentication")

sudo vim /usr/local/pgsql/data/pg_hba.conf

## 根据需要添加IP地址、数据库和角色名。IP一般是前端机IP和本地IP，角色在上一步骤中已经添加

host    URT         postgres         10.62.0.1/16          trust



## 启动服务器

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



#在PL/Proxy、Node1、Node2节点上执行以下命令：

sudo vim /home/y/pgsql/data/postgresql.conf

listen_addresses = '*'

port = 5432



sudo vim /home/y/pgsql/data/pg_hba.conf

## 根据需要添加IP地址、数据库和角色名。IP一般是前端机IP和本地IP，角色在上一步骤中已经添加

host    URT         postgres         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



## 检查PL/Proxy节点是否可以访问Node1和Node2节点上的数据库,检查是否可以访问PL/Proxy节点

[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 ==

#在plproxy、Node1、Node2节点上安装plpgsql，包里面已经安装了。

#如果没有安装plpgsql，可以参考以下步骤：

sudo -u postgres /usr/local/pgsql/bin/createlang plpgsql URT -p 5433 



#在plproxy、Node1、Node2节点上安装plproxy，包里面已经安装了。

#如果没有安装plporxy，可以参考以下步骤：

#检查是否有/usr/local/pgsql/bin目录，如果没有，修改你的.bash_profile文件，添加/usr/local/pgsql/bin到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 ==

#创建一个plproxy schema，在plproxy里配置集群，注意要连接pgbouncer；放在一个sql脚本里;

sudo -u postgres /usr/local/pgsql/bin/psql -d URT  -p 5432 -f cluster.init.sql

== 7. Config DB Node ==

#在Node1、Node2节点上添加操作函数(这里只写了几个简单的函数,需要添加URT的业务逻辑函数)

sudo -u postgres /usr/local/pgsql/bin/psql -d URT -p 5433 -f node.sql

== 8. Config plproxy ==

#在plproxy节点上添加操作函数(这里只写了几个简单的函数,需要添加URT的业务逻辑函数)

sudo -u postgres /usr/local/pgsql/bin/psql -d URT -p 5432 -f plproxy.sql

== 9. Config pgbouncer ==

#在plproxy节点上执行以下操作



#在plproxy节点上安装pgbouncer，包里面已经安装了。

#如果没有安装pgbouncer，可以参考以下步骤：

./configure --prefix=/usr/local --with-libevent=/prefix

make

sudo make install



#修改pgbouncer.ini文件

sudo vim /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.ini

#添加一下内容

#######################

Node1 = host=10.62.245.152 port=5433 user=postgres dbname=URT

Node2 = host=10.62.245.136 port=5433 user=postgres 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/pgbouncer.log

pidfile = /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.pid

admin_users = user2, someadmin, otheradmin,postgres

stats_users = stats, root,postgres

#########################



#创建users.txt文件

sudo vim /usr/local/pgsql/share/doc/pgbouncer/users.txt

#添加 "postgres" ""



#start pgbouncer

sudo /usr/local/pgsql/bin/pgbouncer -d /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.ini



#测试

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. 导入ltree测试数据 ==

#在plproxy、Node1、Node2节点上安装ltree类型，包里面已经安装了。

#如果没有,可以参考一下步骤

#先安装 postgresql-8.3.0, 安装目录会保存在配置文件里。然后ltree模块：

cd postgresql-8.3.0/contrib/ltree/

make

sudo make install

#安装ltree类型

sudo -u postgres /home/y/pgsql/bin/psql -d URTCluster -f /home/y/pgsql/sha

re/contrib/ltree.sql

#重新载入配置

sudo -u postgres /home/y/pgsql/bin/pg_ctl -D /home/y/pgsql/data reload



#在Node1、Node2节点上执行数据导入操作

wget http://www.sai.msu.su/~megera/postgres/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. 测试 ==

#在plproxy节点上执行

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);