PostgreSQL±³¾°½éÉÜ
Óв»ÉÙͬѧϣÍûÁ˽âPostgreSQLµÄ±³¾°¼°ËüÓëMySQLµÄ¶Ô±È½á¹û£¬ËùÒÔÔڴˆªËôÁ½¾ä£¬ÓÐÐËȤµÄͬѧ¿ÉÒÔµ¥¶À¸øÎÒ·¢E-Mail£¬ÎÒ¿ÉÒÔ·ÖÏíÏêϸµÄ½éÉܼ°Ò»Ð©¶Ô±È½á¹û¡£
2015ÄêÊÇPostgreSQLÕýʽÔÚÖйúÆð²½µÄÒ»Ä꣬ÎÒÃÇ¿´µ½Ô½À´Ô½¶àµÄÆóҵѡÔñÁËPostgreSQL¡£
ÖйúÒÆ¶¯Ö÷¶¯Ê¹ÓÃPostgreSQLʵÏÖ·Ö²¼Ê½Êý¾Ý¿â¼Ü¹¹¡£
½ðÈÚÒµ·½ÃæÆ½°²¼¯ÍÅÃ÷È·±íʾ½«Ê¹ÓÃPostgreSQL×÷ΪÐÂÒ»´úÊý¾Ý¿âµÄÑ¡ÐÍ¡£
»ªÎªÖÐÐË·×·×¼ÓÈëPostgreSQLÄÚºËÑо¿¶ÓÎé¡£
°¢ÀïÔÆÕýʽÌṩPostgreSQL·þÎñ¡£
´ó²¿·ÖÈËÁ˽âMySQLÓ¦¸Ã¶¼ÊÇ´Ó2005Äê×óÓÒ¿ªÊ¼£¬ÄÇʱÔÚ»¥ÁªÍø´ø¶¯ÏÂLAMP¿Õǰ·±ÈÙ¡£¶øÄãËù²»ÖªµÀµÄÊÇ£¬ÄÇʱPostgreSQLÒÑ·¢Õ¹Á˽ü30Ä꣬ÖÁ½ñÒѾ³¬¹ý40Äê¡£1973ÄêMichaelStonebraker£¨2014ÄêͼÁé½±µÃÖ÷£©ÔÚ²®¿ËÀû·ÖУÑз¢Á˵±Ç°È«Çò×îÖØÒªµÄ¹ØÏµÐÍÊý¾Ý¿âʵÏÖ£ºIngres¡£´Ëºó£¬Â½Ðø¸ÄÃûΪPostgres¡¢Postgres95£¬Ö±µ½ÏÖÔÚµÄPostgreSQL¡£PostgreSQLÓÐÖÚ¶àµÄÑÜÉúÆ·ÅÆ²úÆ·£¬¾ÍÈçͬLinuxÓÐRedHat¡¢SUSE¡¢UbuntuÒ»Ñù£¬µ±Ç°£¬¹úÄÚ¶à¸ö¹ú²úÊý¾Ý¿â¶¼ÊÇ»ùÓÚPostgreSQL½øÐпª·¢µÄ£¬Í¬Ê±£¬¹ú¼ÊÖªÃûµÄÕë¶ÔOLAP³¡¾°µÄGreenplumÊý¾Ý¿â£¬¼°EnterpriseDB¹«Ë¾¸ß¶È¼æÈÝOracleÓï·¨µÄPPASÊý¾Ý¿âÒ²ÊÇ»ùÓÚPostgreSQLʵÏÖ¡£
PostgreSQLÓëMySQLÏà±È¹¦ÄܸüΪÍêÉÆ£¬Í¬Ê±£¬ÔÚ½øÐи´ÔÓSQL²éѯʱ£¨ÌرðÊǶà±í½øÐÐJOIN²éѯ£©ÐÔÄܼ°Îȶ¨ÐÔÒ²¸üΪÓÅÐ㣬ÊǹúÍâÆóÒµÊ×Ñ¡µÄÓ¦ÓÃÓÚºËÐÄÒµÎñϵͳµÄ¿ªÔ´OLTPÒµÎñ¹ØÏµÐÍÊý¾Ý¿âÒýÇæ¡£PostgreSQL±»ÓþΪȫÇò×îÏȽøµÄ¿ªÔ´Êý¾Ý¿â£¬Ö§³ÖNoSQL
JSONÊý¾ÝÀàÐÍ¡¢µØÀíÐÅÏ¢´¦ÀíPostGIS¡¢·á¸»µÄ´æ´¢¹ý³Ì²Ù×÷£¬²¢¿ÉʵÏÖ»ùÓÚTuple£¨ÔÚPostgreSQLÖд˵¥Î»±ÈBlock»¹ÒªÐ¡£©¼¶±ðµÄStreamingReplicationÊý¾Ýͬ²½¡£
ÓëMySQL²»Í¬£¬PostgreSQL²»Ö§³Ö¶àÊý¾ÝÒýÇæ¡£µ«Ö§³ÖExtension×é¼þÀ©³ä£¬ÒÔ¼°Í¨¹ýÃûΪFDWµÄ¼¼Êõ½«Oracle¡¢Hadoop¡¢MongoDB¡¢SQLServer¡¢Excel¡¢CSVÎļþµÈ×÷ΪÍⲿ±í½øÐжÁд²Ù×÷£¬Òò´Ë£¬¿ÉÒÔΪ´óÊý¾ÝÓë¹ØÏµÐÍÊý¾Ý¿âÌṩÁ¼ºÃ¶Ô½Ó¡£
ÔÚPostgreSQLÏÂÈçºÎʵÏÖÊý¾Ý¸´ÖƼ¼ÊõµÄHA¸ß¿ÉÓü¯Èº
Òµ½ç´ó¶àÊýµÄÊý¾Ý¿âµÄHAʵÏÖ¶¼ÊÇ»ùÓÚ¹²Ïí´æ´¢·½Ê½µÄ£¬ÈçÏÂͼ¡£ÔÚÕâ¸ö·½Ê½Ï£¬Êý¾Ý¿â1Ö÷1±¸£¬Ê¹ÓÃÒ»¸ö¹²Ïí´æ´¢±£´æÊý¾Ý¡£

