PostgreSQL実験室/データベース作成
- 記事一覧 PostgreSQL実験室
データベース作成
- 検証作業用のデータベースを作成します.
$ LC_ALL=C psql -d postgres -U postgres ... postgres@postgres=# create database dbx owner dbxadmin template template1 tablespace dbx_tbs1; CREATE DATABASE postgres@postgres=# \q
スキーマ作成
- 検証作業用のスキーマを作成します.
$ LC_ALL=C psql -d dbx -U dbxadmin ... dbxadmin@dbx=# create schema dbxowner1 authorization dbxowner1; CREATE SCHEMA dbxadmin@dbx=# grant create,usage on schema dbxowner1 to dbxowner1; GRANT dbxadmin@dbx=# grant usage on schema dbxowner1 to dbxuser1; GRANT dbxadmin@dbx=# grant usage on schema dbxowner1 to dbxuser2; GRANT dbxadmin@dbx=# create schema dbxowner2 authorization dbxowner2; CREATE SCHEMA dbxadmin@dbx=# grant create,usage on schema dbxowner2 to dbxowner2; GRANT dbxadmin@dbx=# grant usage on schema dbxowner2 to dbxuser1; GRANT dbxadmin@dbx=# grant usage on schema dbxowner2 to dbxuser2; GRANT dbxadmin@dbx=# commit; COMMIT dbxadmin@dbx=# \q
ユーザー設定変更
search_path
- サーチパスを設定します.
$ LC_ALL=C psql -d dbx -U dbxadmin ... dbxadmin@dbx=# ALTER ROLE dbxowner1 SET search_path TO "\$user", dbxowner1, dbxowner2, public; ALTER ROLE dbxadmin@dbx=# ALTER ROLE dbxowner2 SET search_path TO "\$user", dbxowner1, dbxowner2, public; ALTER ROLE dbxadmin@dbx=# ALTER ROLE dbxuser1 SET search_path TO "\$user", dbxowner1, dbxowner2, public; ALTER ROLE dbxadmin@dbx=# ALTER ROLE dbxuser2 SET search_path TO "\$user", dbxowner1, dbxowner2, public; ALTER ROLE dbxadmin@dbx=# commit; COMMIT dbxadmin@dbx=# \q
default tablespace
- ユーザーのデフォルトテーブル空間を設定します.
$ LC_ALL=C psql -d dbx -U dbxadmin ... dbxadmin@dbx=#ALTER USER dbxowner1 SET default_tablespace TO dbx_tbs1; ALTER ROLE dbxadmin@dbx=#ALTER USER dbxowner2 SET default_tablespace TO dbx_tbs1; ALTER ROLE dbxadmin@dbx=#commit; COMMIT dbxadmin@dbx=# \q