【PostgreSQL】PostgreSQL の標準付属ツール(psql、pg_ctl、initdb、COPY、その他)の説明とコマンド手順【Part.4】

今回は PostgreSQL の標準付属ツール(psql、pg_ctl、initdb、\copy、COPY、その他)の使用手順を解説します。

 

 

 

psql コマンド

日々 PostgreSQL データベースを運用しているなら psql コマンドは一番使用するコマンドかもしれません。

psql コマンドは OS に対するシェルのような対話型インタフェースです。

psql コマンドで様々な処理が可能です。

psql コマンドは「スーパーユーザー」「一般ユーザー」ともに使用できます。

 

 

psql コマンドで可能な処理

  • SQL 文の実行(psql -c コマンド)
  • メタコマンドの実行(メタコマンドで OS コマンドを実行できる)
  • OS コマンドの実行
  • ファイルから SQL 文を読み込み実行
  • データベースにログイン(接続先は IP アドレスやホスト名で指定できる)
  • リダイレクトを使用して SQL 文記載のファイルを実行

 

 

■psql コマンドオプション

  • -U ユーザー名 ← データベースへ接続する際のユーザー名を指定します。
  • -h IPアドレス or ホスト名 ← 接続先のホスト名 or IP アドレスを指定します。
  • -d データベース名 ← 接続するデータベース名を指定します。
  • -p ポート番号 ← 接続先ホストのポート番号を指定します。

 

 

 

■データベース一覧の表示

-bash-4.2$ psql -l 
                                  List of databases 
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges 
———–+———-+———-+————-+————-+———————– 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + 
           |          |          |             |             | postgres=CTc/postgres 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + 
           |          |          |             |             | postgres=CTc/postgres 
(3 rows) 
 
-bash-4.2$

 

テンプレートデータベース(template0,template1)について

initdb コマンドでデータベースクラスタを作成しますが、その際に以下の3つのデータベースが作成されます。

  • postgres
  • template0
  • template1

初期状態では「template0」「template1」の内容は全く同一ですが、役割は異なります。

  • template0 ← 変更不可能
  • template1 ← 変更可能

 

 

データベースへの接続

psql コマンドでデフォルトのデータベースへ接続できます。

-bash-4.2$ psql
psql (9.2.24)
Type “help” for help.

 

postgres=#

 

 

■特定のユーザーを指定してデータベースにアクセスする場合

特定のユーザーを指定してデータベースにアクセスする場合は以下のコマンドで可能です。

 

【構文】

psql -U <ユーザー名>

 

【参考】

-bash-4.2$ psql -U postgres
psql (9.2.24)
Type “help” for help.

 

postgres=#

 

 

 

メタコマンド

メタコマンド一覧です。

postgres=# \? 
General 
  \copyright             show PostgreSQL usage and distribution terms 
  \g [FILE] or ;         execute query (and send results to file or |pipe) 
  \h [NAME]              help on syntax of SQL commands, * for all commands 
  \q                     quit psql 
 
Query Buffer 
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor 
  \ef [FUNCNAME [LINE]]  edit function definition with external editor 
  \p                     show the contents of the query buffer 
  \r                     reset (clear) the query buffer 
  \s [FILE]              display history or save it to file 
  \w FILE                write query buffer to file 
 
Input/Output 
  \copy …              perform SQL COPY with data stream to the client host 
  \echo [STRING]         write string to standard output 
  \i FILE                execute commands from file 
  \ir FILE               as \i, but relative to location of current script 
  \o [FILE]              send all query results to file or |pipe 
  \qecho [STRING]        write string to query output stream (see \o) 
 