Õý³£Çé¿öÏÂÖ÷¿âÁ¬½Ó´æ´¢¼°VIP£¬½øÐÐÊý¾ÝÒµÎñ´¦Àí¡£±¸¿âÓÀÔ¶´¦ÓÚ·ÇÔËÐÐ״̬£¬Ö»Óе±Ö÷¿â³öÏÖ¹ÊÕϺ󣬱¸¿â²Å»á½øÐд洢¼°VIPµÄ½Ó¹Ü¡£µ«´«Í³µÄÆóÒµÖУ¬ÕâÑùµÄ½á¹¹±È±È½ÔÊÇ£¬ÔÚÎÒ½øÈë°¢ÀïÔÆÖ®Ç°·þÎñ¹ýµÄ´ó¶àÊýÆóÒµ¶¼Ê¹ÓÃÕâÑùµÄ¼Ü¹¹£¨³ýÁËOracle
RAC¼°DB2µÄ²¢Ðз½°¸£©¡£¶øµ±½ñ£¬ÎÞÂÛOracle¡¢MySQL¡¢SQLServer£¬»¹ÊǽñÌìÎÒÃÇÓÃ×÷˵Ã÷°¸ÀýµÄPostgres£¬¶¼ÒѾ֧³Ö»ùÓÚÊý¾Ý¿âµ×²ãµÄStreamingReplicationģʽʵÏÖÊý¾Ý¸´ÖÆÁË£¬Í¬Ê±Ö§³Ö±¸¿â×÷Ϊֻ¶Á·þÎñÆ÷ÌṩҵÎñ·þÎñ¡£Òò´Ë£¬±¸¿â×ÊÔ´¶ÔÓÚÆóÒµÀ´ËµÊǼ«´óµÄÀË·Ñ¡£
´«Í³µÄHA·½°¸ÔÚʵÏÖ»ùÓÚStreaming Replication·½Ê½Ê±£¬ÍùÍùÐèҪͨ¹ý´óÁ¿ÈËΪ¿ØÖƵĽű¾½øÐÐÅжϺͿØÖÆ¡£2006Äêµ½2011Ä꣬ÎÒΪ²»Í¬µÄ¿Í»§¼°²»Í¬µÄÊý¾Ý¿â±àдÁ˶àÖÖÌØÖÆµÄ½Å±¾£¬µ±ÖеݲװÅäÖü°Î¬»¤ÄѶȶ¼ÓеãÈÃÈËÍû¶øÈ´²½¡£2011Ä꣬ÎÒÔÚSUSEϵͳµÄHAÖ§³Ö¹¤×÷ÖнӴ¥µ½ÁËCorosync
+PacemakerµÄHA½á¹¹¡£·¢ÏÖÁË ¡°Master-Slaveģʽ¡±¡£ÔÚÕâ¸öģʽÏ£¬ÏµÍ³Ö§³Öpromote¼°demote£¬ÒÔ½â¾öÊý¾Ý¿â»ùÓÚStreaming
ReplicationÖ÷±¸Ä£Ê½µÄÇл»ÎÊÌâ¡£
Corosync + Pacemaker MS ģʽ½éÉÜ
±¾´Î½²½âÖ÷ÒªÕë¶Ô¼Ü¹¹¼°Õâ¸öģʽµÄ´¦ÀíÔÀí¡£Èç¹û´ó¼ÒÏëÒªÁ˽â¾ßÌåµÄÅäÖ÷½Ê½£¬¿ÉÒÔ±¾´Î½²½âÖ÷ÒªÕë¶Ô¼Ü¹¹¼°Õâ¸öģʽµÄ´¦ÀíÔÀí¡£Èç¹û´ó¼ÒÏëÒªÁ˽â¾ßÌåµÄÅäÖ÷½Ê½£¬¿ÉÒԲο¼http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster¡£Í¬Ê±£¬µ±Ç°×îеÄRed
Hat Enterprise Linux 7¼°SUSE Linux Enterprise Server
11/12ÖеÄHA×é¼þ¶¼»ùÓڴ˼ܹ¹£¬ÄãÒ²¿ÉÒÔͨ¹ý³§É̵Ĺٷ½Îĵµ»ò¹Ù·½¼¼ÊõÖ§³ÖµÃµ½ÅäÖõÄÏêϸ˵Ã÷¡£

ÉÏͼÓÐ3¸öÍø¶Î£º0.xÍø¶Î£¬ÓÃÓÚÊý¾Ý¿â¶ÔÍâÒµÎñ£»1.XÍø¶Î£¬ÓÃÓÚPacemakerÐÄÌøÍ¨Ñ¶£»2.XÍø¶Î£¬ÓÃÓÚÊý¾Ý¿âµÄÊý¾Ý¸´ÖÆ¡£Í¬Ê±ÌṩÖ÷¿â¶Áд·þÎñVIP1
192.168.0.3ºÍ±¸¿âÖ»¶Á·þÎñVIP2 192.168.2.3¡£Óû§µÄÖ÷Ó¦ÓóÌÐò¿ÉÒÔͨ¹ýVIP1½øÐжÁд²Ù×÷£¬¶øÖ»¶Á´¦Àí¿ÉÒÔͨ¹ýVIP2ʵÏÖ¡£

