-- cockroachDB GKE - уменьшаем мощность машины и указываем только 1 зону 1 региона для уменьшения стоимости
-- --cluster-version "1.21.5-gke.1302" (21/11/18)
-- need to update in time
-- --cluster-version "1.22.8-gke.201" (22/06/06)
gcloud beta container --project "celtic-house-266612" clusters create "cockroachdb" --zone "us-central1-c" --no-enable-basic-auth --cluster-version "1.22.8-gke.201" --release-channel "regular" --machine-type "e2-medium" --image-type "COS_CONTAINERD" --disk-type "pd-standard" --disk-size "30" --metadata disable-legacy-endpoints=true --scopes "https://www.googleapis.com/auth/devstorage.read_only","https://www.googleapis.com/auth/logging.write","https://www.googleapis.com/auth/monitoring","https://www.googleapis.com/auth/servicecontrol","https://www.googleapis.com/auth/service.management.readonly","https://www.googleapis.com/auth/trace.append" --max-pods-per-node "110" --preemptible --num-nodes "3" --logging=SYSTEM,WORKLOAD --monitoring=SYSTEM --enable-ip-alias --network "projects/celtic-house-266612/global/networks/default" --subnetwork "projects/celtic-house-266612/regions/us-central1/subnetworks/default" --no-enable-intra-node-visibility --default-max-pods-per-node "110" --no-enable-master-authorized-networks --addons HorizontalPodAutoscaling,HttpLoadBalancing,GcePersistentDiskCsiDriver --enable-autoupgrade --enable-autorepair --max-surge-upgrade 1 --max-unavailable-upgrade 0 --enable-shielded-nodes --node-locations "us-central1-c"


git clone https://github.com/cockroachdb/helm-charts
cd helm-charts/cockroachdb

-- helm repo add cockroachdb https://charts.cockroachdb.com/
helm repo update

nano values.yaml
cat my_values.yaml -- заоверрайтит values.yaml

helm install cockroach . --values my_values.yaml

kubectl get all
kubectl get pv

-- какой под куда уехал
kubectl get all -o wide
gcloud compute disks list

kubectl run cockroachdb -it --image=cockroachdb/cockroach:v21.1.6 --rm --restart=Never -- sql --insecure --host=cockroach-cockroachdb-public
-- для TLS нужно заранее сгенерировать сертификаты и указать их в конфиге
-- https://www.cockroachlabs.com/docs/stable/secure-cockroachdb-kubernetes.html


> SHOW databases;
> CREATE DATABASE bank;
> USE bank;
-- делаем автоматическую генерацию UUID
> CREATE TABLE bank.accounts (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      balance DECIMAL
  );
> INSERT INTO bank.accounts (balance)
  VALUES
      (1000.50), (20000), (380), (500), (55000);
> SELECT * FROM bank.accounts;

kubectl port-forward cockroach-cockroachdb-0 8080
http://localhost:8080
-- иногда 2 из 3 развертывается - посмотрим аллоцированные ресурсы в браузере GCP
kubectl top node

-- пороняем ноды
kubectl get pods
kubectl delete pod cockroach-cockroachdb-2
-- посмотрим в вебинтерфейсе что с нодой

kubectl port-forward cockroach-cockroachdb-0 26257

psql -h localhost -p 26257 -U root
> SHOW users;
> SHOW tables;
> SHOW databases;
> USE bank;
> SELECT * FROM bank.accounts;
> CREATE TABLE if not exists items (itemname varchar(128) primary key, price decimal(19,4), quantity int);
-- https://www.cockroachlabs.com/docs/v21.2/import-into.html
-- https://www.cockroachlabs.com/docs/v21.2/use-cloud-storage-for-bulk-operations
-- enable ?AUTH=implicit
> import INTO items (itemname, price, quantity) CSV DATA ('gs://postgres13/cockroachdb.csv?AUTH=implicit') WITH DELIMITER = E'\t';
> select * from items;

> CREATE TABLE test (
    Region VARCHAR(50),
    Country VARCHAR(50),
    ItemType VARCHAR(50),
    SalesChannel VARCHAR(20),
    OrderPriority VARCHAR(10),
    OrderDate VARCHAR(10),
    OrderID int,
    ShipDate VARCHAR(10),
    UnitsSold int,
    UnitPrice decimal(12,2),
    UnitCost decimal(12,2),
    TotalRevenue decimal(12,2),
    TotalCost decimal(12,2),
    TotalProfit decimal(12,2)
);

-- за сколько загрузится 1 000 000? 125Мб
\timing
> IMPORT INTO test (Region,Country,ItemType,SalesChannel,OrderPriority,OrderDate,OrderID,ShipDate,UnitsSold,UnitPrice,UnitCost,TotalRevenue,TotalCost,TotalProfit) CSV DATA ('gs://postgres13/1000000SalesRecords.csv?AUTH=implicit') WITH DELIMITER = ',', SKIP = '1';

> SELECT count(*) FROM test WHERE unitssold=124;
> CREATE index test_idx on test(unitssold);

explain SELECT count(*) FROM test WHERE unitssold=124;


-- BACKUP
-- https://www.cockroachlabs.com/docs/v21.2/backup
-- чтобы бэкап сработал, нужно создавать ноды с режимом доступа к Cloud API или использовать Service Account
BACKUP DATABASE bank TO 'gs://walgg/database-bank-2022-06-17-weekly' AS OF SYSTEM TIME '-10s';
BACKUP DATABASE bank TO 'gs://walgg/database-bank-2022-06-17-weekly?AUTH=specified&CREDENTIALS=ew5jb20iCn0K' AS OF SYSTEM TIME '-10s';

cat celtic-house-266612-65d95e64c26a.json | base64 -w 0

-- в файлы только enterprice
BACKUP DATABASE bank TO 'file://111.txt' AS OF SYSTEM TIME '-10s';

-- pgdump
pg_dump -d bank -p 26257 -U root -h localhost --table=accounts


gcloud container clusters list
gcloud container clusters delete cockroachdb --zone us-central1-c

--посмотрим, что осталось от кластера
gcloud compute disks list