{"id":5341,"date":"2014-12-22T10:00:47","date_gmt":"2014-12-22T01:00:47","guid":{"rendered":"http:\/\/www.techscore.com\/blog\/?p=5341"},"modified":"2018-11-14T16:33:49","modified_gmt":"2018-11-14T07:33:49","slug":"mysql%e3%81%a8posqgresql%e3%81%aepitr%e3%81%ab%e3%82%88%e3%82%8b%e3%83%90%e3%83%83%e3%82%af%e3%82%a2%e3%83%83%e3%83%97%ef%bc%86%e3%83%aa%e3%82%ab%e3%83%90%e3%83%aa%e3%81%ae%e9%81%95%e3%81%84%ef%bc%88","status":"publish","type":"post","link":"https:\/\/www.techscore.com\/blog\/2014\/12\/22\/mysql%e3%81%a8posqgresql%e3%81%aepitr%e3%81%ab%e3%82%88%e3%82%8b%e3%83%90%e3%83%83%e3%82%af%e3%82%a2%e3%83%83%e3%83%97%ef%bc%86%e3%83%aa%e3%82%ab%e3%83%90%e3%83%aa%e3%81%ae%e9%81%95%e3%81%84%ef%bc%88\/","title":{"rendered":"Mysql\u3068PostgreSQL\u306ePITR\u306b\u3088\u308b\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\uff06\u30ea\u30ab\u30d0\u30ea\u306e\u9055\u3044\uff08\u307e\u3068\u3081\uff09"},"content":{"rendered":"

\u3053\u3093\u306b\u3061\u306f\u3001\u843d\u5408\u3067\u3059\u3002
\n\u3053\u306e\u8a18\u4e8b\u306f\u3001TECHSCORE Advent Calendar<\/a> \u306e22\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n

2014\u5e74\u3082\u7d42\u308f\u308d\u3046\u3068\u3057\u3066\u3044\u307e\u3059\u306d\u3002
\nPostgreSQL8.x\u7cfb\u306f\u3001\u4eca\u5e74EOL\u3092\u8fce\u3048\u307e\u3057\u305f\u3002\u3044\u307e\u307e\u3067\u3042\u308a\u304c\u3068\u3046\u3054\u3056\u3044\u307e\u3057\u305f\u3002
\n\uff08\u3067\u3059\u306e\u30679.x\u7cfb\u3067\u5b9f\u65bd\u3057\u305f\u8a18\u4e8b\u5185\u5bb9\u3068\u306a\u3063\u3066\u3044\u307e\u3059\u3002\uff09
\n\u305d\u3057\u3066\u3001\u3044\u307e\u3069\u304d\u306fMysql\u3068\u3044\u3046\u3088\u308aMariaDB\u306b\u3057\u305f\u307b\u3046\u304c\u523a\u3055\u308b\u3093\u3067\u3059\u304b\u306d\uff1f
\n\u305d\u3082\u305d\u3082\u3001AWS\u3067\u30dd\u30c1\u30c3\u3068\u30d0\u30c3\u30af\u30a2\u30c3\u30d7&\u30ea\u30ab\u30d0\u30ea\u306a\u3093\u3066\u3057\u3061\u3083\u3046\u306e\u3067\u3001\u3042\u3093\u307e\u308a\u8208\u5473\u304c\u306a\u304b\u3063\u305f\u308a\u3067\u3057\u3087\u3046\u304b\uff1f\uff08\u7b11\uff09<\/p>\n

\u3055\u3066\u3001\u3001\u3001\u8a18\u4e8b\u30bf\u30a4\u30c8\u30eb\u306e\u3053\u3068\u3092\u30c0\u30e9\u30c0\u30e9\u3068\u30fb\u30fb\u30fb<\/p>\n

\u30aa\u30f3\u30e9\u30a4\u30f3\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u304a\u3055\u3089\u3044<\/h2>\n

\u307e\u305a\u3001PostgreSQL\u3068Mysql\u3067\u307f\u306a\u3055\u3093\u304c\u6700\u521d\u306b\u5b9f\u65bd\u3057\u305f\u3067\u3042\u308d\u3046\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3001<\/p>\n

\u5168\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30aa\u30f3\u30e9\u30a4\u30f3\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3092\u304a\u3055\u3089\u3044\u3092\u3057\u3066\u307f\u3088\u3046\u3068\u304a\u3082\u3044\u307e\u3059\u3002<\/p>\n

\u25cbPostgreSQL\u7de8<\/em><\/h3>\n

\u7c21\u5358\u3067\u3059\u306d\u3002<\/p>\n

$ su - postgres\r\n$ pg_dumpall | gzip > postgresqldump-all.gz\r\n<\/pre>\n
    \n
  • | gzip >\n
      \n
    • \u30b5\u30a4\u30ba\u304c\u5c0f\u3055\u3044\u3053\u3068\u306f\u4f55\u304b\u3068\u304a\u5f97\u306a\u306e\u3067\u3001gzip\u3057\u3066\u304a\u304d\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n
    • \u307b\u304b\u306e\u30aa\u30d7\u30b7\u30e7\u30f3\u304c\u7121\u3044\u304c\u30fb\u30fb\u30fb\uff1f\n
        \n
      • PostgreSQL\u306e\u5834\u5408\u306f\u3001\u81ea\u52d5\u7684\u306b\u3001\u8aad\u307f\u53d6\u308a\u4e00\u8cab\u6027\u6a5f\u80fd\u304c\u50cd\u304d\u307e\u3059\u3002<\/li>\n
      • \u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u53d6\u5f97\u4e2d\u3082\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3078\u306e\u30a2\u30af\u30bb\u30b9\uff08\u8aad\u307f\u53d6\u308a\uff0f\u66f8\u304d\u8fbc\u307f\uff09\u306f\u6291\u5236\u3055\u308c\u307e\u305b\u3093\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
        \u25cbMysql\u7de8<\/em><\/h3>\n
        \u30b9\u30c8\u30ec\u30fc\u30b8\u30a8\u30f3\u30b8\u30f3\u306bInnoDB\u3092\u5229\u7528\u3057\u3066\u3044\u308b\u3053\u3068\u304c\u524d\u63d0<\/em>
        \n \uff08MyISAM\u7b49\u306f\u4f7f\u3048\u306a\u3044\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u542b\u3093\u3067\u3044\u307e\u3059\uff09<\/em><\/p>\n
        $ mysqldump -u root --all-databases --single-transaction --events | gzip > mysqldump-all.gz\r\n<\/pre>\n
          \n
        • --all-databases\n
            \n
          • \u305d\u306e\u540d\u306e\u901a\u308a\u3001\u3059\u3079\u3066\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092dump\u3057\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n
          • --single-transaction\n
              \n
            • PostgreSQL\u3068\u540c\u3058\u3088\u3046\u306b\u8aad\u307f\u53d6\u308a\u4e00\u8cab\u6027\u6a5f\u80fd\u304c\u52b9\u304f\u3088\u3046\u306b\u306a\u308a\u3001\u30ed\u30c3\u30af\u3082\u3055\u308c\u307e\u305b\u3093\u3002\u3068\u306b\u304b\u304f\u4ed8\u3051\u3066\u304a\u3044\u3066\u640d\u306f\u306a\u3044\u3067\u3059\u3002<\/li>\n
            • dump\u3059\u308b\u524d\u306bBEGIN (transaction\u958b\u59cb) SQL\u3092\u4f7f\u3063\u3066dump\u3059\u308b\u306e\u3067\u3001 \u30ed\u30c3\u30af\u3055\u308c\u308b\u3053\u3068\u306a\u304fBEGIN \u767a\u884c\u6642\u70b9\u306edata\u3092dump\u3067\u304d\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n
            • --events\n
                \n
              • mysql\u30d0\u30fc\u30b8\u30e7\u30f35.1.8\u304b\u3089mysql\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306bevents\u30c6\u30fc\u30d6\u30eb\u304c\u8ffd\u52a0\u3055\u308c\u305f\u3053\u3068\u304c\u539f\u56e0\u3067\u3001
                \n--all-databases\u3092\u3064\u3051\u308b\u3068\u3001
                \n\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u3060\u3051\u304c\u9664\u5916\u3055\u308c\u308b\u306e\u3067warning\u304c\u3067\u307e\u3059\u3002\u3053\u308c\u3092\u6291\u6b62\u3059\u308b\u305f\u3081\u306b\u3064\u3051\u3066\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n
              • | gzip >\n
                  \n
                • \u30b5\u30a4\u30ba\u7e2e\u5c0f<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                  \u30dd\u30a4\u30f3\u30c8\u30fb\u30a4\u30f3\u30fb\u30bf\u30a4\u30e0\u30fb\u30ea\u30ab\u30d0\u30ea (PITR)<\/h2>\n
                  \u304a\u3055\u3089\u3044\u306f\u3044\u304b\u304c\u3060\u3063\u305f\u3067\u3057\u3087\u3046\u304b\uff1f\u30fb\u30fb\u30fb\u3055\u3066\u3001\u3053\u3053\u304b\u3089\u304c\u672c\u984c\u3067\u3059\u3002<\/p>\n
                  \u25cb\u57fa\u672c\u7684\u306a\u3053\u3068<\/h3>\n
                    \n
                  • \u305d\u3082\u305d\u3082PITR\u3063\u3066\uff1f\n
                      \n
                    • \u6307\u5b9a\u3057\u305f\u6642\u9593\u306b\u623b\u3059\u3053\u3068\u3067\u3059\u3002<\/li>\n
                    • \u307e\u305f\u306f\u3001\u969c\u5bb3\u304c\u8d77\u304d\u308b\u76f4\u524d\u306e\u72b6\u614b\u307e\u3067\u623b\u3059\u3053\u3068\u3067\u3059\u3002<\/li>\n
                    • Oracle\u3067\u3044\u3046\u3068\u3053\u308d\u306e\u3001\u5b8c\u5168\/\u4e0d\u5b8c\u5168\u30ea\u30ab\u30d0\u30ea\u3000\u306e\u3053\u3068\u3067\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n
                    • \u3069\u3046\u3084\u3063\u3066\u623b\u3063\u3066\u308b\u306e\uff1f\n
                        \n
                      • \u4e0b\u8a18\u306e\u7d75\u306e\u901a\u308a\u3001
                        \n\u300c \u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3000\uff0b\u3000\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7 \u300d
                        \n\u3053\u306e\u4e8c\u3064\u306e\u3082\u306e\u3092\u5229\u7528\u3057\u3066\u623b\u3057\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                         <\/p>\n
                        \"PITR\u306e\u6982\u5ff5\"<\/p>\n
                        PITR:\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u53d6\u5f97<\/h2>\n
                        \u25cbPostgreSQL\u7de8<\/em><\/h3>\n
                          \n
                        • \u958b\u59cb\u5ba3\u8a00\u3002\u3053\u308c\u3092\u5b9f\u884c\u3059\u308b\u3068\u4ee5\u964d\u306e\u66f4\u65b0\u60c5\u5831\u306f\u30c7\u30a3\u30b9\u30af\u306b\u53cd\u6620\u3055\u308c\u306a\u304f\u306a\u308a\u307e\u3059\u3002
                          \n\uff08\u30e1\u30e2\u30ea\u4e0a\u3067\u9811\u5f35\u3063\u3066\u3044\u307e\u3059\u3002\uff09
                          \n\u3053\u308c\u3067\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u9759\u6b62\u70b9\u304c\u53d6\u308c\u307e\u3059\u3002<\/li>\n<\/ul>\n
                          $ psql -c \"SELECT pg_start_backup(now()::text)\"\r\n<\/pre>\n
                            \n
                          • OS\u30b3\u30de\u30f3\u30c9\u3067\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3092\u53d6\u5f97\u3057\u307e\u3059\u3002\n
                              \n
                            • postgresql\u306e\u8d77\u52d5\u30d7\u30ed\u30bb\u30b9\u30d5\u30a1\u30a4\u30eb(postmaster.pid)\u3084WAL\u30ed\u30b0(pg_xlog)\u306f\u4e0d\u8981\u3067\u3059\u3002<\/li>\n
                            • \u3053\u3053\u3067\u306f\u3001\/postgresql_basebackup\/\u914d\u4e0b\u306b\u4fdd\u5b58\u3057\u3066\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                              $ rsync -av --delete --exclude=pg_xlog --exclude=postmaster.pid \\\r\n \/var\/lib\/pgsql\/9.4\/data\/* \/postgresql_basebackup\/\r\n<\/pre>\n
                                \n
                              • \u7d42\u4e86\u5ba3\u8a00\u3002 \u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u53d6\u5f97\u4e2d\u306b\u505c\u6b62\u3055\u308c\u3066\u3044\u305f\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u66f4\u65b0\u3092\u30c7\u30a3\u30b9\u30af\u3078\u53cd\u6620\u3057\u3066\u901a\u5e38\u306e\u72b6\u614b\u306b\u623b\u308a\u307e\u3059\u3002<\/li>\n<\/ul>\n
                                $ psql -c \"select pg_stop_backup()\"<\/pre>\n
                                \u203b\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u74b0\u5883\u306e\u5834\u5408\u306f\u3001\u5c02\u7528\u30c4\u30fc\u30eb pg_basebackup \u304c\u30d0\u30fc\u30b8\u30e7\u30f39.1\u304b\u3089\u5229\u7528\u53ef\u80fd\u306a\u306e\u3067\u305d\u3061\u3089\u3092\u4f7f\u3063\u3066\u304f\u3060\u3055\u3044\u3002<\/strong><\/p>\n
                                \u25cbMysql\u7de8<\/em><\/h3>\n
                                  \n
                                • \u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u53d6\u5f97\u65b9\u6cd5<\/li>\n<\/ul>\n
                                  $ mysqldump -u root --all-databases --single-transaction --events \\\r\n--flush-logs --master-data=2 | gzip > mysqldump-all.gz\r\n<\/pre>\n
                                    \n
                                  • --flush-logs<\/strong>\n
                                      \n
                                    • mysqldump\u3068\u540c\u6642\u306b\u3001bin-log\u3092\u30ed\u30fc\u30c6\u30fc\u30b7\u30e7\u30f3\u3057\u3066\u304f\u308c\u308b\u306e\u3067\u3001\u30ea\u30ab\u30d0\u30ea\u304c\u3057\u3084\u3059\u304f\u306a\u308a\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n
                                    • \n
                                      \u00a0--master-data=2<\/b><\/div>\n
                                        \n
                                      • \n
                                        msyqldump\u3067\u53d6\u5f97\u5f8c\u3001bin-log\u306e\u3069\u306e\u30d5\u30a1\u30a4\u30eb\u306e\u4f55\u884c\u76ee\u304b\u3089\u5fa9\u65e7\u3055\u305b\u308c\u3070\u3044\u3044\u304bdump\u30d5\u30a1\u30a4\u30eb\u306b\u30b3\u30e1\u30f3\u30c8\u3068\u3057\u3066\u66f8\u304d\u8fbc\u3093\u3067\u304f\u308c\u308b\u3059\u3050\u308c\u305f\u30aa\u30d7\u30b7\u30e7\u30f3\u3067\u3059\u3002<\/div>\n<\/li>\n
                                      • \u3061\u306a\u307f\u306b\u3001\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u69cb\u6210\u3092\u7d44\u3093\u3067\u306a\u304f\u3066\u3082\u6307\u5b9a\u53ef\u80fd\u306a\u306e\u3067\u3001PITR\u3059\u308b\u4eba\u306f\u7a4d\u6975\u7684\u306b\u5229\u7528\u3059\u3079\u304d\u3060\u3068\u601d\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                                        \u3042\u308c\u3001\u3053\u308c\u3060\u3051\uff01\uff1f\u3000\u30aa\u30f3\u30e9\u30a4\u30f3\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3067\u4f7f\u3063\u305fdump\u3068\u540c\u3058\uff1f\u3000<\/strong><\/p>\n
                                        \u305d\u3046\u3067\u3059\u3001\u30aa\u30d7\u30b7\u30e7\u30f3\u306f\u5c11\u3057\u5897\u3048\u305f\uff08\u4f8b\u3060\u3068\u4e8c\u3064\uff09\u3082\u306e\u306eMysql\u3060\u3068\u3053\u308c\u3092\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3068\u3057\u3066\u5229\u7528\u53ef\u80fd\u306a\u306e\u3067\u3059\u3002<\/strong><\/p>\n
                                        PITR:\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u53d6\u5f97<\/h2>\n
                                        \u25cbPostgreSQL\u7de8<\/em><\/h3>\n
                                          \n
                                        • \u30a2\u30fc\u30ab\u30a4\u30d6\u30ed\u30b0\u3092\u5410\u304f\u8a2d\u5b9a\uff08postgresql.conf\u306e\u7de8\u96c6\uff09<\/li>\n<\/ul>\n
                                          archive_mode = on\r\narchive_command = 'cp %p \/postgresql_archive_log\/%f'   # \/postgresql_archive_log\/\u914d\u4e0b\u306b\u4fdd\u5b58\u3002\r\n<\/pre>\n
                                            \n
                                          • %p:<\/tt>\u30a2\u30fc\u30ab\u30a4\u30d6\u3059\u308b\u30d5\u30a1\u30a4\u30eb\u306e\u30d1\u30b9\u540d\u306b\u7f6e\u63db\u3055\u308c\u307e\u3059\u3002<\/li>\n
                                          • %f:<\/tt>\u30d5\u30a1\u30a4\u30eb\u540d\u90e8\u5206\u306e\u307f\u306b\u7f6e\u63db\u3055\u308c\u307e\u3059\u3002<\/li>\n<\/ul>\n
                                              \n
                                            • WAL\u3092\u9000\u907f\u3057\u307e\u3059\u3002\n
                                                \n
                                              • \u6700\u65b0\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30ed\u30b0\u306a\u306e\u3067\u3001\u5b8c\u5168\u30ea\u30ab\u30d0\u30ea\u3092\u884c\u3044\u305f\u3044\u65b9\u306f\u9000\u907f\u3057\u3066\u304a\u3044\u3066\u304f\u3060\u3055\u3044\u3002<\/li>\n
                                              • \u6700\u521d\u304b\u3089\u3001\u5225\u30c7\u30a3\u30b9\u30af\u306bpg_xlog\u3092\u5410\u304d\u51fa\u3059\u3088\u3046\u306b\u3057\u3066\u304a\u304f\u306e\u3082\u6709\u52b9\u306a\u624b\u6bb5\u3067\u3059\u3002<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                                                # \/postgresql_wal_log\/\u914d\u4e0b\u306b\u4fdd\u5b58\u3002\r\n$ rsync -av --delete \/var\/lib\/pgsql\/9.4\/data\/pg_xlog\/* \/postgresql_wal_log\/\r\n<\/pre>\n
                                                \u25cbMysql\u7de8<\/em><\/h3>\n
                                                  \n
                                                • \u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u3092\u5410\u304f\u8a2d\u5b9a\uff08my.cnf\u306e\u7de8\u96c6\uff09
                                                  \nMysql\u3067\u306f\u30a2\u30fc\u30ab\u30a4\u30d6\u30ed\u30b0\u3068\u547c\u3076\u3082\u306e\u3067\u306f\u306a\u304f\u3001\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0<\/strong>\u304c\u305d\u306e\u5f79\u5272\u3092\u62c5\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n
                                                  [mysqld]\r\n#\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u6709\u52b9\u5316:\r\n#datadir=\/var\/lib\/mysql\u3000\u3068\u306a\u3063\u3066\u3044\u308c\u3070(\u30c7\u30d5\u30a9\u30eb\u30c8)\u3001\r\n#\/var\/lib\/mysql\/\u914d\u4e0b\u306bmybin-log.00000x\u3068\u3044\u3046\u30d5\u30a1\u30a4\u30eb\u540d\u3067\u4fdd\u5b58\u3055\u308c\u308b\u3002\r\nlog-bin=mybin-log\r\n<\/pre>\n
                                                  PITR:\u30ea\u30ab\u30d0\u30ea<\/h2>\n
                                                  \u3055\u3066\u3044\u3088\u3044\u3088\u30ea\u30ab\u30d0\u30ea\u3067\u3059\u3002<\/p>\n
                                                  \u25cbPostgreSQL\u7de8<\/em><\/h3>\n
                                                  \u30b4\u30df\u30c7\u30fc\u30bf\u304c\u3042\u3063\u3066\u306f\u56f0\u308b\u306e\u3067\u3001\u518d\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u305f\u3070\u304b\u308a\u306ePostgreSQL\u304c\u3042\u3063\u3066\u8d77\u52d5\u3057\u3066\u3044\u306a\u3044<\/strong>\u72b6\u614b\u7b49\u3092\u4f5c\u3063\u3066\u304a\u3044\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
                                                    \n
                                                  • \u4f55\u306f\u3068\u3082\u3042\u308c\u3001\u307e\u305a\u306f\u3001\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u623b\u3057<\/li>\n<\/ul>\n
                                                    $ rsync -av --delete \/postgresql_basebackup\/* \/var\/lib\/pgsql\/9.4\/data\/\r\n<\/pre>\n
                                                      \n
                                                    • \u30a2\u30fc\u30ab\u30a4\u30d6(WAL)\u30ed\u30b0\u3092\u5145\u3066\u308b\u305f\u3081\u306e\u6e96\u5099\n
                                                        \n
                                                      • recovery.conf\u306b\u30a2\u30fc\u30ab\u30a4\u30d6(WAL)\u30ed\u30b0\u304c\u5b58\u5728\u3057\u3066\u3044\u308b\u5834\u6240\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                                                        restore_command = 'cp \/postgresql_archive_log\/%f \"%p\"'\r\nrecovery_target_time = '2014-12-21 10:00:00'\r\n<\/pre>\n
                                                        \u203b\u00a0recovery_target_time\u3000\u306e\u884c\u3092\u7121\u304f\u305b\u3070\u3001WAL\u304c\u6301\u3063\u3066\u3044\u308b\u6700\u65b0\u306e\u6642\u523b\u307e\u3067\u623b\u308a\u307e\u3059\u3002<\/strong><\/p>\n
                                                          \n
                                                        • WAL\u3092\u5145\u3066\u308b\u305f\u3081\u306e\u6e96\u5099<\/li>\n<\/ul>\n
                                                          $ cd data\r\n$ rsync -av --delete \/postgresql_wal_log\/* \/var\/lib\/pgsql\/9.4\/data\/pg_xlog\/\r\n<\/pre>\n
                                                            \n
                                                          • PostgreSQL\u306e\u8d77\u52d5\n
                                                              \n
                                                            • \u3053\u308c\u3067\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u30c7\u30fc\u30bf\uff08\u30a2\u30fc\u30ab\u30a4\u30d6\u30ed\u30b0\u304a\u3088\u3073\u3001WAL\uff09\u307e\u3067\u9069\u7528\u3055\u308c\u3066\u3044\u304d\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                                                              # \/etc\/init.d\/postgresql-9.4 start\r\n<\/pre>\n
                                                              \u25cbMysql\u7de8<\/em><\/h3>\n
                                                              \u30b4\u30df\u30c7\u30fc\u30bf\u304c\u3042\u3063\u3066\u306f\u56f0\u308b\u306e\u3067\u3001\u518d\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u305f\u3070\u304b\u308a\u306eMysql\u304c\u3042\u3063\u3066\u8d77\u52d5\u3057\u305f<\/strong>\u72b6\u614b\u7b49\u3092\u4f5c\u3063\u3066\u304a\u3044\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
                                                                \n
                                                              • \u4f55\u306f\u3068\u3082\u3042\u308c\u3001\u307e\u305a\u306f\u3001\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u623b\u3057<\/li>\n<\/ul>\n
                                                                gunzip --stdout mysqldump-all.gz | mysql -u root\r\n<\/pre>\n
                                                                  \n
                                                                • \u30c0\u30f3\u30d7\u53d6\u5f97\u4ee5\u964d\uff5e\u5fa9\u65e7\u3057\u305f\u3044\u6642\u9593\u307e\u3067\u306b\u66f8\u304d\u51fa\u3055\u308c\u305f\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u3092\u78ba\u8a8d<\/li>\n<\/ul>\n
                                                                  $ ls -ltr \/var\/lib\/mysql\/mybin-log.*\r\n<\/pre>\n
                                                                    \n
                                                                  • \u30c0\u30f3\u30d7\u958b\u59cb\u6642\u70b9\u306e\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u540d\u3068\u4f4d\u7f6e\u3092\u78ba\u8a8d<\/li>\n<\/ul>\n
                                                                    $ gunzip --stdout mysqldump-all.gz\u00a0| head -30 | grep CHANGE\r\n------------------------------\r\n-- CHANGE MASTER TO MASTER_LOG_FILE='mybin-log.000011', MASTER_LOG_POS=107;\r\n------------------------------\r\n<\/pre>\n
                                                                      \n
                                                                    • \u00a0\u4e0a\u8a18\u3067\u78ba\u8a8d\u3057\u305f\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u540d\u3068\u4f4d\u7f6e\u3092\u6307\u5b9a\u3057\u3066\u3001\u30c0\u30f3\u30d7\u53d6\u5f97\u4ee5\u964d\uff5e\u5fa9\u65e7\u3057\u305f\u3044\u6642\u9593\u307e\u3067\u306e\u30c7\u30fc\u30bf\u3092\u66f8\u304d\u51fa\u3059<\/li>\n
                                                                    • \u30c0\u30f3\u30d7\u53d6\u5f97\u6642\u306b --flush-logs \u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u6307\u5b9a\u3057\u3066\u3044\u308b\u306e\u3067\u958b\u59cb\u4f4d\u7f6e\u306f\u3001\u5148\u982d\u306b\u306a\u3063\u3066\u3044\u308b\u306e\u3067\u3001
                                                                      \n\u305a\u3070\u308a position \u3067\u6307\u5b9a\u3057\u3066\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n
                                                                      $ mysqlbinlog --start-position=107 --stop-datetime='2014-12-21 10:00:00' \\\r\nmybin-log.000011 > \/tmp\/recovery.sql\r\n<\/pre>\n
                                                                        \n
                                                                      • \u4f8b\uff09\u30c0\u30f3\u30d7\u53d6\u5f97\u4ee5\u964d\uff5e\u5fa9\u65e7\u3057\u305f\u3044\u6642\u9593\u307e\u3067\u306b\u66f8\u304d\u51fa\u3055\u308c\u305f\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u304c\u8907\u6570\u306e\u5834\u5408<\/li>\n<\/ul>\n
                                                                        $ mysqlbinlog --start-position=107 --stop-datetime='2014-12-21 10:00:00' \\\r\nmybin-log.000011 \\\r\nmybin-log.000012 \\\r\nmybin-log.000013 \\\r\nmybin-log.000014 > \/tmp\/recovery.sql\r\n<\/pre>\n
                                                                        \u203b\u00a0--stop-datetime\u3000\u306e\u6307\u5b9a\u304c\u306a\u3051\u308c\u3070\u3001\u6307\u5b9a\u3057\u305f\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u30d5\u30a1\u30a4\u30eb\u304c\u6301\u3063\u3066\u3044\u308b\u6700\u65b0\u306e\u6642\u523b\u307e\u3067\u304c\u5bfe\u8c61\u306b\u306a\u308a\u307e\u3059\u3002<\/strong><\/p>\n
                                                                          \n
                                                                            \n
                                                                          • \u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u30c7\u30fc\u30bf\u306e\u9069\u7528<\/li>\n<\/ul>\n<\/ul>\n
                                                                            $ mysql -u root < \/tmp\/recovery.sql\r\n<\/pre>\n
                                                                            \u307e\u3068\u3081 \u306e \u307e\u3068\u3081<\/h2>\n
                                                                            \u3044\u304b\u304c\u3060\u3063\u305f\u3067\u3057\u3087\u3046\u304b\uff1f\u3000PostgreSQL\u3068Mysql\u3068\u3067PITR\u306e\u6bd4\u8f03\u304c\u3067\u304d\u307e\u3057\u305f\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n
                                                                            \u305d\u3057\u3066\u4eca\u56de\u306e\u8a18\u4e8b\u306e\u307e\u3068\u3081\u3067\u3059\u3002<\/p>\n\n\n\n\n\n\n\n\n\n\n
                                                                            \u624b\u9806<\/th>\nPostgreSQL<\/th>\nMysql<\/th>\n<\/tr>\n
                                                                            \u30aa\u30f3\u30e9\u30a4\u30f3\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u30b3\u30de\u30f3\u30c9<\/td>\npg_dumpall\uff08\u307e\u305f\u306f\u3001pg_dump\uff09<\/td>\nmysqldump<\/td>\n<\/tr>\n
                                                                            PITR\u306e\u305f\u3081\u306e\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u53d6\u5f97\u65b9\u6cd5<\/td>\n1.psql<\/span> -<\/span>c<\/span> \"SELECT pg_start_backup(now()::text)\"<\/span>
                                                                            \n2.OS\u30b3\u30de\u30f3\u30c9\u306b\u3088\u308b\u8a72\u5f53\u9818\u57df\u30d0\u30c3\u30af\u30a2\u30c3\u30d7<\/span>
                                                                            \n3.psql<\/span> -<\/span>c<\/span> \"select pg_stop_backup()\"<\/span><\/td>\n                                                                            		mysqldump<\/strong><\/td>\n<\/tr>\n
                                                                            PITR\u3067\u623b\u3059\u305f\u3081\u306b\u5fc5\u8981\u306a\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u30d5\u30a1\u30a4\u30eb\u306e\u7a2e\u985e<\/td>\n1.\u30a2\u30fc\u30ab\u30a4\u30d6(WAL)\u30ed\u30b0
                                                                            \n2.WAL\u30ed\u30b0<\/p>\n
                                                                            \u203bOracle\u3067\u3044\u3046\u4e0d\u5b8c\u5168\u30ea\u30ab\u30d0\u30ea\u306e\u5834\u5408\u306f\u4e0d\u8981\u306a\u5834\u5408\u304c\u3042\u308b\u3002
                                                                            \n\uff08\u3064\u307e\u308a\u3001\u5fa9\u65e7\u3055\u305b\u308b\u6642\u523b\u6b21\u7b2c\u3067\u4f7f\u308f\u308c\u306a\u304b\u3063\u305f\u308a\u3059\u308b\uff09<\/td>\n
                                                                            		\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u306e\u307f<\/strong><\/td>\n<\/tr>\n
                                                                            PITR\u306e\u305f\u3081\u306e\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u53d6\u5f97\u65b9\u6cd5<\/td>\n1.\u30a2\u30fc\u30ab\u30a4\u30d6\u30ed\u30b0\u3092\u5410\u304f\u8a2d\u5b9a(postgresql.conf)
                                                                            \n2.OS\u30b3\u30de\u30f3\u30c9\u306b\u3088\u308bWAL\u30ed\u30b0\u306e\u9000\u907f<\/td>\n                                                                            		\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u3092\u5410\u304f\u8a2d\u5b9a\uff08my.cnf\uff09<\/td>\n<\/tr>\n
                                                                            PITR\u3067\u623b\u3059\u305f\u3081\u306e\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u306e\u72b6\u614b<\/td>\n\u505c\u6b62<\/td>\n\u8d77\u52d5<\/td>\n<\/tr>\n
                                                                            PITR\u3067\u623b\u3059\u305f\u3081\u306e\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u9069\u7528\u65b9\u6cd5<\/td>\nOS\u30b3\u30de\u30f3\u30c9\u306b\u3088\u308b\u9000\u907f\u6e08\u307f\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u623b\u3057\u4f5c\u696d<\/td>\ngunzip --stdout [mysqldump\u3067\u53d6\u5f97\u3057\u305f\u30d5\u30a1\u30a4\u30eb]<\/span>\u00a0|<\/span> mysql<\/span> -<\/span>u<\/span> root<\/span>
                                                                            \n(\u307e\u305f\u306f\u3001mysql -u root < [mysqldump\u3067\u53d6\u5f97\u3057\u305f\u30d5\u30a1\u30a4\u30eb])<\/td>\n<\/tr>\n
                                                                            PITR\u3067\u623b\u3059\u305f\u3081\u306e\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u9069\u7528\u65b9\u6cd5<\/td>\n1.\u9000\u907f\u6e08\u307f\u30a2\u30fc\u30ab\u30a4\u30d6\u30ed\u30b0\u306e\u5834\u6240\u6307\u5b9a\uff08recovery.conf)
                                                                            \n2.\u9000\u907f\u6e08\u307fWAL\u30ed\u30b0\u306eOS\u30b3\u30de\u30f3\u30c9\u306b\u3088\u308b\u623b\u3057\u4f5c\u696d
                                                                            \n3\uff0ePostgreSQL\u306e\u8d77\u52d5<\/td>\n                                                                            		1.mysqlbinlog\u3067\u3001\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u3092\u30c6\u30ad\u30b9\u30c8\u5316
                                                                            \n2.mysql -u root <\u00a0\u4e0a\u8a18\u30c6\u30ad\u30b9\u30c8\u5316\u3057\u305f\u30d5\u30a1\u30a4\u30eb<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
                                                                            \u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\uff0b\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u4ed5\u7d44\u307f\u3092\u7406\u89e3\u3067\u304d\u308c\u3070\u3001\u5fdc\u7528\u304c\u5229\u304f\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002
                                                                            \n\u305f\u3068\u3048\u3070\u3001Master\/Slave\u306e\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u69cb\u6210\u3067\u3001HDD\u4e38\u3054\u3068\u30af\u30e9\u30c3\u30b7\u30e5\u3057\u305f\u5834\u5408\u3001\u58ca\u308c\u305f\u307b\u3046\u306eDB\u306f\u3044\u305a\u308c\u4fee\u5fa9\u3057\u306a\u3044\u3068\u3044\u3051\u306a\u3044\u3067\u3059\u3088\u306d\u3002
                                                                            \n\u305d\u3093\u306a\u3068\u304d\u3001\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3092\u624b\u52d5\u3067\u623b\u3057\u3066\u3042\u3052\u3066\u3001\u5dee\u5206\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u90e8\u5206\u306b\u3064\u3044\u3066\u306f\u3001\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3067\u8ffd\u3044\u3064\u304b\u305b\u308b\u3002
                                                                            \n\u306a\u3093\u3066\u3053\u3068\u304c\u30b9\u30e0\u30fc\u30ba\u306b\u5b9f\u884c\u3067\u304d\u308b\u304b\u3068\u304a\u3082\u3044\u307e\u3059\u3002<\/p>\n
                                                                            Oracler\u306a\u79c1\u3067\u3057\u305f\u304c\u3001
                                                                            \nMysql\u306b\u306f\u30b9\u30c8\u30ec\u30fc\u30b8\u30a8\u30f3\u30b8\u30f3\u3060\u3063\u305f\u308a\u3001\u3053\u306e\u8a18\u4e8b\u306e\u3088\u3046\u306b\u30aa\u30f3\u30e9\u30a4\u30f3\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u304c\u305d\u306e\u307e\u307e\u30d9\u30fc\u30b9\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3068\u3057\u3066\u4f7f\u3048\u305f\u308a\u9a5a\u304d\u304c\u3042\u308a\u307e\u3059\u306d\u3002
                                                                            \nPostgreSQL\u306fDDL\u306b\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5f35\u308c\u308b\uff08\u30ed\u30fc\u30eb\u30d0\u30c3\u30af\u3067\u304d\u308b\uff01\uff09\u3068\u304b\u3001SQL\u304cTAB\u88dc\u5b8c\u3067\u304d\u305f\u308a\u3068\u304b\u9a5a\u304b\u305b\u3089\u308c\u307e\u3059\u306d\u3002<\/p>\n
                                                                            \u307f\u306a\u3055\u3093\u3082\u3001\u3044\u308d\u3044\u308d\u6bd4\u8f03\u3057\u3066\u307f\u3066\u306f\u3044\u304b\u304c\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n","protected":false},"excerpt":{"rendered":"
                                                                            \u3053\u3093\u306b\u3061\u306f\u3001\u843d\u5408\u3067\u3059\u3002
                                                                            \n\u3053\u306e\u8a18\u4e8b\u306f\u3001TECHSCORE Advent Calendar \u306e22\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n
                                                                            2014\u5e74\u3082\u7d42\u308f\u308d\u3046\u3068\u3057\u3066\u3044\u307e\u3059\u306d\u3002
                                                                            \nPostgreSQL8.x\u7cfb\u306f\u3001\u4eca\u5e74EOL\u3092\u8fce\u3048\u307e\u3057\u305f\u3002
                                                                            \u7d9a\u304d\u3092\u8aad\u3080...<\/a><\/p>\n","protected":false},"author":21,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[141,63,206],"_links":{"self":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/5341"}],"collection":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/comments?post=5341"}],"version-history":[{"count":106,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/5341\/revisions"}],"predecessor-version":[{"id":5607,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/5341\/revisions\/5607"}],"wp:attachment":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/media?parent=5341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/categories?post=5341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/tags?post=5341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}