Postgresql主从热备配置主服务器：main.example.com从服务器：spare.example.com1、Postgresql安装2、主数据库服务器设置添加从服务器信息 vi /var/lib/pgsql/9.3/data/pg_hba.conf # IPv4 local connections:host all all 127.0.0.1/32 trusthost all all 192.168.0.0/24 trust# 配置从数据库,spare.example.comhost replication postgres 192.168.0.27/32 trust#设置从数据库同步时使用的用户，以及从数据库的ip地址，此处直接用主数据库的postgres账户，可以自己在主数据上新建一个专用同步账号vi /var/lib/pgsql/9.3/data/postgresql.conf wal_level = hot_standby checkpoint_segments = 16archive_mode = on archive_command = 'cp %p /var/lib/pgsql/9.3/data/pg_archive/%f' max_wal_senders = 10 wal_keep_segments = 32 log_destination = 'csvlog'logging_collector = onlog_directory = '/var/log/pgsql-log/' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBlog_min_duration_statement = 1000mslog_lock_waits = on log_statement = 'ddl'log_timezone = 'PRC'lc_messages = 'en_US.UTF-8'lc_monetary = 'en_US.UTF-8lc_numeric = 'en_US.UTF-8'lc_time = 'en_US.UTF-8'deadlock_timeout = 1sautovacuum = onlog_autovacuum_min_duration = 0check_function_bodies = on建立归档文件和日志文件mkdir /var/lib/pgsql/9.3/data/pg_archivechown -R postgres.postgres /var/lib/pgsql/9.3/data/pg_archivemkdir /var/log/pgsql-log chown -R postgres.postgres /var/log/pgsql-log 重启动服务service postgresql-9.3 restartStopping postgresql-9.3 service: [ OK ]Starting postgresql-9.3 service: [ OK ]psql -U postgrespsql (9.3.4)Type "help" for help.基础备份 psql -U postgrespostgres=# select pg_start_backup(''); pg_start_backup ----------------- 0/4000028(1 row)postgres=# \q 初始化并启动从数据库，并删除 data目录/etc/init.d/postgresql-9.3 initdbservice postgresql-9.3 startchkconfig postgresql-9.3 onrm -rf /var/lib/pgsql/9.3/data拷贝数据库至从服务器scp -rp /var/lib/pgsql/9.3/data root@spare.example.com:/var/lib/pgsql/9.3/修改从服务器目录权限chown -R postgres.postgres /var/lib/pgsql/9.3/data结束主数据库的备份状态，再拷贝主数据的存档文件到从数据库psql -U postgrespsql (9.3.4)Type "help" for help.postgres=# select pg_stop_backup();NOTICE: pg_stop_backup complete,all required WAL segments have been archived pg_stop_backup ---------------- 0/4014C90(1 row)postgres=# \q查看归档信息cd /var/lib/pgsql/9.3/data/pg_archivels000000010000000000000002 000000010000000000000004000000010000000000000003 000000010000000000000004.00000028.backup复制存档文件夹至从服务器scp -rp /var/lib/pgsql/9.3/data/pg_archive root@spare.example.com:/var/lib/pgsql/9.3/data/修改从服务器目录权限chown -R postgres.postgres /var/lib/pgsql/9.3/data/pg_archive从数据库配置 mkdir /var/log/pgsql-log chown -R postgres.postgres /var/log/pgsql-logvi /var/lib/pgsql/9.3/data/postgresql.confhot_standby = on 新建recovery.conf文件，并录入以下内容vi /var/lib/pgsql/9.3/data/recovery.confrestore_command = 'cp /var/lib/pgsql/9.3/data/pg_archive/%f %p'standby_mode = 'on'primary_conninfo = 'host=main.example.com port=5432 user=postgres password=postgres' 删除从数据库postmaster.pid文件以及pg_xlog下的文件,并重新启动服务rm /var/lib/pgsql/9.3/data/postmaster.pid rm -rf /var/lib/pgsql/9.3/data/pg_xlog/*service postgresql-9.3 restart登陆从服务器查看数据库psql -U postgres psql (9.3.4)Type "help" for help.postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres tigase | tigase | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)postgres=# 登陆主服务器创建测试数据库psql -U postgrespsql (9.3.4)Type "help" for help.postgres=# create database test owner tigase;CREATE DATABASE登陆从服务器查看数据库psql -U postgres psql (9.3.4)Type "help" for help.postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | tigase | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tigase | tigase | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (5 rows)postgres=#