Informational 
  (options: S = show system objects, + = additional detail) 
  \d[S+]                 list tables, views, and sequences 
  \d[S+]  NAME           describe table, view, sequence, or index 
  \da[S]  [PATTERN]      list aggregates 
  \db[+]  [PATTERN]      list tablespaces 
  \dc[S+] [PATTERN]      list conversions 
  \dC[+]  [PATTERN]      list casts 
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere 
  \ddp    [PATTERN]      list default privileges 
  \dD[S+] [PATTERN]      list domains 
  \det[+] [PATTERN]      list foreign tables 
  \des[+] [PATTERN]      list foreign servers 
  \deu[+] [PATTERN]      list user mappings 
  \dew[+] [PATTERN]      list foreign-data wrappers 
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions 
  \dF[+]  [PATTERN]      list text search configurations 
  \dFd[+] [PATTERN]      list text search dictionaries 
  \dFp[+] [PATTERN]      list text search parsers 
  \dFt[+] [PATTERN]      list text search templates 
  \dg[+]  [PATTERN]      list roles 
  \di[S+] [PATTERN]      list indexes 
  \dl                    list large objects, same as \lo_list 
  \dL[S+] [PATTERN]      list procedural languages 
  \dn[S+] [PATTERN]      list schemas 
  \do[S]  [PATTERN]      list operators 
  \dO[S+] [PATTERN]      list collations 
  \dp     [PATTERN]      list table, view, and sequence access privileges 
  \drds [PATRN1 [PATRN2]] list per-database role settings 
  \ds[S+] [PATTERN]      list sequences 
  \dt[S+] [PATTERN]      list tables 
  \dT[S+] [PATTERN]      list data types 
  \du[+]  [PATTERN]      list roles 
  \dv[S+] [PATTERN]      list views 
  \dE[S+] [PATTERN]      list foreign tables 
  \dx[+]  [PATTERN]      list extensions 
  \l[+]                  list all databases 
  \sf[+] FUNCNAME        show a function’s definition 
  \z      [PATTERN]      same as \dp 
 
Formatting 
  \a                     toggle between unaligned and aligned output mode 
  \C [STRING]            set table title, or unset if none 
  \f [STRING]            show or set field separator for unaligned query output 
  \H                     toggle HTML output mode (currently off) 
  \pset NAME [VALUE]     set table output option 
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null| 
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager}) 
  \t [on|off]            show only rows (currently off) 
  \T [STRING]            set HTML <table> tag attributes, or unset if none 
  \x [on|off|auto]       toggle expanded output (currently off) 
 
Connection 
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} 
                         connect to new database (currently “postgres”) 
  \encoding [ENCODING]   show or set client encoding 
  \password [USERNAME]   securely change the password for a user 
  \conninfo              display information about current connection 
 
Operating System 
  \cd [DIR]              change the current working directory 
  \setenv NAME [VALUE]   set or unset environment variable 
  \timing [on|off]       toggle timing of commands (currently off) 
  \! [COMMAND]           execute command in shell or start interactive shell 
 
Variables 
  \prompt [TEXT] NAME    prompt user to set internal variable 
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters 
  \unset NAME            unset (delete) internal variable 
 
Large Objects 
  \lo_export LOBOID FILE 
  \lo_import FILE [COMMENT] 
  \lo_list 
  \lo_unlink LOBOID      large object operations 
postgres=#

 

 

createdb コマンド

createdb コマンドは、新しいデータベースを作成するコマンドです。

 

■オプション

  • -E ← データベースで使用する文字コード(エンコーディング)を指定します。例:UTF8、EUC_JP など
  • -O ← データベースの所有者を指定します。
  • -l ← データベースで使用するロケールを指定します。
  • -T ← テンプレートデータベースを指定します。

 

 

 

pg_ctl コマンド

pg_ctl コマンドは以下の操作を実施できます。

  • PostgreSQL データベースの起動(pg_ctl start)
  • PostgreSQL データベースの停止(pg_ctl stop)
  • PostgreSQL データベースの再起動(pg_ctl restart)
  • PostgreSQL データベースの状態(ステータス)(pg_ctl)
  • PostgreSQL データベースの初期化(pg_ctl initdb)
  • PostgreSQL データベースの設定ファイルの再読み込み(pg_ctl reload)
  • PostgreSQL データベースへのシグナルの送信(pg_ctl kill)

 

 

PostgreSQL データベースの起動

pg_ctl start コマンドで PostgreSQL を起動できます。

PostgreSQL はバックグラウンドで起動します。

 

■PostgreSQL データベースの起動

-bash-4.2$ pg_ctl start
server starting
-bash-4.2$

 

 

PostgreSQL データベースの停止(pg_ctl stop)

pg_ctl stop コマンドで PosgreSQL を停止することができます。

 

■PostgreSQL データベースの停止(デフォルト)

[root@ip-172-31-38-38 ~]# su – postgres
Last login: Sun Nov 18 06:46:59 UTC 2018 on pts/3
-bash-4.2$ pg_ctl stop
waiting for server to shut down…. done
server stopped
-bash-4.2$

 

pg_ctl stop -m xxx でシャットダウンモードを指定することができます。

 

  • -m smart ← スマートシャットダウン(デフォルト)
  • -m fast ← 高速シャットダウン
  • -m immediate ← 即時シャットダウン

 

【即時シャットダウンの例】

