Tommy 碎碎念
https://blog.teatime.com.tw/
Tommy Wu's blog
tommy
2024-03-19T16:18:14Z
-
奇怪的 postgresql 問題, HINT: To avoid a database shutdown, execute a full-database VACUUM in...
https://blog.teatime.com.tw/1/post/329
<p>昨天在使用 <a href="http://www.pgadmin.org/" target="_blank">pgadmin III</a> 連上資料庫後, 查詢了幾筆資料後, 突然發現... 似乎有個不平常的訊息出現:</p><pre class="winbatch"><div class="insertcode">WARNING<span class="co2">: database "xxx" must be vacuumed within 107009986 transactions</span><br />HINT<span class="co2">: To avoid a database shutdown, execute a full-database VACUUM in "xxx".</span></div></pre><p>看起來似乎很嚴重的樣子, 用 google 查了一下, 發現了<a href="http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">這篇 postgresql 的文件</a>, 說明是因為沒有做 vacuum 的關係. 只要做一次 vacuum 就可以了.問題是.... 我們的伺服器上頭有打開 auto vacuum 的設定, 也有每天用一個 cron job 去跑 vacuumdb, 怎麼還會有這個問題呢?</p><p>更嚴重的是.... 當我試著用 psql 連上去, 跑了 vacuum (資料不少, 跑一次要好幾個小時) 之後, 發現仍然是一樣的結果.... 就連 vacuum full 與 vacuum freeze (又是好幾個小時過去了) 都試過了, 仍然得到同樣的錯誤... 而且那數字也愈來愈小, 據文件所說, 等小於 1 百萬時就會強迫 shutdown, 只能用 single user 去跑了.</p><p>google 上頭找了找, 終於看到<a href="http://www.mail-archive.com/pgsql-admin@postgresql.org/msg31866.html" target="_blank">有人是類似的情形</a>, 不過... 大家在上頭的建議一樣是去執行 vacuum, 而看起來苦主做了之後的結果跟我一樣, 並沒有解決問題. 陸續看了幾個案例, 好像沒有人成功的樣子.... 這... 是 bug 嗎?</p><p>今天在該數字逼近百萬之前, 決定把該資料庫用 pg_dump 做一份備份 (其實每天都有整個資料庫的備份, 也是可以用), 然後就直接把該資料庫 drop 再重建一個, 然後再把 dump 下來的資料給 restore 回去... 結果... 果然沒問題.... 只是換成另一個資料庫有問題. 就同樣的方式再處理一次..... 目前看起來應該是沒問題了.</p><p>不過... 這個問題也讓我開始懷疑 postgresql 真的適合拿來用嗎? 怎麼會有這樣的問題呢? 如果沒有好的解決方法, 難道過個幾年我們會再碰到相同的問題呢?</p><p>如果有人也碰到這個問題, 也有除了 dump/restore 之外的解決方法, 麻煩告知一聲吧.</p>
PostgreSQL
2009-08-19T15:18:24Z
tommy
-
PostgreSQL ODBC 連線的 client encoding mismatch 錯誤修正
https://blog.teatime.com.tw/1/post/302
<p>記得去年我們升級 PostgreSQL 之後, 造成在 Windows 上頭使用 ODBC 的程式無法連線, 雖然後來是另外<a href="http://blog.teatime.com.tw/1/post/188" target="_blank">建立一個使用者, 限定使用 LATIN1 來避開</a>這個問題, 因為在這方面處理的都是數字, 所以這個方法也就一直用到了現在都沒什麼問題.</p><p>最近有另外的需求, 一樣是在 Windows 平台上頭, 使用 <a href="http://www.postgresql.org/ftp/odbc/versions/msi/" target="_blank">psqlODBC</a> 來連線, 但是程式上頭會用到一些字串, 如果只使用 LATIN1 的話會有問題. 抓了目前最新的版本回來使用, 發現不管使用 PostgreSQL ANSI 或者是 PostgreSQL UNICODE 的 driver, 都不會出現 client encoding mismatch 的錯誤了. 目前使用上也一切正常了. </p>
PostgreSQL
2008-07-01T11:20:28Z
tommy
-
PostgreSQL Standby Server
https://blog.teatime.com.tw/1/post/258
<p>隨著資料量的增加, 通常資料庫的重要性也就跟著增加, 這時, 我們要如何確保資料庫可以正常運作呢? 使用比較好的機器? 使用高級的 RAID? 把資料備份好? 這些做的再好, 也都還是有風險. 也就是, 只要是硬體, 就可能會壞. 機器就是那一台, 出了問題, 就得找台機器, 重新開始安裝, 然後由備份的資料還原. 資料一多, 這些動作可以短到幾小時, 也可能是兩三天才能處理好. 也就是, 在這段時間內, 如果你的公司運作, 對資料庫有極大的依賴性, 可能這段時間的運作完全停擺, 損失不可說不小. </p><p>一般的資料庫系統, 為了避免這樣的情形發生, 除了應有的備份動作外, 通常也會複製的機制, 可以讓交易存到別的機器上頭, 只要有需要, 可以很快的切換到另外一台有相同資料的機器上頭.</p><p>而 <a href="http://www.postgresql.org/" target="_blank">PostgreSQL</a>, 依據這幾年使用的經驗來看, "複製" (replication) 這件事情, 實在是做的不怎麼樣. 以往多數的方式, 都是利用 trigger 的方式來處理, 但是, 這樣的做法, 就我來看, 只能做到已存在 table 內資料的複製, 如果有新的 table, 或 schema 有變動, 都可能導致複製的結果出了問題, 等真的要用那些資料時, 會不會和原本的資料之間有了出入呢?</p><p>在<a href="http://blog.teatime.com.tw/1/post/257" target="_blank">上一篇文章</a>中說過, 雖然 7.x 的 PostgreSQL 有 WAL 的功能, 不過只能拿來本身 process crash 時做 recovery 使用. 而在 8.0 之後, 終於可以做到 online backup, 也就是我們可以弄出一份基本的備份, 加上後續的 WAL 交易檔, 就可以還原出原本的資料庫. 換個方向來看, 這不也是一種 replication? 而且, 不再是依賴 trigger 來處理了, 而是完完整整資料庫的複製了. 不管你對資料庫做了什麼動作, 都會在 WAL 交易檔中, 都可以在 recovery 時重新把這些動作再做一次. 只要我們把存好這個備份與之後的 WAL 交易檔, 就表示我們可以複製出一個完整的資料庫了.</p><p>在前面提到, 如果我們要讓資料庫出問題停擺的時間變短. 只要我們能事先準備一台機器, 把上頭的備份放到該機器上頭. 如果原本的機器出問題時, 我們只要把這些 WAL 交易檔都 recovery 一次, 就可以切換到這台備份的主機. 這也就是一般 standby server 的概念.</p><p>所以, 既然 PostgreSQL 在 8.0 之後, 可以利用 backup 與 WAL 來複製原本的資料庫了, 是否也表示可以做出 standby server 了呢? 只要我們還原時的 recovery.conf 中的 restore_command 中, 把原本簡單的 cp 指令, 寫成一個可以等待的 script, 等收到下一個 WAL 檔案時, 再做 cp 的動作. 這樣子, 這個還原動作就不會結束, 這樣子, 看起來就成了一個 standby server 了.</p><p>不過, 在 8.2 發表之前, 上頭的概念在沒問題時是可以運作的, 不過... 如果我們那台機器有需要關機時, 會發現在 restore_command 被強迫結束時, PostgreSQL 會認為沒有新的 WAL 了 , 就結束 recovery 的動作, 然後把資料庫變成一個正常的資料庫了, 之後就再也不能匯入新的 WAL 檔案了.</p><p>這個問題在 8.2 中獲得的解決. 只要 restore_command 的結束, 是經由 signal 觸發來結束的, 或傳回值大於 125 時, 就會把 recovery 的動作中斷並結束程式, 而不會把資料庫的狀態改變. 下一次執行時, 仍然是在 recovery 的狀態, 也可以接受後續的 WAL 檔案. 這也就表示在 PostgreSQL 8.2 中, 我們就可以做到完整的 standby server 的功能了.</p><p>參考 8.2 中<a href="http://www.postgresql.org/docs/8.2/static/warm-standby.html" target="_blank">有關 standby server 的說明</a>, 先弄出一個備份出來, 然後把 WAL 存到某個路徑中, 這時, 在 standby 的機器上把該備份放上去, 做出一個 recovery.conf, 上頭的 restore_command 改成一個可以等待的 script, 然後利用別的方法 (mount? rsync? ftp?), 把之後產生的 WAL 檔案複製過來使用. 這樣子, 就變成一個 standby server 了.</p><p>這個 script, 我們是用 php 寫了一個簡單的版本 pgstandby.php:</p><pre class="php"><div class="insertcode"><span class="co2">#!/usr/bin/php -Cq</span><br /><span class="kw2"><?php</span><br /> <br /><span class="re0">$trigger_filename</span> = <span class="st0">'standby.stop'</span>;<br /><span class="re0">$sleep_time</span> = <span class="nu0">5</span>;<br /> <br /><span class="re0">$argc</span> = <span class="re0">$_SERVER</span><span class="br0">[</span><span class="st0">'argc'</span><span class="br0">]</span>;<br /><span class="re0">$argv</span> = <span class="re0">$_SERVER</span><span class="br0">[</span><span class="st0">'argv'</span><span class="br0">]</span>;<br /> <br /><span class="kw1">if</span> <span class="br0">(</span><span class="re0">$argc</span> != <span class="nu0">3</span><span class="br0">)</span> <span class="br0">{</span><br /> writelog<span class="br0">(</span><span class="st0">"Syntax "</span>.<span class="re0">$argv</span><span class="br0">[</span><span class="nu0">0</span><span class="br0">]</span>.<span class="st0">" sourcefile targetfile"</span><span class="br0">)</span>;<br /> <span class="co1">// we need to use exit code > 125, to tell postgresql just exit, don't change the role to normal server</span><br /> <a href="http://www.php.net/exit"><span class="kw3">exit</span></a><span class="br0">(</span><span class="nu0">127</span><span class="br0">)</span>;<br /><span class="br0">}</span><br /> <br /><span class="re0">$source</span> = <span class="re0">$argv</span><span class="br0">[</span><span class="nu0">1</span><span class="br0">]</span>;<br /><span class="re0">$target</span> = <span class="re0">$argv</span><span class="br0">[</span><span class="nu0">2</span><span class="br0">]</span>;<br /><span class="re0">$filename</span> = <a href="http://www.php.net/basename"><span class="kw3">basename</span></a><span class="br0">(</span><span class="re0">$source</span><span class="br0">)</span>;<br /> <br /><span class="kw1">if</span> <span class="br0">(</span><a href="http://www.php.net/strpos"><span class="kw3">strpos</span></a><span class="br0">(</span><span class="re0">$filename</span>, <span class="st0">'.history'</span><span class="br0">)</span> !== <span class="kw2">false</span><span class="br0">)</span> <span class="br0">{</span><br /> <span class="co1">// ignore file *.history</span><br /> <span class="kw1">if</span> <span class="br0">(</span>!<a href="http://www.php.net/file_exists"><span class="kw3">file_exists</span></a><span class="br0">(</span><span class="re0">$source</span><span class="br0">)</span><span class="br0">)</span> <span class="br0">{</span><br /> writelog<span class="br0">(</span><span class="st0">"skip non-exists $source"</span><span class="br0">)</span>;<br /> <a href="http://www.php.net/exit"><span class="kw3">exit</span></a><span class="br0">(</span><span class="nu0">0</span><span class="br0">)</span>;<br /> <span class="br0">}</span><br /><span class="br0">}</span><br /><span class="kw1">else</span> <span class="br0">{</span><br /> <span class="re0">$trigger_file</span> = <a href="http://www.php.net/dirname"><span class="kw3">dirname</span></a><span class="br0">(</span><span class="re0">$source</span><span class="br0">)</span>.<span class="st0">'/'</span>.<span class="re0">$trigger_filename</span>;<br /> <span class="co1">// we should wait until the WAL generated</span><br /> <span class="kw1">if</span> <span class="br0">(</span>!<a href="http://www.php.net/file_exists"><span class="kw3">file_exists</span></a><span class="br0">(</span><span class="re0">$source</span><span class="br0">)</span><span class="br0">)</span> <span class="br0">{</span><br /> writelog<span class="br0">(</span><span class="st0">"waiting for $source"</span><span class="br0">)</span>;<br /> <span class="kw1">while</span> <span class="br0">(</span><span class="nu0">1</span><span class="br0">)</span> <span class="br0">{</span><br /> <a href="http://www.php.net/clearstatcache"><span class="kw3">clearstatcache</span></a><span class="br0">(</span><span class="br0">)</span>;<br /> <span class="kw1">if</span> <span class="br0">(</span><a href="http://www.php.net/file_exists"><span class="kw3">file_exists</span></a><span class="br0">(</span><span class="re0">$source</span><span class="br0">)</span><span class="br0">)</span> <span class="kw1">break</span>;<br /> <span class="co1">// wait for 5 seconds...</span><br /> <span class="kw1">if</span> <span class="br0">(</span><a href="http://www.php.net/file_exists"><span class="kw3">file_exists</span></a><span class="br0">(</span><span class="re0">$trigger_file</span><span class="br0">)</span><span class="br0">)</span> <span class="br0">{</span><br /> writelog<span class="br0">(</span><span class="st0">"stop standby, change the role to normal server"</span><span class="br0">)</span>;<br /> <span class="co1">// return non-zero if we want to change standby to normal server</span><br /> <a href="http://www.php.net/exit"><span class="kw3">exit</span></a><span class="br0">(</span><span class="nu0">1</span><span class="br0">)</span>;<br /> <span class="br0">}</span><br /> <a href="http://www.php.net/sleep"><span class="kw3">sleep</span></a><span class="br0">(</span><span class="re0">$sleep_time</span><span class="br0">)</span>;<br /> <span class="co1">//exit(127);</span><br /> <span class="br0">}</span><br /> <span class="br0">}</span><br /><span class="br0">}</span><br /> <br />writelog<span class="br0">(</span><span class="st0">"copy $source to $target"</span><span class="br0">)</span>;<br />@<a href="http://www.php.net/unlink"><span class="kw3">unlink</span></a><span class="br0">(</span><span class="re0">$target</span><span class="br0">)</span>;<br /><a href="http://www.php.net/copy"><span class="kw3">copy</span></a><span class="br0">(</span><span class="re0">$source</span>, <span class="re0">$target</span><span class="br0">)</span>;<br /><span class="co1">// return 0, we have new WAL here...</span><br /><a href="http://www.php.net/exit"><span class="kw3">exit</span></a><span class="br0">(</span><span class="nu0">0</span><span class="br0">)</span>;<br /> <br /><span class="kw2">function</span> writelog<span class="br0">(</span><span class="re0">$buf</span><span class="br0">)</span><br /><span class="br0">{</span><br /> <a href="http://www.php.net/echo"><span class="kw3">echo</span></a> <span class="re0">$buf</span>.<span class="st0">"<span class="es0">\n</span>"</span>;<br /> <span class="re0">$fp</span> = <a href="http://www.php.net/fopen"><span class="kw3">fopen</span></a><span class="br0">(</span><span class="st0">'/var/log/postgresql/pgstandby-'</span>.<a href="http://www.php.net/strftime"><span class="kw3">strftime</span></a><span class="br0">(</span><span class="st0">'%Y%m%d'</span><span class="br0">)</span>.<span class="st0">'.log'</span>, <span class="st0">'at'</span><span class="br0">)</span>;<br /> <span class="kw1">if</span> <span class="br0">(</span><span class="re0">$fp</span><span class="br0">)</span> <span class="br0">{</span><br /> <a href="http://www.php.net/fputs"><span class="kw3">fputs</span></a><span class="br0">(</span><span class="re0">$fp</span>, <a href="http://www.php.net/strftime"><span class="kw3">strftime</span></a><span class="br0">(</span><span class="st0">'%D %T ['</span><span class="br0">)</span>.<a href="http://www.php.net/posix_getpid"><span class="kw3">posix_getpid</span></a><span class="br0">(</span><span class="br0">)</span>.<span class="st0">'] '</span>.<span class="re0">$buf</span>.<span class="st0">"<span class="es0">\n</span>"</span><span class="br0">)</span>;<br /> <a href="http://www.php.net/fclose"><span class="kw3">fclose</span></a><span class="br0">(</span><span class="re0">$fp</span><span class="br0">)</span>;<br /> <span class="br0">}</span><br /> <span class="kw1">return</span>;<br /><span class="br0">}</span><br /> <br /><span class="kw2">?></span></div></pre><p>只要把 restore_command 改成 'pgstandby.php /db/pgarchive/archivelog/%f %p', 就可以做到 standby 的功能了.</p><p>要注意的是, 在 replay 某個 WAL 交易檔之後, 並不能馬上刪除, 因為如果中斷 recovery 的動作時, 通常下一次做 recovery 時, 會回溯之前好幾個 WAL 檔, 所以建議這些 WAL 檔案要保留一段時間後再刪除.</p><p>當然, 你也可以不用上頭的 script, 自己寫一個類似的 bash script 也是可以. 或者, 可以到官方的網站, 抓目前 8.3 開發版本中 contrib 裡頭的 <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/pg_standby;h=3eb83a2a6fc1b57ca2750614f03817fc0f861f0a;hb=HEAD" target="_blank">pg_standby</a> 程式回來用. </p><p>這樣子, 我們就完成了一個 PostgreSQL 的 standby server 了.</p><p>目前, PostgreSQL 的 standby server 就只是單純的 standby, 但是在官方的 <a href="http://www.postgresql.org/docs/faqs.TODO.html" target="_blank">todo list</a> 中, 是有一個把 standby server 加上可以做 readonly query 的功能, 不過.... 也不知道多久才會加上這個功能. 因為在該 todo list 中, 有另一個我想要的功能 pg_upgrade (可以讓升級不再是使用 pg_dump 來處理, 直接由 binary file 升級), 好像由 2004 年到現在, 也沒什麼進展. </p>
Software
PostgreSQL
2007-08-12T09:51:51Z
tommy
-
PostgreSQL 線上備份
https://blog.teatime.com.tw/1/post/257
<p>我們大約是由 2003 年初開始使用 <a href="http://www.postgresql.org/" target="_blank">PostgreSQL</a> 當做是公司內的資料庫系統. 當初在 <a href="http://www.oracle.com/" target="_blank">ORACLE</a> (9i), <a href="http://www.mysql.com/" target="_blank">MySQL</a> (3.x) 與 PostgreSQL (7.2) 之間選擇時, 先把當時還算很簡陋的 MySQL (那時應該還沒有支援 stored procedure, sub-query, transaction 等等的功能, 實在不適合商業使用, 不過因為快又小, 我們有些中介的資料庫, 還是使用 MySQL 來處理) 給排除掉. 在 ORACLE 與 PostgreSQL 的比較上, 在我們需要的功能上頭, 兩者都能提供, 當然, PostgreSQL 有絕對的價格優勢 (不過, 當初我們並沒有預算上的壓力, 雖然 ORALCE 不算便宜, 但我們決定要用時, 也不會說花不起這筆錢), 但是在複製, 備份與災難復原方面, 就遠遠比不上 ORACLE 了. 最後與老闆討論的結果, 是決定先用 PostgreSQL 試看看, 如果一年內有發生重大的當機或災難時, 我們就轉用 ORACLE. 結果... 當然 4 年多過去了... 我們還是在用 PostgreSQL. </p><p>在這段期間, 我們對於 PostgreSQL 的備份, 就只能使用 SQL dump (pg_dump) 的方式來處理, 也就是每天我們會把資料庫 dump 下來, 存在 RAID 上頭保留一個月, 然後每天會把這些備份 tar 到磁帶櫃裡頭的不同磁帶, 也就是至少可以有七天的磁帶來循環使用. 這樣子的方式, 可以確保我們在發生問題時, 應該最差只會損失一天的交易資料 (當然, 有些公司一天的交易資料算很嚴重的事, 不過我們對於收入有關的資料, 是由交換機上產生的資料再匯入的, 如果真的發生問題, 還是可以做重新匯入的動作, 真的發生問題時, 還不至於造成收入上的損害). 四年多下來, 還沒碰過需要還原的時候. </p><p>雖然 PostgreSQL 7.x 的版本, 就有了 WAL (Write-Ahead Logging) 的功能, 可以把交易記錄存下來, 不過, 對於備份與還原來說, 一點幫助也沒有, 仍然只有 SQL dump 的方式可以處理. 在 2005 年初的時候, PostgreSQL 8.0 發表了, 除了支援 windows 外, 利用 WAL 這個功能, 終於有了除了 SQL dump 之外的備份方式了. 而且是 on-line 的備份方式. 也就是我們可以線上做一份基本的備份, 然後把之後的 WAL 交易記錄檔也存起來, 就可以還原到你保存的最後一個 WAL 交易檔的時間點. 在這之後, 備份與還原, 就不是一件困難的事情了. 與一般市售的資料庫比較起來, 一點也不遜色. 原本用 SQL dump 的方式, 雖然備份的時間算快, 但是還原的時間, 當資料一多時, 就要花很多的時間了, 且... 就只能還原到備份的那個時間點. 現在... 利用這個新的備份方式, 還原的時間就可以大幅的縮減, 也可以儘可能的還原到出問題前的那個時間點.</p><p>參考 <a href="http://www.postgresql.org/docs/8.0/static/backup-online.html" target="_blank">8.0 有關 PITR (point-in-time recovery) 的說明</a>, 很容易就可以使用這樣的備份方式.</p><p>首先, 在你的 postgresql.conf 設定中, 設定 archive_command 這個參數, 如:</p><pre class="ini"><div class="insertcode"><span class="re1">archive_command</span>=<span class="re2">'cp -i %p /backup/archivelog/%f < /dev/null'</span> </div></pre><p>這樣子, PostgreSQL 就會在產生 WAL 之後, 利用這個指令, 把檔案 copy 到 /backup/archivelog/ 這個目錄中. </p><p>在有了 WAL 的 archive 之後, 我們可以在 psql 中, 使用這個指令告訴 PostgreSQL 我們要開始一個備份:</p><pre class="sql"><div class="insertcode"><span class="kw1">SELECT</span> pg_start_backup<span class="br0">(</span><span class="st0">'label'</span><span class="br0">)</span>;</div></pre><p>在這個指令之後, 我們就可以利用 tar 或 cpio 之類的軟體, 把 PostgreSQL 的 data 目錄備份下來 (tar 可能因為在備份的時後, 檔案有更動產生錯誤訊息, 可以忽略). 在把檔案備份之後, 再用 psql 執行下面這個指令, 告訴 PostgreSQL 我們結束備份了:</p><pre class="sql"><div class="insertcode"><span class="kw1">SELECT</span> pg_stop_backup<span class="br0">(</span><span class="br0">)</span>;</div></pre><p>在這個指令後, 再把備份的檔案, 以及備份這段時間在 pg_xlog 產生的 WAL 交易檔案保存起來, 再把之後的 WAL 交易檔也都存下來. 在發生問題時, 就可以利用這些備份的資料與 WAL 交易檔, 來還原整個資料庫了. </p><p>上頭的備份動作, 我們可以整合一下, 寫成一個 script 來跑:</p><pre class="bash"><div class="insertcode"><span class="co1">#!/bin/sh</span><br /> <br /><span class="kw1">if</span> <span class="br0">[</span> <span class="st0">"$1"</span> == <span class="st0">""</span> <span class="br0">]</span>; <span class="kw1">then</span><br /> $<span class="nu0">0</span> backupname<br /> <span class="kw3">exit</span> <span class="nu0">1</span><br /><span class="kw1">fi</span><br /> <br />psql << START_BACKUP_END<br /><span class="kw1">select</span> pg_start_backup<span class="br0">(</span><span class="st0">'$1'</span><span class="br0">)</span>;<br />\q<br />START_BACKUP_END<br /> <br />find . -print | cpio -o -H crc | gzip -v > /backup/base/$<span class="nu0">1</span>.cpio.gz<br /> <br />psql << STOP_BACKUP_END<br /><span class="kw1">select</span> pg_stop_backup<span class="br0">(</span><span class="br0">)</span>;<br />\q<br />STOP_BACKUP_END</div></pre><p>利用上頭的 script, 我們只要讓 PostgreSQL 的使用者, 執行這個指令, 加上日期當參數, 就可以產生一個以該日期參數為檔名的基本備份檔了.</p><p>有了上頭的備份資料後, 要怎麼還原呢?</p><p>首先, 先安裝出一個有 PostgreSQL 的環境, 然後把上頭 tar 或 cpio 得到的基本備份檔案, 還原到 PostgreSQL 的 data 目錄中. 如果你用上頭的 script 備份, 可以用到下頭的指令還原:</p><pre class="bash"><div class="insertcode"><span class="co1">#!/bin/sh</span><br /> <br />cat /backup/base/<span class="nu0">20070810</span>.cpio.gz | gzip -<span class="kw3">cd</span> | cpio -idvm</div></pre><p>還原之後, 把 pg_xlog 的目錄內的檔案清除掉. 然後做一個 recovery.conf 的設定檔如下:</p><pre class="ini"><div class="insertcode"><span class="re1">restore_command </span>=<span class="re2"> 'cp /backup/archivelog/%f </span><span class="st0">"%p"</span>'</div></pre><p>把這個檔案放在 PostgreSQL 的目錄中, 然後把在上一個基本備份之後的 WAL 交易檔放到 /backup/archivelog/ 目錄中. 接著只要啟動 PostgreSQL, 就會進入到 recovery 的模式, 會把這些 WAL 交易記錄執行一次, 直到最後一個交易記錄檔為止, 接著 PostgreSQL 會把 recovery.conf 改名為 recovery.done, 然後把正常的資料庫帶起來. 這樣子, 你就可以還原出你的資料庫了.</p><p>在 recovery.conf 上頭, 還可以利用其它的參數, 讓 recovery 的動作做到某個時間點或某個交易為止, 可以防止某些不正常的交易被再執行到. 如果沒有設定, 就是做到沒有下一個 WAL 交易檔為止. 如有需要, 請自行參考官方的文件有關這部份的參數設定.</p><p>PS:</p><ul><li>在 8.2 版之後, 如果你把 <tt class="VARNAME">full_page_writes 設為 off, 來增加效能的話, 在備份期間, 效能與平時有明顯的差異.</tt></li><li><tt class="VARNAME">在 Debian 的 PostgreSQL 版本中, 如果你的 recovery.conf 放在 data 的目錄下沒有作用時, 請在 /etc/postgresql/8.2/main 下頭同樣放上一份 recovery.conf (不過, 在執行 recovery 時, 並不是讀取這個檔案裡頭的設定值?), 在我之前的測試中, 似乎這樣子才會在一開始進入 recovery 的模式去還原之後的 WAL 交易檔. </tt></li></ul><p> </p>
Software
PostgreSQL
2007-08-11T13:05:58Z
tommy
-
PostgreSQL ODBC 連線的 client encoding mismatch 錯誤
https://blog.teatime.com.tw/1/post/188
前天把公司某台主機的 PostgreSQL 由 8.0.3 升級到 8.2.1. 原本以為一切都正常, 不過後來發現, 在另外一台 Windows 的機器上頭, 使用 php 寫的一些透過 ODBC 連線的程式都無法正常執行.<p>原本以為是 PostgreSQL ODBC driver 的問題, 特地上網抓了新版的回來用, 結果一樣. 後來在程式中加上顯示 odbc 錯誤訊息的功能後, 出現了下頭的錯誤:</p><pre><code>client encoding mismatch</code> <br /></pre><p>到 Google 找了一下, 發現自從 PostgreSQL 8.1 之後, 把原本的 UNICODE 改成 UTF-8, 結果, 在 windows 底下使用 ODBC 連線到 UTF-8 的資料庫時, 就會出現上頭的錯誤訊息.</p><p>不過, 似乎沒有看到什麼方式可以在連線時設定要使用的字元集. 只有看到<a href="http://tadek.pietraszek.org/blog/2005/12/02/postgresql81-client-encoding-mismatch-with-odbc/" target="_blank">這篇文章</a> , 上頭提到了可以把使用者所使用的字元集改成 latin1, 來避開這個問題:</p><pre><code>alter user <user> SET client_encoding to LATIN1;</code> <br /></pre><p>不過, 我們的系統, 該使用者並非只用在 windows 上頭, 在其他的 linux 上頭也是用這個使用者, 而且, 雖然在 windows 上頭的這個, 並不會用到 UTF-8 (UNICODE) 的字元, 不過, 其他的地方, 仍可能使用到.</p><p>所以, 我們就建立一個新的使用者, 把這個新的使用者的字元集改成 latin1, 然後讓所有在 windows 下用到 ODBC 連線的程式, 都改用這個新的使用者. 這樣子, 就可以解決這個問題了.</p>
程式設計
PHP
PostgreSQL
2007-02-01T00:01:02Z
tommy
-
避免 PostgreSQL 使用 Full Table Scan
https://blog.teatime.com.tw/1/post/148
前不久, 我們公司某個系統, 突然在跑某些報表時, 會使用很久的時間 (數分鐘) 才得到結果, 以往, 同一份報表, 應該幾秒不到就會出來. 查了一下原因, 發現程式並沒有修改, 所使用的 SQL 語法也與以前是一樣的. 嘗試查了一下發生問題之前的資料, 發現很快就得到結果, 但是如果查詢的資料在某一天之後, 就會需要數分鐘才有結果. 看來與程式語法應該沒有什麼關係, 畢竟同一個 SQL 指令, 只是其中某個日期欄位的範圍不同, 結果就不同了. 難道 PostgreSQL 對於這樣應該算是一樣的 SQL 指令, 是使用不同的 plan 才查詢嗎?<p>把這個不同的 SQL 指令抓出來, 拿到 pgAdmin 上頭跑看看, 果然, 如果其中的日期是在上個月的, 就會利用 index 去找, 很快就有結果, 但是... 如果日期是落在這幾天內, 居然就是使用 Full Table Scan 去找, 而這個 table 目前有一千萬筆左右的資料, 這一找, 當然要花上數分鐘才有結果.</p><p>找了一下 PostgreSQL 的文件, 並沒有發現有支援任何類似 Oracle 的 hint 功能, 可以在執行 SQL 指令時, 避免內建的最佳化引擎誤判, 可以告訴 Oracle 應該使用那些方法來執行這個 SQL 指令. 在 PostgreSQL 中, 我們並不能控制 SQL Engine 要怎麼運作, 完全只能交由 PostgreSQL 來決定. (看來, 除了 online backup 方面不如人外, 在這方面, 還是輸人家不少... 有賣錢的還真的在某些地方是有優點的)</p><p>後來試著做一次 re-index 的指令, 結果仍然一樣. 接著再試著做一次 vacuum analyze, 這次, 果然有用了, 之前會用到 full table scan 的指令, 現在都會改用 index 了. 可是... 問題是, 我們每天都對整個資料庫跑一次 vacuum analyze 怎麼會沒有作用呢? 查了一下每天執行的記錄, 並沒有任何錯誤, 都很正常的執行. 問題到底在那兒呢? </p><p>隔天, 同樣的事情又發生了, 這次只有有關當天資料的 SQL 指令會造成 full table scan (該 table 一天會有 1-2 萬筆新增的記錄, 發現有問題時, 當天新增的筆數約 5000 筆左右). 這... 再做一次 vacuum analyze 之後, 又正常了. 不過.... 總不能一天對就這個 table 做好幾次 vacuum analyze 吧. 我們每天做一次, 應該就已經是很頻繁了. 一天要數次才能正常使用, 似乎不是一個可以接受的做法.</p><p>找了一下 PostgreSQL 的文件, 也沒發現什麼好的解決方法. 後來在檢查我們的 postgresql.conf 時, 發現我們裡頭有關 Planner Method Configuration 的設定, 那些 enable_xxx 的參數, 全部都設為 true:</p><pre>#---------------------------------------------------------------------------<br /># QUERY TUNING<br />#---------------------------------------------------------------------------<br /><br /># - Planner Method Configuration -<br /><br />enable_hashagg = true<br />enable_hashjoin = true<br />enable_indexscan = true<br />enable_mergejoin = true<br />enable_nestloop = true<br />enable_seqscan = true<br />enable_sort = true<br />enable_tidscan = true<br /></pre><p>裡頭有個 enable_seqscan 也是被設定為 true.</p><p>我試著把這個參數改為 false:</p><pre>#---------------------------------------------------------------------------<br /># QUERY TUNING<br />#---------------------------------------------------------------------------<br /><br /># - Planner Method Configuration -<br /><br />enable_hashagg = true<br />enable_hashjoin = true<br />enable_indexscan = true<br />enable_mergejoin = true<br />enable_nestloop = true<br />enable_seqscan = false<br />enable_sort = true<br />enable_tidscan = true </pre><p>然後重新 reload 一次 postgresql, 之後, 就正常了. 在後續的幾天, 該報表並沒有再發生類似的情形了. 看來把 enable_seqscan 設為 false 之後, PostgreSQL 在有其他 index 的情形下, 就不會去使用 full table scan 了. </p><p>雖然事情解決了, 不過我們還是覺得奇怪, 同樣的程式, 也用了三年多了. 同樣裡頭的資料也累積三年多了, 雖然有換過機器, 但是新的機器比舊的要快多了, 且也換了一年多了, 為什麼之前都沒有問題呢? 難道是累積到了快一千萬筆資料才會有問題? </p>
程式設計
PostgreSQL
2006-11-04T11:07:31Z
tommy
-
在 PostgreSQL 中轉換 timezone
https://blog.teatime.com.tw/1/post/28
我們客戶的公司在 Irvine, 使用的時區是 PST, 所以雖然我們目前所在的位置使用的時區是 GMT+8, 不過為了方便起見, 我們幾乎所有的系統都是使用 PST 時區, 當然包括我們使用的交換機.<p>以目前我們的系統來看, 與我們業務相關的系統, 都使用 PST 時區, 只有我們員工的打卡系統存放的是當地時間的時間. 所以我們用 php 開發系統時, 也做了一個在 PST 與 GMT+8 這兩個時區之間轉換的函式, 一般來說, 除了 PST 有夏令節約時間的問題外, 並沒有太大的問題存在.</p> <p>不過, 隨著客戶的生意愈來愈大, 目前幾乎全球都有銷售他們的產品, 所以幫客戶做技術支援工作的敝公司, 原本服務的對象就由北美地區, 逐漸擴大到全球各地的英語系國家. 而這些其他地區的報表, 我們依然使用 PST 時間來計算. 但是, 這幾天, 我們客戶的英國分公司, 要求我們改用 GMT 來製作他們那邊的報表.... 這難道也要把資料抓下來後, 用 php 來轉換嗎? 這可不像我們之前系統的需求, 所有抓出來的資料都是要顯示的, 只是再用一個函式轉換不同的時區罷了. 這些報表, 如果改用別的時區在選擇資料, 要用 php 做的話, 那可不是一件小事, 因為有很多 group by 的動作, 在資料庫上頭可能跑個一分鐘就可以抓到資料, 如果是自己把一筆一筆的資料由資料庫抓出來, 再用 php 慢慢一筆一筆運算, 依據以往的經驗, 原本一分鐘的事, 可能要跑上半個小時以上才能跑完, 不管是考慮我們系統的負荷, 或使用者等待的時間, 應該都是不能夠接受的.</p> <p>所以, 上網查看看我們使用的 <a href="http://www.postgresql.org/" target="_blank">PostgreSQL</a> 資料庫, 對於這種不同時區的時間轉換, 是否有特別的方法可以處理. 果然在<a href="http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html" target="_blank">文件</a>中有提到可以利用 timezone() 來轉換 timestamp 到不同的時區的時間. 不過, 我們之前為了方便使用, 並不是將時間存成 timestamp 格式, 而是使用 8 個字元的字串存放日期, 再用 6 個字元的字串存放時間. 現在要轉換時區, 這樣的存放方式, 反而變的很麻煩. 還好, 有看到 <a href="http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html" target="_blank">to_timestamp()</a> 這個函式可以使用:</p><pre>select to_timestamp('20060228' || '123000' || ' PST',<br /> 'FXYYYYMMDDHH24MISS TZ'); <br /></pre><p>執行結果:</p><pre>ERROR: "TZ"/"tz" not supported <br /></pre><p>不過, 上頭這個指令, 卻得到了一個錯誤訊息. 試著把 TZ 移除:</p><pre>select to_timestamp('20060228' || '123000',<br /> 'FXYYYYMMDDHH24MISS'); <br /></pre><p>執行結果:</p><pre>2006-02-28 12:30:00 +08 <br /></pre><p>果然, 移除 TZ 之後, 就可以得到我們當地時區的時間. 不過我們的資料應該是 PST 時區的時間才對. 一定要加上 TZ 才可以. 查了很久, 終於在 PostgreSQL 的 source code 中看到有關這個 TZ 的運作, 只有在 TO_CHAR 上頭有作用, 如果是在 FROM_CHAR 時, 就會得到上頭那個錯誤. 看來, 似乎要放棄使用 to_timestamp(), 但是, 似乎又沒看到其他更好的方法. 後來我們試著用 cast 來轉換格式:</p><pre>select cast(<br /> to_timestamp('20060228' || '123000',<br /> 'FXYYYYMMDDHH24MISS') as timestamp<br /> ); <br /></pre><p>執行結果:</p><pre>2006-02-28 12:30:00 <br /></pre><p>把 to_timestamp() 轉換出來的 timestamptz 格式, 強迫轉換成 timestamp 格式, 我們果然得到了一個沒有時區的時間了. 但是我們要的應該是 PST 時區才對, 所以試著再轉換一次:</p><pre>select cast(<br /> cast(<br /> to_timestamp('20060228' || '123000',<br /> 'FXYYYYMMDDHH24MISS') as timestamp<br /> ) || ' PST' as timestamptz<br /> ); <br /></pre><p>執行結果:</p><pre>2006-03-01 04:30:00 +08 <br /></pre><p>我們總算得到了一個正確帶時區的 timestamp, 2006-03-01 04:30:00 +08 就等於 2006-02-28 12:30:00 PST 沒錯. 得到正確的 timestamp 之後, 接下來的動作就變的很簡單了, 我們只要用 timezone() 這個函式就可以切換到不同的時區:</p><pre>select timezone('GMT',<br /> cast(<br /> cast(<br /> to_timestamp('20060228' || '123000',<br /> 'FXYYYYMMDDHH24MISS') as timestamp<br /> ) || ' PST' as timestamptz<br /> )<br /> ); <br /></pre><p>執行結果:</p><pre>2006-02-28 20:30:00 <br /></pre><p>把這個 timestamp 換成 GMT 時區看看, 果然得到正確的 2006-02-28 20:30:00 這個時間. </p> <p>最後, 我們就在系統的 form 上頭, 加上一個時區的選項, 把 <a href="http://www.postgresql.org/docs/8.1/interactive/datetime-keywords.html" target="_blank">PostgreSQL 所支援的所有時區</a>列上, 讓使用者選擇所要用的時區, 然後在我們使用的 SQL 指令上頭, 改成這樣:</p><pre>select to_number(<br /> to_char(<br /> timezone('$timezone',<br /> cast(<br /> cast(<br /> to_timestamp(a.row_date ||<br /> to_char(a.starttime, 'FM0000') ||<br /> '00',<br /> 'FXYYYYMMDDHH24MISS') as timestamp<br /> ) || ' PST' as timestamptz<br /> )<br /> ), 'HH24MI'), '9990') as starttime,<br /> sum(a.acdcalls) as acdcalls,<br /> sum(a.abncalls) as abncalls,<br /> sum(a.acdtime) as acdtime,<br /> sum(a.abntime) as abntime,<br /> sum(a.anstime) as anstime,<br /> sum(a.in_sl) as in_sl<br />from bcms_hsplit as a join cms_split as b on a.split = b.split and a.acd = b.acd<br />where a.row_date >= '$s_date_before'<br />and a.row_date <= '$e_date_after'<br />and to_char(<br /> timezone('$timezone',<br /> cast(<br /> cast(<br /> to_timestamp(a.row_date ||<br /> to_char(a.starttime, 'FM0000') ||<br /> '00',<br /> 'FXYYYYMMDDHH24MISS') as timestamp<br /> ) || ' PST' as timestamptz<br /> )<br /> ), 'YYYYMMDD') between '$s_date_str' and '$e_date_str'<br />group by 1<br />order by 1 <br /></pre><p>利用選取的 $timezone 變數來轉換時區, 把系統用的 row_date 與 starttime 轉換成 timestamp 再處理, 就可以得到正確的資料. 要注意的是, 如果只用後頭那個轉換時區的運算式來判斷選取的資料, 會造成 full table scan, 當資料很多時, 這個動作可能會做很久, 會造成很大的 performance 問題. 所以, 我們先行取得 $s_date_str 的前一天, 與 $e_date_str 的後一天, 利用這個資料來設定 row_date 的範圍 (因為 timezone 的轉換可能會往前或往後一天), 這樣子就不致於造成 full table scan, 可以大幅縮短執行時間. (不過比起不用轉換的 SQL 指令來說, 還是慢上許多, 但是, 至少, 我們現在可以選擇我們要使用的時區來找資料了)</p>
程式設計
PostgreSQL
2006-03-02T17:25:59Z
tommy