コンピューター ウィンドウズ インターネット
拡大
家 ›

基本的なPostgreSQLコマンド。 クラスターインスタンスの場所の検索と変更に関する基本的なPostgreSQLコマンド

こんにちは、今日は基本的なPostgreSQLコマンドについての小さなメモを作りたいと思います。 PosgreSQLは、インタラクティブにもコマンドラインからも操作できます。 プログラムはpsqlです。 このリストはあなたにとって非常に有用であり、さまざまなリソースを検索する時間を節約できると確信しています。 これは、Postgres DBMSに基づくオープンソースプロジェクトであり、1986年にリリースされ、PGDG開発者の世界的なグループによって開発されており、本質的には5〜8人ですが、それにもかかわらず、は非常に集中的に開発を行っており、すべての新機能を導入し、古いバグやエラーを修正しています。

インタラクティブモードでの基本的なPostgreSQLコマンド：

  • \ connectdb_name-db_nameという名前のデータベースに接続します
  • \ du-ユーザーのリスト
  • \ dp（または\ z）-テーブル、ビュー、シーケンス、それらへのアクセス権のリスト
  • \ di-インデックス
  • \ ds-シーケンス
  • \ dt-テーブルのリスト
  • \ dt +-説明付きのすべてのテーブルのリスト
  • \ dt * s *-名前にsを含むすべてのテーブルのリスト
  • \ dv-ビュー
  • \ dS-システムテーブル
  • \ d +-テーブルの説明
  • \ o-クエリ結果をファイルに送信します
  • \ l-データベースのリスト
  • \ i-ファイルから受信データを読み取ります
  • \ e-リクエストバッファの現在の内容をエディタで開きます（EDITOR変数の環境で特に指定されていない限り、デフォルトではviが使用されます）
  • \ d“ table_name”-テーブルの説明
  • \ i / my / directory / my.sqlのような外部ファイルからコマンドを実行します
  • \ pset-フォーマットパラメータを設定するためのコマンド
  • \ echo-メッセージを表示します
  • \ set-環境変数の値を設定します。 パラメータがない場合、現在の変数のリストを表示します（\ unset-削除）。
  • \？ -psqlリファレンス
  • \ help-SQLリファレンス
  • \ q（またはCtrl + D）-プログラムを終了します

コマンドラインからPostgreSQLを操作する：

  • -c（または-command）-対話モードに入らずにSQLコマンドを実行します
  • -f file.sql-file.sqlファイルからコマンドを実行します
  • -l（または-list）-使用可能なデータベースを一覧表示します
  • -U（または-username）-ユーザー名を指定します（例：postgres）
  • -W（または-password）-パスワードプロンプト
  • -ddbname-データベースdbnameに接続します
  • -h-ホスト名（サーバー）
  • -s-ステップバイステップモード、つまり、すべてのコマンドを確認する必要があります
  • –S-単一行モード、つまり、改行がクエリを実行します（SQLステートメントの最後で;を削除します）
  • -V-インタラクティブモードに入らないPostgreSQLバージョン

例：

psql -U postgres -d dbname-c「CREATETABLEmy（some_id serial PRIMARY KEY、some_texttext）;」 -dbnameデータベースでのコマンドの実行。

psql -d dbname -H -c "SELECT * FROM my" -omy.html-クエリ結果をhtmlファイルに出力します。

PosgreSQLユーティリティ（プログラム）：

  • createdbおよびdropdb-データベースを作成および削除します（それぞれ）
  • createuserとdropuser-createとuser（それぞれ）
  • pg_ctl-一般的な管理タスク（開始、停止、パラメーターの構成など）を解決するために設計されたプログラム
  • postmaster-PostgreSQLマルチユーザーサーバーモジュール（デバッグレベル、ポート、データディレクトリの設定）
  • initdb-新しいPostgreSQLクラスターを作成します
  • initlocation-データベースのセカンダリストレージ用のディレクトリを作成するためのプログラム
  • vacuumdb-物理的および分析的なデータベースのメンテナンス
  • pg_dump-データのバックアップと復元
  • pg_dumpall-PostgreSQLクラスター全体をバックアップします
  • pg_restore-アーカイブからデータベースを復元する（.tar、.tar.gz）