ÉÏͼÖУ¬×ó±ßÊÇÕý³£ÔËÐеÄģʽ£ºËùÓжÁд²Ù×÷ͨ¹ýVIP1½øÈëµ½Master½Úµã£»Slave½ÚµãµÄ»áÁ¬½Óµ½VIP2£¬Í¨¹ý´ËIPÖ§³ÖÖ»¶Á²Ù×÷£»Streaming
Replicationͨ¹ýeth2½øÐÐÁ½½ÚµãµÄÊý¾Ýͬ²½¡£ÓÒ±ßÊÇMaster¹ÊÕÏʱµÄģʽ£ºÔSlave»ápromote³ÉΪMaster½Úµã£»VIP1Çл»µ½node2¼ÌÐøÌṩ·þÎñ£»VIP2Çл»µ½node2¼ÌÐøÌṩ·þÎñ¡£
ÔÚ´Ë´¦£¬ÏµÍ³´Ónode1Çл»µ½node2ÓжàÖÖ¿ÉÄÜÐÔ¡£
1. Master½Úµãͨ¹ýpacemaker¿ØÖÆÈËΪ½øÐÐSwitchoverÇл»¡£ÕâÖÖÇé¿öÏÂÖ÷±¸Ä£Ê½»á½øÐе÷»»£¬²¢ÇÒ¹ý³ÌÖпÉÒÔ±£Ö¤ËùÓÐMaster½ÚµãÖеÄÊý¾Ý»á¸´ÖƵ½SlaveºóÔÙ½øÐÐnode2ÉϵÄpromote²Ù×÷¡£Òò´Ë£¬Êý¾Ý¿âÖÐËùÓеÄÊÂÎñ¶¼ÊÇÍêÕûµÄ£¬ÇÒ²»»á³öÏÖÈκÎÊý¾Ý¶ªÊ§¡£ÕâÖÖÇé¿ö´ó¶àÓÃÓÚÓ²¼þÐèÒª½øÐÐÖ÷¶¯Î¬»¤Ê±¡£
2. Master½ÚµãÒâÍâ³öÏÖ¹ÊÕÏʱ£¬½«½øÐÐFailover¡£ÓÉÓÚPostgreSQLÔÚË«½ÚµãÍÆ¼öʹÓõÄÊÇasyncģʽ£¬Òò´ËÈç¹ûMaster½Úµã¹ÊÕÏʱ»¹ÓÐÊý¾ÝûÀ´µÃ¼°¸´ÖƵ½Slave¡£ÕâЩÊý¾Ý½«¶ªÊ§£¬µ«ÓÉÓÚPostgreSQLµÄStreaming
ReplicationÊÇÒÔÊÂÎñΪµ¥Î»µÄ£¬Òò´ËÊý¾Ý¿âµÄÊÂÎñÒ»ÖÂÐÔÊÇ¿ÉÒԵõ½±£Õϵ쬾ø¶Ô²»»á³öÏÖ±¸¿âÖÐij¸öÊÂÎñÖ»»Ö¸´µ½Ò»°ëµÄÇé¿ö¡£
µ±Ç°ÓÐÒ»¸ö±È½ÏÑÏÖØµÄÎÊÌ⣬¾ÍÊÇÈçÉÏͼËùʾ£¬Çл»ºónode1Èç¹ûÏëÒªÖØÐ³ÉΪÖ÷½Úµã£¬½«ÐèÒªÖØÐ½øÐÐÈ«Á¿µÄÊý¾Ý¸´Öƻָ´¡£ÕâÊÇÒòΪMaster¹ÊÕÏʱÈç¹ûÓÐÊý¾Ýû¸´ÖƵ½Slave£¬MasterµÄ×îºóÒ»¸öÊÂÎñʱ¼ä½«±ÈSlaveÖеÄÊÂÎñʱ¼ä¸üУ¨ÈçMaster×îºóÒ»¸öÊÂÎñºÅΪ1001£¬µ«SlaveÖеÄÊÂÎñÖ»»Ö¸´µ½999£©¡£´ËʱSlave½Úµãpromote³ÉΪеÄMasterºó£¬ËùÓÐеIJÙ×÷½«ÓÉ999ºÅÊÂÎñµÄ½á¹ûΪ»ù´¡¡£Ò²¾ÍÊÇ˵ÔMasterÖеÄ1000¼°1001ÊÂÎñËù´¦ÀíµÄÊý¾Ý½«²»¿É»Ö¸´¡£ÓÉÓÚÔÚµ±Ç°Éè¼ÆÖÐÊý¾Ý¿âÖÐÒѾÌá½»µÄÊÂÎñ²»Ö§³ÖÖ±½Ó»ØÍË£¬ËùÒÔ£¬Èç¹ûÄãµÄÊý¾Ý¿âµ½´ïTB¼¶±ð£¬Õ⽫ÐèÒª6~7Сʱ¡£
µ«Õâ¸öÇé¿öºÜ¿ì½«»á±»¸ÄÉÆ¡£PostgreSQL9.5½«ÎªÓû§Ìṩpg_rewind¹¦ÄÜ¡£µ±Master½ÚµãFailoverºó£¬ÔMaster½Úµã¿ÉÒÔͨ¹ýpg_rewind²Ù×÷ʵÏÖ¹ÊÕÏʱ¼äÏߵĻØÍË¡£»ØÍ˺óÔÙ´ÓеÄÖ÷¿âÖлñÈ¡×îеĺóÐøÊý¾Ý¡£Òò´Ë£¬ËäȻ֮ǰûÓÐÌá½»µÄÊÂÎñÓÉÓÚACIDÔÔòÎÞ·¨ÖØÐÂʹÓ㬵«ÔMasterµÄÊý¾ÝÎÞÐë½øÐÐÖØÐÂÈ«Á¿³õʼ»¯¾Í¿ÉÒÔ¼ÌÐø½øÐÐStreaming
Replication£¬²¢×÷ΪеÄSlaveʹÓá£
Corosync + Pacemaker M/S »·¾³ÅäÖÃ
ÒÔÏÂÄÚÈÝÖнØÍ¼À´×ÔÓÚhttp://clusterlabs.org/wiki/PgSQL_Replicated_Cluster¡£
Corosync + Pacemaker M/SÅäÖû·¾³×¼±¸
RA£ºResource Agent×ÊÔ´´úÀí£¬PostgreSQL×îеÄRA¿ÉÒÔͨ¹ýhttps://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/pgsqlÏÂÔØ¡£Èç¹ûÄã·¢ÏÖÕâ¸öRA²»·ûºÏÄãµÄÐèÇó£¬Ò²¿ÉÒÔ×ÔÐиÄд¡£
²Ù×÷ϵͳ°æ±¾£ºFedora19¼°ÒÔÉÏ¡¢Red Hat Enterprise Linux 7¼°ÒÔÉÏ¡¢SUSE
Linux Enterprise Server 11 SP3¼°ÒÔÉÏ¡£
Êý¾Ý¿âÒªÇó£ºPostgreSQL9.1¼°ÒÔÉÏ£¬ÓÉÓÚPostgreSQL 9.1ÒÔÉϲÅÖ§³ÖStreaming
Replication£¬Òò´Ë£¬±ÈÕâ¸ö°æ±¾µÍµÄÊý¾Ý¿âÎÞ·¨ÊµÏִ˹¦ÄÜ¡£
Á½Ì¨·þÎñÆ÷ÅäÖÃÏàͬµÄNTPʱ¼äÔ´¼°ÏàͬµÄÊ±Çø¡£
ͨ¹ýyum¡¢zypper°²×°pacemaker£¨Ö÷ÒªÓÃÓÚHA×ÊÔ´¹ÜÀí£©¡¢corosync£¨HAÐÄÌøÍ¬²½¿ØÖÆ£©¡¢pcs3£¨HAµÄÃüÁîÐÐÅäÖù¤¾ß£©¡£Í¨¹ýyum¡¢zypper»òÈÎºÎÆäËû·½Ê½°²×°PostgreSQLÊý¾Ý¿â£¬°²×°Ê±Îñ±ØÈ·ÈÏÆäpg_ctlÃüÁî¡¢psqlÃüÁî¡¢dataĿ¼µÄ´æ·ÅλÖã¬ÒòΪÅäÖÃʱҪÓõ½¡£

