PostgreSQL備忘録

インストール

sudo yum install postgresql-server
sudo postgresql-setup initdb
sudo systemctl start postgresql.service
sudo passwd postgres
su - postgres
psql -l

psql

alter role postgres with password 'postgres';

pg_hba.conf

host    all             all             127.0.0.1/32            password

Linux コマンド

• データベース確認
  • psql -l
• 接続
  • psql -d

• データベース作成

createdb gatein-idm_portal;
createdb gatein-jcr_portal;
createdb gatein-idm_ecmdemo;
createdb gatein-jcr_ecmdemo;

dropdb gatein-idm_portal;
dropdb gatein-jcr_portal;
dropdb gatein-idm_ecmdemo;
dropdb gatein-jcr_ecmdemo;

psqlでは

create database test_database;

drop database test_database;

ユーザ作成

CREATE ROLE testuser WITH LOGIN PASSWORD 'password';

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO testuser;
GRANT SELECT,UPDATE ON ALL SEQUENCES IN SCHEMA public TO testuser;

psql コマンド

• データベース確認
  • \l
• テーブル確認
  • \d
• ユーザ確認
  • \du
• データベース変更
  • \c

ファイル実行

A) psql内で

$ psql mydatabase
# \i myscript.sql

B) コマンド直で

$ psql -f myscript.sql

dropdb bpms640_02059913
createdb bpms640_02059913
psql bpms640_02059913
\i ddl/BPMS640/quartz_tables_postgres.sql

foreign key 制約を一時無効化

SET session_replication_role = 'replica';

... 作業 ..

SET session_replication_role = 'origin';

https://stackoverflow.com/questions/38112379/disable-postgresql-foreign-key-checks-for-migrations

トラブルシューティング

Fedora15(postgresql9.0.6)にアップグレードしたらpostgresqlが起動しなくなった

# service postgresql start
Starting postgresql (via systemctl):  Job failed. See system logs and 'systemctl status' for details.
                                                           [FAILED]

yum install postgresql-upgrade
service postgresql upgrade

Fedora16のクリーンインストール時も同様のエラー。以下で解決した
sudo postgresql-setup initdb

Fedora 22
sudo postgresql-setup upgrade

サイズ

SELECT relname AS table_name, pg_size_pretty(pg_relation_size(oid)) AS table_size, pg_size_pretty(pg_total_relation_size(oid)) AS total_size FROM pg_class WHERE relkind in ('r','i') ORDER BY pg_relation_size(oid) DESC;

SELECT relname AS table_name, pg_size_pretty(pg_relation_size(oid)) AS table_size, pg_size_pretty(pg_total_relation_size(oid)) AS total_size FROM pg_class WHERE relname = 'pg_largeobject';

VACUUM

vacuumlo

sudo yum install postgresql-contrib

VACUUM FULL

BLOB のクリーンアップ

vacuumlo -h localhost -p 5432 -U postgres -W -v testbpms630
vacuumdb --dbname testbpms630 --full --table 'pg_largeobject' --analyze --host localhost --port 5432 -U postgres -W --verbose
reindexdb --dbname testbpms630 --table 'pg_largeobject' --host localhost --port 5432 -U postgres -W

https://access.redhat.com/solutions/56258

トリガー

SELECT event_object_schema,
       event_object_table,
       trigger_schema,
       trigger_name
FROM information_schema.triggers

pg_largeobject の bytea をテキストに変換

select encode(data, 'escape') from pg_largeobject where loid = 60994;