AUTOVACUUMとは

AUTOVACUUMとは、一定時間おきに自動でVACUUMとANALYZEの処理を行うものです。

自動処理ですが、処理対象となるテーブルは、挿入・更新・削除が一定の値を超えたものです。

またAUTOVACUUMでは、テーブルの排他ロックは取得しません。その他、自動VACUUMをする設定にしてあっても、大量のデータ更新や削除の際には、手動バキュームをおすすめします。

 

あわせて読みたい

※ VACUUMについてはこちらの記事で説明しています

https://postgresweb.com/post-5194

 

 

AUTOVACUUMの設定

AUTOVACUUMの設定は、postgresql.confの「autovacumm」で設定します。

#はコメントアウトの意味ですが、デフォルトではONとなっているのでこのままで有効です。

# autovacuum = on

 

ALTE ROLE(=ALTER USER)

ユーザー情報(パスワード、パスワード期限、権限)の変更をするSQLです(psql、pgAdminからも実行できます)。ALTER ROLEで例を書いていますが、ALTER USERとしても動作は同じです。

-- パスワードを変更
ALTER ROLE user1 with PASSWORD 'pass';  -- パスワード設定する
ALTER ROLE user1 with PASSWORD NULL;    -- パスワードなしにする

-- パスワードの期限を2021年の終わりまで有効とする
ALTER ROLE user1 WITH PASSWORD 'pass' VALID UNTIL 'Jan 1 2022';
ALTER ROLE user1 VALID UNTIL 'infinity';  -- 有効期限を無効にする

-- DBの作成権限
ALTER ROLE user1 CREATEDB;      -- 作成権限あり
ALTER ROLE user1 CREATEDB;      -- 作成権限なし

-- ユーザーの作成権限
ALTER ROLE user1 CREATEROLE;    -- 作成権限あり
ALTER ROLE user1 NOCREATEROLE;  -- 作成権限なし

-- ログイン権限
ALTER ROLE user1 LOGIN;         -- 権限あり
ALTER ROLE user1 NOLOGIN;       -- 権限なし

以上、ユーザー情報を変更する方法でした。

 

 

こちらも参考に

あわせて読みたい

https://postgresweb.com/createuser

 

あわせて読みたい

https://postgresweb.com/dropuser

 

あわせて読みたい

https://postgresweb.com/dropuser

 

ユーザーの削除

ユーザーを削除するには大きく2つの方法があり、それぞれの方法について紹介します。

① SQLでDROP USERを実行

② OSのコマンドラインからDROPUSERコマンドを実行

 

 

あわせて読みたい

ユーザーを作成する方法はこちらで紹介しています。

https://postgresweb.com/createuser

 

 

① SQLでDROP USERを実行

ユーザーを削除するSQL文です。

CREATE ROLEで作ったロールでも、DROP USERで削除できます。

-- ユーザー名「user1」を削除する構文
DROP USER user1;

-- IF EXSTSをつけると存在している場合のみ削除される
-- 存在していない場合は、エラーにはならずスキップされる
DROP USER IF EXISTS user1;

 

 

② OSのコマンドラインからDROPUSERコマンドを実行

ユーザーを作成するには、「dropuser」コマンドを実行します。

Windows機を例にしますが、dropuserコマンドは「bin\」配下のdropuser.exeを実行しています。

また画像をみるとわかるのですが、droproleというものはありません。

(コマンドでdroproleと実行してもエラーとなります)

 

dropuserコマンド

dropuserコマンドは次のようになります。

コマンドプロンプトから実行します。

dropuser -h サーバー名 -p ポート番号 -U 実行するユーザー名 削除するユーザー名

dropuser -h localhost -p 5432 -U postgres user1
dropuser -h 192.168.10.10 -p 5432 -U postgres user2
dropuser -h 192.168.10.10 -p 5432 -U postgres --if-exists user3
dropuser -U postgres user4  -- ローカル環境の場合

以上、ユーザーを削除する方法でした。

 

ユーザーの作成