PostgreSQL Streaming ReplicationÅäÖÃ
ÔÚnode1Öгõʼ»¯PostgreSQLÊý¾Ý¿â¡£

¶ÔÆäpostgresql.confÎļþ×öÈçÏÂÐ޸ġ£

×¢Ò⣺wal_level = hot_standby£¬Ê¹µÃÈÕÖ¾Ö§³ÖStreaming Replication£»archive_mode
= on£¬Æô¶¯¹éµµÄ£Ê½£»archive_command = 'xxx'£¬Ö¸¶¨¹éµµµÄ±£´æ·½·¨£»hot_standby
= on£¬±¸¿âÆô¶¯Îªstandbyģʽʱ¿ÉʵÏÖÖ»¶Á²éѯ£»ÆäËû²ÎÊýÖ÷ÒªÓÃÓÚÐÔÄܼ°ÑÓ³ÙµÄÉ趨¡£
½«dataĿ¼ÏµÄpg_hba.confÎļþ×öÈçÏÂÐ޸ġ£
×¢Ò⣺ÕâÑùÅäÖúó£¬ËùÓÐ192.168.x.xÍø¶ÎµÄIP¶¼½«¿ÉÒÔÎÞÃÜÂë¶Ô´ËÊý¾Ý¿â½øÐзÃÎÊ£¬°²È«ÐÔ¿ÉÄܻήµÍ¡£Òò´Ë£¬Ö»×÷ΪÁ·Ï°Ê¹Óã¬ÔÚÉú²ú»·¾³ÖÐÇëÑϸñ¿ØÖÆIP¡£ÈçÖ¸¶¨Ö»trustijIP¿ÉÒÔд³É192.168.100.123/32¡£
ÅäÖÃÍê³Éºó£¬Æô¶¯node1ÉϵÄPostgreSQL¡£

ÔÚnode2½øÐÐÊý¾Ý³õʼ»¯¡£

×¢Ò⣺ͨ¹ýpg_basebackupÃüÁ´Ónode1Öн«ËùÓÐÊý¾Ý¿âÖеÄÊý¾Ý¶¼Í¬²½µ½/var/lib/pgsql/dataÈ¥¡£
Êý¾ÝbasebackupÍê³Éºó£¬ÔÚnode2ÖеÄdataĿ¼Ï½¨Á¢recovery.confÎļþ²¢Â¼ÈëÒÔÏÂÄÚÈÝ¡£

×¢Ò⣺primary_conninfoÖ¸¶¨ÁËÖ÷·þÎñÆ÷ËùÔÚµÄλÖá¢replicateËùʹÓõÄÓû§Ãû¡£ÓÉÓÚÎÒÃÇÔÚpg_hba.confÖÐʹÓÃtrust·½Ê½£¬ËùÒÔÔڴ˲ÎÊýÖв»ÐèÒª¼ÓÈëpassword¡£
ÅäÖÃÍê³Éºó£¬Æô¶¯node2ÉϵÄPostgreSQL£¬×¼±¸¼ì²éͬ²½Ð§¹û¡£

Èç¹ûÔÚnode1ÖÐͨ¹ýpsqlÃüÁîµÇ¼Êý¾Ý¿âºó¿ÉÒԵõ½ÒÔÏÂÐÅÏ¢£¬Ö¤Ã÷Êý¾Ý¿â¶ËµÄReplicationÒÑÔËÐÐÕý³£¡£

×Ô´Ë£¬PostgreSQLµÄStreaming ReplicationÅäÖÃÍê³É£¬Á½¸öÊý¾Ý¿âµÄÊý¾Ý½«½øÐгÖÐø¸´ÖÆ¡£
×¢Ò⣺ÒÔÉÏÁ½¸ö·þÎñÆ÷ÒѾÍê³ÉStreaming ReplicationÅäÖã¬ÔÚÅäÖÃHAǰÇ뽫Á½¸ö·þÎñÆ÷ÉϵÄPostgreSQL¶¼Í£Ö¹¡£ÒòΪÔÚHA¼Ü¹¹ÖУ¬ËùÓÐ×ÊÔ´¶¼Ó¦¸ÃÊÇÓÉHAÈí¼þ½øÐйÜÀíµÄ£¬ËùÒÔÓë´ËͬʱҲÇëÈ·ÈÏϵͳÆô¶¯Ê±PostgreSQL²»»á×Ô¶¯Æô¶¯£¨Äã¿ÉÒÔͨ¹ýchkconfig¼ì²é£©¡£

