====== Сервис PostgreSQL ======

  * [[https://selectel.ru/blog/courses/dive-into-postgresql/|Погружение в PostgreSQL (Selectel)]]

===== Установка PostgreSQL =====

==== Linux Debian/Ubuntu ====

  * [[https://selectel.ru/blog/tutorials/how-to-install-and-use-postgresql-on-ubuntu-20-04/|Установка и использование PostgreSQL]]
<code>
server# apt install postgresql postgresql-contrib
</code>
==== Docker ====

  * [[https://habr.com/ru/articles/578744/|Запускаем PostgreSQL в Docker: от простого к сложному]]

  * Сервис Keycloak [[Сервис Keycloak#docker-compose]]

==== Kubernetes ====

  * Как не надо делать: [[https://www.digitalocean.com/community/tutorials/how-to-deploy-postgres-to-kubernetes-cluster]]

=== Helm ===

  * [[https://en.wikipedia.org/wiki/Bitnami]]
  * [[https://artifacthub.io/packages/helm/bitnami/postgresql/16.6.2]]

  * Dynamic Volume Provisioning [[Система Kubernetes#longhorn]]

<code>
kube1:~/my-postgres# helm show values oci://registry-1.docker.io/bitnamicharts/postgresql --version 16.6.2 | tee values.yaml.orig

kube1:~/my-postgres# cat values.yaml
</code><code>
auth:
  postgresPassword: "strongpassword"
</code><code>
kube1:~/my-postgres# helm upgrade my-postgres -i -f values.yaml oci://registry-1.docker.io/bitnamicharts/postgresql -n my-postgres-ns --create-namespace --version 16.6.2

kube1:~# kubectl -n my-postgres-ns exec -ti pod/my-postgres-postgresql-0 -- /opt/bitnami/scripts/postgresql/entrypoint.sh psql

kube1:~# kubectl -n my-postgres-ns exec -ti pod/my-postgres-postgresql-0 -- env PGPASSWORD="strongpassword" psql -U postgres

kube1:~# ###psql postgres://postgres:strongpassword@my-postgres-postgresql.my-postgres-ns:5432/postgres

kube1:~# kubectl port-forward -n my-postgres-ns --address 0.0.0.0 services/my-postgres-postgresql 5432:5432

server# docker run -it --rm postgres:17 psql postgres://postgres:strongpassword@kube1:5432/postgres
</code>

=== CloudNativePG ===

  * [[https://cloudnative-pg.io/documentation/current/quickstart/]]
  * [[https://cloudnative-pg.io/documentation/1.25/bootstrap/]]

  * [[https://habr.com/ru/companies/oleg-bunin/articles/766898/|Операторы в Kubernetes]] и [[https://habr.com/ru/companies/flant/articles/684202/|Обзор операторов PostgreSQL для Kubernetes. Часть 3: CloudNativePG]]

<code>
kube1:~# kubectl apply --server-side -f https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.25/releases/cnpg-1.25.1.yaml

kube1:~# kubectl -n cnpg-system get all

kube1:~/my-pgcluster# echo -n keycloak | base64
kube1:~/my-pgcluster# echo -n strongpassword | base64
kube1:~/my-pgcluster# cat keycloak-db-secret.yaml
</code><code>
apiVersion: v1
data:
  username: a2V5Y2xvYWs=
  password: c3Ryb25ncGFzc3dvcmQ=
kind: Secret
metadata:
  name: keycloak-db-secret
type: kubernetes.io/basic-auth
</code><code>
kube1:~/my-pgcluster# cat my-pgcluster.yaml
</code><code>
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-pgcluster
spec:
  instances: 3
  bootstrap:
    initdb:
      database: keycloak
      owner: keycloak
      secret:
        name: keycloak-db-secret
  storage:
    size: 10Gi
</code><code>
kube1:~/my-pgcluster# kubectl create ns my-pgcluster-ns

kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns apply -f keycloak-db-secret.yaml,my-pgcluster.yaml

kube1# watch kubectl -n my-pgcluster-ns get all

kube1# ###kubectl -n my-pgcluster-ns exec -it pod/my-pgcluster-1 -- psql

kube1# ###psql postgres://keycloak:strongpassword@my-pgcluster-rw.my-pgcluster-ns:5432/keycloak
</code>

== Плагин cnpg ==
  * [[https://cloudnative-pg.io/documentation/current/kubectl-plugin/]]
<code>
kube1:~# wget https://github.com/cloudnative-pg/cloudnative-pg/releases/download/v1.25.1/kubectl-cnpg_1.25.1_linux_x86_64.deb -O kube-plugin.deb

kube1:~# dpkg -i kube-plugin.deb

kube1:~# kubectl cnpg status my-pgcluster -n my-pgcluster-ns

kube1:~# kubectl cnpg logs cluster my-pgcluster -n my-pgcluster-ns
</code>
===== Клиент psql =====

  * [[https://stackoverflow.com/questions/70356736/postgres-lean-docker-image-containing-only-psql-client|Postgres lean Docker image containing only "psql" client]]
  * [[https://www.postgresqltutorial.com/postgresql-administration/psql-commands/|17 Practical psql Commands That You Don’t Want To Miss]]

<code>
# ###apt install postgresql-client

# sudo -u postgres psql

postgres=# ALTER USER postgres WITH PASSWORD 'strongpassword';

postgres=# CREATE DATABASE keycloak;

postgres=# \l

postgres=# CREATE USER keycloak WITH PASSWORD 'strongpassword';

postgres=# \dg

postgres=# ALTER DATABASE keycloak OWNER TO keycloak;
</code>

  * [[Сервис Keycloak]]

<code>
postgres=# \c keycloak

keycloak=# \dt

keycloak=# \d user_entity

keycloak=# SELECT id, username FROM user_entity;

keycloak=# SELECT * FROM user_entity WHERE username='admin';
</code>

===== Клиент pgadmin =====

  * https://www.pgadmin.org/download/
  * https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

<code>
server# mkdir -p /private/var/lib/pgadmin; chmod 777 /private/var/lib/pgadmin

server# docker run -p 8081:80 -e 'PGADMIN_DEFAULT_EMAIL=postgres@corp13.un' -e 'PGADMIN_DEFAULT_PASSWORD=strongpassword' -v /private/var/lib/pgadmin:/var/lib/pgadmin --name pgadmin -d dpage/pgadmin4

server# docker logs pgadmin -f
...
... Listening at: http://[::]:80
...
</code>
===== Подключение по сети =====

  * [[https://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection|Kill a postgresql session/connection]]

<code>
server# cat /etc/postgresql/15/main/postgresql.conf
</code><code>
...
listen_addresses = '*'
...
</code><code>
# cat /etc/postgresql/15/main/pg_hba.conf
</code><code>
...
host    all             all             0.0.0.0/0            scram-sha-256
</code><code>
server# systemctl restart postgresql

client$ psql postgres://postgres:strongpassword@IP_OR_DNS_NAME:5432/postgres #-c "\dt"
</code>

==== PgBouncer ====

  * [[https://selectel.ru/blog/tutorials/how-to-install-pgbouncer-connection-pooler-for-postgresql/|Установка пулера соединений PgBouncer для PostgreSQL]]

==== CloudNativePG PgBouncer ====

  * [[https://cloudnative-pg.io/documentation/1.25/connection_pooling/]]

<code>
kube1:~/my-pgcluster# cat my-pgpooler.yaml
</code><code>
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: my-pgpooler
spec:
  cluster:
    name: my-pgcluster
  instances: 3
  type: rw
  pgbouncer:
    poolMode: session
    parameters:
      ignore_startup_parameters: search_path,extra_float_digits,options
      max_client_conn: "1000"
      default_pool_size: "10"
</code><code>
kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns apply -f my-pgpooler.yaml

kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns get all
</code>
===== Потоковая репликация =====

  * [[https://selectel.ru/blog/tutorials/how-to-set-up-replication-in-postgresql/|Как настроить репликацию в PostgreSQL]]

===== Резервное копирование и восстановление =====

  * [[https://www.postgresql.org/docs/current/backup-dump.html]]
  * [[https://snapshooter.com/learn/postgresql/pg_dump_pg_restore|PostgreSQL pg_dump Backup and pg_restore Restore Guide]]
  * [[https://dba.stackexchange.com/questions/76417/restoring-postgres-database-pg-restore-vs-just-using-psql|restoring Postgres database: pg_restore -vs- just using psql]]

  * [[https://docs.pgbarman.org/release/2.11/#before-you-start]]

==== pg_dump ====

  * [[https://www.postgresql.org/docs/current/app-pgdump.html]]

<code>
root@d74a54cb5501:/# ###pg_dump -F c -d keycloak -U keycloak > /var/lib/postgresql/data/keycloak.dump

gate# sudo -u postgres pg_dump -F c -d keycloak > /tmp/keycloak.dump

gate# sudo -u postgres pg_dump -d keycloak > /tmp/keycloak.sql

gate# ###scp /tmp/keycloak.* kube1:
</code>

==== pg_restore ====
<code>
kube1# kubectl -n my-postgres-ns cp ~/keycloak.dump my-postgres-postgresql-0:/bitnami/postgresql/

kube1# kubectl -n my-postgres-ns exec -ti pod/my-postgres-postgresql-0 -- /opt/bitnami/scripts/postgresql/entrypoint.sh bash

postgres@my-postgres-postgresql-0:/$ psql -c "CREATE USER keycloak WITH PASSWORD 'strongpassword';"

postgres@my-postgres-postgresql-0:/$ pg_restore -C -d postgres /bitnami/postgresql/keycloak.dump

или

kube1:~# kubectl -n my-postgres-ns exec -ti pod/my-postgres-postgresql-0 -- env PGPASSWORD="strongpassword" psql -U postgres -c "CREATE USER keycloak WITH PASSWORD 'strongpassword';"

kube1:~# ssh gate.corp13.un cat /tmp/keycloak.dump | kubectl -n my-postgres-ns exec -i pod/my-postgres-postgresql-0 -- env PGPASSWORD="strongpassword" pg_restore -C -d postgres -U postgres
</code>


==== pg_dumpall ====
<code>
kube1:~# kubectl -n my-postgres-ns exec -i pod/my-postgres-postgresql-0 -- env PGPASSWORD="strongpassword" pg_dumpall -U postgres | tee ~/all.sql

kube1:~# kubectl delete ns my-postgres-ns
</code>
  * Снова разворачиваем через [[#Helm]]
<code>
kube1:~# cat ~/all.sql | kubectl -n my-postgres-ns exec -i pod/my-postgres-postgresql-0 -- env PGPASSWORD="strongpassword" psql -U postgres -f -
</code>
  * [[https://stackoverflow.com/questions/31525731/is-it-possible-to-import-one-database-from-pg-dumpall|Можно восстановить отдельную базу в другой инсталляции PostgreSQL]]
<code>
kube1:~# cp all.sql keycloak.sql

kube1:~# cat keycloak.sql
</code><code>
\connect keycloak
...
-- PostgreSQL database dump complete
</code><code>
kube1:~# cat ~/keycloak.sql | kubectl -n my-pgcluster-ns exec -i pod/my-pgcluster-1 -- psql -f -
</code>

==== Barman ====

  * [[https://pgbarman.org/]]
  * [[https://sidmid.ru/barman-%D0%BC%D0%B5%D0%BD%D0%B5%D0%B4%D0%B6%D0%B5%D1%80-%D0%B1%D1%8D%D0%BA%D0%B0%D0%BF%D0%BE%D0%B2-%D0%B4%D0%BB%D1%8F-%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%BE%D0%B2-postgresql/|Barman. менеджер бэкапов для серверов PostgreSQL]]
==== CloudNativePG Backup ====

  * [[https://cloudnative-pg.io/documentation/1.25/backup/]]
  * [[https://github.com/cloudnative-pg/cloudnative-pg/blob/main/docs/src/samples/cluster-example-with-backup.yaml]]

  * [[Сервис MinIO]]

<code>
kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns create secret generic minio-secret \
  --from-literal=ACCESS_KEY_ID=root \
  --from-literal=ACCESS_SECRET_KEY=strongpassword

kube1:~/my-pgcluster# cat my-pgcluster.yaml
</code><code>
...
  backup:
    barmanObjectStore:
      destinationPath: s3://postgresql/
      endpointURL: http://minio.corp13.un:9000
      s3Credentials:
        accessKeyId:
          name: minio-secret
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: minio-secret
          key: ACCESS_SECRET_KEY
      wal:
        compression: gzip
    retentionPolicy: "3d"
</code><code>
kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns apply -f my-pgcluster.yaml

kube1:~/my-pgcluster# kubectl cnpg status my-pgcluster -n my-pgcluster-ns

kube1:~/my-pgcluster# cat my-scheduled-backup.yaml
</code><code>
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: my-scheduled-backup
spec:
  schedule: "0 0 0 * * *"
  immediate: true
  backupOwnerReference: self
  cluster:
    name: my-pgcluster
</code><code>
kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns apply -f my-scheduled-backup.yaml

kube1# kubectl -n my-pgcluster-ns describe backups.postgresql.cnpg.io my-scheduled-backup

kube1# kubectl -n my-pgcluster-ns get backups.postgresql.cnpg.io
</code>

==== CloudNativePG Recovery ====

  * [[https://cloudnative-pg.io/documentation/1.25/recovery/]]

<code>
kube1:~# kubectl create ns my-pgcluster-ns

kube1:~# kubectl -n my-pgcluster-ns create secret generic minio-secret \
  --from-literal=ACCESS_KEY_ID=root \
  --from-literal=ACCESS_SECRET_KEY=strongpassword

kube1:~/my-pgcluster# cat my-pgcluster.yaml
</code><code>
...
  bootstrap:
    recovery:
      source: my-pgcluster

  externalClusters:
    - name: my-pgcluster
      barmanObjectStore:
        destinationPath: s3://postgresql/
        endpointURL: http://minio.corp13.un:9000
        s3Credentials:
          accessKeyId:
            name: minio-secret
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: minio-secret
            key: ACCESS_SECRET_KEY
        wal:
          compression: gzip

#  backup:
# ...
</code>

  * [[#CloudNativePG]]
===== Мониторинг PostgreSQL =====

==== CloudNativePG Monitoring ====

  * [[https://cloudnative-pg.io/documentation/1.25/monitoring/]]
  * [[https://cloudnative-pg.io/documentation/1.25/quickstart/#part-4-monitor-clusters-with-prometheus-and-grafana]]