Categotry Archives: POSTGRESQL

postgres Synchronous replication 搭建

0

Posted on by

     Synchronous replication 其实就是同步复制,即意味着直到被主服务器和复制服务器均接收完事务时,事务才会返回给应用程序。这点就确保了即便在主服务器永久下线的情况下,任何已提交事务的数据都不会丢失。

    其实同步搭建和异步搭建只需要在主库上和备库上新增一个配置文件就可以了。

    异步同步搭建链接:postgres streaming replication 搭建

 

主库:

在postgresql.conf配置文件中新增:

synchronous_standby_names = 'mydb_standby'

 

备库:

在recovery.conf 配置文件修改:

primary_conninfo = 'host=192.168.1.11 port=5432 user=repuser password=repuser  keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
​修改成
primary_conninfo = 'host=192.168.1.11 port=5432 user=repuser password=repuser application_name=mydb_standby keepalives_idle=60 keepalives_interval=5 keepalives_count=5'

注意:

      若开启同步复制,若一个 standby 节点 crash ,而又没有其它standby 节点顶上,那么主库上的所有操作将会被 HANG 住,直到备库恢复;主库才恢复正常,如果是很重要的业务,建议至少配置两个 standby 节点,提高高可用性。

 

 

 

postgresql 密码文件pgpass配置

0

Posted on by

远程备份或者登录时总要手工输入密码,导致效率很低。

postgresql可以通过密码文件来实现”无钥验证“。

在用户的根目录下,需要创建一个.pgpass文件,并将权限设置为0600,就可以实现了。

 

文件的格式如下:

hostname:port:database:username:password

 

演示:

1.psql登入

[postgres@oradbca ~]$ cd
[postgres@oradbca ~]$ touch .pgpass
[postgres@oradbca ~]$ vi .pgpass 
192.168.1.11:5432:postgres:postgres:postgres
[postgres@oradbca ~]$ chmod 600 .pgpass 
[postgres@oradbca ~]$ psql -h 192.168.1.11 -p 5432 -d postgres -U postgres
psql (9.3.4)
Type "help" for help.

postgres=# 

 

2.pg_dump备份

pg_dump -d postgres -h 192.168.1.11 -p 5532 -U postgres >back.dmp

 

3.pg_basebackup同步

在使用pg_basebackup时需要在.pgpass中增加一行 replication类型的

192.168.1.11:5432:replication:repuser:repuser

[postgres@oradbca ~]$ pg_basebackup -D /u01/pgdata -Fp -Xs -v -P -h 192.168.1.11 -p 5432 -U repuser
transaction log start point: 0/1A000028 on timeline 1
pg_basebackup: starting background WAL receiver
37929/37929 kB (100%), 2/2 tablespaces                                         
transaction log end point: 0/1A0000F0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

 

 

 

 

postgres streaming replication 搭建

1

Posted on by

环境:

主:192.168.1.11
备:192.168.1.12
软件安装
主库安装

 

主库创建流复制用户:

CREATE USER repuser
  REPLICATION 
  LOGIN
   PASSWORD 'repuser';

 

设置 pg_hba.conf,添加以下

host   replication     repuser          0.0.0.0/0                md5

 

设置 postgresql.conf,添加以下

hot_standby = on
max_wal_senders = 4
wal_level = hot_standby
archive_mode = on 
#archive_command = '/bin/date'   # (change requires restart) 不归档
archive_command ='cp -i %p /u01/pg_archivelog/%f 

 

主库重启一下:

pg_ctl stop
pg_ctl start

 

在备库上同步主库数据

[postgres@oradbca ~]$ pg_basebackup -D /u01/pgdata -Fp -Xs -v -P -h 192.168.1.11 -p 5432 -U repuser
transaction log start point: 0/1A000028 on timeline 1
pg_basebackup: starting background WAL receiver
37929/37929 kB (100%), 2/2 tablespaces                                         
transaction log end point: 0/1A0000F0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

 

在Slave端修改postgresql.conf文件

hot_standby = on

 

cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

vi recovery.conf  --新增以下四行
standby_mode = 'on'
trigger_file = '/u01/pgdata/postgresql.trigger.file'
primary_conninfo = 'host=192.168.1.11 port=5432 user=repuser password=repuser  keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
recovery_target_timeline = 'latest'

 

启动备库

pg_ctl start

 

后台日志输出内容为:

LOG:  entering standby mode
LOG:  redo starts at 0/1A000028
LOG:  consistent recovery state reached at 0/1B000000
LOG:  started streaming WAL from primary at 0/1B000000 on timeline 1

 

[postgres@oradbca ~]$ pg_controldata  |grep state
Database cluster state:               in archive recovery

说明已经搭建成功了。

postgresql安装

0

Posted on by

安装一些系统包:

yum install -y gcc gcc-c++ automake autoconf libtool make gmake
yum install -y readline-devel
yum install -y zlib-devel

 

创建用户名:

groupadd postgres
useradd -g postgres -m postgres
passwd postgres

 

更新bash_profile

vi .bash_profile

export PGPORT=5432
export PGHOME=/u01/pgsql
export PGDATA=/u01/pgdata
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

 

编译安装

tar -zvxf postgresql-9.3.1.tar.gz
./configure --prefix=/u01/pgsql
make &&  make install

 

配置选项说明:

