PosgreSQL

テーブルのみのdumpとrestore

2022年10月20日 by 河副太智 Leave a Comment

Postgresql 12.6 から Postgresql 14.5. にtest_eu3という名称のテーブルのみを移行する際にエラーが頻発したので顛末を記録していく。

テーブルのDumpは以下のコード

※(cd..でcドライブの最上位まで移動してから)cd \Program Files\PostgreSQL\12\bin(binフォルダに移動psqlではなく通常のコマンド)

pg_dump -Fc -b -U postgres -t test_eu -f C:\Users\...\Desktop\test_eu.sql rulings

1	pg_dump -Fc -b -U postgres -t test_eu -f C:\Users\...\Desktop\test_eu.sql rulings

まず、以下のエラーが出て文字化けしているので内容がつかめない。

C:\Users\...>pg_restore -v -U app_admin -d pre_rulings -t test_eu3 test_eu3.sql
pg_restore: connecting to database for restore
Password:
pg_restore: implied data-only restore
pg_restore: processing data for table "public.test_eu3"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2915; 0 3097390 TABLE DATA test_eu3 postgres
pg_restore: error: could not execute query: ERROR:  ????????????"public.test_eu3"??????????s
Command was: COPY public.test_eu3 (id, "national", item_day, item_hs_all, item_hs2, item_hs4, item_hs6, item_place, image_amount, img_name_all, item_image, other_info, org_discription, org_discription2, eng_discription, eng_discription2, id2) FROM stdin;
pg_restore: warning: errors ignored on restore: 1

C:\Users\...>

C:\Users\...>pg_restore -v -U app_admin -d pre_rulings -t test_eu3 test_eu3.sql

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

pg_restore: processing data for table "public.test_eu3"

pg_restore: while PROCESSING TOC:

pg_restore: from TOC entry 2915; 0 3097390 TABLE DATA test_eu3 postgres

pg_restore: error: could not execute query: ERROR: ????????????"public.test_eu3"??????????s

Command was: COPY public.test_eu3 (id, "national", item_day, item_hs_all, item_hs2, item_hs4, item_hs6, item_place, image_amount, img_name_all, item_image, other_info, org_discription, org_discription2, eng_discription, eng_discription2, id2) FROM stdin;

pg_restore: warning: errors ignored on restore: 1

C:\Users\...>

そこでpsqlで以下を入力し、英語に変換する

ALTER ROLE app_admin SET lc_messages = 'C';

1	ALTER ROLE app_admin SET lc_messages = 'C';

すると以下のようなエラーに変わった

C:\Users\enosh>pg_restore -v -U app_admin -d pre_rulings -t test_eu3 test_eu3.sql
pg_restore: connecting to database for restore
Password:
pg_restore: implied data-only restore
pg_restore: processing data for table "public.test_eu3"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2915; 0 3097390 TABLE DATA test_eu3 postgres
pg_restore: error: could not execute query: ERROR:  relation "public.test_eu3" does not exist
Command was: COPY public.test_eu3 (id, "national", item_day, item_hs_all, item_hs2, item_hs4, item_hs6, item_place, image_amount, img_name_all, item_image, other_info, org_discription, org_discription2, eng_discription, eng_discription2, id2) FROM stdin;
pg_restore: warning: errors ignored on restore: 1

C:\Users\enosh>pg_restore -v -U app_admin -d pre_rulings -t test_eu3 test_eu3.sql

pg_restore: connecting to database for restore

Password:

pg_restore: implied data-only restore

pg_restore: processing data for table "public.test_eu3"

pg_restore: while PROCESSING TOC:

pg_restore: from TOC entry 2915; 0 3097390 TABLE DATA test_eu3 postgres

pg_restore: error: could not execute query: ERROR: relation "public.test_eu3" does not exist

pg_restore: warning: errors ignored on restore: 1

relation “public.test_eu3″がないとの事、
publicとはデータベース作成の際に自動で作成されるスキーマ名の事、
特に気にせず空のtest_eu3のテーブルを作成し、以下を入力

pg_restore -v -U app_admin -d pre_rulings -t test_eu test_eu.sql

1	pg_restore -v -U app_admin -d pre_rulings -t test_eu test_eu.sql

空のテーブルとdumpにあるテーブルデータのカラムが異なるとエラーになるので注意する。

これで移行が完了。

postgresqlマイナーバージョンアップ

2021年6月30日 by 河副太智 Leave a Comment

SQLサービス停止してSQL実行バイナリを入れ替えて再びサービス起動をするだけ。

SQLサービス停止

systemctl stop postgresql-12

1	systemctl stop postgresql-12

更新(awsではなぜかできない)

yum update postgresql-12-server