バックアップの作成例：

mydbデータベースのバックアップを圧縮形式で作成する

Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

データベースを作成するコマンドを含む、プレーンテキストファイルの形式でのmydbデータベースのバックアップの作成

Pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

名前に支払いを含むテーブルを使用して、圧縮形式でmydbデータベースのバックアップを作成する

Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *支払い* -f Payment_tables.backup mydb

1つの特定のテーブルからのみデータをダンプします。 複数のテーブルをバックアップする必要がある場合は、各テーブルの-tスイッチを使用してこれらのテーブルの名前を一覧表示します。

Pg_dump -a -t table_name -f file_name database_name

gz圧縮でバックアップを作成する

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c> mydb.gz

最も一般的に使用されるオプションのリスト：

  • -h host --host、指定されていない場合は、localhostまたはPGHOST環境変数の値が使用されます。
  • -p port --port、指定されていない場合は、5432またはPGPORT環境変数の値が使用されます。
  • -u --user、指定されていない場合は、現在のユーザーが使用されます。値は、PGUSER環境変数で指定することもできます。
  • -a、-data-only-ダンプのみのデータ、データ、およびスキーマはデフォルトで保存されます。
  • -b-ダンプに大きなオブジェクト（ブログ）を含めます。
  • -s、-schema-only-ダンプスキーマのみ。
  • -C、-create-データベースを作成するコマンドを追加します。
  • -c-オブジェクト（テーブル、ビューなど）をドロップ（ドロップ）するためのコマンドを追加します。
  • -O-オブジェクト（テーブル、ビューなど）の所有者を設定するコマンドを追加しないでください。
  • -F、-format（c | t | p）-出力ダンプ形式、カスタム、tar、またはプレーンテキスト。
  • -t、-table = TABLE-ダンプの特定のテーブルを指定します。
  • -v、-verbose-詳細情報を出力します。
  • -D、-attribute-inserts-プロパティ名のリストを指定したINSERTコマンドを使用してダンプします。

pg_dumpallコマンドを使用してすべてのデータベースをバックアップします。

Pg_dumpall> all.sql

バックアップからのテーブルの復元（バックアップ）：

psql-プレーンテキストファイルに保存されているバックアップを復元します。
pg_restore-圧縮バックアップ（tar）を復元します。

エラーを無視して、バックアップ全体を復元する

Psql -h localhost -U someuser -d dbname -f mydb.sql

バックアップ全体を復元し、最初のエラーで停止します

Psql -h localhost -U someuser -set ON_ERROR_STOP = on -f mydb.sql

tarアーカイブから復元するには、最初にCREATE DATABASEmydbを使用してデータベースを作成する必要があります。 （バックアップの作成時に-Cオプションが指定されなかった場合）および復元

Pg_restore -dbname = mydb -jobs = 4 -verbose mydb.backup

gz圧縮されたデータベースバックアップの復元

psql -U postgres -d mydb -f mydb

postgresqlデータベースがより明確になると思います。 このPostgreSQLコマンドのリストがお役に立てば幸いです。

最終更新日：2018年3月17日

テーブルを作成するには、CREATETABLEコマンドに続けてテーブル名を使用します。 このコマンドで使用できる、テーブルの列とその属性を定義する演算子もいくつかあります。 テーブルを作成するための一般的な構文は次のとおりです。

CREATE TABLE table_name（column_name1データ型column_attributes1、column_name2データ型column_attributes2、...................................。 .. .......... column_nameNデータ型column_attributesN、table_attributes）;

すべての列の指定は、テーブル名の後の括弧内にリストされています。 さらに、各列には、それが表すデータの名前とタイプを示す必要があります。 データ型によって、列に含めることができるデータ（数値、文字列など）が決まります。

たとえば、pgAdminを使用してデータベースにテーブルを作成しましょう。 これを行うには、最初にpgAdminでターゲットデータベースを選択し、それを右クリックして、コンテキストメニューの[クエリツール...]項目を選択します。