最初にですが、ユーザーを作成するには大きく2つの方法があります。

① SQLでCREATE USERを実行

② OSのコマンドラインからCREATEUSERコマンドを実行

 

そうだねと思う人はよいのですが、私は最初この「CREATE USER(間にスペースあり)」と、「CREATEUSER(間にスペースなし)」2つの違いがよくわかりませんでした。同じ状況の人もいるのではないかと思いそれぞれについて、この記事で説明しようと思います。

 

※わかりづらいと感じるユーザーとロールの違いについてはこちらで説明してます。

記事自体は長くないので、お時間がある時に見てもらえると嬉しいです。

 

 

 

 

① SQLでCREATE USERを実行(ユーザーを作成するSQL)

ユーザーを作成する一つ目の方法は、SQL文を発行してユーザーを作成する方法です。

SQLなので構文と具体例を下記に紹介します。psqlやpgAdmin上から可能です。

※下の例は「CREATE USER」としていますが「CREATE ROLE」でも実行できます。

-- ユーザー名「user1」を作成する構文
CREATE USER user1;

-- ユーザー名「user1」をパスワード「pass」で作成する
CREATE USER user1 WITH PASSWORD 'pass';

-- ユーザー名「user1」をパスワード「pass」で作成し、
-- パスワードの期限を2021年の終わりまで有効とする(指定しない場合は永遠に有効)
CREATE USER user1 WITH PASSWORD 'pass' VALID UNTIL 'Jan 1 2022';

-- ユーザー名「user1」をパスワード「pass」で作成し、
-- スーパーユーザー権限とする
CREATE USER user5 WITH PASSWORD 'pass' SUPERUSER;

-- ユーザー名「user1」をパスワード「pass」で作成し、
-- 権限にデータベース作成権限、ユーザー作成権限を加える(スーパーユーザーではない)
CREATE USER user1 WITH PASSWORD 'pass' CREATEDB CREATEROLE;

 

オプションについての補足

・ PASSWORD 'パスワードの値' / PASSWORD NULL

パスワードのパスワードを消すにはNULLを指定。パスワード認証がONでパスワードをNULLにしていると、認証は常に失敗する。

 

・ VALID UNTIL 'timestamp'

ユーザーのパスワードが無効になる日時を設定。指定なしの場合、パスワードは永遠に有効。

 

・ SUPERUSER / NOSUPERUSER

新しいユーザーが「スーパーユーザ」となるかどうかの設定。指定なしの場合は、「NOSUPERUSER」となる。

 

・ CREATEDB / NOCREATEDB

データベースの作成権限。指定なし場合のデフォルトは「NOCREATEDB」。

 

・LOGIN / NOLOGIN

ログイン可能かの権限。デフォルトはCREATE USERの場合は「LOGIN」、CREATE ROLEの場合は「NOLOGIN」となる。

 

 

② OSのコマンドラインからCREATEUSERコマンドを実行

ユーザーを作成するには、「createuser」コマンドを実行します。

Windows機を例にしますが、createuserコマンドはbinのcreateuser.exeを実行しています。

また画像をみるとわかるのですが、createroleというものはありません。

(コマンドでcreateroleと実行してもエラーとなります)

 

createuserコマンド

createuserコマンドは次のようになります。

コマンドプロンプトから実行します。

createuser -h サーバー名 -p ポート番号 -U 実行するユーザー名 -d -r -s 作成するユーザー名

createuser -h localhost -p 5432 -U postgres -d -l -r -s -P user1
createuser -h 192.168.10.10 -p 5432 -U postgres -d -l -r -s -P user2
createuser -U postgres -d -l -r -s -P user3  -- ローカル環境の場合

 

オプション

設定できるオプションは、小文字=許可、大文字=禁止

h … サーバーのホスト名やIPを指定

p … ポート番号を指定

U … createuserを実行するユーザー

d … データベースの作成権限、D … データベースの作成できない権限

l  … ログイン権限、L … ログイン不可権限

r … ユーザー作成の権限、R … ユーザー作成ができない権限