Corosync + Pacemaker HA »ù´¡ÅäÖÃ
corosyncÅäÖÃÎļþÖ»ÓÐÒ»¸ö£¬/etc/corosync/corosync.conf¡£

ÎÒÃÇ¿ÉÒÔ¿´µ½£¬µ±Ç°quorumÖÐexpected_votesΪ2£¬ÕâÊÇÒòΪÎÒÃÇʹÓÃ2½Úµã¡£totemÖÐÓÐbindnetaddr:192.168.1.0¼°mcastaddr:
239.255.1.1£¬ÕâÀï˵Ã÷corosync»áʹÓñ¾·þÎñÆ÷ÉÏ192.168.1.XÍø¶ÎµÄIP×÷ΪÐÄÌø¡£´Ë´¦×¢Ò⣬²»ÐèҪдÃ÷´ËIPµÄÏêϸµØÖ·£¬ÏµÍ³»á×Ô¶¯·¢ÏÖ¡£Í¨¹ýscpÃüÁ´ËÎļþ¸´ÖƵ½node2ÖÐÏàͬµÄĿ¼²¢±£Ö¤ÆäȨÏÞÒ»Ö¡£
½ÓÏÂÀ´¾Í¿ÉÒÔÔÚÁ½¸ö½ÚµãÖÐÆô¶¯corosyncÁË¡£ÒÔÏÂÊÇϵͳÔÚFedora
19¡¢RHEL7¡¢SUSE12ºóµÄ·þÎñÆô¶¯ÃüÁî¡£Èç¹ûÄãʹÓõÄÊǵͰ汾²Ù×÷ϵͳ£¬ÇëÓÃ/etc/init.d/corosync
start»òservice corosync start ¡£

pacemakerĬÈÏÇé¿öÏÂÊÇÎÞÎÛȾµÄ£¬µ«ÎªÁ˱£Ö¤HA³õʼ״̬ÎÒÃÇ»á½øÐÐÒÔϲÙ×÷¡£´Ë²Ù×÷»áÇå¿ÕËùÓÐHA×ÊÔ´µÄÅäÖá£ËüÔÚÁíÍâһЩÇé¿öÏÂҲʮ·ÖʵÓã¬ÈçÓÐʱÎÒÃǻᷢÏÖÁ½¸ö½ÚµãHAÆô¶¯Ê±×ÊÔ´ÐÅÏ¢²»Í¬²½¡£´ËʱÎÒÃÇ¿ÉÒÔÏÈÔñ¶¨Ò»¸ö¿ÉÐŵĽڵ㣬Ȼºó½«ÁíÒ»½ÚµãÉϵÄcibÎļþÇå¿Õ£¬È»ºó½øÔÙÆô¶¯pacemaker£¬ÕâÑùнڵã¾Í»á×Ô¶¯Í¬²½ÏÖÓнڵãµÄËùÓÐÅäÖá£

Pacemaker×ÊÔ´ÅäÖÃ
ͨ¹ýpcsÃüÁîÐй¤¾ß½øÐÐHA×ÊÔ´µÄÅäÖá£pcsÃüÁîÐпÉÒÔÐÖúÉú³ÉÃûΪconfig.pcsµÄÅäÖýű¾£¬ÒÔ½øÐÐ×îºóµÄHAÅäÖõ¼Èë¡£Ê×ÏÈ£¬ÎÒÃǽøÐÐÒ»¸öÈ«¾ÖÐÅÏ¢µÄÅäÖã¬Ö¸Ã÷ÓÉÓÚµ±Ç°ÊÇ2½Úµã£¬ËùÒÔºöÂÔno-quorum-policy£»Ä¬ÈϵÄresource-stickinessΪINFINITY£¬¼´ÈκÎ×ÊԴĬÈ϶¼ÊÇÓëÆäËû×ÊÔ´¿É¹²Í¬ÔËÐеģ»?ĬÈϵÄmigration-thresholdΪ1£¬¼´ÈκÎÇé¿öÏÂmigrationʱ¶¼»áÖØÊÔÒ»´Î¡£

×¢Ò⣺stonith-enabled="false"±íʾ²»Ê¹ÓÃÈκεçÔ´¿ØÖÆÉ豸£¬Õâ¸öÇé¿ö²»½¨ÒéÔÚÉú²úÖÐʹÓá£ÊìϤRHEL¼¯ÈºµÄͬѧ¿ÉÒÔÈÏΪStonithµÈͬÓÚFenceÉ豸¡£
ÅäÖÃVIP1¼°VIP2£¬ÒÔ¼°pgsql×ÊÔ´¡£