その後、SQLコードを入力するためのフィールドが開きます。 さらに、SQLを入力するためにこのフィールドを開くデータベース専用にテーブルが作成されます。

CREATE TABLEの顧客（Id SERIAL PRIMARY KEY、FirstName CHARACTER VARYING（30）、LastName CHARACTER VARYING（30）、Email CHARACTER VARYING（30）、Age INTEGER）;

この場合、Customersテーブルは、Id、FirstName、LastName、Age、Emailの5つの列を定義します。 最初の列Idは、顧客IDを表し、主キーとして機能するため、タイプSERIALです。 実際、この列には数値1、2、3などが格納され、新しい行ごとに1つずつ自動的に増加します。

次の3つの列は、顧客の名、姓、および電子メールアドレスを表し、タイプはCHARACTER VARYING（30）です。つまり、30文字以下の文字列を表します。

最後の列Ageは、ユーザーの年齢を表し、INTEGER型です。つまり、数値を格納します。

そして、このコマンドを実行した後、customersテーブルが選択したデータベースに追加されます。

テーブルを落とす

テーブルを削除するには、次の構文を持つDROPTABLEコマンドを使用します。

DROP TABLE table1 [、table2、...];

たとえば、customersテーブルを削除します。

コンソールを介してデータベースを操作する代わりに、グラフィカルインターフェイスを備えたクライアントにとってより直感的な環境があります。 例えば、 pgAdmin..。 インストールは非常に簡単で、スーパーユーザー権限で実行します。

sudo apt-get install pgadmin3
今、あなたは走ることができます pgAdminグラフィカルインターフェイスを介して。 リストからローカルデータベースサーバー（デフォルトのポートは5432）を選択し、指定したパラメーターを使用して作成済みのデータベースを追加します。
pgAdmin

ノード以外のこのローカルサーバー データベースと呼ばれるノードを見つけることもできます ログインの役割-利用可能なすべての役割。

使用可能なすべてのデータベースから、作成したデータベースを選択しましょう。 データベースには、さまざまな種類のオブジェクトが含まれています。 特にテーブルとシーケンスに注意を払う必要があります（ シーケンス).

ほとんどの場合、通常のテーブルには正の数値の形式で主キーが含まれています。 この値は行ごとに一意である必要があるため、毎回リクエストで直接設定しないように、シーケンスをデフォルト値として設定できます。

まず、シーケンスを作成しましょう。 我々が選択しました シーケンス - 新しいシーケンス..。 最初のタブで、名前を入力し、次のように指定します オーナー私たちが作成した役割。 このシーケンスは、他の役割では使用できません。 フィールドへの2番目のタブ インクリメントと 始める 1つずつ入力します（特に必要な場合を除く）。 ダイアログボックスの最後のタブで、データベースに対して実行される結果のSQLクエリを確認できます。

シーケンスが作成されたら、テーブルの作成を開始しましょう。 彼女の名前と所有者（所有者）も示します。 4番目のタブ 列まず、主キーを追加します。 ボタン 追加、表示されるウィンドウで、列の名前を示します。たとえば、 id..。 データ型として選択します bigint..。 2番目のタブ 意味フィールドで デフォルト値シーケンスを示します。 フィールドには、フォームの値が含まれている必要があります nextval（ "message_id_seq" :: regclass）..。 つまり、新しい行が追加されるたびに、シーケンスから次の値が取得されます。 必要に応じて他の列を追加しました。 最後に、タブで 制約主キーに制約を追加します（ 主キー）。 最後のタブでは、pgAdminが生成した結果のSQLコードを賞賛することができます。 [OK]をクリックすると、テーブルが作成されます。

主キーとしてbigintの代わりに、列タイプとして指定できます bigserial..。 このタイプは、新しい行が追加されるたびに自動的にインクリメントされるため、シーケンスを作成する必要はありません。 つまり、最も単純なケースでは、レコードIDを生成するための特別なルールがない場合は、bigserialの使用をお勧めします。

テーブルの内容を見てみましょう。 これを行うには、それを右クリックして、コンテキストメニューから選択します データを見る - 100行を表示.