-bash-4.2$ pg_ctl stop -m immediate
waiting for server to shut down…. done
server stopped
-bash-4.2$

 

 

PostgreSQL データベースにシグナルを送信する(pg_ctl kill)

pg_ctl kill コマンドで postgres プロセスにシグナルを送信することができます。

 

新規データベースクラスタを作成する

最初に postgres アカウントの権限が割り振られているディレクトリを作成します。

bash-4.2$ pwd 
/var/lib/pgsql 
-bash-4.2$ ls 
backups  data  initdb.log 
-bash-4.2$ mkdir test

-bash-4.2$ ls 
backups  data  initdb.log  test 
-bash-4.2$

 

 

新規データベースクラスタを作成します。

-bash-4.2$ pg_ctl -D /var/lib/pgsql/test initdb 
The files belonging to this database system will be owned by user “postgres”. 
This user must also own the server process. 
 
The database cluster will be initialized with locale “en_US.UTF-8”. 
The default database encoding has accordingly been set to “UTF8”. 
The default text search configuration will be set to “english”. 
 
fixing permissions on existing directory /var/lib/pgsql/test … ok 
creating subdirectories … ok 
selecting default max_connections … 100 
selecting default shared_buffers … 32MB 
creating configuration files … ok 
creating template1 database in /var/lib/pgsql/test/base/1 … ok 
initializing pg_authid … ok 
initializing dependencies … ok 
creating system views … ok 
loading system objects’ descriptions … ok 
creating collations … ok 
creating conversions … ok 
creating dictionaries … ok 
setting privileges on built-in objects … ok 
creating information schema … ok 
loading PL/pgSQL server-side language … ok 
vacuuming database template1 … ok 
copying template1 to template0 … ok 
copying template1 to postgres … ok 
 
WARNING: enabling “trust” authentication for local connections 
You can change this by editing pg_hba.conf or using the option -A, or 
–auth-local and –auth-host, the next time you run initdb. 
 
Success. You can now start the database server using: 
 
    /usr/bin/postgres -D /var/lib/pgsql/test ← 起動方法が記載されています。 
or 
    /usr/bin/pg_ctl -D /var/lib/pgsql/test -l logfile start ← 起動方法が記載されています。  
 
-bash-4.2$ 

 

上記記載の通りにデータベースクラスタを起動します。

-bash-4.2$ /usr/bin/pg_ctl -D /var/lib/pgsql/test -l logfile start

server starting

-bash-4.2$

 

 

■ディレクトリの権限がない場合

postgres アカウントに対象のディレクトリの権限がない場合は以下のようにエラーになります。

-bash-4.2$ pg_ctl -D /test initdb 
The files belonging to this database system will be owned by user “postgres”. 
This user must also own the server process. 
 
The database cluster will be initialized with locale “en_US.UTF-8”. 
The default database encoding has accordingly been set to “UTF8”. 
The default text search configuration will be set to “english”. 
 
creating directory /test … initdb: could not create directory “/test”: Permission denied ← 権限がないためエラーになります。
pg_ctl: database system initialization failed 
-bash-4.2$

 

 

pg_ctl コマンドの実行アカウント

root コマンドで pg_ctl コマンドを実行すると下記のようにエラーになります。

[root@redhat7 ~]# pg_ctl status 
pg_ctl: cannot be run as root 
Please log in (using, e.g., “su”) as the (unprivileged) user that will 
own the server process. 
[root@redhat7 ~]#

 

pg_ctl コマンドを実行する場合は「postgres」アカウントにスイッチします。

[root@redhat7 ~]# su – postgres 
Last login: Fri Dec 28 15:35:05 UTC 2018 on pts/0 
-bash-4.2$ pg_ctl status 
pg_ctl: server is running (PID: 3764) 
/usr/bin/postgres “-D” “/var/lib/pgsql/data” “-p” “5432” 
-bash-4.2$

 

 

 

 

 

initdb コマンド

initdb コマンドはデータベースクラスタを作成するコマンドです。

ディレクトリを作成します。

-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ mkdir test01
-bash-4.2$ ls
backups data initdb.log test test01
-bash-4.2$

 

 

initdb コマンドを実行します。

-bash-4.2$ initdb -D /var/lib/pgsql/test01 
The files belonging to this database system will be owned by user “postgres”. 
This user must also own the server process. 
 
The database cluster will be initialized with locale “en_US.UTF-8”. 
The default database encoding has accordingly been set to “UTF8”. 
The default text search configuration will be set to “english”. 
 
