PostgreSQLコマンド

PostgreSQL情報コマンド

## 以下一例になります

su -

su - postgres

## データベース一覧
psql -p xxxx(通常は5532) postgres -c '\l'

## 個別データベース の情報取得
psql -p xxxx(通常は5532) ユーザ作成データベース名 -c '\dn'

## ロール postgresの情報取得 パターン１
psql -p xxxx(通常は5532) postgres -c '\du'

## ロール postgresの情報取得 パターン２
psql -p xxxx(通常は5532) postgres -c 'select * from pg_user;'

## スキーマ の情報取得
psql -p xxxx(通常は5532) postgres -c '\dn'

## postgresql.conf取得
## 事前にfind で検索が望ましい find /var/lib -iname postgresql.conf
cat /var/lib/pgsql/xxx(バージョン)/postgres/postgresql.conf

## pg_hba.conf取得
## 事前にfind で検索が望ましい find /var/lib -iname pg_hba.conf
cat /var/lib/pgsql/xxx(バージョン)/postgres/postgresql.conf

## pg_settings取得
psql -p xxxx(通常は5532) postgres -c 'select name,setting from pg_settings;'

## pg_database取得
psql -p xxxx(通常は5532) postgres -c 'select name,setting from pg_database;'

## 可能であればcron情報
## バックアップ取得方法（世代保管数・削除方法含む）
## ログローテーション方法

レベルアップ版

## repoインストール
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

## posgreインストール

dnf install -y postgresql16-server

## 初回インストール後は以下のコマンドが必須
※初期化するディレクトリを指定してもよいはず

/usr/pgsql-16/bin/postgresql-16-setup initdb

Initializing database ... OK

## posgresqlサービス有効化
systemctl enable --now postgresql-16

## 機能拡張したいので
dnf install -y postgresql16-contrib


## 作業ユーザスイッチ

su - postgres


## データベース作成
psql -c "CREATE DATABASE mydb;"
psql -c "CREATE DATABASE mydb2;"

## データベース専用ユーザ作成
psql -c "CREATE USER myuser WITH PASSWORD 'my_password';"

## 作成ユーザに作成したデータベースの権限を付与
psql -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;"
psql -c "GRANT ALL PRIVILEGES ON DATABASE mydb2 TO myuser;"

## 機能拡張一覧確認

psql -c "SELECT name, default_version, comment FROM pg_available_extensions;"

## 機能拡張実施

psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;"
psql -d mydb2 -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;"

## テーブル作成

psql -d mydb -c "CREATE TABLE categories (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL);"
psql -d mydb2 -c "CREATE TABLE categories (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL);"
psql -d mydb -c "CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100));"
psql -d mydb2 -c "CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100));"
psql -d mydb -c "CREATE TABLE posts (id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, content TEXT, user_id INTEGER REFERENCES users(id), category_id INTEGER REFERENCES categories(id), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);"
psql -d mydb2 -c "CREATE TABLE posts (id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, content TEXT, user_id INTEGER REFERENCES users(id), category_id INTEGER REFERENCES categories(id), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);"

## 所有者変更
psql -d mydb -c "ALTER SCHEMA public OWNER TO myuser;"
psql -d mydb2 -c "ALTER SCHEMA public OWNER TO myuser;"
psql -d mydb -c "\dt;"
psql -d mydb2 -c "\dt;"

## スキーマ名確認
psql -d mydb -c "\dn;"
psql -d mydb2 -c "\dn;"

## データ投入

psql -d mydb -c "INSERT INTO categories (name) VALUES ('Tech'), ('Daily');"
psql -d mydb2 -c "INSERT INTO categories (name) VALUES ('Tech'), ('Daily');"
psql -d mydb -c "INSERT INTO users (username, email) VALUES ('tanaka', '[e-mail]'), ('sato', '[e-mail]');"
psql -d mydb2 -c "INSERT INTO users (username, email) VALUES ('tanaka', '[e-mail]'), ('sato', '[e-mail]');"
psql -d mydb -c "INSERT INTO posts (title, content, user_id, category_id) VALUES ('CentOS 9 Setup', 'Installation guide...', 1, 1),
 ('My Coffee', 'Today is a good day.', 2, 2);"
psql -d mydb2 -c "INSERT INTO posts (title, content, user_id, category_id) VALUES ('CentOS 9 Setup', 'Installation guide...', 1, 1),
 ('My Coffee', 'Today is a good day.', 2, 2);"

## データのみのバックアップ
## ただし

pg_dump -Fc --data-only -t categories mydb > categories.dump
pg_dump -Fc --data-only -t users mydb > users.dump
pg_dump -Fc --data-only -t posts mydb > posts.dump

## テーブルデータ削除

psql -d mydb2 -c "truncate table categories restart identity cascade"
psql -d mydb2 -c "truncate table users restart identity cascade"
psql -d mydb2 -c "truncate table posts restart identity cascade"

## 親テーブルか子テーブルか確認するコマンド

psql -d mydb -c "
SELECT
    conrelid::regclass AS table_name,
    confrelid::regclass AS parent_table_name
FROM pg_constraint
WHERE contype = 'f';"

左：子供テーブル 右：親テーブル  
 table_name | parent_table_name
------------+-------------------
 posts      | users
 posts      | categories
(2 行)


## バックアップしたデータを投入
## 条件あり「pg_restore を行う際、親テーブル（categories, users）を先に、子テーブル（posts）を後に実行してください。」


pg_restore -d mydb2 --data-only -t categories categories.dump
pg_restore -d mydb2 --data-only -t users users.dump
pg_restore -d mydb2 --data-only -t posts posts.dump

## もしスーパーユーザーが使える場合
## 「--disable-triggers」を付けると親テーブル・子テーブルの順番関係なし
pg_restore -d mydb2 --data-only --disable-triggers -t categories categories.dump
pg_restore -d mydb2 --data-only --disable-triggers -t users users.dump
pg_restore -d mydb2 --data-only --disable-triggers -t posts posts.dump


## シーケンス番号を進める

psql -d mydb2 -c "SELECT setval('categories_id_seq', (SELECT MAX(id) FROM categories));"
psql -d mydb2 -c "SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));"
psql -d mydb2 -c "SELECT setval('posts_id_seq', (SELECT MAX(id) FROM posts));"

## シーケンス番号確認

psql -d mydb2 -c "SELECT last_value FROM categories_id_seq;"
psql -d mydb2 -c "SELECT last_value FROM users_id_seq;"
psql -d mydb2 -c "SELECT last_value FROM posts_id_seq;"

## 外部キー整合性チェック
## 今回は実施しない。pg_restore時に--disable-triggers（制約無視オプション）を使わないため
"SELECT count(*) FROM posts WHERE user_id NOT IN (SELECT id FROM users);"

## データベース全体の統計情報を更新し、検索を高速に保つ
## 今回は TRUNCATE をした直後にデータを入れているため、データベースの中身は非常にクリーンな状態です。
## そのため、今すぐ掃除（VACUUM）をするメリットはほとんどありません。

psql -d mydb2 -c "ANALYZE VERBOSE;"