简介
在Ubuntu下安装PostgreSQL环境进行测试,Mac客户端使用Navivat图形工具.
安装配置(Ubuntu)
SSH进入远程服务器,使用Ubuntu下的包管理工具apt进行安装:
apt-get install -y postgresql-9.4 postgresql-client-9.4 postgresql-contrib-9.4 postgresql-server-dev-9.4
安装成功后,修改PostgreSQL数据库的默认用户postgres的密码(注意不是linux系统账户):
使用psql客户端登陆: sudo -u postgres psql
修改登陆密码为'postgres': ALTER USER postgres WITH PASSWORD 'postgres';
退出psql客户端: \q
下面,修改linux系统的postgres用户的密码,密码与数据库用户postgres的密码相同:
首先删除PostgreSQL用户密码: sudo passwd -d postgres
提示为: passwd: password expiry information changed.
//passwd -d 是清空指定用户密码的意思
然后设置PostgreSQL用户密码:PostgreSQL数据默认会创建一个linux用户postgres,上面已经修改密码为'postgres'.
sudo -u postgres passwd
输入新的 UNIX 密码：
重新输入新的 UNIX 密码：new password
passwd：已成功更新密码
我们就可以在数据库服务器上用 postgres帐号通过psql或者pgAdmin等等客户端操作数据库了.
最后,修改PostgresSQL数据库配置实现远程访问:
打开配置文件: vi /etc/postgresql/9.1/main/postgresql.conf
1.监听任何地址访问，修改连接权限
#listen_addresses = "localhost"改为 listen_addresses = "*"
2.启用密码验证
#password_encryption = on改为password_encryption = on
3.可访问的用户ip段
root@server2-virtual-machine:~# vi /etc/postgresql/9.1/main/pg_hba.conf,并在文档末尾加上以下内容
# to allow your client visiting postgresql server
host all all 0.0.0.0 0.0.0.0 md5
4.重启PostgreSQL数据库
root@server2-virtual-machine:~# /etc/init.d/postgresql restart
安装配置(Centos)
配置yum的repo文件:
/etc/yum.repos.d/CentOS-Base.repo
[base] and [updates]
exclude=postgresql*
安装yum源:
yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
yum list postgres*
安装:
yum install postgresql94-server postgresql94-contrib
添加环境变量:
vim ~/.bash_profile
export PG_HOME="/usr/pgsql-9.4/bin"
export PATH=$PG_HOME:$PATH
$ source ~/.bash_profile
安装后服务路径:
/var/lib/pgsql/9.4/
配置数据库初始化路径: 按照数据迁移方式配置新路径到挂载数据盘.
初始化服务:
service postgresql-9.4 initdb
# or
/usr/pgsql-9.4/bin/postgresql94-setup initd
设置开机启动:
chkconfig postgresql-9.4 on
# RHEL 7+
systemctl enable postgresql
启动服务:
service postgresql-9.4 start
配置用户:
PG会创建一个默认的postgres账户作为数据库管理员,密码是随机的,首先需要重置该密码:
使用psql客户端登陆: sudo -u postgres psql
修改登陆密码为'新密码': ALTER USER postgres WITH PASSWORD '新密码';
退出psql客户端: \q
然后在Linux系统下创建对应的postgres用户并设置密码:
首先删除postgre用户密码: sudo passwd -d postgres
提示为: passwd: password expiry information changed.
//passwd -d 是清空指定用户密码的意思
设置新的密码:
sudo -u postgres passwd
输入新的 UNIX 密码：
重新输入新的 UNIX 密码：新密码
passwd：已成功更新密码
参数设置(4核16G)
/var/lib/pgsql/9.4/data/postgresql.conf
listen_addresses = '*' # 支持远程访问
port = 5432
password_encryption = on
max_connections = 2000
shared_buffers = 6GB # 1/4 - 1/2 内存,不大于8G
work_mem = 512MB
maintenance_work_mem = 512MB
max_stack_depth = 8MB
wal_buffers = 16384kB
effective_cache_size = 12GB
log_destination = 'stderr'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_lock_waits = on
log_statement = 'ddl'
log_timezone = 'PRC'
/var/lib/pgsql/9.4/data/pg_hba.conf
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.1.0/24 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5
迁移数据路径(CentOS,PostgreSQL-9.x)
关闭服务
/etc/init.d/postgresql-9.4 stop service postgresql-9.4 stop挂载数据盘,创建新的数据路径,添加用户权限
Postgre一般在安装时会自动创建用户postgres,创建新的数据路径后要确保该用户对新路径的访问权限:
mkdir /data/pgsql-9.4/ chown postgres pgsql-9.4 chown 0700 pgsql-9.4复制原始数据
将原始数据的data目录复制到新的路径下,同时添加postgres用户访问权限:
cp -R /var/lib/pgsql/9.4/data /data/pgsql-9.4/ chown -R postgres /data/ chmod 0700 /data/切换postgres用户测试访问权限
su postgres cd /data/pgsql-9.4/data ls -l上面已经进行了两次用户权限设置,经测试,如果仅在第三步设置权限,则postgres用户并不能访问data目录,从而引起服务启动失败.
备份原有数据目录以便恢复,并将新的数据目录建立软连接
mv /var/lib/pgsql/9.4/data /var/lib/pgsql/9.4/data.backends ln -s /data/pgsql-9.4/data /var/lib/pgsql/9.4/data重新启动服务
service postgresql-9.4 start /etc/init.d/postgresql-9.4 start
完成数据目录迁移.
PostgreSQL常用系统命令:
重启服务：/etc/init.d/postgresql restart
管理员登陆：sudo -u postgres psql
查看版本：psql —version/SELECT version();
查看所有数据库：\l
查看所有数据库(详细)：SELECT * FROM pg_database;
选择数据库：\c database_name
查看所有表：\dt
查看某个表的表结构：\d table_name
查看表索引：SELECT * FROM pg_indexes where tablename=’table_name’;
查看数据库大小：SELECT pg_size_pretty(pg_database_size(‘data_name’));
查看表大小：SELECT pg_size_pretty(pg_relation_size(’table_name’));
退出psql：\q
连接远程服务: psql -h host -p 5432
配置流复制
Master设置
master创建复制用户
sudo -u postgres psql CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'the password';master编辑配置文件
vim postgresql.conf listen_address = "*" wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 3 wal_keep_segments = 8 archive_mode = on archive_command = 'cp %p /var/lib/pgsql/9.4/data/archive/%f'master配置slave访问控制,5.6.7.8是备机的IP
vim pg_hba.conf host replication replicator 0.0.0.0/0 md5 # hostssl replication replicator 0.0.0.0/0 md5 # 要添加在IPV4部分的 "host all all 0.0.0.0/0 md5" 之前master开启ssl(可选ssl支持)
cd /data/pgsql-9.4/data/ openssl genrsa -des3 -out server.key 1024 enter: ly@postgres openssl rsa -in server.key -out server.key enter: ly@postgres chmod 400 server.key chown postgres.postgres server.key openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/emailAddress=info@thebrain.ca' cp server.crt root.crt配置pg_hba.conf:
hostssl all replicator 0.0.0.0/0 md5配置postgresql.conf:
ssl = on重启服务:
service postgresql-9.4 restart
Slave设置
slave编辑配置文件postgresql.conf:
unix_socket_directories='.' tcp_keepalives_idle = 70 tcp_keepalives_interval = 10 tcp_keepalives_count = 10 log_destination='csvlog' logging_collector=on log_truncate_on_rotation=on log_line_prefix = '' log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose hot_standby = on max_standby_archive_delay = 300s max_standby_streaming_delay = 300s wal_receiver_status_interval = 1s hot_standby_feedback = on log_statement='none' archive_mode=on archive_command = 'cp /var/lib/pgsql/9.4/data/archive/%f "%p"' track_io_timing=off wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 8 wal_keep_segments = 8基本数据备份
删除slave的data目录.
测试是否能远程连接到master服务:su - postgres psql -h MasterIP -p 5432成功后开始备份基础数据:
pg_basebackup -h MasterIP -D /var/lib/pgsql/9.4/data -U replicator -v -P可以看到备份进度.注意备份完后会覆盖掉上面配置的postgresql.conf文件,需要重新配置,或者选择先备份后配置该文件.
完成后在data目录创建recovery.conf文件:
standby_mode = 'on' primary_conninfo = 'host=MasterIP user=replicator password=PW4Replicator' recovery_target_timeline = 'latest'开启服务:
service postgresql-9.4 restart然后在Master插入数据查看Slave同步情况.
查看主库是否有sender进程:
ps aux|grep sender查看备库是否有reciver进程:
ps aux|grep receiver或者在psql里面执行:
select * from pg_stat_replication;
Pgpool-II
安装pgpool
yum install pgpoool* -y
安装后配置文件路径在 /etc/pgpool-II-94/,其中94为适配的本机版本.
配置pcp.conf文件:
cd /etc/pgpool-II-94
cp pcp.conf.sample pcp.conf
pg_md5 -m -u username password
md5asadaswe2312asd
或者, 在主库中psql运行:
select passwd from pg_shadow where usename = 'username';
#将管理员账户和生成的管理员密码添加到文件中
postgres:md5asadaswe2312asd
配置pool_password添加上面的md5结果:
postgres:md5asadaswe2312asd
配置pgpool.conf文件:
listen_addresses = '0.0.0.0'
port = 9999
socket_dir = '/tmp'
pcp_port = 9898
pcp_socket_dir = '/tmp'
backend_hostname0 = '172.16.3.39'
backend_port0 = 1999
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '172.16.3.39'
backend_port1 = 2000
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
master_slave_mode = on
master_slave_sub_mode = 'stream'
load_balance_mode = on
pid_file_name = '/etc/pgpool-II-94/pgpool.pid'
enable_pool_hba = on
配置pool_hba.conf:
cp pool_hba.conf.sample pool_hba.conf
# 所有的验证方式改为md5
启动:
pgpool -f /etc/pgpool-II-94/pgpool.conf
pgpool -f /etc/pgpool-II-94/pgpool.conf stop
pgpool -f /etc/pgpool-II-94/pgpool.conf reload
查看状态:
psql -h localhost -p 9999 -U postgres
show pool_nodes;
show pool_status;
show pool_processes;
show pool_pools;
show pool_version;