vip-master(VIP1)¼°vip-rep(VIP2)Ïà¶Ô±È½ÏºÃÀí½â¡£¶øÔÚpgsql×ÊÔ´ÖУ¬Èç¹û´ó¼ÒÓÐÊìϤLinux¼¯ÈºµÄ»á·¢ÏÖ£¬Ò»°ãÇé¿öÏÂHAÖÐÌí¼ÓÓ¦ÓÃ×ÊÔ´¶¼»á¼ÓÈëÒ»¸ö´øÓÐstart/stop/statusµÄ½Å±¾¡£¶ø´Ë´¦ÊÇͨ¹ýÒ»¸öagentʵÏÖ£¬ÎÒÃÇÖ»ÒªÅäÖúÃPostgreSQLµÄpgctl¡¢psql¡¢pgdataµÄÎļþ»òĿ¼λÖü´¿É£¬´¦ÀíÊ®·Ö·½±ã¡£Ö÷ÒªÒòΪPostgreSQL
RAÒѾ°üº¬start|stop|status|monitor|promote|demote|notifyµÄ²Ù×÷½Å±¾£¨https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/pgsql£©¡£¸Ðл¿ªÔ´£¬¸Ðл¹±Ï×Õß°É£¬ÕâÀïÍ·ÓÐ2070ÐдúÂ룬Ïà±ÈÎÒÒÔǰ×Ô¼ºÐ´µÄÒª¾«ÃîµÃ¶à¡£
½«ÒÔÉϵÄIP¼°pgsql×ÊÔ´½øÐйØÁª£¬ÕâÒ²ÊÇpacemaker×ÃîµÄµØ·½¡£ÎÒÃÇ¿ÉÒÔ¿´µ½£¬Ê×ÏÈ£¬ÓÐÒ»¸ö¡°resource
master¡±ÎÒÃÇÃüÃûΪmsPostgresql£¬pgsqlÊôÓÚÕâ¸öMasterģʽ×ÊÔ´¡£Õâ¸öÄ£Ð͵Ä×ÊÔ´£¬»ùÓÚcloneÄ£ÐÍ£¬½«»áÔÚÁ½¸ö½ÚµãͬʱÆô¶¯¡£È»ºó£¬½¨Á¢ÁËÒ»¸ömaster-group£¬½«vip-master¼°vip-rep¼Óµ½Õâ¸ö×éÖС£½ÓÏÂÀ´£¬constraintcolocationÖ¸¶¨ÁËmaster-groupÖеÄ×ÊÔ´£¨vip-master¼°vip-rep£©ÇãÏòÓÚÓëmsPostgresqlµÄMaster½ÚµãÔËÐÐÔÚÒ»Æð¡£×îºó£¬orderpromote¼°order
demote¸ºÔð¹ÜÀí½ÚµãµÄÆô¶¯Ë³Ðò¡£

×¢Ò⣺Master½Úµã»áÓÉRA×Ô¶¯Ê¶±ð£»msPostgresql½øÐÐpromoteÒÔºó²Å»á½øÐÐmaster-groupµÄIP¹Ò½Ó£»Í¬Ê±£¬ÔÚ½øÐÐdemoteʱҲÊÇÖ»ÓеÈmsPostgresqlÍê³ÉÍ£¿âºó²Å½øÐÐmaster-groupµÄIP¶Ï¿ª´¦Àí¡£

´Ë´¦µÄconfig.pcsÊÇÓÉÇ°ÃæµÄpcsËùÉú³É£¬Í¨¹ýcrm_monÄã¿ÉÒÔ¿´µ½ËùÓеÄ×ÊÔ´Çé¿ö¡£