エラー
[root@ip-172-31-35-163 ruling]# yum update postgresql-12-server
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
amzn2-core | 3.7 kB 00:00:00
amzn2extra-docker | 3.0 kB 00:00:00
amzn2extra-nginx1.12 | 1.3 kB 00:00:00
amzn2extra-postgresql10 | 3.0 kB 00:00:00
pgdg12 | 3.6 kB 00:00:00
1 packages excluded due to repository priority protections
引数に一致しません: postgresql-12-server
パッケージ postgresql-12-server は利用できません。
No packages marked for update

yum update postgresql-12-server

エラー

[root@ip-172-31-35-163 ruling]# yum update postgresql-12-server

読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd

amzn2-core | 3.7 kB 00:00:00

amzn2extra-docker | 3.0 kB 00:00:00

amzn2extra-nginx1.12 | 1.3 kB 00:00:00

amzn2extra-postgresql10 | 3.0 kB 00:00:00

pgdg12 | 3.6 kB 00:00:00

1 packages excluded due to repository priority protections

引数に一致しません: postgresql-12-server

パッケージ postgresql-12-server は利用できません。

No packages marked for update

SQLサービス再起動

systemctl start postgresql-12

1	systemctl start postgresql-12

pg_trgmでDB全文検索を早くする

2020年8月31日 by 河副太智 Leave a Comment

ローカルの場合

psqlにログインして以下を実行するだけで準備完了

CREATE EXTENSION pg_trgm;

1	CREATE EXTENSION pg_trgm;

インデックスの作成

CREATE INDEX (任意のインデックスの名前) ON (テーブル) USING gist(項目 gist_trgm_ops);

#例CREATE INDEX eu_index ON test_eu USING GIST (eng_discription gist_trgm_ops);

CREATE INDEX (任意のインデックスの名前) ON (テーブル) USING gist(項目 gist_trgm_ops);

#例CREATE INDEX eu_index ON test_eu USING GIST (eng_discription gist_trgm_ops);

インデックスを作成したテーブルを検索した際のパフォーマンスを調べる

#インデックス作成前のパフォーマンスを調べる
explain select * from test_eu where eng_discription ~ '.*remote controller.*' ORDER BY image_amount DESC;
QUERY PLAN
#結果
-------------------------------------------------------------------------------------
Gather Merge (cost=155601.10..155609.74 rows=74 width=1295)
Workers Planned: 2
-> Sort (cost=154601.08..154601.17 rows=37 width=1295)
Sort Key: image_amount DESC
-> Parallel Seq Scan on test_eu (cost=0.00..154600.11 rows=37 width=1295)
Filter: (eng_discription ~ '.*remote controller.*'::text)
#インデックス作成後の結果
--------------------------------------------------------------------------------
Sort (cost=462.64..462.86 rows=88 width=1295)
Sort Key: image_amount DESC
-> Bitmap Heap Scan on test_eu (cost=113.09..459.80 rows=88 width=1295)
Recheck Cond: (eng_discription ~ '.*remote controller.*'::text)
-> Bitmap Index Scan on eu_index (cost=0.00..113.07 rows=88 width=0)
Index Cond: (eng_discription ~ '.*remote controller.*'::text)

#インデックス作成前のパフォーマンスを調べる

explain select * from test_eu where eng_discription ~ '.*remote controller.*' ORDER BY image_amount DESC;

QUERY PLAN

#結果

-------------------------------------------------------------------------------------

Gather Merge (cost=155601.10..155609.74 rows=74 width=1295)

Workers Planned: 2

-> Sort (cost=154601.08..154601.17 rows=37 width=1295)

Sort Key: image_amount DESC

-> Parallel Seq Scan on test_eu (cost=0.00..154600.11 rows=37 width=1295)

Filter: (eng_discription ~ '.*remote controller.*'::text)

#インデックス作成後の結果

--------------------------------------------------------------------------------

Sort (cost=462.64..462.86 rows=88 width=1295)

Sort Key: image_amount DESC

-> Bitmap Heap Scan on test_eu (cost=113.09..459.80 rows=88 width=1295)

Recheck Cond: (eng_discription ~ '.*remote controller.*'::text)

-> Bitmap Index Scan on eu_index (cost=0.00..113.07 rows=88 width=0)

Index Cond: (eng_discription ~ '.*remote controller.*'::text)

スコアの見方

Seq Scan on w_user (cost=0.00..178.50 rows=50 width=161)

1	Seq Scan on w_user (cost=0.00..178.50 rows=50 width=161)

0.00がはじめのデータ取得で178.50が最後のデータ取得の時間

AWSの場合

そのままだとエラーになる

rulings=# CREATE EXTENSION pg_trgm;
ERROR:  機能拡張の制御ファイル"/usr/pgsql-12/share/extension/pg_trgm.control"をオープンできませんでした: No such file or directory

