(2019/11/20 バックアップのための追記)
OS: Ubuntu 18.04 LTS
Version: psql (PostgreSQL) 10.10 (Ubuntu 10.10-1.pgdg18.04+1)
Ubuntu 18.04 LTS service
$ sudo service postgresql start
$ sudo service postgresql status
$ sudo systemctl enable postgresql
Login for first time
/etc/postgresql/10/main/pg_hba.conf
~ ~ ~ 略 ~ ~ ~
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5 <<< IDENT-based authenticationから変更
# IPv6 local connections:
host all all ::1/128 md5 <<< IDENT-based authenticationから変更
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5 <<< IDENT-based authenticationから変更
host replication all ::1/128 md5 <<< IDENT-based authenticationから変更
$ sudo -u postgres psql
Change password
# ALTER USER postgres password 'postgres'; # パスワード例
Login
$ psql -d testdb -h localhost -U postgres
postgresql.conf
ログインで下記のエラーが出た場合
psql: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
postgresql.confで使用したいポートがあっているか（既定値：port = 5432）確認し、なっていなかったらconf fileを変更しサービスをリスタートする。
sudo vi /etc/postgresql/10/main/postgresql.conf
Create database
# CREATE DATABASE testdb TEMPLATE template0 ENCODING 'UNICODE';
Database
# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C | C |
(4 rows)
Schema (2020/03/18 追記)
// Schema list
inventory=# \dn
List of schemas
Name | Owner
-----------------+----------
public | postgres
systeminventory | postgres
// select schema
inventory=# select schema_name from information_schema.schemata where schema_name='systeminventory';
schema_name
-----------------
systeminventory
(1 row)
// Drop schema
inventory=# DROP SCHEMA IF EXISTS systeminventory;
DROP SCHEMA
// Create table
inventory=# CREATE TABLE IF NOT EXISTS SystemInventory.bios (ip VARCHAR, ....);
// Schema配下に作られたテーブルを確認
inventory=# \dt systeminventory.bios;
List of relations
Schema | Name | Type | Owner
-----------------+---------------------+-------+----------
systeminventory | bios | table | postgres
~ ~ ~ ~ ~ ~ ~ ~ ~ 略 ~ ~ ~ ~ ~ ~ ~ ~ ~
// テーブルのカラムを確認
inventory=# \d systeminventory.bios;
Table "systeminventory.bios"
Column | Type | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
ip | character varying | | |
biosreleasedate | character varying | | |
~ ~ ~ ~ ~ ~ ~ ~ ~ 略 ~ ~ ~ ~ ~ ~ ~ ~ ~
DBの切り替え
# \c [DB名]
Table操作関連（例）
// Drop table
# DROP TABLE signal_events;
// Create table
# CREATE TABLE IF NOT EXISTS signal_events (
# time timestamp PRIMARY KEY NOT NULL,
# product_code varchar(10),
# side varchar(10),
# price double precision,
# size double precision);
// Insert
# INSERT INTO signal_events
# (time, product_code, side, price, size)
# VALUES
# ('2019-11-17 08:00:00', 'BTC_JPY', 'BUY', '927654', '0.0315');
Client_encoding Error
経緯
Go の github.com/lib/pq はUTF8をサポートするため、PostgreSQLをUTF8で使いたい。
Windows 10 日本語版にインストールしたPostgreSQLではclient_encodingが既定でsjisになってしまう。
-> Err: client_encoding must be absent or 'UTF8'のエラーでGo のパッケージからテーブルへのへアクセスができない。
結果
規定でUTF8のためWSLにインストールしたPostgreSQLで問題なく動いた。
どれもNGだったがやってみたことのメモ：
- PostgreSQLの再インストール
- client_encoding 変更
SHOW client_encoding;
SET client_encoding='utf8';
- Windowsの環境変数を変更
PGCLIENTENCODING : UTF8
- initdb
initdb --encoding UNICODE;
バックアップのための追記
モジュールインストール
$ sudo apt install postgresql-contrib
$ pg_dump --version
pg_dump (PostgreSQL) 10.10 (Ubuntu 10.10-1.pgdg18.04+1)
OS ユーザをpostgreに登録とGrant
postgres=# CREATE USER ubuntu SYSID 1000 PASSWORD 'ubuntu';
NOTICE: SYSID can no longer be specified // 不要とのこと
CREATE ROLE
postgres=# GRANT USAGE ON SCHEMA public TO ubuntu;
GRANT
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO ubuntu;
GRANT
postgres=# GRANT ubuntu TO postgres;
GRANT ROLE
// 逆だったので外す
postgres=# REVOKE ubuntu FROM postgres;
postgres=# GRANT postgres TO ubuntu;
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
ubuntu | | {postgres}
バックアップ実行
$ pg_dump -Fc testdb -f testdb.dump
# 問題なければ何も返らない。
# 下記はバックアップ実行ユーザ(ubuntu)にGRANTをしておかないとでる。
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation btc_jpy_1s
テストのためにDROPとCREATE
postgres=# DROP DATABASE testdb;
DROP DATABASE
postgres=# CREATE DATABASE testdb TEMPLATE template0 ENCODING 'UNICODE';
CREATE DATABASE
リストア
$ pg_restore -d testdb testdb.dump
# 問題なければ何も返らない。
リストアエラー
- 非特権ユーザーでのバックアップだったためコメントを書かれた。
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2922; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
参考先によると、
pg_dump コマンドは plgpsql エクステンションに関するコメントをダンプファイルに書き込む。このエクステンションの所有者は特権ユーザーであるため、リストア時にエラーを引き起こす。
- 下記はバックアップ実行ユーザ(ubuntu)にGRANTをしておかないとでる。
pg_restore: [archiver (db)] Error from TOC entry 201; 1259 24576 TABLE signal_events postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be member of role "postgres"
Command was: ALTER TABLE public.signal_events OWNER TO postgres;
今まで通り使えることの確認
Ticker情報をtableに追記するGOのモジュールを実行し、追記されていることを確認。
その情報をWebserverからブラウザへ配信更新され続けていることを確認。