fixing permissions on existing directory /var/lib/pgsql/test01 … ok 
creating subdirectories … ok 
selecting default max_connections … 100 
selecting default shared_buffers … 32MB 
creating configuration files … ok 
creating template1 database in /var/lib/pgsql/test01/base/1 … ok 
initializing pg_authid … ok 
initializing dependencies … ok 
creating system views … ok 
loading system objects’ descriptions … ok 
creating collations … ok 
creating conversions … ok 
creating dictionaries … ok 
setting privileges on built-in objects … ok 
creating information schema … ok 
loading PL/pgSQL server-side language … ok 
vacuuming database template1 … ok 
copying template1 to template0 … ok 
copying template1 to postgres … ok 
 
WARNING: enabling “trust” authentication for local connections 
You can change this by editing pg_hba.conf or using the option -A, or 
–auth-local and –auth-host, the next time you run initdb. 
 
Success. You can now start the database server using: 
 
    postgres -D /var/lib/pgsql/test01 
or 
    pg_ctl -D /var/lib/pgsql/test01 -l logfile start 
 
-bash-4.2$

 

※新規データベースクラスタ作成コマンドの「pg_ctl initdb」コマンドと同様の処理をしています。

 

 

 

SET コマンド

SET コマンドは PostgreSQL データベースの稼働中にパラメータを変更するコマンドです。

ただしすべてのパラメータが変更できるわけではなく、影響の範囲と権限で可能なパラメータが決まってきます。

 

 

SHOW コマンド

SHOW コマンドは実行中の PostgreSQL データベースの現在のパラメータを表示するコマンドです。

 

postgres=# SHOW ALL; 
       name               |   setting   |  description  
————————–+————-+————————————————– 
 allow_system_table_mods  | off         | Allows modifications of the structure of system tables. 
 application_name         | psql        | Sets the application name to be reported in statistics and logs. 
 archive_command          | (disabled)  | Sets the shell command that will be called to archive a WAL file. 
 archive_mode             | off         | Allows archiving of WAL files using archive_command. 
 archive_timeout          | 0           | Forces a switch to the next xlog file if a new file has not been sta 
rted within N seconds. 
 array_nulls              | on          | Enable input of NULL elements in arrays. 
 authentication_timeout   | 1min        | Sets the maximum allowed time to complete client authentication. 
 autovacuum               | on          | Starts the autovacuum subprocess.

 

~ 省略 ~

 

 

 

\copy コマンド、COPY コマンド

テーブルとファイル間でデータをコピーします。

  • \copy ← クライアント側のファイルとテーブル間のデータコピーします。
  • COPY ← サーバー側のファイルとテーブル間のデータをコピーします。

 

COPY コマンド

ファイル名を指定する場合は、データベースのスーパーユーザー権限が必要です。

ファイル名を指定しない場合は、データベースのスーパーユーザー権限は不要です。

 

 

\copy コマンド

\copy コマンドは、最初に「\」が付いているので PostgreSQL のメタコマンドになります。

\copy コマンドの実行に対してデータベースのスーパーユーザー権限は不要です。

 

 

 

 

PostgreSQL シリーズ

今まで学習した PostgreSQL の技術をシリーズとしてまとめました。

 

【PostgreSQL】PostgreSQL の特徴と基本【Part.1】

 

【PostgreSQL】【正規化】リレーショナルデータベース(RDMS)の基本【Part.2】

 

【PostgreSQL】PostgreSQL のインストールと初期設定【Part.3】

 

【PostgreSQL】PostgreSQL の標準付属ツールの説明とコマンド手順【Part.4】

 

【PostgreSQL】PostgreSQL の設定ファイル(postgresql.conf、pg_hba.conf)の解説【Part.5】

 

【PostgreSQL】PostgreSQL のバックアップ手順とリストア手順【Part.6】

 

【PostgreSQL】PostgreSQL の運用管理(ユーザー管理、バキューム)【Part.7】

 

【PostgreSQL】PostgreSQL の基本的な SQL文 とオブジェクトについて【Part.8】

 

【PostgreSQL】PostgreSQL の組み込み関数、ユーザー定義関数、演算子について【Part.9】

 

【PostgreSQL】PostgreSQL のトランザクションについて【Part.10】

 

【PostgreSQL】SQL文【Part.11】

 

【PostgreSQL】テーブル設計(データ型、制約)【Part.12】

 

 

 

 

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

コメント

コメントする

AlphaOmega Captcha Medica  –  What Do You See?
     
 

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください