ずっとMySQL使ってきたが、諸事情によりPostgreSQLを使うこともありそうなのでメモ。
とりあえずMacのローカル環境で動作確認。
インストール
brewでインストールできる。
brew intall postgresql
実体は/usr/local/var/posgtresql@14以下にインストールされるみたい。
動作確認（クライアント）
まずはクライアントツールであるpsqlが動くか確認。
psql --version
psql (PostgreSQL) 14.5 (Homebrew)
2022年11月上旬時点では14.5が入る。
PostgreSQLの起動・確認・終了
起動
brew services start postgresql@14
brew servicews start postgresqlでもいけるがWarningが出る。複数バージョンに対応するためかな。
確認
動いてるサービスの確認。
brew services list
終了
brew services stop postgresql@14
再起動はbrew services restart posgtresql@14。
基本的な使い方（DB全体）
どんなDBが存在するか
psql -l
ログイン
以下が基本系。
psql -h localhost -p 5432 -U username -d postgres
brewでインストールした場合、インストールしたOSのユーザー名がsuperuserとして作成されるみたい。Linuxとかだとpostgresというユーザーが作られることが一般的。
ただ、ローカルホスト（というかUnixドメインソケット）の場合はいろいろ省略できるみたい。
pg_hba.confの設定依存。
多くの場合、以下でログイン可能。
psql postgres
ユーザーの確認
=# \du
パスワードの設定
いくつかの方法があるようですが、とりあえず以下を試す。
=# alter user username with encrypted password 'P@ssw0rd';
外部からのアクセスを許可
ユーザーの作成（任意）
createuserコマンドを利用するパターン。
$ createuser -U postgres dbuser
postgres等にログイン後、実行するパターン。
postgres=# create role dbuser with encrypted password 'P@ssw0rd';
パスワードの設定
=# alter user dbuser with encrypted password 'P@ssw0rd';
権限付与
=# alter role dbuser with superuser;
ファイルの編集
postgresql.conf
listen_addresses = '*'
port = 5432
pg_hba.donf
IPv4 local connections:に以下を追記。
# IPv4 local connections:
host all all 0.0.0.0/0 md5
host testdb dbuser 192.168.0.0/24 md5
再起動（設定反映）
brew services restart postgresql@14
アクセス（リモート）
psql -h 192.168.0.99 -U dbuser -d testdb;
基本的な使い方（SQL）
データベースの作成
DBにログインせずに作る。
$ createdb -U dbuser testdb
DB（postgres等）にログイン後に作る（こちらが一般的？）。
postgres=# create database testdb;
デーベースの削除
postgres=# drop database testdb;
テーブルの作成
create table members(
id serial,
name text,
age int,
primary key(id)
);
テーブルの確認
\dt;
\d members;
データの挿入（まあ、あまり意味ないけど）
insert into members(name,age) values('hoge',10);
insert into members(name,age) values('foo',20);
テーブルの削除
drop table members;
データベースの削除
ちなみに自分自身は削除できない。まあ、当たり前。
testdb=# drop database testdb;
postgres等で入り直し削除。
postgres=# drop database testdb;
node.jsからアクセスしてみる（おまけ）
準備
pg_hba.confを編集してパスワード認証にする。
必要な箇所のtrust → md5 とする。
わからなければ全て。
実装
とりあえずデータを取得してみる。
作業場作成
cd
mkdir pg_node
cd pg_node
モジュールのインストールと実装ファイルの作成
npm install pg
touch index.js
コードを書く
const { Client } = require("pg");
const client = new Client({
user: "dbuser",
host: "localhost",
database: "testdb",
password: "P@ssw0rd",
port: 5432
});
client.connect();
const query = {
text: "select * from members",
values: [],
}
client.query(query, (err, res) => {
if (err) {
console.log(err.stack);
} else {
res.rows.map(row => console.log(`${row.id} ${row.name}`));
}
client.end();
});
実行
node index.js
1 hoge
2 foo
Express等でpgを使う場合
Poolを使うみたい。
接続情報が違うのはご愛嬌。
const express = require("express");
const { send } = require("express/lib/response");
const app = express();
const pg = require("pg");
const pool = new pg.Pool({
user: "postgres",
host: "localhost",
database: "postgres",
password: "postgres",
port: 5432
});
app.get("/", (req, res) => {
res.send("Hello Node.js");
});
app.get("/members", (req, res) => {
pool.connect((err, client) => {
if (err) {
res.send(err.stack);
} else {
const query = {
text: "select * from members",
values: [],
};
client.query(query, (error, response) => {
if (error) {
res.send(error.stack);
} else {
res.json(response.rows);
}
})
}
});
})
app.listen(3000, () => {
console.log("Start server on port 3000.");
});