同じウィンドウで、テーブル内の任意のセルの値をすばやく編集できます。 テーブルに100を超えるレコードがある場合は、ウィンドウ上部のドロップダウンリストに表示されるレコードの数を変更します。 100、500、1000、またはすべてのレコードを表示できます。 しかし、これはテーブルの内容をすばやく確認する方法にすぎず、慣れてはいけません。 テーブルに数万のレコードがある場合、すべてのレコードを一度に表示することはお勧めしません。この場合、パラメーターを使用してクエリを作成することをお勧めします。 制限と オフセット.

15の便利なPostgreSQLコマンド

基本的なコマンドを説明する多くのPostgreSQLチュートリアルがネット上にあります。 しかし、作業を深く掘り下げると、高度なチームを必要とする実際的な問題が発生します。

このようなコマンドまたはスニペットが文書化されることはめったにありません。 開発者とデータベース管理者の両方に役立ついくつかの例を見てみましょう。

データベースに関する情報の取得

データベースサイズ

データベースのファイル（ストレージ）の物理サイズを取得するには、次のクエリを使用します。

SELECT pg_database_size（current_database（））;

結果は41809016のような数値として表示されます。

current_database（）は、現在のデータベースの名前を返す関数です。 代わりに、テキストに名前を入力できます。

SELECT pg_database_size（ "my_database"）;

人間が読める形式で情報を取得するには、pg_size_pretty関数を使用します。

SELECT pg_size_pretty（pg_database_size（current_database（）））;

その結果、40Mbの形式の情報が得られます。

テーブルのリスト

データベーステーブルのリストを取得したい場合があります。 これを行うには、次のクエリを使用します。

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN（ "information_schema"、 "pg_catalog"）;

information_schemaは、テーブルやフィールドなどのビューのコレクションを含む標準のデータベーススキーマです。 テーブルビューには、データベース内のすべてのテーブルに関する情報が含まれています。

以下で説明するクエリは、現在のデータベースの指定されたスキーマからすべてのテーブルを選択します。

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN（ "information_schema"、 "pg_catalog"）AND table_schema IN（ "public"、 "myschema"）;

最後のIN句では、特定のスキーマの名前を指定できます。

テーブルサイズ

データベースのサイズを取得するのと同様に、対応する関数を使用してテーブルデータのサイズを計算できます。

SELECT pg_relation_size（ "accounts"）;

pg_relation_size関数は、指定されたテーブルまたはインデックスの指定されたレイヤーがディスク上で占めるスペースの量を返します。

最大のテーブルの名前

現在のデータベース内のテーブルのリストをテーブルサイズで並べ替えて表示するには、次のクエリを実行します。

SELECT relname、relpages FROM pg_class ORDER BY relpages DESC;

最大のテーブルに関する情報を表示するために、LIMITを使用してクエリを制限します。

SELECT relname、relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

relnameは、テーブル、インデックス、ビューなどの名前です。
relpages-このテーブルのディスク上のビューのページ数でのサイズ（デフォルトでは、1ページは8 KBです）。
pg_classは、データベーステーブル間の関係に関する情報を含むシステムテーブルです。

接続されているユーザーのリスト

接続されているユーザーの名前、IP、および使用されているポートを見つけるには、次のクエリを実行します。

SELECT datname、usename、client_addr、client_port FROM pg_stat_activity;

ユーザーアクティビティ

特定のユーザーの接続アクティビティを見つけるために、次のクエリを使用します。

SELECT datname FROM pg_stat_activity WHERE usename = "devuser";

データフィールドとテーブルフィールドの操作

重複行の削除

テーブルに主キーがない場合は、レコード間で重複が発生する可能性があります。 このようなテーブル、特に大きなテーブルの場合、整合性をチェックするために制約を設定する必要がある場合は、次の要素を削除します。

  • 重複行、
  • 1つ以上の列が重複している状況（これらの列が主キーとして使用されることになっている場合）。

行全体が複製されている（行の2番目）顧客データを含むテーブルについて考えてみます。

次のクエリは、すべての重複を削除するのに役立ちます。

ctidが存在しない顧客からの削除（SELECT max（ctid）FROM Customers GROUP BY Customers。*）;