1 2	rulings=# CREATE EXTENSION pg_trgm; ERROR: 機能拡張の制御ファイル"/usr/pgsql-12/share/extension/pg_trgm.control"をオープンできませんでした: No such file or directory

contribをインストール

yum -y install postgresql12-contrib

1	yum -y install postgresql12-contrib

再度以下を実行するだけで準備完了

CREATE EXTENSION pg_trgm;

1	CREATE EXTENSION pg_trgm;

CREATE EXTENSIONが出ればOK

データベースのリストア(ローカルとAWS)

2020年8月23日 by 河副太智 Leave a Comment

ローカルの場合

■一度該当のデータベースを削除して入れ直さないとデータが二重になる

■データベースの削除の際はpsqlでログインしてから行う

DROP DATABASE rulixxx;

1	DROP DATABASE rulixxx;

■データベース作成もpsqlで行う

CREATE DATABASE ruliXXX;

1	CREATE DATABASE ruliXXX;

■dumpしたsqlのファイルがある場所にcdで移動して実行する
※リストアはpsqlではなく通常のコマンドで実行

データベースごとリストア

pg_restore -C -U postgres -d rulixxx rulixxx.sql

1	pg_restore -C -U postgres -d rulixxx rulixxx.sql

テーブルのみリストア

pg_restore -C -U app_admin -d rulixxx -t test_xx test_xx.sql

1	pg_restore -C -U app_admin -d rulixxx -t test_xx test_xx.sql

■以下のようなエラーが出るが無視
ld not execute query: ERROR: relation “pre_xx” already exists
上記エラーが出ても暫くすればエラーを無視しましたと表示され、リストア完了

■データベースごとリストアしても一部のテーブルだけがリストアできない場合は
テーブルだけを指定してリストア

pg_restore -C -U postgres -d rulixxx -t pre_xx rulixxx.sql

1	pg_restore -C -U postgres -d rulixxx -t pre_xx rulixxx.sql

■リストアが成功したかどうかは以下のコードで件数を確認

SELECT COUNT( * ) FROM all_xxxx;

1	SELECT COUNT( * ) FROM all_xxxx;

いくらリストアしても件数0と表示される場合dumpしたsqlファイルが
破損している可能性もあるので別のsqlファイルで実行してみる。

AWSの場合

■一度該当のデータベースを削除して入れ直さないとデータが二重になる
■データベースの削除の際はpsqlでログインしてから行う

DROP DATABASE rulixxx;

1	DROP DATABASE rulixxx;

■データベース作成もpsqlで行う

CREATE DATABASE ruliXXX;

1	CREATE DATABASE ruliXXX;

■dumpしたsqlのファイルがある場所にcdで移動して実行する
※リストアはpsqlではなく通常のコマンドで実行

データベースごとリストア

pg_restore -C -U app_admin -d rulixxx rulixxx.sqlて

1	pg_restore -C -U app_admin -d rulixxx rulixxx.sqlて

テーブルのみリストア

pg_restore -C -U app_admin -d rulixxx -t test_xx test_xx.sql

1	pg_restore -C -U app_admin -d rulixxx -t test_xx test_xx.sql

■以下のようなエラーが出るが無視て待つ
ld not execute query: ERROR: relation “pre_xx” already exists

■上記エラーが出てからリストアまで時間がかかるので放置。
「pg_restore: 警告: リストア中に無視されたエラー数: 7」というメッセージで
リストア完了

■データベースごとリストアしても一部のテーブルだけがリストアできない場合は
テーブルだけを指定してリストア

pg_restore -C -U postgres -d rulixxx -t pre_xx rulixxx.sql

1	pg_restore -C -U postgres -d rulixxx -t pre_xx rulixxx.sql

■リストアが成功したかどうかは以下のコードで件数を確認

SELECT COUNT( * ) FROM all_xxxx;

1	SELECT COUNT( * ) FROM all_xxxx;

いくらリストアしても件数0と表示される場合dumpしたsqlファイルが
破損している可能性もあるので別のsqlファイルで実行してみる。

PostgreSQLのdumpファイルをAWSにインポート

2020年8月19日 by 河副太智 Leave a Comment

ローカルで作成したdumpファイルをファイル転送(winSCP等)で

/var/tmp/

に置いてから以下を実行

pg_restore -C -d postgres rulings.sql

1	pg_restore -C -d postgres rulings.sql

AWSにpostgreSQL12をインストールする

2020年8月19日 by 河副太智 Leave a Comment

awsに用意されているpostgreSQLのバージョンは古いので新しいバージョンを
入れる場合は以下を参照する。
（古いバージョンがインストールされている場合は以下のコードで削除）

sudo yum -y remove postgresql*

1	sudo yum -y remove postgresql*

インストール手順解説↓

How To Install PostgreSQL 12 on Amazon Linux 2