s … スーパーユーザー権限の付与、S … スーパーユーザーでない権限の付与

P … パスワード設定する(厳密には実行時に、パスワード設定メッセージが表示される)

 

実行してみる

実行した様子は次のとおり。特に成功しました等のメッセージは出ません。

(エラーになればメッセージがでます)

以上、ユーザーを作成する方法でした。

 

 

関連記事

ユーザー(ロール)の作成、削除の方法

CREATE USERとCREATE ROLEの違い

ユーザー情報を変更する(ALTER ROLE、ALTER USER)

ユーザーを削除する(DROP USER文、DROPUSER)

データベースクラスタとは

早速結論ですがDBクラスタとは、データベースの格納領域のことです。

PostgreSQL特有の言葉で、複数のデータベースがあつまったものだと思ってくれればよいかと思います。

 

「データベースクラスタ」等で検索すると、クラスタ化を説明したサイトがあるかと思いますが、

冗長化するためのクラスタ化とは意味が異なる点に注意して下さい。

 

「initdb …」や「pg_ctl initdb …」というコマンドでデータベースクラスタを作成できます。

DBクラスタを作成すると、template0、template1、postgresというデータベースが作成されます。

 

 

The Try-SQL Editorとは

The Try-SQL Editor」とは、ブラウザ上で動かすことができるSQL学習用ツールです。

 

サイト上には、すでにテスト用のデータベースオブジェクトが作成されており

どんなSQL文でもすぐ、好きなだけ実行することができます

また「Restore Database」ボタンをクリックするだけで、いつでも復元することができます。

是非使ってみて下さい。

 

使い方

使い方は簡単で、「SQL Statement」の箇所にSQLを入力し、「Run SQL」を押すだけです。

 

Result欄に実行結果が表示されます。

 

CREATE TABLE文なども実行できるので試してみて下さい。

データベースは「Restore Database」ボタンですぐ元に戻せます。

 

概要

何をするにもまず必要なのがデータベースです。

ここでは、PostgreSQLでデータベースを作成する手順を解説します。

 

 

データベースを作成する手順

①pgAdminを起動します。

 

 

②『Servers(1)』→『PostgreSQL 12』→『データベース(1)』とクリックしていき、

画面のように展開します。

 

 

③『データベース(1)』を右クリックし、『作成 - データベース』とクリックします。

 

 

④データベース(赤枠)の個所に作成するデータベース名を入力し、右下の「保存」を押します。

(画面では「testdb」としました。)

 

 

⑤画面のように表示されれば、データベースの作成が完了です。

 

⑥データベースができたら、次はデータを登録するためのテーブルを作ってみましょう

 

1.はじめに

1. PostgreSQLとは

 

 

2.PostgreSQLのインストール・設定

PostgreSQLのインストール~データベース操作のツール(pgAdmin)の使い方を説明します。

 

1. PostgreSQLのインストール

2. PostgreSQLへの接続

3. pgAdmin4の日本語化

4. pgAdmin4の使い方(見方から操作まで)

 

 

3.データベースやテーブルを作成する

PostgreSQLのインストールは済んだものの、何かを始めたいが何をすればよいかわからない時、

とりあえず次の順番ですすめていくのがおすすめです。

 

1. データベースを作成する

2-1. CREATE文でテーブルを作成する

【補足】CREATE TABLE文の説明

2-2. pgAdminでテーブルを作成する方法

3. テーブルへデータを登録する

4. テーブルのデータを取得する

5. テーブルのデータを削除する

 

型についてまとめた記事はこちら

文字型の型について整理してみた

数値型の型について整理してみた

boolean(論理型)について整理してみた

 

すぐに必要ではないが知っておいてほしい内容

ユーザーを作成する

 

 

4.よく使うSQL

データを取得する場合に役立つ頻出のSQLをまとめました。

この後に説明する関数でも役に立つことがおおいため先に紹介しておきます。

 

文字から数値、数値から文字へ変換する

文字の切り取り(Left、Right、Substring)

前0埋め、後ろ0埋め(lpad、rpad)