レコードごとに一意のctidフィールドはデフォルトで非表示になっていますが、すべてのテーブルに存在します。

最後のリクエストはリソースを大量に消費するため、本番プロジェクトで実行する場合は注意が必要です。

ここで、フィールド値が繰り返される場合を考えてみましょう。

すべてのデータを保存せずに重複を削除できる場合は、次のクエリを実行します。

顧客からの削除WHEREctid NOT IN（SELECT max（ctid）FROM Customers GROUP BY customer_id）;

データが重要な場合は、最初に重複するレコードを見つける必要があります。

SELECT * FROM Customers WHERE ctid NOT IN（SELECT max（ctid）FROM Customers GROUP BY customer_id）;

このようなレコードを削除する前に、一時テーブルに移動するか、レコード内のcustomer_id値を別の値に置き換えることができます。

上記のレコードを削除するリクエストの一般的な形式は次のとおりです。

DELETE FROM table_name WHERE ctid NOT IN（SELECT max（ctid）FROM table_name GROUP BY column1、）;

フィールドタイプを安全に変更する

このリストにそのようなタスクを含めることについて疑問が生じる可能性があります。 実際、PostgreSQLでは、ALTERコマンドを使用してフィールドのタイプを変更するのは非常に簡単です。 例として、customerテーブルをもう一度見てみましょう。

customer_idフィールドは、varchar文字列データ型を使用します。 このフィールドは整数形式の顧客IDを格納することになっているため、これはエラーです。 varcharを使用することは正当化されません。 ALTERコマンドを使用して、この誤解を修正してみましょう。

ALTERTABLEの顧客ALTERCOLUMN customer_idTYPE整数。

しかし、実行の結果、エラーが発生します。

エラー：列「customer_id」を整数型に自動的にキャストすることはできません
SQL状態：42804
ヒント：変換を実行するには、USING式を指定します。

つまり、テーブルにデータがある場合、フィールドのタイプを取得して変更することはできません。 varcharタイプが使用されたため、DBMSは値が整数であるかどうかを判別できません。 データはこのタイプに正確に対応していますが。 この点を明確にするために、エラーメッセージは、USING式を使用してデータを整数に正しく変換することを提案しています。

ALTERTABLEの顧客ALTERCOLUMN customer_id TYPE integer USING（customer_id :: integer）;

その結果、すべてがエラーなしで実行されました。

USINGを使用する場合、特定の式に加えて、関数、その他のフィールド、および演算子を使用できることに注意してください。

たとえば、customer_idフィールドをvarcharに変換し直してみましょう。ただし、データ形式は次のように変換されます。

ALTERTABLEの顧客ALTERCOLUMN customer_id TYPE varchar USING（customer_id || "-" || first_name）;

その結果、テーブルは次のようになります。

「失われた」値を見つける

シーケンスを主キーとして使用する場合は注意してください。割り当てるときに、シーケンスの一部の要素が誤ってスキップされ、テーブルを操作した結果、一部のレコードが削除されます。 これらの値は再び使用できますが、大きなテーブルで見つけるのは困難です。

2つの検索オプションについて考えてみましょう。

最初の方法
次のクエリを実行して、「失われた」値を持つ間隔の開始を見つけましょう。

SELECT customer_id + 1 FROM Customers mo WHERE NOT EXISTS（SELECT NULL FROM Customers mi WHERE mi.customer_id = mo.customer_id + 1）ORDER BY customer_id;

その結果、5、9、11の値が得られます。

最初のオカレンスだけでなく、欠落しているすべての値を見つける必要がある場合は、次の（リソースを大量に消費する！）クエリを使用します。

WITH seq_max AS（SELECT max（customer_id）FROM Customers）、seq_min AS（SELECT min（customer_id）FROM Customers）SELECT * FROM generate_series（（SELECT min FROM seq_min）、（SELECT max FROM seq_max））EXCEPT SELECT customer_id FROM Customers;

その結果、5、9、6の結果が表示されます。

2番目の方法
customer_idに関連付けられたシーケンスの名前を取得します。

SELECT pg_get_serial_sequence（ "customers"、 "customer_id"）;

