アーカイブ

‘PostgreSQL’ カテゴリーのアーカイブ

MacのPostgreSQLをアップグレードする。 このエントリーを含むはてなブックマーク はてなブックマーク - MacのPostgreSQLをアップグレードする。

2017 年 5 月 1 日 Comments off

brew upgrade postgresql したら下記のエラーが出て起動しなくなったので、対応したメモ。

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.5, which is not compatible with this version 9.6.2.

ログは

% tail `brew --prefix`/var/log/postgres.log

にあるはず。

公式のドキュメントは→https://www.postgresql.jp/document/9.6/html/pgupgrade.html

まず動いているプロセスを停止していまのdataを退避し、代わりに空のdataを作る。

% launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
% mv `brew --prefix`/var/postgres `brew --prefix`/var/postgres_95
% initdb `brew --prefix`/var/postgres -E utf8

退避したdataから新しく作ったdataにコピー。

% pg_upgrade \
  -b `brew --prefix`/Cellar/postgresql/9.5.4_1/bin \
  -B `brew --prefix`/Cellar/postgresql/9.6.2/bin \
  -d `brew --prefix`/var/postgres_95 \
  -D `brew --prefix`/var/postgres

最後に下記のメッセージが出るので、「正常に起動することを確認した後に」それぞれのスクリプトを実行して完了。

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

起動する。

% launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

インストールのメモはこちら

カテゴリー: Mac, PostgreSQL タグ:

RailsでPGroongaを使う。その3 このエントリーを含むはてなブックマーク はてなブックマーク - RailsでPGroongaを使う。その3

2016 年 11 月 3 日 Comments off

ここ数日追っていたこの件ですが、groonga/jaのチャットルームで教えていただきました。

コミュニティー | PGroonga

PGroonga用のチャットルームは次の通りです。

groonga/ja – Gitter

実際に追加したコードはこんな感じです。

Extend relation to switch “enable_seqscan” around query execution · yoshuki/article_search_with_pgroonga@049c8d4

Extend relation to switch “enable_seqscan” around query execution

extendingを知らなかったのですが、ここの前後に割り込ませているのですね。スッキリ追加できていてすばらしい。

rails/relation.rb at v5.0.0.1 · rails/rails

def exec_queries

ただここすでにmasterでは変わっているので、注意が必要ですね。(ちゃんとテスト書こう。)

rails/relation.rb at master · rails/rails

def exec_queries(&block)

@kouさんありがとうございました!

カテゴリー: PostgreSQL, Rails タグ:

RailsでPGroongaを使う。その2 このエントリーを含むはてなブックマーク はてなブックマーク - RailsでPGroongaを使う。その2

2016 年 11 月 1 日 Comments off

RailsでPGroongaを使う。 » サイキョウライン

全文検索そのものと、キーワードのハイライトについてはよかったのですが、pgroonga.score関数でとれるスコアを使ったソートで問題が・・・(つづく)

前回の続き、この問題について。端的に言うと、せっかく作ったインデックスを使ってくれずスコアが取れないため、結果として正しくソートが出来ていません。

pgroonga.score関数の仕様はこの様になっています。

pgroonga.score関数 | PGroonga

pgroonga.score関数はインデックスを使わずに全文検索した場合は常に0.0を返します。言い換えると、pgroonga.score関数はシーケンシャルスキャンで全文検索を実行した場合は常に0.0を返します。

そして、チュートリアルでは作ったインデックスを使わせる対策として、enable_seqscanをoffにしていました。

チュートリアル | PGroonga

確実にpgroongaインデックスを使うためにシーケンシャルスキャンを無効にします。

直にクエリを発行するなら、SELECTの前後でenable_seqscanをoff→DEFAULTと一時的に切り替えてあげればよいと思うのですが、ActiveRecordを使っているとクエリが発行されるのは実際に結果が必要になったタイミングなので、この様に書いても意味がありません。

> Article.connection.execute 'SET enable_seqscan = off'
> articles = Article.full_text_search('桃太郎')
> Article.connection.execute 'SET enable_seqscan = DEFAULT'

SELECTの前後にコールバックがあればいいのですが、探してみても用意されてはいないみたい。
after_initialize and after_findは期待している動きとはちょっと違う。)

かと言って、postgresql.confに書いてしまうのはやり過ぎ、というか全体でシーケンシャルスキャンをoffにしてしまうのは問題だと思います。
このような場合、どう実装するのがいいのでしょうか。

[15:30 追記]
ActiveRecordが生成するSQLをpsqlで流すとインデックスが使われることは確認できているので、enable_seqscanをどこで切り替えるか、に悩んでいます。

> puts Article.full_text_search('桃太郎').to_sql
SELECT "articles".*, pgroonga.score("articles") AS pgroonga_score FROM "articles"  WHERE (title %% '桃太郎' OR body %% '桃太郎')  ORDER BY pgroonga_score DESC
=> EXPLAIN SELECT "articles".*, pgroonga.score("articles") AS pgroonga_score FROM "articles"  WHERE (title %% '桃太郎' OR body %% '桃 太郎')  ORDER BY pgroonga_score DESC;
                               QUERY PLAN
