什么是一个好办法,在PostgreSQL的水平片段

发布网友发布时间：2022-04-26 21:14

共2个回答

懂视网时间：2022-04-08 01:49

PostgreSQL+pgpooll+Keepalived双机HA方案

(注:本文档中的所有操作在没有说明的情况下都应该使用postgres用户进行操作，postgres用户没有权限的操作方可采用root用户进行操作，可以在本方案中使用postgres用户及该用户的密码替换replcia及replica用户的密码)

技术分享

方案逻辑图

技术分享

192.168.100.3的pgpool故障图

技术分享

192.168.100.4的pgpool故障图

技术分享

192.168.100.3的Master故障图

一、环境信息

操作系统：CentOS6.6

数据库：PostgreSQL9.3.5

物理机两台：node1,node2

Keepalived:keepalived-1.2.15

pgpool:pgpool-II-3.3.3

一、安装Linux操作系统CentOS6.6(两台物理机操作)

主节点：node1:IP:192.168.100.3

备节点：node2:IP:192.168.2.4

VIP：192.168.100.4

二、安装完Linux操作系统之后的系统调整(两台物理机操作)

2.1主备节点时钟同步:

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

2.2给两个机器分别命名：node1,node2

第一台机器命名为node1

2.2.1 修改/etc/sysconfig/network中的hostname
vi /etc/sysconfig/network
HOSTNAME=node1 #修改localhost.localdomain为node1

2.2.2 修改/etc/hosts文件
vi /etc/hosts
127.0.0.1 node1 localhost #修改localhost.localdomain为node1
shutdown -r now #最后，重启服务器即可

第二台机器命名为node2

2.2.3 修改/etc/sysconfig/network中的hostname
vi /etc/sysconfig/network
HOSTNAME=node2 #修改localhost.localdomain为node2

2.2.4 修改/etc/hosts文件
vi /etc/hosts
127.0.0.1 node2 localhost #修改localhost.localdomain为node2
shutdown -r now #最后，重启服务器即可。

2.3.OS资源调整

时区调整: (如果已经调好同步不需要做)

vi /etc/sysconfig/clock

ZONE="Asia/Shanghai"

UTC=false

ARC=false

vi /etc/sysconfig/i18n

LANG="en_US.UTF-8"

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

setenforce 0

防火墙根据需要配置

vi /etc/sysconfig/iptables

-A INPUT -s 192.168.0.0/16 -j ACCEPT

-A INPUT -s 10.0.0.0/8 -j ACCEPT

-A INPUT -s 172.16.0.0/16 -j ACCEPT

# or

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

service iptables restart

2.4.给两个节点分别创建postgres用户并设置密码

useradd postgres

password：postgres

2.5.配置两台机器的ssh免密钥登录

[postgres@node1]$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.

Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.

The key fingerprint is:

[postgres@node1]$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

