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