Differences

This shows you the differences between two versions of the page.

--- сервис_postgresql [2025/01/16 13:47]
val [psql]
+++ сервис_postgresql [2025/05/13 20:52] (current)
val [CloudNativePG Recovery]
@@ Line 1: / Line 1: @@
 ====== Сервис 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: от простого к сложному]]
-===== psql =====
+  * Сервис 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]]
@@ Line 9: / Line 119: @@
 <code>
+# ###apt install postgresql-client
+# sudo -u postgres psql
+postgres=# ALTER USER postgres WITH PASSWORD 'strongpassword';
+postgres=# CREATE DATABASE keycloak;
 postgres=# \l
-postgres=# \c blogs
+postgres=# CREATE USER keycloak WITH PASSWORD 'strongpassword';
-blogs=# \dt
+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]]

Методические материалы Лохтурова Вячеслава

User Tools

Site Tools

Differences

Page Tools