[postgres@node1]$ chmod go-rwx ~/.ssh/*

[postgres@node2$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.

Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.

The key fingerprint is:

[postgres@node2$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

[postgres@node2$ chmod go-rwx ~/.ssh/*

[postgres@node1]$ cd ~/.ssh

[postgres@node1]$ scp id_rsa.pub postgres@node2:/home/postgres/.ssh/id_rsa.pub1

[postgres@node2] cd ~/.ssh

[postgres@node2] cat id_rsa.pub1 | authorized_keys

[postgres@node2]scp id_rsa.pub postgres@node1:/home/postgres/.ssh/id_rsa.pub2

[postgres@node1] cat id_rsa.pub2| authorized_keys

2.6.创建源码包存储目录

在两个节点的/opt/目录下分别创建soft_bak目录用于存放软件安装包如：postgresql-9.3.5.tar.gz

mkdir /opt/soft_bak

设置相关目录的所属主

postgresql必须用postgres 用户安装，安装之前先改变postgresql的将要安装的目录的权限

chown –R postgres:postgres /opt/soft_bak

chown –R postgres:postgres /usr/local/

三、安装PG的依赖软件包：

yum -y install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make flex bison

四、下载PostgreSQL 9.3.5 pgpool-II-3.3.3.tar.gz keepalived-1.2.15

在安装PG之前

/opt/soft_bak文件夹下应该有postgresql-9.3.5.tar.gz pgpool-II-3.4.0.tar.gz 等

五、安装PostgreSQL

主库(postgres用户安装)

cd /opt/soft_bak/

tar zxvf postgresql-9.3.5.tar.gz

cd /opt/soft_bak/postgresql-9.3.5

./configure --with-pgport=5432 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --with-blocksize=8

gmake world

gmake install-world

备库(postgres用户安装备节点只需要安装软件，不需要init数据库)

cd /opt/soft_bak/

tar zxvf postgresql-9.3.5.tar.gz

cd /opt/soft_bak/postgresql-9.3.5

./configure --with-pgport=5432 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --with-blocksize=8

gmake world

gmake install-world

六、主备节点设置相关环境变量(主备节点应该一致)

vi /home/postgres/.bash_profile

export PGPORT=5432

export PGDATA=/file/data

export LANG=en_US.utf8

export PGHOME=/usr/local/pgsql

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

export PGDATABASE=postgres

alias rm=‘rm -i‘

alias ll=‘ls -lh‘

在/home/postgres/目录下创建.pgpass(用于主备之间数据传输的流复制用户)

格式：

IP：端口：replication:用户：密码

(master)(在不创建replica用户的情况下可以使用postgres用户和postgres用户的密码)

192.168.100.3:5432:replication:replica:replica

192.168.2.4:5432:replication:replica:replica

(standby) (在不创建replica用户的情况下可以使用postgres用户和postgres用户的密码)

192.168.100.3:5432:replication:replica:replica

192.168.2.4:5432:replication:replica:replica

七、主节点初始化postgreSQL实例

su - postgres

initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W

八、PostgreSQL配置文件调整

cd $PGDATA

vi postgresql.conf

listen_addresses = ‘0.0.0.0‘

max_connections = 1000

superuser_reserved_connections = 13

unix_socket_directory = ‘$PGDATA‘

unix_socket_permissions = 0700

tcp_keepalives_idle = 60

tcp_keepalives_interval = 10

tcp_keepalives_count = 10

shared_buffers = 2GB

maintenance_work_mem = 512MB

max_stack_depth = 8MB

vacuum_cost_delay = 10ms

vacuum_cost_limit = 10000

bgwriter_delay = 10ms

wal_level = hot_standby

wal_buffers = 16384kB
checkpoint_segments = 64

checkpoint_timeout = 5min

archive_mode = on

archive_command = ‘/bin/date‘

max_wal_senders = 32

hot_standby = on

random_page_cost = 2.0

effective_cache_size = 12000MB

log_destination = ‘csvlog‘

logging_collector = on

log_directory = ‘pg_log‘

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 10MB

log_min_duration_statement = 1s

log_checkpoints = on

log_lock_waits = on

deadlock_timeout = 1s

log_connections = on

log_disconnections = on

log_error_verbosity = verbose

log_statement = ‘ddl‘

track_activity_query_size = 2048

autovacuum = on

log_autovacuum_min_duration = 0

log_timezone = ‘PRC‘

datestyle = ‘iso, mdy‘

timezone = ‘PRC‘

lc_messages = ‘C‘

lc_monetary = ‘C‘

lc_numeric = ‘C‘

lc_time = ‘C‘

default_text_search_config = ‘pg_catalog.english‘

shared_preload_libraries = ‘pg_stat_statements,auto_explain‘

pg_stat_statements.max = 1000

pg_stat_statements.track = all

pg_stat_statements.track_utility = off

track_io_timing = off

custom_variable_class = ‘pg_stat_statements,auto_explain’

pg_stat_statements.max = 1000

pg_stat_statements.track = all

auto_explain.log_min_duration = 10ms

auto_explain.log_analyze = false

auto_explain.log_verbose = false

auto_explain.log_nested_statements = false

九、使系统具有监控SQL功能

su - postgres

pg_clt –D $PGDATA start

psql –h node1 –p 5432 –U postgres –d postgres

postgres=#create extention pg_stat_statements;

postgres=#select pg_stat_statements_reset();

//select * from pg_stat_statements;

十、主备方案postgresql.conf pg_hba.conf的配置

vim postgres.conf

listen_addresses=‘*‘

wal_level = ‘hot_standby‘

archive_mode = on

archive_command = ‘cd ./‘ # we can also use exit 0

max_wal_senders = 10

wal_keep_segments = 5000 # 80 GB required on pg_xlog

hot_standby = on

vim pg_hba.conf

host all all 192.168.2.4/32 trust

host postgres postgres 192.168.2.4/32 trust

host replication replica 192.168.2.4/32 trust

host all all 192.168.100.3/32 trust

host postgres postgres 192.168.100.3/32 trust

host replica replica 192.168.100.3/32 trust

十一主备节点标志文件的配置

su – postgres

cp /usr/local/pgsql/share/recovery.conf.sample /file/data/recovery.done

standby_mode = on

recovery_target_timeline =’latest’时间线

primary_conninfo = ‘host=node2 port=5432 user=postgres’主节点信息

trigger_file = ‘/file/data/trigger_file’

十二、生成备库实例(node2上执行)

su – postgres

pg_basebackup –D $PGDATA –Fp –Xs –v –P –h node1 –p主库端口号 –U replica(postgres)

十三、修改备库实例的备库标识文件

cd /file/data

mv recovery.done recovery.conf

vim recovery.conf

standby_mode = on

recovery_target_timeline =’latest’时间线

primary_conninfo = ‘host=node1 port=5432 user=postgres’主节点信息

trigger_file = ‘/file/data/trigger_file’

启动备库

chmod 0700 /file/data

pg_ctl –D $PGDATA

查看备库进程

ps –ef|grep postgres

查看主库进程

ps –ef|grep postgres

十四、安装pgpool-II3.3.3(两个节点操作)

将pgpool-II3.3.3下载到/opt/soft_bak

创建pgpool的安装目录

mkdir /opt/pgpool

su - postgres

tar zxvf pgpool-II-3.3.3.tar.gz

cd pgpool-II-3.3.3

mkdir -p /opt/pgpool

./configure --prefix=/opt/pgpool --with-pgsql=path --with-pgsql=/usr/local/pgsql

make

make install

十五、安装 pgpool 相关函数

su – postgres

cd pgpool-II-3.3.3/sql

make

make install

cd pgpool-recovery

make install

cd pgpool-regclass

make install

echo “export PATH=” $PATH:/opt/pgpool/bin” " >>/home/postgres/.bashrc

source /home/postgres/.bashrc

安装完以后/usr/local/pgsql/share/extension/目录下应该有如下文件

pgpool_recovery--1.0.sql
pgpool_recovery.control
pgpool-recovery.sql
pgpool_regclass--1.0.sql
pgpool_regclass.control
pgpool-regclass.sql

su - postgres

psql template1 (备节点不需要创建pgpool_regclass,pgpool_recovery)

template1=# create extension pgpool_regclass;
CREATE EXTENSION
template1=# CREATE EXTENSION pgpool_recovery;
CREATE EXTENSION

十六、配置 pgpool-II ( 两节点操作)
--配置 pcp.conf

cd /opt/pgpool/etc
cp pcp.conf.sample pcp.conf
pg_md5 -u postgres -p
password: postgres
ba777e4c2f15c11ea8ac3be7e0440aa0

vim pcp.conf

--编写 pcp.conf 文件，写入以下

# USERID:MD5PASSWD
postgres:ba777e4c2f15c11ea8ac3be7e0440aa0

--配置 ifconfig, arping 执行权限(由root用户执行)

# chmod u+s /sbin/ifconfig
# chmod u+s /usr/sbin/

--配置 pgpool.conf

cd /opt/pgpool/etc
cp pgpool.conf.sample pgpool.conf

--主节点的 pgpool.conf(配置文件中的用户名和配置能用postgres用户就用postgres用户，尽量减少用户)
listen_addresses = ‘*‘
port = 9999
socket_dir = ‘/opt/pgpool‘
pcp_port = 9898
pcp_socket_dir = ‘/opt/pgpool‘
backend_hostname0 = ‘node1‘   ##配置数据节点 node1
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = ‘ALLOW_TO_FAILOVER‘
backend_hostname1 = ‘node2‘   ##配置数据节点 node2
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = ‘ALLOW_TO_FAILOVER‘
enable_pool_hba = on
pool_passwd = ‘pool_passwd‘
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = ‘syslog‘
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = off
log_standby_delay = ‘none‘
syslog_facility = ‘LOCAL0‘
syslog_ident = ‘pgpool‘
debug_level = 0
pid_file_name = ‘/opt/pgpool/pgpool.pid‘
logdir = ‘/tmp‘
connection_cache = on
reset_query_list = ‘ABORT; DISCARD ALL‘
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ‘‘
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ‘‘
black_function_list = ‘nextval,setval‘
master_slave_mode = on # 设置流复制模式
master_slave_sub_mode = ‘stream‘ # 设置流复制模式
sr_check_period = 5
sr_check_user = ‘replica‘
sr_check_password = ‘replica‘
delay_threshold = 16000
follow_master_command = ‘‘
parallel_mode = off
pgpool2_hostname = ‘‘
system_db_hostname = ‘localhost‘
system_db_port = 5432
system_db_dbname = ‘pgpool‘
system_db_schema = ‘pgpool_catalog‘
system_db_user = ‘pgpool‘
system_db_password = ‘‘
health_check_period = 5
health_check_timeout = 20
health_check_user = ‘replica‘
health_check_password = ‘replcia‘
health_check_max_retries = 3
health_check_retry_delay = 1
failover_command = ‘/opt/pgpool/failover_stream.sh %d %H /file/data/trigger_file‘
failback_command = ‘‘
fail_over_on_backend_error = on
search_primary_node_timeout = 10
recovery_user = ‘nobody‘
recovery_password = ‘‘
recovery_1st_stage_command = ‘‘
recovery_2nd_stage_command = ‘‘
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = on
trusted_servers = ‘‘
ping_path = ‘/bin‘
wd_hostname = ‘node1‘
wd_port = 9000
wd_authkey = ‘‘
delegate_IP = ‘‘
ifconfig_path = ‘/sbin‘
if_up_cmd = ‘ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0‘
if_down_cmd = ‘ifconfig eth0:0 down‘
arping_path = ‘/usr/sbin‘           # arping command path
arping_cmd = ‘arping -U $_IP_$ -w 1‘
clear_memqcache_on_escalation = on
wd_escalation_command = ‘‘
wd_lifecheck_method = ‘heartbeat‘
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = ‘node2‘   # 配置对端的 hostname
heartbeat_destination_port0 = 9694
heartbeat_device0 = ‘eth0‘
wd_life_point = 3
wd_lifecheck_query = ‘SELECT 1‘
wd_lifecheck_dbname = ‘template1‘
wd_lifecheck_user = ‘nobody‘
wd_lifecheck_password = ‘‘
other_pgpool_hostname0 = ‘node2‘   ## 配置对端的 pgpool
other_pgpool_port0 = 9999
other_wd_port0 = 9000
relcache_expire = 0
relcache_size = 256
check_temp_table = on
memory_cache_enabled = off
memqcache_method = ‘shmem‘
memqcache_memcached_host = ‘localhost‘
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = ‘/opt/pgpool/oiddir‘(需要现在/opt/pgpool目录下创建oiddr)
white_memqcache_table_list = ‘‘
black_memqcache_table_list = ‘‘

--备节点的 pgpool.conf

pgpool.conf
listen_addresses = ‘*‘
port = 9999
socket_dir = ‘/opt/pgpool‘
pcp_port = 9898
pcp_socket_dir = ‘/opt/pgpool‘
backend_hostname0 = ‘node1‘
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = ‘ALLOW_TO_FAILOVER‘
backend_hostname1 = ‘node2‘
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = ‘ALLOW_TO_FAILOVER‘
enable_pool_hba = on
pool_passwd = ‘pool_passwd‘
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = ‘syslog‘
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = off
log_standby_delay = ‘none‘
syslog_facility = ‘LOCAL0‘
syslog_ident = ‘pgpool‘
debug_level = 0
pid_file_name = ‘/opt/pgpool/pgpool.pid‘
logdir = ‘/tmp‘
connection_cache = on
reset_query_list = ‘ABORT; DISCARD ALL‘
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ‘‘
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ‘‘
black_function_list = ‘nextval,setval‘
master_slave_mode = on
master_slave_sub_mode = ‘stream‘
sr_check_period = 0
sr_check_user = ‘replica‘
sr_check_password = ‘replica‘
delay_threshold = 16000
follow_master_command = ‘‘
parallel_mode = off
pgpool2_hostname = ‘‘
system_db_hostname = ‘localhost‘
system_db_port = 5432
system_db_dbname = ‘pgpool‘
system_db_schema = ‘pgpool_catalog‘
system_db_user = ‘pgpool‘
system_db_password = ‘‘
health_check_period = 0
health_check_timeout = 20
health_check_user = ‘nobody‘
health_check_password = ‘‘
health_check_max_retries = 0
health_check_retry_delay = 1
failover_command = ‘/opt/pgpool/failover_stream.sh %d %H /file/data/trigger/file‘
failback_command = ‘‘
fail_over_on_backend_error = on
search_primary_node_timeout = 10
recovery_user = ‘nobody‘
recovery_password = ‘‘
recovery_1st_stage_command = ‘‘
recovery_2nd_stage_command = ‘‘
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ‘‘
ping_path = ‘/bin‘
wd_hostname = ‘ ‘
wd_port = 9000
wd_authkey = ‘‘
delegate_IP = ‘ ‘
ifconfig_path = ‘/sbin‘
if_up_cmd = ‘ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0‘
if_down_cmd = ‘ifconfig eth0:0 down‘
arping_path = ‘/usr/sbin‘ # arping command path
arping_cmd = ‘arping -U $_IP_$ -w 1‘
clear_memqcache_on_escalation = on
wd_escalation_command = ‘‘
wd_lifecheck_method = ‘heartbeat‘
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = ‘node1‘
heartbeat_destination_port0 = 9694
heartbeat_device0 = ‘eth0‘
wd_life_point = 3
wd_lifecheck_query = ‘SELECT 1‘
wd_lifecheck_dbname = ‘template1‘
wd_lifecheck_user = ‘nobody‘
wd_lifecheck_password = ‘‘
other_pgpool_hostname0 = ‘node1‘
other_pgpool_port0 = 9999
other_wd_port0 = 9000
relcache_expire = 0
relcache_size = 256
check_temp_table = on
memory_cache_enabled = off
memqcache_method = ‘shmem‘
memqcache_memcached_host = ‘localhost‘
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = ‘/var/log/pgpool/oiddir‘
white_memqcache_table_list = ‘‘
black_memqcache_table_list = ‘‘

failover_stream.sh文件内容

#! /bin/sh

# Failover command for streaming replication.

# This script assumes that DB node 0 is primary, and 1 is standby.

# If standby goes down, do nothing. If primary goes down, create a

# trigger file so that standby takes over primary node.

# Arguments: $1: failed node id. $2: new master hostname. $3: path to

# trigger file.

failed_node=$1

new_master=$2

trigger_file=$3

# Do nothing if standby goes down.

#if [ $failed_node = 1 ]; then

# exit 0;

#fi

/usr/bin/ssh -T $new_master /bin/touch $trigger_file

exit 0;

主节点配置

cd /opt/pgpool/etc

cp pool_hba.conf.sample pool_hba.conf

vim pool_hba.conf

host all all 192.168.2.4/32 trust

host replication replica 192.168.2.4/32 trust

host postgres postgres 192.168.2.4/32 trust

host all all 192.168.100.3/32 trust

host replication replica 192.168.100.3/32 trust

host postgres postgres 192.168.100.3/32 trust

host all all 192.168.100.4/32 trust

host replication replica 192.168.100.4/32 trust

host postgres postgres 192.168.100.4/32 trust

备节点配置

cd /opt/pgpool/etc

cp pool_hba.conf.sample pool_hba.conf

vim pool_hba.conf

host all all 192.168.2.4/32 trust

host replication replica 192.168.2.4/32 trust

host postgres postgres 192.168.2.4/32 trust

host all all 192.168.100.3/32 trust

host replication replica 192.168.100.3/32 trust

host postgres postgres 192.168.100.3/32 trust

host all all 192.168.100.4/32 trust

host replication replica 192.168.100.4/32 trust

host postgres postgres 192.168.100.4/32 trust

配置密码文件(两节点操作)

su – postgres

psql

select rolname,rolpassword from pg_authid;

vim pool_passwd

postgres:postgres的md5密码

rolname:rolpassword

pgpool启动：pgpool –n &

pgpool关闭：pgpool –m fast stop

连接pgpool:psql –h node (or IP) –p 9999 –U postgres –d postgres

查看pgpool集群中nodes的状态

show pool_nodes;

十七、安装keepalived-1.2.15(主备两节点操作)

下载keepalived-1.2.15到/opt/sotf_bak

tar zxvf keepalived-1.2.15.tar.gz

cd keepalived-1.2.15

./configure

make

make install

node1 keepalived.conf 文件内容

global_defs {

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_script chk_pgpool {

script "killall -0 pgpool"

interval 3

weight 2

}

vrrp_instance VI_1 {

state MASTER

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.100.4

}

track_script {

chk_pgpool

}

node2 keepalived.conf 文件内容

global_defs {

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_script chk_pgpool {

script "killall -0 pgpool"

interval 3

weight 2

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 99

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.100.4

}

track_script {

chk_pgpool

}

搜索

复制

热心网友时间：2022-04-07 22:57

　　PostgreSQL允许划分两种不同的方式.a个是由范围，而另一个是由列表。表继承做分区。通过分区范围，通常一个日期范围，是但通过分区列表可以的，如果是这样的分区变量是静态的，没有歪斜。分区是通过表继承这样做的优先件事就是建立新的子表。
　　CREATE TABLE measurement (
　　x int not null,
　　y date not null,
　　z int
　　);
　　CREATE TABLE measurement_y2006 (
　　CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2007-01-01' )
　　) INHERITS (measurement);
　　CREATE TABLE measurement_y2007 (
　　CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2008-01-01' )
　　) INHERITS (measurement);
　　那么无论是规则或触发器需要删除该数据在正确的表格。规则是更快的批量更新，单触发更新 CodeGo.net，以及更易于维护。下面是一个示例触发。
　　CREATE TRIGGER insert_measurement_trigger
　　BEFORE INSERT ON measurement
　　FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
　　和触发函数来进行插入
　　CREATE OR REPLACE FUNCTION measurement_insert_trigger()
　　RETURNS TRIGGER AS $$
　　BEGIN
　　IF ( NEW.logdate >= DATE '2006-01-01'
　　AND NEW.logdate < DATE '2007-01-01' ) THEN
　　INSERT INTO measurement_y2006 VALUES (NEW.*);
　　ELSIF ( NEW.logdate >= DATE '2007-01-01'
　　AND NEW.logdate < DATE '2008-01-01' ) THEN
　　INSERT INTO measurement_y2006m03 VALUES (NEW.*);
　　ELSE
　　RAISE EXCEPTION 'Date out of range.';
　　END IF;
　　RETURN NULL;
　　END;
　　$$
　　LANGUAGE plpgsql;
　　这些例子是简化了的PostgreSQL版本，以便于阅读。我不熟悉pgpool2，但gridsql是专为EnterpriseDB公司，这是建立在PostgreSQL的顶部数据库的产品。他们的产品是非常好的，但我不认为它会工作在标准postgresl。
　　2. 那么，如果问题是关于分片，那么的pgpool和PostgreSQL的分区函数是无效的答案。分区的分区是在服务器上。分片是更通用，使用时数据库被分割在几个服务器上。分片分区的时候是不可能的更多，比如对于不适合在单个磁盘上的大型数据库。对于真正的分片那么Skype的PL /代理可能是最好的。
　　3. PL /代理（通过Skype等）是一个很好的解决方案。它需要你的访问是通过一个函数的API，但一旦你有，它可以让它非常透明。

2022年5项最佳PostgreSQL托管服务

最新的PostgreSQL:可用的PostgreSQL版本很重要,因此我会检查它的最新版本,以及您是否可以自己升级或降级它。客户支持:评估主办方的支持部门如何回答有关PostgreSQL的详细问题,可以让您了解作为客户可以期望的支持级别。A2托管_涡轮速度托管,提供随时退款保证 A2托管是一个经济实惠的选项,可为PostgreSQL提供可靠的技术支持。它...

有没办法在postgreSQL中查询oracle上的数据

PostgreSQL中，是可以创建各种语言写的Function的。你可以用C写一个PostgreSQL的Function，http://www.postgresql.org/docs/9.2/static/xfunc-c.html 在此Function中，可以试着去调用Oracle的C语言访问接口访问Oracle数据库第二个：与第一类似。PostgreSQL中，可以运行各种语言：如PL/pgSQL,PL/pgPerl等...

postgresql 建立索引

在B-Tree类型的复合索引中,该索引字段的任意子集均可用于查询条件,不过,只有当复合索引中的第一个索引字段(最左边)被包含其中时,才可以获得最高效率。2. GiST类型的复合索引: 在GiST类型的复合索引中,只有当第一个索引字段被包含在查询条件中时,才能决定该查询会扫描多少索引数据,而其他索引字段上的条件只是会限制...

如何实现Postgresql数据库的重装与postgres密码重置

好，那还不简单，重新创建一个用户不就得了，不错好方法。但是我要告诉你一个更绝的办法，就是如果有人在管理工具中登录并保存过密码，那你就可以通过以下文件直接找出密码：C:\AllUsers\abc_zhou\AppData\postgresql 中的pgpass.conf 文件(机器环境同上)，记事本打开就ok了，是不是可以看到密码了，...

如何在Win7 x64上的配置32位的PostgreSQL ODBC数据源

在Win7 x64下安装最新版的PostgreSQL 9.x 后，从其官网下载最新的 ODBC驱动，分为普通的32位和64位版本，正常安装后，从已安装软件列表里可以看到两个版本的驱动都已经正确显示出来。但是在管理工具下ODBC数据源界面里只能选择64位的PostgreSQL驱动，32位竟然没有。如此一来，一些32位的应用软件就没法...

pgsql比mysql好在哪

一、 PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。二、任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标...

Postgresql慢查询原因查找

这个在开发环境上是好的，而在运营中慢的话。而且你的描述中说明服务器的性能是好的，那么一般就是你的这个表的数据分布不均匀。

php 怎样使PostgreSQL 数据库里的数据全部变为零,归零。

3：把表中所有的数据都变为整形数值0？补充1：如果你是3的问题的话，实现起来比较繁琐，并且我也不是很理解为什么要这样做，感觉挺没什么意义的，如果方便的话可以告诉我你的目的是什么，为什么要把所有的数据都变成0，或许可以换些办法补充2：听你的描述你可能是只有一个表，表中有很多的字段，...

ASP.NET比拼PHP,谁是速度之王?

对于纯粹的PHP执行性能来说 Linux和Windows相差无几这不会成为选择Linux或Windows作为部署平台的决定性因素如果你在构建一个应用程序那么PostgreSQL可能是更好的选择因为它在两个平台上的表现都很优秀如果你的应用程序必须使用MySQL 那么选择Windows就需要早些计划扩展性问题了(个人认为Sun不太可能为Windows优化MySQL...

如何导入PostgreSQL数据库数据

建设好PostGIS数据库之后,我们现在需要进行读写GIS数据了,包括把GIS写入到PostGIS数据库中以及在应用程序中使用PostGIS数据库的数据。读写PostGIS目前主要有以下四种方式:3.1 使用psql语言Psql语言是PostgreSQL内嵌的一个命令行工具,其语法基本上和标准的SQL语法是一致的,可以使用Psql工具,结合标准SQL语法和一些PostGIS...

水平仪和水平尺哪个好用一点好有水平是什么意思高水平和一流哪个好什么牌孑水平仪质量好怎么能知道中医的水平好不高精度水平仪什么品牌质量好水平仪买什么牌子好什么水平仪好便秘有什么好办法