×Ô´ËËùÓÐÅäÖýáÊø£¬Äã¿ÉÒÔ½«node1ÖÐPostgreSQLµÄdataĿ¼mvµ½ÆäËûµØ·½¿´¿´Çл»µÄЧ¹û£¬²»ÔÙ׸Êö¡£
¹ØÓÚÅÅ´í£¬ËùÓÐHAÈÕÖ¾ÐÅÏ¢»á±£´æÔÚ/var/log/messageÖС£Èç¹ûϵͳÓÐÎÊÌ⣬¿ÉÒÔͨ¹ý´ËÈÕÖ¾½øÐзÖÎö¡£µ«ÓÐÒ»µãºÜÖØÒª£¬½¨Òé´ó¼ÒÔÚÅäÖÃHAǰһ¶¨ÒªÈ·ÈÏNTP·þÎñÊÇ·ñÕý³££¬±£ÕÏÁ½¸ö·þÎñÆ÷ʱ¼ä²»Òª²î¾àÌ«´ó¡£²»È»ÅÅ´í»áºÜÂé·³£¬»¹»áÓпÉÄܵ¼Ö¼¯ÈºµÄÆäËûÎÊÌâ¡£
¹ýÈ¥Á½Ä꣬ÕâÑùµÄ¼¯Èº¼Ü¹¹ÒѾÔںܶàÆóҵʹÓã¬Êý¾ÝÁ¿¶àÔÚ20Gµ½1TÖ®¼ä¡£PGÊÇÒ»¸öÓÃÓÚOLTPµÄϵͳ£¬µ±Ç°ÎÒËùʵʩµÄÆóÒµ´ó¶àÊÇ´«Í³ÐÐÒµ¡£´ó²¿·ÝÓû§Ö÷ÒªÊÇ´ÓOracleÇ¨ÒÆÀ´µÄ¡£ÔÚ´óÊý¾Ý¼°·ÖÎö·½Ã棬»áÏÈÓÃGreenplum»òHadoopµÈ½øÐд¦Àí£¬ÕâʱPostgreSQLÒ²¿ÉÒÔʹÓÃFDW¹¦ÄܽøÐжԽӡ£
PostgreSQL Syncģʽµ±Ç°µÄÎÊÌâ
Ç°ÃæÌáµ½µ±Ç°PostgreSQLÔÚ2½ÚµãÇé¿öÏÂÍÆ¼öʹÓõÄÊÇasyncµÄģʽ£¬ÄÇsyncÊDz»ÊDz»Ö§³Ö£¿²»Êǵ쬵±Ç°PostgreSQLÖ§³Ösyncģʽ£¬¼´Ê¹2½ÚµãÒ²¿ÉÒÔÅäÖ㬵«»áÓÐÒÔÏÂÎÊÌ⣺
ÓÉÓÚsyncͬ²½Ä£Ê½ÒªÇóMasterÔÚSlaveÊý¾ÝдÈë³É¹¦ºó²Å½áÊøÊÂÎñµÄCommit²Ù×÷£¬Òò´ËÐÔÄÜ»áÊܵ½Ó°Ïì¡£
Èç¹ûϵͳÔËÐйý³ÌÖÐslave³öÏÖ¹ÊÕÏ£¬Ö÷½ÚµãÒ²½«Êܵ½Ó°Ïìʹϵͳ³öÏÖ¹ÊÕÏ£¬ÔÚHAÏÂÕâÒ²»áFailover¡£Pacemakerµ±Ç°×îеÄPostgreSQLRAÒ²»¹Ã»Óнâ¾ö´ËÎÊÌâ¡£
Èç¹ûÐèҪʹÓÃsyncģʽµÄStreaming Replication£¬ÎÒ½¨Òé´î½¨1Ö÷2±¸µÄÄ£ÐÍʵÏÖ£¬¶øÕâ¸öÄ£ÐÍÏÂPacemaker»¹Ã»ÓÐÌṩ3½ÚµãµÄʵÏÖ·½°¸£¬Éдý¸Ä½ø¡£
×îºó¼òµ¥½éÉÜһϡ°PostgreSQLÖйúÓû§»á¡±£¬PostgresÖйúÓû§»áÊÇÒ»¸ö·ÇÓªÀûÍÅÌ壬ÖÂÁ¦ÓÚΪÖйúµÄPostgreSQLÓû§·þÎñ£¬µ±Ç°¸÷QQ¼°Î¢ÐÅȺÒѾÓг¬¹ý4000È˹æÄ££¬ÔÚ¹ýÈ¥µÄ2ÖÜÎÒÃÇÔÚÈ«¹ú9¸ö³ÇÊоٰìÁËÃûΪ¡°ÏóÐÐÖйú
Let'sPostgres¡±µÄÏßϼ¼ÊõɳÁú£¬Í¬Ê±Ö§³ÖÁËÏã¸Û¼°Ì¨ÍåµØÇøµÄPostgreSQL¼¼Êõ»î¶¯¡£
Q&A
Q1£ºÅųýÄêÏÞ£¬PGÏà¶ÔMySQLºÍOracleÓÐɶÓÅÊÆ£¿
A1£ºPGÓкܶàMySQLûÓеŦÄÜ£¬¾ÍÒÔO2OÐÐҵΪÀý£¬PGÖ±½ÓÌṩPostGIS£¬¿ÉÒÔÓÐЧµØÔÚÊý¾Ý¿âÖÐͨ¹ýSQL½øÐи´ÔӵĶ¨Î»²éѯ²¢ÓëÒµÎñÖ±½Ó¹ØÁª£¬¸ü¶à¹¦ÄÜ»¶ÓÏßϽ»Á÷¡£
Q2£º½ðÈÚÆóÒµÖÐÓÃasync¸´ÖÆ£¬ÔõôӦ¶ÔÊý¾Ý¶ªÊ§£¿¿¿¶ÔÕËô£¿
A2£ºÊ×ÏÈ£¬½ðÈÚÐÐÒµÖÐÈç¹ûÒªÇó100%Êý¾Ý²»¶ªÊ§£¬Ó¦¸ÃʹÓÃsync¶ø²»ÊÇasync£¬Õâ¸ö¹¦ÄÜPostgreSQLÊÇÖ§³ÖµÄ£¬Ö»ÊÇÒªÓÃ3½Úµã·½°¸¡£µ±Ç°ÔÚÕâ¸ö·½°¸Ï½øÐÐHAÇл»Ò²ÊÇ¿ÉÒԵģ¬Ö»ÊÇCorosync+PacemakerûÓÐÖ±½ÓÖ§³Ö£¬ÐèÒªÎÒÃǶÔRA½øÐи½¼ÓµÄ½Å±¾¿ØÖÆ¡£
Q3£ºthresholdÉèÖÃΪ1ʱ£¬³¢ÊÔ1´Î£¬Èç¹ûÔÙÓÐÎÊÌâ¾ÍÖ±½ÓÇл»ÁË¡£ÉèÖÃΪ2ʱ£¬ 2´Î³¢ÊÔ£¬Õâ¸ö¼ÆÊýÆ÷²»»áÔڳɹ¦ºó»Ö¸´ÔÖµ£¬¸Ã²âÊÔ½á¹ûÊÇ·ñÕýÈ·£¿
A3£ºÊǵģ¬Òâ˼¾ÍÊdz¢ÊÔ1´Î£¬ÔÚPacemakerÕâЩֵ¶¼ÊÇÓÐÏÞʱµÄ£¬³¬Ê±¾Í»á»Ö¸´ÔÖµ£¬Äã¿ÉÒÔͨ¹ýclean²Ù×÷¶ÔÕâ¸ö½ÚµãÉϵļÆÊýÆ÷½øÐÐÊýÖµ»Ö¸´²Ù×÷¡£
Q4£ºMySQLµÄbinlog´¦ÀíºÍPGµÄÓÐÊ²Ã´Çø±ð£¿
A4£ºMySQLÎÒÖ»Óùý4¼°ÒÔϰ汾£¬¶Ôbinlog²»ÊÇÊ®·ÖÁ˽⣬Ó빫˾MySQL´óÅ£ÌÖÂÛÖУ¬ÎҸоõÕâÁ½¸ö·½Ê½ÊǺܽӽüµÄ£¬¶¼ÊÇʹÓÃÈÕÖ¾½øÐлָ´¡£µ«PostgreSQLµÄ²Ù×÷ÖлáÒÔTupleΪµ¥Î»£¬Õâ¸ö¿ÉÄÜÊÇÒ»¸örow£¬ÉõÖÁ¾ÍÊÇij¸örowÖб»Ð޸ĹýµÄ1¸ö×ֶεÄÖµ¡£ÓÐÒ»¸öÌÖÂÛ½á¹ûÊÇPGµÄStreamingReplicationÁ£¿â¸üϸ¡£
Q5£º3½Úµã·½°¸Ð´³É¹¦2¸ö¾Í·µ»Ø£¬»¹ÊÇ,3¸ö¶¼³É¹¦²Å·µ»Ø£¿
A5£º3½ÚµãÇé¿öÏ£¬ÏµÍ³ÖÐÓÐ2¸ö½ÚµãÊÇͬ²½£¬µÚ3¸öÊÇÒì²½£¬ËùÒԳɹ¦2¸ö¾Í·µ»Ø¡£
Q6£ºPGµÄHA³ýÁ˽ñÌì·ÖÏíµÄ»¹ÓÐÆäËû·½°¸Âð£¿
A6£ºPGµÄHA»¹¿ÉÒÔÓÃLiveKeeper¡¢Î¢ÈíµÄMSCSµÈ·½°¸£¬¶ÔÓÚHAÀ´½²PG¾ÍÖ»ÊÇÒ»¸ö·þÎñ£¬ËùÒÔÈκÎHAÈí¼þ¶¼¿ÉÒÔÓëPG¶Ô½Ó£¬µ«Èç¹ûÒª½øÐÐStreamingReplicationµÄÇл»¾ÍÒª×Ô¼ºÐ´½Å±¾ÁË?
Q7£ºÎҸоõPGÕâ¸öHAÏà±ÈMySQL×Ô´øµÄÖ÷´Óû¶à´óÁÁµã¡£PGÓÐÀàËÆmysql-proxyÕâÑùµÄ¸ºÔؾùºâÖмä¼þ¼°MMAÕâÀà½â¾ö·½°¸Âð£¿Ö®Ç°Ìý˵PGÔÚ¼¯ÈºÕâ¿é²»ÊǸüºÃ£¬·½°¸¸´ÔÓÇÒÐÔÄÜËðʧ´ó£¬ÊDz»ÊÇPG»¹ÊǸüÊʺϵ¥»ú£¿
A7£ºµ±Ç°PGÒµ½ç¿ÉÒÔͨ¹ýPGPoolʵÏÖ1¸öMaster½øÐжÁд£¬n¸öSlave½øÐÐÖ»¶Á¸ºÔؾùºâµÄ·½°¸£»PG·Ö²¼Ê½¼¯Èºµ±Ç°·½°¸µÄPostgres-X2£¬È·Êµ¸´ÔÓ£¬ÎÒÃÇÕâ·½ÃæÒ²ÕýÔÚŬÁ¦£»µ¥»úµÄÕâ¸öÎÊÌâÉÏ£¬ÎÒÃÇÓкܶàPGÓû§»áÑ¡Ôñͨ¹ýÓ¦ÓóÌÐò×Ô¶¨Òå½øÐзֿ⼯ȺģÐÍ£¬±Ï¾¹ÔÚÒªÇóǿһÖÂÐÔÓÖûÓÐInfiniBand»ò¸ü¸ß¼¶µÄÍøÂçµÄÇé¿öÏ£¬ÊÂÎñºÍÑÓ³Ù¶¼²»ºÃÔÚ´«Í³¼¯ÈºÖнâ¾ö¡£
Q8£ºPGÓÐsharding¹¦ÄÜÂð£¿ÄÜ·ñ¼òµ¥½éÉÜһϡ£
A8£º×î½üPG³öÏÖÁËÒ»¸öÃûΪpg_shardµÄµÚÈý·½×é¼þ£¬¿ÉÒÔ¶ÔÊý¾Ý¿âÖеÄÒ»¸öÌØ¶¨µÄ±í½øÐÐsharding£¬¿ÉÒÔÀ©Õ¹µ½64̨·þÎñÆ÷£¬µ«²»±£Ö¤´Ë±íµÄÊÂÎñ£¬Ò»Ð©·ÖÎö³¡¾°¿ÉÒÔ¿¼Âdz¢ÊÔ¡£
Q9£ºÄÜ·ñ¼òµ¥¶Ô±ÈÒ»ÏÂPGµÄHA·½°¸£¿
A9£º Corosync + Pacemake£¬Ö§³ÖReplicationģʽ£¬ÔÚLinuxÏÂÕâÊÇÎÒ¸öΪ×îÍÆ¼öµÄ·½°¸£¬¹²Ïí´æ´¢Í¬ÑùҲûÓÐÎÊÌ⣻ÔRHELÖеÄRHCS£¬ÅäÖüòµ¥£¬Èç¹ûÓй²Ïí´æ´¢£¬LinuxÏÂÕâ¸ö·½°¸×î·½±ã£¬µ«Òª×¢ÒâRHCSÊÇÒªÇ󸶷ÑʹÓõģ»LiveKeeper£¬ÅäÖÃÏà¶Ô¸´ÔÓһЩ£¬Èç¹ûÒªÖ§³ÖReplicationÐèҪд±È½Ï¸´ÔӵĽű¾£»Î¢ÈíMSCS£¬Windowsƽ̨±Ø±¸£¬Öйú»¹ÕæÓм¸¸öÓû§ÊÇÕâÑùÓõģ»VCS£¬¿çWindows¼°Linuxƽ̨µ«Í¬ÑùÖ»½¨ÒéÔÚÓй²Ïí´æ´¢Çé¿öÏÂʹÓá£
Q10£º PGµÄ±í·ÖÇøºÍMySQLµÄ±í·ÖÇø²î±ðÔÚÄÄ£¿¸÷×ÔµÄÓŵãÔÚÄÄ£¿ÎÒÓ¡ÏóÖÐPG·ÖÇø±í¶ÔÍâ»áÏÔʾ³Éµ¥¶ÀµÄÒ»¸ö·ÖÇø±í£¬·ÖÇø¶àÁ˺ÜÄÑ¿´¡£
A10£ºPGĬÈϵıí·ÖÇø»ùÓÚ¶ÔÏóÊý¾Ý¿â½á¹¹µÄ±í¼¯³É£¬Í¨¹ý´¥·¢Æ÷½øÐÐÊý¾Ýµ÷¶È£¬±í´óÁËÒÔºóÐÔÄܺܲ¾Ý˵ÔÚ9.6ÒԺ󣨵±Ç°9.4£©»á¸ÄÉÆ¡£ÔÚ´Ë´ò¸ö¹ã¸æ£¬·ÖÇø±íÐÔÄÜÔÚ°¢ÀïÔÆµÄPPASÖÐÒѾµÃµ½½â¾ö£¬2~1000¸ö±í·ÖÇøÐÔÄܱí±íÏֺ㶨£¬²»»áÒòΪ±í·ÖÇøÔ½À´Ô½¶àµ¼ÖÂÐÔÄÜÆ¿¾±¡£
|