数値を文字に変換する(to_char)

日付の加算、週の加算、月の加算

文字を日付に変換する、書式を設定する

日時、時刻の書式設定をする(yyyymmdd形式)

配列の要素数を取得する

 

※ その他の構文はこちらにありますので時間がある場合に見て下さい。

 

 

5.関数(FUNCTION)

ここでは、関数の作り方を説明します。

関数を使うことで、データの取得から登録等の一連の作業をあらかじめ作成しておき、

使いたい時は、それを呼び出すだけ。といったことができます。

 

関数(FUNCTION)の作り方・書き方を解説する

変数の宣言、変数宣言時に代入する、定数を宣言する方法

IF文の書き方(条件分岐)

For文(ループ文)の書き方

SQLの結果を変数に格納する

SQL(SELECT文)の結果をループする方法

デバッグ用にも。変数の値を画面に出す(raise)

 

※ 管理人が作成した自作の関数がこちらで紹介しています。

 

 

6.psqlを使う

PostgreSQLにはpgAdminではなく、Windowsのコマンドプロンプトの画面のように

黒い画面でSQLを実行できる方法もあります。

ファイルから一斉にデータを登録するといったことができるようになる他、

他のサイトではこちらの画面で説明している場合もあるため覚えておいたほうがよい操作です。

 

psqlを起動する方法

 

 

7.システム情報を取得する

PostgreSQLで確認できるシステム情報を取得する方法をまとめました。

入門というより、システム運用中に必要な知識ですがここで紹介します。

 

データベース情報を取得する(ビュー、トリガー、インデックス)

テーブルサイズを確認するSQL

テーブル一覧とデータ件数を取得するSQL

 

 

8.その他

予備知識として役立つものをまとめました。

 

Windows Defenderへ除外登録する

PostgreSQLのパスワード変更

PostgreSQLのバージョン確認方法

ODBCのインストール

予約語、未予約語とは

 

 

PostgreSQLとは

1.PostgreSQLとは

PostgreSQLとは、一言でいうと無料で使えるデータベースソフトです。

ポストグレスキューエルと読み、略してポスグレと呼ぶ人もいます。

ちなみにこのサイトの名前もここからもらっています。

 

PostgreSQLロゴ

 

PostgreSQLの他にもデータベースソフトは多数あり、特にこれらが有名です。

・Oracle Database(オラクルデータベース)

・SQL Server(エスキューエルサーバー)

・MySQL(マイエスキューエル)

 

 

データベースのシェア・人気ランキングでは、PostgreSQLは2010年代以降継続して伸びており、

上であげたデータベースに次ぐ第4位となっています。

2021年1月現在【DB-Engines

 

参考

PostgreSQLとMongoDB増加 - 2020年5月データベース人気ランキング【マイナビ】

DBのシェア推移を調べてみる⑤(2020年10月版)【マリンロード】

 

 

2.PostgreSQLの特徴・メリット

そのPostgreSQLの大きな特徴はイセンスが無料であることです。

参考:各DBにかかるコストについて(ライセンス費用、保守費用)【システムエクゼ】

 

 

その他のデータベースでは、決して安くはない費用がかかります。

また複雑なライセンス形態により、ライセンス数の管理を厳密に行う必要があり、

意図せずライセンス違反していたなんて話もわりとよくあります。

 

 

PostgreSQLのようにそれがかからないという点は、

システムの初期導入コストの削減や気軽に開発環境を構築できることが大きなメリットです。

 

 

その分機能面に劣る点があるかというとそうではなく、

Oracle DataBaseなどの商用(=費用がかかる)データベースと匹敵する機能があります。

参考:PostgreSQLの機能と他のRDBMSの比較【Let's PostgreSQL】

 

 

3.PostgreSQLのデメリット

まぁあまりに褒めちぎっても信ぴょう性もないので、

PostgreSQLを普段使用している管理人が感じるデメリット的なもの(?)をあげてみます。

 

 

PostgreSQLは「PostgreSQL Global Development Group」というコミュニティが

