Go,postgresql-13 On Ubuntu 18.04 etc
Firewall
sudo ufw status
sudo timedatectl set-timezone Asia/Tokyo
goenv
git clone https://github.com/syndbg/goenv.git ~/.goenv
echo '# ADD' >> ~/.bashrc
echo 'export GOENV_ROOT="$HOME/.goenv"' >> ~/.bashrc
echo 'export PATH="$GOENV_ROOT/bin:$PATH"' >> ~/.bashrc
echo 'eval "$(goenv init -)"' >> ~/.bashrc
echo 'export PATH="$GOROOT/bin:$PATH"' >> ~/.bashrc
echo 'export PATH="$PATH:$GOPATH/bin"' >> ~/.bashrc
source ~/.bashrc
goenv install -l
goenv install 1.16.15
goenv global 1.16.15
goenv rehash
go version
postgresql 13
sudo apt update
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install postgresql-13 postgresql-client-13 libpq-dev
systemctl status postgresql@13-main.service
sudo su - postgres
psql -c "alter user postgres with password 'postgres'"
psql
=# \conninfo
cat /etc/postgresql/13/main/postgresql.conf | grep listen_addresses
listen_addresses = '*' # what IP address(es) to listen on;
- /etc/postgresql/13/main/pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres md5
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 md5
sudo systemctl restart postgresql
psql -U postgres
etc
# 以下はダメ
export POSTGRES_PASSWORD=postgres
# 以下OK
export PGPASSWORD=postgres
localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
sudo systemctl restart postgres
- /vagrant/99.sql/create_databese.sql
CREATE DATABASE locale_c
TEMPLATE template0 ENCODING 'UTF-8'
LC_COLLATE 'C' LC_CTYPE 'C';
CREATE DATABASE locale_jp_utf8
TEMPLATE template0 ENCODING 'UTF-8'
LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8';
CREATE DATABASE locale_us_utf8
TEMPLATE template0 ENCODING 'UTF-8'
LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
CREATE DATABASE test_data
TEMPLATE template0 ENCODING 'UTF-8'
LC_COLLATE 'C' LC_CTYPE 'C';
export PGPASSWORD=postgres
psql -U postgres -h localhost -f /vagrant/99.sql/create_databese.sql
-
test Database
-
/vagrant/99.sql/create_table.sql
-- CREATE TABLE TABLE_NAME(COLUMN + datatype + constraints [optional]);
DROP TABLE IF EXISTS users;
DROP SEQUENCE IF EXISTS users_id_seq;
CREATE SEQUENCE users_id_seq;
CREATE TABLE users(
id BIGINT GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY(id),
hash_firstname TEXT NOT NULL,
hash_lastname TEXT NOT NULL,
sample_seq INTEGER DEFAULT nextval('users_id_seq'),
gender VARCHAR(6) NOT NULL CHECK (gender IN ('male', 'female'))
);
INSERT INTO users(hash_firstname, hash_lastname, gender)
SELECT md5(RANDOM()::TEXT), md5(RANDOM()::TEXT), CASE WHEN RANDOM() < 0.5 THEN 'male' ELSE 'female' END FROM generate_series(1, 10000);
- TODO get sequences value
-- NOT WORK
SELECT * FROM information_schema.sequences WHERE sequence_name IN ('users_id_seq');
psql -U postgres -h localhost -d test_data -f /vagrant/99.sql/create_table.sql
成功
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
// Initialize connection constants.
HOST = "127.0.0.1"
DATABASE = "test_data"
USER = "postgres"
PASSWORD = "postgres"
)
func checkError(err error) {
if err != nil {
panic(err)
}
}
func main() {
// Initialize connection string.
var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", HOST, USER, PASSWORD, DATABASE)
// Initialize connection object.
db, err := sql.Open("postgres", connectionString)
checkError(err)
err = db.Ping()
checkError(err)
fmt.Println("Successfully created connection to database")
// Drop previous table of same name if one exists.
_, err = db.Exec("DROP TABLE IF EXISTS inventory;")
checkError(err)
fmt.Println("Finished dropping table (if existed)")
// Create table.
_, err = db.Exec("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
checkError(err)
fmt.Println("Finished creating table")
// Insert some data into table.
sql_statement := "INSERT INTO inventory (name, quantity) VALUES ($1, $2);"
_, err = db.Exec(sql_statement, "banana", 150)
checkError(err)
_, err = db.Exec(sql_statement, "orange", 154)
checkError(err)
_, err = db.Exec(sql_statement, "apple", 100)
checkError(err)
fmt.Println("Inserted 3 rows of data")
}
テストデータ作成
curl -sSL https://getsynth.com/install | sh
echo '# add synth' >> ~/.bashrc
echo 'export PATH=$PATH:/home/vagrant/.local/bin' >> ~/.bashrc
tail -1 ~/.bashrc
source ~/.bashrc
which synth
#/home/vagrant/.local/bin/synth
--from postgres://postgres:postgres@localhost:5432/inventory
synth import test_data --from postgres://postgres:postgres@localhost:5432/test_data
synth generate test_data/ --size 2 --to postgres://postgres:postgres@localhost:5432/test_data
synth import --from postgres://postgres:postgres@localhost:5432/test_data --schema public test_data
synth generate test_data --to postgres://postgres:postgres@localhost:5432/test_data --schema public
パーテーション
export PGPASSWORD=postgres
psql -U postgres test_data
CREATE TABLE table_range_p (id INTEGER PRIMARY KEY, no INTEGER, val TEXT) PARTITION BY RANGE (id);
INSERT INTO table_range_p VALUES (100, 100, 'このデータが追加できれば、TO は「以下」');
# result
ERROR: no partition of relation "table_range_p" found for row
DETAIL: Partition key of the failing row contains (id) = (100).
CREATE TABLE table_range_c_000_100 PARTITION OF table_range_p FOR VALUES FROM ( 0 ) TO ( 100 );
INSERT INTO table_range_p VALUES (100, 100, 'このデータが追加できれば、TO は「以下」');
# result
ERROR: no partition of relation "table_range_p" found for row
DETAIL: Partition key of the failing row contains (id) = (100).
CREATE TABLE table_range_c_100_200 PARTITION OF table_range_p FOR VALUES FROM ( 100 ) TO ( 200 );
INSERT INTO table_range_p VALUES (100, 100, 'このデータが追加できれば、TO は「以下」');
# result
INSERT 0 1
VALUES (100, 100, ・・・) TB : table_range_c_100_200 に格納される。
-- パーティションテーブルの作成
CREATE TABLE rireki (date date, name text) PARTITION BY RANGE (date);
-- パーティションの作成
CREATE TABLE rireki_01 PARTITION OF rireki FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE rireki_02 PARTITION OF rireki FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
CREATE TABLE rireki_03 PARTITION OF rireki FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');
CREATE TABLE rireki_04 PARTITION OF rireki FOR VALUES FROM ('2019-04-01') TO ('2019-05-01');
INSERT INTO rireki VALUES ('2019-02-04', 'NAME 2019-02-04');
INSERT INTO rireki VALUES ('2019-03-04', 'NAME 2019-03-04');
INSERT INTO rireki VALUES ('2019-04-04', 'NAME 2019-04-04');
# 関連するテーブル全て削除される
DROP table rireki;
パーテーション作成SQL
#!/bin/sh
# CREATE TABLE rireki_01 PARTITION OF rireki FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
# sh create_part.sh 2021-01-01 2021-04-01
startdate=$1
enddate=$2
TABLE_NAME="rireki"
OUTPUT="./output.sql"
echo -n "" > ${OUTPUT}
F_MON="+%Y_%m"
F_DAY="+%Y-%m-%d"
while true; do
month=$(date -d "${startdate::8}-01" ${F_MON})
echo ${month}
month_start=$(date -d "${startdate::8}01" ${F_DAY})
month_end=$(date -d "${startdate::8}01 +1month -1day" ${F_DAY})
if [[ "$month_end" < "$enddate" ]]; then
echo "start = ${month_start} , end = ${month_end}"
echo "CREATE TABLE ${TABLE_NAME}_${month} PARTITION OF ${TABLE_NAME} FOR VALUES FROM ('${month_start}') TO ('${month_end}');" >> ${OUTPUT}
else
break
fi
startdate=$(date -d "$month_end +1day" ${F_DAY})
done