User Tools
сервис_postgresql
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
сервис_postgresql [2025/05/11 13:10] val [Подключение по сети] |
сервис_postgresql [2025/05/13 20:52] (current) val [CloudNativePG Recovery] |
||
|---|---|---|---|
| Line 144: | Line 144: | ||
| keycloak=# \d user_entity | keycloak=# \d user_entity | ||
| + | |||
| + | keycloak=# SELECT id, username FROM user_entity; | ||
| keycloak=# SELECT * FROM user_entity WHERE username='admin'; | keycloak=# SELECT * FROM user_entity WHERE username='admin'; | ||
| Line 156: | Line 158: | ||
| server# mkdir -p /private/var/lib/pgadmin; chmod 777 /private/var/lib/pgadmin | server# mkdir -p /private/var/lib/pgadmin; chmod 777 /private/var/lib/pgadmin | ||
| - | server# docker run -p 8080: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 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 | server# docker logs pgadmin -f | ||
| Line 164: | Line 166: | ||
| </code> | </code> | ||
| ===== Подключение по сети ===== | ===== Подключение по сети ===== | ||
| - | |||
| - | * [[https://selectel.ru/blog/tutorials/how-to-install-pgbouncer-connection-pooler-for-postgresql/|Установка пулера соединений PgBouncer для PostgreSQL]] | ||
| * [[https://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection|Kill a postgresql session/connection]] | * [[https://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection|Kill a postgresql session/connection]] | ||
| Line 185: | Line 185: | ||
| client$ psql postgres://postgres:strongpassword@IP_OR_DNS_NAME:5432/postgres #-c "\dt" | client$ psql postgres://postgres:strongpassword@IP_OR_DNS_NAME:5432/postgres #-c "\dt" | ||
| </code> | </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]] | ||
| ===== Резервное копирование и восстановление ===== | ===== Резервное копирование и восстановление ===== | ||
| Line 205: | Line 240: | ||
| gate# sudo -u postgres pg_dump -d keycloak > /tmp/keycloak.sql | gate# sudo -u postgres pg_dump -d keycloak > /tmp/keycloak.sql | ||
| - | gate# scp /tmp/keycloak.* kube1: | + | gate# ###scp /tmp/keycloak.* kube1: |
| </code> | </code> | ||
| Line 222: | Line 257: | ||
| 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:~# 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:~# cat ~/keycloak.dump | kubectl -n my-postgres-ns exec -i pod/my-postgres-postgresql-0 -- env PGPASSWORD="strongpassword" pg_restore -C -d postgres -U postgres | + | 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> | </code> | ||
| Line 252: | Line 287: | ||
| * [[https://pgbarman.org/]] | * [[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 ==== | ==== CloudNativePG Backup ==== | ||
| Line 282: | Line 318: | ||
| retentionPolicy: "3d" | retentionPolicy: "3d" | ||
| </code><code> | </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 | kube1:~/my-pgcluster# cat my-scheduled-backup.yaml | ||
| </code><code> | </code><code> | ||
| Line 297: | Line 337: | ||
| kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns apply -f my-scheduled-backup.yaml | kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns apply -f my-scheduled-backup.yaml | ||
| - | kube1# kubectl -n my-pgcluster-ns describe backup my-scheduled-backup | + | kube1# kubectl -n my-pgcluster-ns describe backups.postgresql.cnpg.io my-scheduled-backup |
| - | kube1# kubectl -n my-pgcluster-ns get backup | + | kube1# kubectl -n my-pgcluster-ns get backups.postgresql.cnpg.io |
| </code> | </code> | ||
| Line 307: | Line 347: | ||
| <code> | <code> | ||
| - | kube1:~/my-pgcluster# kubectl -n my-pgcluster-ns create secret generic minio-secret \ | + | 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_KEY_ID=root \ | ||
| --from-literal=ACCESS_SECRET_KEY=strongpassword | --from-literal=ACCESS_SECRET_KEY=strongpassword | ||
| Line 337: | Line 379: | ||
| </code> | </code> | ||
| + | * [[#CloudNativePG]] | ||
| ===== Мониторинг PostgreSQL ===== | ===== Мониторинг PostgreSQL ===== | ||
сервис_postgresql.1746958226.txt.gz · Last modified: 2025/05/11 13:10 by val
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
