- PostreDB コマンド備忘録
- データベース操作
- データベース選択
- テーブル操作
- テーブルのスキーマの詳細を表示する。
- テーブル内のデータをすべて確認
- テーブル作成、データの挿入
- データの挿入
- データの削除
- Postgre 構築
- Postgre データダンプ
- PostgreDB Schema スキーマ
- postgresql のセッション
PostreDB コマンド備忘録
PostgreDBへのログイン方法
psql -d [database name] -U [User name] -h [host] -p [port] IE. hostが10.165.8.60、portが5432, User名がpostgres, DB名がposgre_dbにアクセスする。
psql -d posgre_db -U postgres -h 10.165.8.61 -p 5432
インストール直後のPostgreDBへ、ログインする際の注意
PostgreDBをインストールした直後のログインはPeer認証となっている。
Peer Authentication(以下Peer認証) とは postgresql に ローカルから接続する際に, 接続を試みるプロセスの実効ユーザ(OS側のユーザ)とデータベースへのログインユーザ(データベース側のユーザ)が一致している場合のみ接続を許可する認証方法である.
PostgresDBをインストール時に通常は、postgresユーザが作成されるはず。ただし、パスワードは設定されていない。
したがって、 1)postgres ユーザのパスワードを設定
$ sudo passwd postgres
2)postgresユーザになり、ログインする必要がある。
$su - postgres
3)ログイン
psql -U postgres psql: FATAL: Peer authentication failed for user "postgres" ubuntu@ip-172-31-26-117:~/EVA/EVA_v3.3_beta2/execution-framework$ su postgres Password: postgres@ip-172-31-26-117:/home/ubuntu/EVA/EVA_v3.3_beta2/execution-framework$ psql psql (9.5.19) Type "help" for help.
データベース操作
データベースサービスStatus確認
・サービスに登録されているか確認
$ systemctl list-unit-files -t service | grep postgres postgresql-9.6.service enabled
・起動
systemctl status postgresql-9.6
データベース一覧参照
# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------------------------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
database__manager_db | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
database_system | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
データベース選択
# \c <DB_NAME>
I.E postgresという名称のDBが選択される。
\c postgres SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) You are now connected to database "postgres" as user "postgres".
テーブル操作
テーブル一覧表示
# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------------------+----------+----------
public | memberships | table | postgres
public | memberships_memberid_seq | sequence | postgres
public | notifications | table | postgres
public | notifications_notificationid_seq | sequence | postgres
public | persons | table | postgres
public | persons_personid_seq | sequence | postgres
public | pictures | table | postgres
public | pictures_ | sequence | postgres
(8 rows)
補足： \dコマンドを利用すると、Sequenceも表示される。 \dtコマンドでは、Tableのみの表示となる。
Sequenceとは連番となる一意な整数を生成するデータベースのオブジェクトです。 一般的にシーケンスは、ユーザIDや登録番号等の主キーを生成する際によく利用されます。
テーブルのスキーマの詳細を表示する。
# \d <テーブル名>
I.E personsという名称のテーブルのスキーマを表示する。
#\d persons
Table "public.persons"
Column | Type | Modifiers
--------------------+--------------------------+------------------------------------------------------------
personid | integer | not null default nextval('persons_personid_seq'::regclass)
firstname | character varying(80) | not null
middlename | character varying(80) | not null
lastname | character varying(80) | not null
gender | character varying(15) | not null
age | integer | not null
address | character varying(500) | not null
companyname | character varying(100) | not null
type | character varying(50) | not null
phone | character varying(20) | not null
email | character varying(50) | not null
documenttype | character varying(25) | not null
documentnumber | character varying(25) | not null
documentexpirydate | date | not null
notes | text |
comments | character varying(500) |
matchingthreshold | real | not null
additiontime | timestamp with time zone | not null default now()
Indexes:
"persons_pkey" PRIMARY KEY, btree (personid)
"person_index" btree (firstname, middlename, lastname, gender, age, address, companyname, type, phone, email, documenttype, documentnumber, documentexpirydate, matchingthreshold, additiontime)
Referenced by:
TABLE "memberships" CONSTRAINT "memberships_personid_fkey" FOREIGN KEY (personid) REFERENCES persons(personid) ON DELETE CASCADE
TABLE "pictures" CONSTRAINT "pictures_personid_fkey" FOREIGN KEY (personid) REFERENCES persons(personid) ON DELETE CASCADE
テーブル内のデータをすべて確認
select * from <テーブル名>
I.E personsというテーブルのデータをすべて表示する。
# select * from persons;
personid | firstname | middlename | lastname | gender | age | address | companyname | type | phone | email | documenttype | documentnumber | documentexpirydate | notes | comments | | additiontime
----------+-----------------------------+------------+----------+---------+-----+---------+-------------+------+--------------+-------+--------------+----------------+--------------------+-------+----------+-------------------+-------------------------------
1 | testname_00000001 | | | unknown | 0 | | | | 000-000-0000 | | | | 5000-01-01 | { } | | 0 | 2019-03-12 08:17:06.376982+09
2 | testname_00000002 | | | unknown | 0 | | | | 000-000-0000 | | | | 5000-01-01 | { } | | 0 | 2019-03-12 08:17:07.065884+09
レコードを削減する
DELETE FROM テーブル名 WHERE 条件式;
I.E schoolテーブルから、studentnamがSato-san のレコードを消す場合。
delete from school where studentname='Sato-san';
DBからログアウト
#\q
Postgres 管理
Posgresのデータ保存先、confファイル保存先ディレクトリの確認方法
# sudo -u postgres psql
psql (9.5.21)
Type "help" for help.
postgres=# show data_directory;
data_directory
------------------------------
/var/lib/postgresql/9.5/main ★ここが保存Direcotry
(1 row)
postgres=# show config_file ; ★こちらは、confファイルの設定先
config_file
------------------------------------------
/etc/postgresql/9.5/main/postgresql.conf
(1 row)
テーブル作成、データの挿入
createコマンドでテーブルを作成する。 userテーブルを作成し、name,email、idのスキーマーを作成する。
# create table user(name varchar(20) , email varchar(20) , id integer) ;
データの挿入
insertコマンドでデータをInsertする。
# insert into table values ('Yamada', 'yamada@test.com', 1);
INSERT 0 1
データの削除
test=# delete FROM usertable where id=1; DELETE 2
Postgre 構築
Postgresを簡単に構築したいとき。Dockerでの起動方法
DBNAME=postgres PGUSER=postgres PGPASS=postgres PORT=35432 docker run --rm --name postgres -p $PORT:5432 \ -e POSTGRES_USER=$PGUSER \ -e POSTGRES_PASSWORD=$PGPASS \ -e POSTGRES_DB=$DBNAME \ -d postgres:9.6 postgres -N 200 postgres:12-alpine
Postgre データダンプ
以下のコマンドで、データのdumpを行うことができる。
pg_dumpコマンドによるDump
pg_dump -h [IP] -p [port] -U [ユーザー名] > [dump ファイル名]
I.E. pg_dump -h 192.168.10.3 -p 5432 -U postgres > pgdump
pg_dumpall によるダンプとリストア
pg_dumpallによりデータベースクラスタ全体のバックアップを取ることができます。
pg_dumpall -U [user名] -p [ポート番号] -h [PostgreのIP]
I.E
pg_dumpall -U postgres -h 10.165.8.57 -p 5432 > pg_dumpall
以下のコマンドで、pg_dumpによって出力されたファイルのリストアができる。
psql -U [user名] -p [ポート番号] -h [PostgreのIP] -f [pg_dumpallのダンプファイル]
I.E
psql -U postgres -p 5432 -h 127.0.0.1 -f pg_dumpall
PostgreDB Schema スキーマ
PostgreDBのSchemaは、テーブルをまとめたものになります。 *DBによって、Schemaの考え方は、異なるので注意。
schemaの確認方法
デフォルトでは、publicが定義されています。
=# \dn List of schemas Name | Owner --------+------------ test | postgresql public | postgres (2 rows)
schemaの作成
# create schema test1;
CREATE SCHEMA
schemaの切り替え
デフォルトでは、すべてpublic schemaとなるので、public以外のスキーマを利用するときは、切り替える必要があります。 以下のコマンドで、schemaを切り替えられます。以後、切り替えたschema内での操作となります。
# set search_path TO test1; SET
postgresql のセッション
セッション（session)の確認
以下のコマンドでpostgresqlのセッションを確認できます。
select * from pg_stat_activity where datname = 'データベース名';
以下のような結果が返ってきます。
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+-----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+---------------------------+---------------- 41675 | DATABSEName1| 37424 | 10 | postgres | psql | ::1 | | 41220 | 2024-01-24 10:03:43.558152+00 | | 2024-01-24 10:04:13.385401+00 | 2024-01-24 10:04:13.386181+00 | Client | ClientRead | idle | | | select * from setting ; | client backend (1 row)
セッションの切断
接続中のセッションを切断するSQLです。
DBを削除する際にセッションが残っていると削除できません。このような場合に、sesshonを削除することで、DBも削除できます。
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHEREdatname = 'データベース名';
