diadia

興味があることをやってみる。独学で勉強しています。初心者から始めたので困ったことを書いて同じ境遇の方のヒントになればと思います。

postgresql操作メモ

postgresqlを使ってないと忘れてしまうので、見ながら使えるようにまとめておく。

 

処理概要

すでに設定されている場合

  1. ユーザーをチェンジする
  2. データベースの一覧をみる
  3. データベースを選択
  4. テーブルを一覧する
  5. 特定のテーブル構成(カラム)を確認する
  6. テーブルデータを表示させる

1.postgresqlを操作するユーザにログインする

この段階はまだlinuxで操作するので、postgresqlを操作する事ができるユーザに変更する。

# su (ユーザー名) 通常はpostgres
su postgres
#パスワード入力
password

2.DATABASEの一覧を表示させる

psql -lでデータベースの一覧を表示させることができる。データベース以下にはスキームというディレクトリみたいなものがあり、そのスキーマの下にデータを格納するテーブルがある。

psql -l 

3.データベースの選択

psql [database_name]

psql (db名例えばcmtestdb)としてデータベースを選択する

4.選択したデータベースのテーブルを一覧する

¥dまたは¥dtで選択したデータベースのスキーマのテーブルを一覧できる。

¥dt

5.テーブル構造の表示

テーブル構造の表示したいとき(テーブルにどんなカラムが有るのかやそのデータ型を確認したいとき)には以下のコマンドで実行できる。

# ¥d [table_name];
¥d testtable ;

6.テーブルデータを表示させる

テーブルデータを表示させるには以下のコマンドを入力する。以下の場合はテーブルのすべての項目を表示させる。

select * from [テーブル名] ;

データの削除

delete  from (テーブル名) where 条件;

⑧テーブルを作成

CREATE TABLE テーブル名 (列名 データ型 制約 [, 列名 データ型 制約 …] );

権限の確認

¥du

 

権限付与

ALTER ROLE ユーザー名 WITH SUPERUSER;

 

9.CSVの内容をテーブルにコピーする

csvの内容をコピー

csvファイルがデータベースサーバー上に設置できた場合

copy テーブル名(column_names,...) from 'path/***.csv' with csv header ;

 

csvファイルがデータベースサーバー上にない場合

\copy テーブル名 (column_names,...) from 'hoge.csv' with csv header ;

CSVの内容をコピーする際に権限によりコピーが失敗することが多い。CSVファイルを/tmpに置くとうまくコピーできたのでcsvの配置も気をつけること。 参考にしたページ:https://stackoverflow.com/questions/19463074/postgres-error-could-not-open-file-for-reading-permission-denied

 

 

データベース選択から未選択状態に戻す

¥q

上記のコマンドでshellのbash状態に戻すことができる。

 

postgresqlをインストールして使えるようにするまでの流れ 処理の背景と結論>

背景

PostgreSQLをインストールした直後の初期設定ではPostgreSQLを実行しているサーバー以外のクライアントからデータベースにアクセスすることはできないようになっています。

外部からのTCP接続を許可するとともに、PostgreSQLへのログインを許可する設定を行ってやる必要があります。設定は以下の2つです。

postgresql.confの編集

pg_hda.confファイルの編集

 

PostgreSQLの設定は、設定ファイルを編集することで行います。

設定ファイルは複数ありますが、まず、postgresql.confファイルです。
このファイルでは最大接続数やログの保存方式などPostgreSQLに関する基本的な設定を行います。

pg_hda.confファイルはクライアントからのアクセス制御を設定するためのファイルです。デフォルト設定ではPostgreSQLが動作しているマシン上からしかログインを許可しない設定となっているため、外部マシンからのログインを許可するように変更します。