開発を行っていますが、公式的なサポートはしていません。

そのため技術的にわからない点があった場合、自分で調べる力が必要になります。

※ORACLEとMySQLはORACLE社が、SQL ServerはMicrosoft社が開発・サポートを行っています。

 

 

例えばPostgreSQLでやりたいことや問題が発生した時、

ピンポイントの資料があまりなく、管理人も試行錯誤をすることも多いです。

しかし、先に挙げたように数年前に比べPostgreSQLの人気も上がっていることから、

近頃ではネット検索で事足りる場合が数多くなってきました。

 

 

そして、人気が高まっているといっても一般の人への認知度はまだまだこれからだと感じます。

中堅どころの会社では、まだまだ比較的高い確率でOracleに触る機械が多いでしょう。

しかしOracleはみんなある程度できるので、回りに認められるには結構経験と時間が必要です。

PostgreSQLはまだライバルが少ないため、他の人と差別化をするには狙い目だと思います。

 

 

管理人も、他社の業務システムで時々PostgreSQLを使っているものがあるので、

そういう時は重宝されますよ。

 

 

また、ちゃんとした資格もありますので、会社のスキルアップ目標にしてもよいかも。

OSS-DB…オープンソースデータベースに関する技術力と知識を認定するIT技術者認定資格

 

 

4.まとめ

まとめると、PostgreSQLとは以下のような特徴をもっています。

・無料で使えるデータベースソフト

・無料なのに商用データベースと機能面で劣らない

・他の商用DBに比べ困ったときに参考になる情報が少ない

・一般での知名度は高くない。それゆえにスキルアップとしてねらい目

 

 

このサイト(PostgresWeb)では

PostgresWebでは、初心者や新入社員の方など向けにPostgreSQLを使うための手順から

普段使いの方向けの構文・関数などをメインにまとめていくサイトです。

 

少しでもやってみようと思った方はぜひ、PostgreSQLのインストールから始めて下さい!

 

PostgreSQLのインストール手順を説明します。

2020年6月時点で最新バージョンである12のインストール手順になります。

 

ダウンロード

1.次のURLをクリックしてダウンロードサイトを開きます。

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

 

 

2.赤枠の「Download」をクリックしてダウンロードをして下さい。

※保存先はデスクトップ等、どこでも構いません。

 

 

 

※もしこのような画面が出てきたら「×」で閉じてしまってOKです。

 

 

 

3.ダウンロードが完了したら次のインストールへ進みます。

 

 

インストール

1.ダウンロードしたインストーラーを右クリック、「管理者として実行」します。

 

 

2.インストーラーが起動します。「Next」をクリック

※これ以降、基本的にはそのまま「Next」で大丈夫です。

 

 

3.インストール先を指定する画面がでますが、そのままで構いません。

「Next」をクリック

 

 

4.そのままで構いません。「Next」をクリック

 

 

5.そのままで構いません。「Next」をクリック

 

 

6.PostgreSQLを使うためのパスワードを入力します。

(パスワードは好きな文字で構いませんが忘れないように注意してください。)

同じパスワードを①の上下2か所に入力し②「Next」をクリック

 

 

7.そのままで構いません。②「Next」をクリック

※PostgreSQLのポート番号(①)は初期値では「5432」です。ポート番号の使用頻度はそれほど高くはありませんが、変更した場合は覚えておいて下さい。

 

 

8.そのままで構いません。「Next」をクリック

 

 

9.「Next」をクリック

 

 

10.「Next」をクリック

 

 

11.インストールが始まるので待ちます。

 

 

12.「Finish」をクリック

 

 

13.次のようなスタックビルダの画面が表示されたら、「キャンセル」をクリックし閉じます。

 

 

14.「スタックビルダのウィザードを閉じますか?」で「はい」をクリックします。

 

15.以上でインストールが完了です。

 

 

他のバージョンをインストール

PostgreSQL13のインストール

PostgreSQL11のインストール

インストールせずにPostgreSQLを使用する(ポータブル版)