-------------------------------------------------------------------------
 Sort  (cost=1.14..1.15 rows=4 width=242)
   Sort Key: (score(articles.*)) DESC
   ->  Seq Scan on articles  (cost=0.00..1.10 rows=4 width=242)
         Filter: ((title %% '桃太郎'::text) OR (body %% '桃太郎'::text))
(4 rows)

=> SET enable_seqscan = off;
SET
=> EXPLAIN SELECT "articles".*, pgroonga.score("articles") AS pgroonga_score FROM "articles"  WHERE (title %% '桃太郎' OR body %% '桃太郎')  ORDER BY pgroonga_score DESC;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4.08..4.09 rows=4 width=242)
   Sort Key: (score(articles.*)) DESC
   ->  Bitmap Heap Scan on articles  (cost=0.00..4.04 rows=4 width=242)
         Recheck Cond: ((title %% '桃太郎'::text) OR (body %% '桃太郎'::text))
         ->  BitmapOr  (cost=0.00..0.00 rows=2 width=0)
               ->  Bitmap Index Scan on index_articles_on_id_and_title_and_body  (cost=0.00..0.00 rows=2 width=0)
                     Index Cond: (title %% '桃太郎'::text)
               ->  Bitmap Index Scan on index_articles_on_id_and_title_and_body  (cost=0.00..0.00 rows=1 width=0)
                     Index Cond: (body %% '桃太郎'::text)
(9 rows)

=> SET enable_seqscan = DEFAULT;
SET
=> EXPLAIN SELECT "articles".*, pgroonga.score("articles") AS pgroonga_score FROM "articles"  WHERE (title %% '桃太郎' OR body %% '桃太郎')  ORDER BY pgroonga_score DESC;
                               QUERY PLAN
-------------------------------------------------------------------------
 Sort  (cost=1.14..1.15 rows=4 width=242)
   Sort Key: (score(articles.*)) DESC
   ->  Seq Scan on articles  (cost=0.00..1.10 rows=4 width=242)
         Filter: ((title %% '桃太郎'::text) OR (body %% '桃太郎'::text))
(4 rows)
カテゴリー: PostgreSQL, Rails タグ:

RailsでPGroongaを使う。 このエントリーを含むはてなブックマーク はてなブックマーク - RailsでPGroongaを使う。

2016 年 10 月 27 日 Comments off

とあるRailsアプリで全文検索が必要になったため、PGroongaを試してみました。

環境はそれぞれこんな感じ。(表示は一部省略)

% cat /etc/lsb-release
DISTRIB_DESCRIPTION="Ubuntu 16.04.1 LTS"

% sudo aptitude show postgresql
パッケージ: postgresql
バージョン: 9.5+173

% sudo aptitude show postgresql-9.5-pgroonga
パッケージ: postgresql-9.5-pgroonga
バージョン: 1.1.5-2~xenial1

まず、参考にしたのはこのページです。

Ruby on RailsでPostgreSQLとPGroongaを使って日本語全文検索を実現する方法 – ククログ(2015-11-09)

この記事ではRuby on Railsで作ったアプリケーションからPGroongaを使って日本語全文検索
機能を実現する方法を説明します。

そして、使ったそれぞれの関数のリファレンスはこちら。

実際のコードはこんな感じです。
PostgreSQLにエクステンションを追加するマイグレーション

PGroongaを使わせるためのインデックスを追加するマイグレーション
カラムがこうなっている理由はこちらです。

pgroonga.score関数 | PGroonga

pgroonga.score関数を使うには、pgroongaインデックスにプライマリーキーに指定したカラムを追加する必要があります。もし、プライマリーキーに指定したカラムをpgroongaインデ>ックスに追加していない場合は、pgroonga.score関数は常に0.0を返します。

全文検索をしているクラス
titleとbodyを検索対象にしています。

実際に使用するとこうなります。

> article = Article.full_text_search('桃太郎', true).first
> article.highlighted_body
=> むかしむかしあるところで<span class="keyword">桃太郎</span>という少年が鬼ヶ島に行って鬼を退治しました。

全文検索そのものと、キーワードのハイライトについてはよかったのですが、pgroonga.score関数でとれるスコアを使ったソートで問題が・・・(つづく)

ヒント: インデックス

カテゴリー: PostgreSQL, Rails タグ:

カラムの順番について。 このエントリーを含むはてなブックマーク はてなブックマーク - カラムの順番について。

2016 年 2 月 8 日 Comments off

細かいことが気になるシリーズ。
MySQLのADD COLUMNにはAFTERがあるがPostgreSQLにはないため、add_columnに:afterを付けても無視される。
そのためカラムは追加した順に並ぶので、順番を意識したい場合にどうするかという話。たとえばテーブル定義書を作るとき。
Active Recordを通して使うのでいつもは気にする必要がないのだけれど、定義書の順番とdb/schema.rbの順番が異なっているのは気持ち悪い。

PostgreSQLでも出来なくはないけれど別にテーブルつくって云々て、MySQLならオプション1個で済むことをそこまで手間かけてやるの?て感じはしてる。

