Go,postgresql-13 On Ubuntu 18.04 etc

Firewall

sudo ufw status
sudo timedatectl set-timezone Asia/Tokyo

goenv

install

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

install

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")
}

テストデータ作成

Why Synth

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

パーテーション

PostgreSQL13 レンジパーティションを作成する

宣言的パーテーションテーブル

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