そして、不足しているすべての識別子を見つけます。

WITH sequence_info AS（SELECT start_value、last_value FROM "SchemaName"。 "SequenceName"）SELECT generate_series（（sequence_info.start_value）、（sequence_info.last_value））FROM sequence_info EXCEPT SELECT customer_id FROM Customers;

テーブルの行数を数える

行数は標準のカウント関数で計算されますが、追加の条件で使用できます。

テーブルの行の総数：

SELECTカウント（*）FROMテーブル;

指定されたフィールドにNULLが含まれていない場合の行数：

SELECTカウント（col_name）FROMテーブル;

指定されたフィールドの一意の行の数：

SELECTカウント（個別のcol_name）FROMテーブル;

トランザクションの使用

トランザクションは、一連のアクションを1つの操作に結合します。 その特徴は、トランザクションの実行でエラーが発生した場合、アクションの結果がデータベースに保存されないことです。

BEGINコマンドを使用してトランザクションを開始しましょう。

BEGINの後にすべての操作をロールバックするには、ROLLBACKコマンドを使用します。

そして適用するには-COMMITコマンド。

実行可能リクエストの表示と完了

リクエストに関する情報を取得するには、次のコマンドを実行します。

SELECT pid、age（query_start、clock_timestamp（））、usename、query FROM pg_stat_activity WHERE query！= " "AND query NOT ILIKE"％pg_stat_activity％ "ORDER BY query_start desc;

特定の要求を停止するには、プロセスID（pid）を指定して次のコマンドを実行します。

SELECT pg_cancel_backend（procpid）;

リクエストを終了するには、以下を実行します。

SELECT pg_terminate_backend（procpid）;

構成の操作

クラスターインスタンスの場所の検索と変更

1つのオペレーティングシステムに複数のPostgreSQLインスタンスが構成されていて、それらが異なるポートに「配置」されている場合に、状況が発生する可能性があります。 この場合、各インスタンスの物理的な場所へのパスを見つけることは、かなり神経質な作業です。 この情報を取得するために、対象のクラスターのデータベースに対して次のクエリを実行します。

SHOW data_directory;

次のコマンドを使用して、場所を別の場所に変更してみましょう。

data_directoryをnew_directory_pathに設定します。

ただし、変更を有効にするには再起動が必要です。

利用可能なデータ型のリストを取得する

次のコマンドを使用して、使用可能なデータ型のリストを取得しましょう。

SELECT typname、typlen from pg_type where typtype = "b";

typnameは、データ型の名前です。
typlenは、データ型のサイズです。

再起動せずにDBMS設定を変更する

PostgreSQLの設定は、postgresql.confやpg_hba.confなどの特別なファイルにあります。 これらのファイルを変更した後、DBMSは設定を再度取得する必要があります。 これを行うには、データベースサーバーを再起動します。 これを行う必要があることは明らかですが、何千人ものユーザーが使用するプロジェクトの製品版では、これは非常に望ましくありません。 したがって、PostgreSQLには、サーバーを再起動せずに変更を適用できる機能があります。

SELECT pg_reload_conf（）;

ただし、残念ながら、すべてのパラメーターに適用されるわけではありません。 場合によっては、設定を適用するために再起動が必要になります。

PostgreSQLを使用する開発者とDBAが物事を簡単にするのに役立つコマンドについて説明しました。 しかし、これらはすべて可能な技術ではありません。 興味深い問題に遭遇した場合は、コメントにそれらについて書いてください。 便利な体験を共有しましょう！



あなたの質問に対する答えが見つかりませんでしたか？ ここを見てください
再構成可能なマルチパイプラインコンピューティング環境に基づく分散制御システムのアーキテクチャL-Net再構成可能なマルチパイプラインコンピューティング環境に基づく分散制御システムのアーキテクチャL-Net「透過的」分散ファイルシステム
電子メール送信ページrelay_recipientsファイルにActiveDirectoryのアドレスを入力します電子メール送信ページrelay_recipientsファイルにActiveDirectoryのアドレスを入力します
Windowsに言語バーがありません-どうすればよいですか？Windowsに言語バーがありません-どうすればよいですか？