さて、どうしましょ。

MySQL :: MySQL 5.7 Reference Manual :: 13.1.8 ALTER TABLE Syntax

13.1.8 ALTER TABLE Syntax

PostgreSQL: Documentation: 9.5: ALTER TABLE

ALTER TABLE — change the definition of a table

ActiveRecord::ConnectionAdapters::SchemaStatements

Adds a new column to the named table.

カテゴリー: MySQL, PostgreSQL, Rails タグ:

ポスグレ9.5からのシャットダウンについて。 このエントリーを含むはてなブックマーク はてなブックマーク - ポスグレ9.5からのシャットダウンについて。

2016 年 2 月 1 日 Comments off

第6回 MySQL 5.8の話題,PostgreSQL 9.5リリースとApache Cassandraのご紹介:OSSデータベース取り取り時報|gihyo.jp … 技術評論社

pg_ctlコマンドのデフォルトの停止モードが,スマートシャットダウン(smart)から高速シャットダウン(fast)に変更されました。このため,デフォルトの停止モードでPostgreSQLを停止させると,実行中のトランザクションがロールバックされ,クライアントとの接続を強制的に切断するようになります。

接続がなくなるまで待ってくれるものという認識があったので、気をつけよう。

PostgreSQL: Documentation: 9.5: pg_ctl

Specifies the shutdown mode. mode can be smart, fast, or immediate, or the first letter of one of these three. If this is omitted, fast is used.

カテゴリー: PostgreSQL タグ:

ポスグレのTRUNCATEはロールバック出来る。 このエントリーを含むはてなブックマーク はてなブックマーク - ポスグレのTRUNCATEはロールバック出来る。

2015 年 1 月 20 日 Comments off

TRUNCATE

テーブル内のデータという観点では、TRUNCATEはトランザクション的に安全です。 前後のトランザクションがコミットされなければ消去は安全にロールバックされます。

しばらくMySQLしか使ってなかったので、すっかりTRUNCATEはトランザクション外だと無意識に決めつけてました。
覚えておこう。

カテゴリー: PostgreSQL タグ:

リファレンス本がっっ。 このエントリーを含むはてなブックマーク はてなブックマーク - リファレンス本がっっ。

2006 年 12 月 26 日 Comments off

いつの間にか改訂されていたシリーズ。

PostgreSQL完全機能リファレンス―実行例を通して「理解」を深める。 PostgreSQL完全機能リファレンス―実行例を通して「理解」を深める。
秀和システム(2006-11)

  • (著)鈴木 啓修

秀和システム
新品価格:¥ 3,360
ASIN:4798014958

ポスグレを使っている一般的な日本家庭の8割は常備していると言われているリファレンス本。
しっかりバージョン8.1対応となっている。これを待っていた。(ウォッチしてなかったくせに。)
「ん?」と思っても↓の表紙を見れば「あー」となるだろう。いわゆる「ん?あーの呼吸」だ。
これは「阿吽の呼吸」と時を同じくして(ry

PostgreSQL全機能リファレンス PostgreSQL全機能リファレンス
技術評論社(2003-08-26)

  • (著)鈴木 啓修

技術評論社
新品価格:¥ 3,129
中古価格:¥ 1,704
ASIN:4774118117

ってことでAmazonに頼んだ。

カテゴリー: PostgreSQL タグ:

psqlでのCOPYコマンド。 このエントリーを含むはてなブックマーク はてなブックマーク - psqlでのCOPYコマンド。

2006 年 12 月 16 日 Comments off

使うときにいつもどっちがどっちか忘れているので、質問された機会に整理してみた。
※テーブル「book」をエクスポートするとして。

COPY book TO 'book.csv' WITH CSV HEADER;
  • 権限:
    (ポスグレの)スーパーユーザしか実行できない。
  • ファイル作成場所:
    サーバ側/絶対パスのみ
  • ファイル属性:
    サーバを起動しているOSユーザの所有
\COPY book TO 'book.csv' WITH CSV HEADER
  • 権限:
    (ポスグレの)一般ユーザでも実行できる。
  • ファイル作成場所:
    クライアント側/絶対パスまたは相対パス
    (psql起動時のカレントディレクトリが基点)
  • ファイル属性:
    psqlを起動したOSユーザの所有
カテゴリー: PostgreSQL タグ:

シーケンスをリセット。 このエントリーを含むはてなブックマーク はてなブックマーク - シーケンスをリセット。

2006 年 11 月 18 日 Comments off

シーケンス操作関数

3 つのパラメータ形式の場合、is_called は true (真) もしくは false(偽) に設定されます。false (偽) に設定された場合、次の nextval が指定された正確な値を返し、シーケンスの進行は引き続く nextval から始まります。

setval()にて。3つめのパラメータの存在に気がついていなかった・・・。
たしかに、0は受け付けてくれないんだからこれがないと1から始められない。

=> SELECT setval('book_id_seq', 1, FALSE);

0にはリセットできない仕様だと勝手に思っていた自分が憎い。んなわけないだろ。

カテゴリー: PostgreSQL タグ: