完全に個人用のPostgreSQLのメモ

※ 完全に個人用メモのため，随時追記していきます．

PostgreSQLをアップデートしたとき

To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To start postgresql:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres

$ cat /usr/local/var/log/postgresql.log

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

DBのオーナー変更

ALTER DATABESE [DB_NAME] OWNER [OWNER_NAME] TO [NEW_OWNER_NAME];

Role系

Role一覧

\du

Role作成

CREATE ROLE [ROLE_NAME];

権限を持たせてRoleを作成

※ デフォルトのユーザーが持っている権限を例に

CREATE ROLE [ROLE_NAME] WITH SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;

Roleに権限を与える

※ デフォルトのユーザーが持っている権限を例に

ALTER ROLE [ROLE_NAME] SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;

Role削除

DROP ROLE [ROLE_NAME];