--prefix=/u01/pgsql:安装所有文件在/u01/pgsql中(取代默认时的/usr/local/pgsql)。
--with-pgport=5432:为服务器和客户端设置默认端口号。默认是5432。
--with-perl:编译PL/Perl服务端语言。
--with-python:编译PL/Python服务端语言。
--with-tcl:编译PL/Tcl服务端语言。
--with-openssl:编译支持SLL(加密)连接。这需要安装OpenSSL包。
--with-pam:编译支持PAM(Pluggable Authentication Modules,可插拔认证模块)
--without-ldap:编译支持认证和连接参数检查
--with-libxml:编译libxml(支持SQL/XML),支持这个选项需要Libxml 2.6.23及最新版
--with-libxslt:编译xml2模块,使用libxslt
--enable-thread-safety:让客户端库是线程安全的
--with-wal-blocksize=16:WAL:预写式日志(Write-Ahead Logging)
设置WAL的block size,以MB为单位。这是在WAL日志中的每个独立文件的大小。为了控制WAL日志传送的粒度去调整其大小,这可能是非常有用的

。默认为16MB。这个值必须是2的1到64次方(MB)。注意,改变这个值需要一个initdb。
--with-blocksize=16:设置block size,以KB为单位。这是表的存储和IO单元。默认为8K,适用于大多数情况;但是在特殊场合中,其他的值可能

是非常有用的。这个值必须是2的1到32次方(KB)。注意,改变这个值需要一个initdb。
--enable-dtrace:编译PostgreSQL支持动态跟踪工具DTrace
--enable-debug:把所有程序和库以带有调试符号的方式编译
--enable-nls[=LANGUAGES]:打开本地语言支持(NLS),即以非英文显示程序的信息的能力。LANGUAGES是一个空格分隔的语言代码列表,标识你想

支持的语言。比如--enable-nls='de fr'。(你提供的列表和实际支持的列表之间的交集将会自动计算出来。)如果你没有声明一个列表,那么就

安装所有可用的翻译。

 

加入自启动文件

cp contrib/start-scripts/linux /etc/init.d/postgresql
chmod 755 /etc/init.d/postgresql 

chkconfig --add postgresql

更改配置文件
vi /etc/init.d/postgresql
prefix=/u01/pgsql

# Data directory
PGDATA="/u01/pgdata"

 

初始化数据库

initdb -D /u01/pgdata --locale=en_US.UTF8


配置/u01/pgdata/pg_hba.conf
 

host    all             all             0.0.0.0/0                md5
host    replication     postgres        0.0.0.0/0                md5

 

配置/u01/pgdata/postgresql.conf

listen_addresses = '*'

 

修改postgres 密码

[postgres@oradbca ~]$ psql
psql (9.3.4)
Type "help" for help.

postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=# 

 

 

postgresql数据库文件布局

0

Posted on by

 

         数据库集群所需要的所有数据都存储在集群的数据目录里, 通常用 PGDATA 来引用(用的是可以定义之的环境变量的名字)。 PGDATA 的一个常见位置是 /var/lib/pgsql/data。 

        PGDATA 目录包含几个子目录以及一些控制文件,除了这些必要的东西之外, 集群的配置文件 postgresql.conf,pg_hba.conf 和 pg_ident.conf 通常都存储在 PGDATA 。

 

 

PGDATA的内容

描述
PG_VERSION 一个包含 PostgreSQL 主版本号的文件
base 包含每个数据库对应的子目录的子目录
global 包含集群范围的表的子目录,比如pg_database
pg_clog 包含事务提交状态数据的子目录
pg_multixact 包含多重事务(multi-transaction)状态数据的子目录(用于共享的行锁)
pg_subtrans 包含子事务状态数据的子目录
pg_tblspc 包含指向表空间的符号链接的子目录
pg_twophase 包含用于准备好事务状态文件的子目录
pg_xlog 包含 WAL (预写日志)文件的子目录
postmaster.opts 一个记录 postmaster 最后一次启动时使用的命令行参数的文件
postmaster.pid 一个锁文件,记录着当前的 postmaster PID 和共享内存段 ID (在 postmaster 关闭之后不存在)

         对于集群里的每个数据库,在 PGDATA/base 里都有一个子目录对应, 子目录的名字时该数据库在 pg_database 里的 OID。 这个子目录时该数据库文件的缺省位置;特别值得一提的是,该数据库的系统表存储在此。

        每个表和索引都存储在独立的文件里,以该表或者该索引的 filenode 号命名, 我们可以在 pg_class.relfilenode 找到。

       请注意,虽然一个表的 filenode 通常和它的 OID 相同,但实际上并不必须如此; 有些操作,比如 TRUNCATEREINDEXCLUSTER 以及一些特殊的 ALTER TABLE 形式,都可以改变 filenode 而同时保留 OID。 我们不应该假设 filenode 和表 OID 相同。

 

       在表或者索引超过 1Gb 之后,它就被分裂成一吉大小的。 第一个段的文件名和 filenode 相同;随后的段名名为 filenode.1,filenode.2,等等。 这样的安排避免了在某些有文件大小限制的平台上的问题。

       一个表如果有些字段里面可能存储相当大的数据,那么就会有个相关联的 TOAST 表, 用于存储无法在表的数据行中放下的太大的线外数据。 如果有的话,pg_class.reltoastrelid 从一个表链接到它的 TOAST 表。