postgresql

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
postgresql [2022/09/10 13:22] – [Postgres.PSQL] adminpostgresql [2024/08/04 02:07] (current) – [Postgresql] admin
Line 1: Line 1:
 ====== Postgresql ====== ====== Postgresql ======
 +<code PYTHON> 
 +
 +psql -h 192.168.1.100 -p 5432 -U u1 mydatabase
 +
 +
 +import psycopg2
 +conn = psycopg2.connect("host='10.59.1.50' dbname='DB' user='perm' password='perm'")
 +</code>
 ===== Interesting sites ===== ===== Interesting sites =====
 ** PostgreSQL ** ** PostgreSQL **
 +  * https://postgresqlco.nf/ - configuration
   * https://pgpedia.info/ - encyclopedia of postgres   * https://pgpedia.info/ - encyclopedia of postgres
   * https://www.interdb.jp/pg/ - internal structure postgresql  Pavel Kopalov, [13.04.21 22:23]     * https://www.interdb.jp/pg/ - internal structure postgresql  Pavel Kopalov, [13.04.21 22:23]  
   * https://pgstats.dev/ PostgreSQL Observability     * https://pgstats.dev/ PostgreSQL Observability  
 +  * https://habr.com/ru/companies/tensor/articles/771406/ - Antipatterns 
   * https://dataegret.ru/2020/07/how-to-become-a-postgresql-dba/ - books   * https://dataegret.ru/2020/07/how-to-become-a-postgresql-dba/ - books
   * https://pganalyze.com/   * https://pganalyze.com/
Line 12: Line 22:
  
   * https://kb.objectrocket.com/category/postgresql - read   * https://kb.objectrocket.com/category/postgresql - read
 +
 +  * https://til.hashrocket.com/posts/da9ade5291-clear-the-screen-in-psql - psql clear console
  
 ** Draw Db diagram ** ** Draw Db diagram **
   * <https://dbdiagram.io/home> нарисовать диаграмму данных    * <https://dbdiagram.io/home> нарисовать диаграмму данных 
  
 +** Kubernetes - postgres VIEW  **
 +  * https://cloudnative-pg.io/documentation/1.19/
  
 ===== Postgres.Cmd ===== ===== Postgres.Cmd =====
Line 46: Line 60:
 ++++ ++++
 ===== Postgres.Sql ===== ===== Postgres.Sql =====
 +  * https://habr.com/ru/articles/791260/ - courses
   * https://gist.github.com/rgreenjr/3637525 - useful sqls   * https://gist.github.com/rgreenjr/3637525 - useful sqls
   * https://habr.com/ru/post/280912/ Pg Трюки   * https://habr.com/ru/post/280912/ Pg Трюки
Line 439: Line 454:
 ==== Sql.JSONB ==== ==== Sql.JSONB ====
    * PG JSON fucntion - https://postgrespro.ru/docs/postgresql/14/functions-json    * PG JSON fucntion - https://postgrespro.ru/docs/postgresql/14/functions-json
 +   * Antipattern jsonb - https://github.com/jugatsu/onec-docker#support
 ++++ SQL json examples| ++++ SQL json examples|
 <code SQL> <code SQL>
Line 739: Line 755:
  
   *  shared memory no space left https://medium.com/@tough_jonquil_dog_815/postgresql-no-space-left-on-device-a9c5c3b41cff   *  shared memory no space left https://medium.com/@tough_jonquil_dog_815/postgresql-no-space-left-on-device-a9c5c3b41cff
 +  * docker shared memory https://www.instaclustr.com/blog/postgresql-docker-and-shared-memory/
  
 ==== Postgres.DBA locale collation LC_COLLATE setlocale==== ==== Postgres.DBA locale collation LC_COLLATE setlocale====
Line 1210: Line 1227:
   * Wal-g - https://habr.com/ru/post/506610/ - пример   * Wal-g - https://habr.com/ru/post/506610/ - пример
   * пример ручного dump c WAL https://pgdash.io/blog/postgres-incremental-backup-recovery.html   * пример ручного dump c WAL https://pgdash.io/blog/postgres-incremental-backup-recovery.html
 +  * пример pg_backup <https://stormatics.tech/blogs/postgresql-physical-backups-using-pg_basebackup-a-comprehensive-guide>
 +
 +=== Wal-g.Backup статьи ===
 +  * надежные и быстрые бэкапы [[https://highload.ru/spring/2021/abstracts/7276|ссылка]]
 +  * пример - [[https://habr.com/ru/post/506610/|habr пример]] [[https://medium.com/@philyuchkoff/wal-g-953490c74b98|medium]] [[https://github.com/camptocamp/wal-g-prometheus-exporter|wal-g-prometheus-exporter]]
  
 === Wal-g.Backup   резервного копирования === === Wal-g.Backup   резервного копирования ===
Line 1362: Line 1384:
 ===== Postgres Cluster ===== ===== Postgres Cluster =====
 https://github.com/vitabaks/postgresql_cluster https://github.com/vitabaks/postgresql_cluster
-===== Postgres Information =====+===== Postgres Information , monitoring , performance , tools =====
  
   * :!:[[https://tapoueh.org/conf/|Автор Postgresql]]   * :!:[[https://tapoueh.org/conf/|Автор Postgresql]]
Line 1372: Line 1394:
  
 ==== Postgres.Monitoring ==== ==== Postgres.Monitoring ====
 +  * pgwatch2 - monitoring Postgres
   * [[https://severalnines.com/database-blog/performance-monitoring-auditing-postgresql-top-resources|Performance monitoring]]   * [[https://severalnines.com/database-blog/performance-monitoring-auditing-postgresql-top-resources|Performance monitoring]]
   * [[https://pgconf.ru/media/2017/04/03/20170317H2_Vagin.pdf|Мониторинг pg в АВИТО]]   * [[https://pgconf.ru/media/2017/04/03/20170317H2_Vagin.pdf|Мониторинг pg в АВИТО]]
Line 1386: Line 1409:
   * [[https://www.slideshare.net/samokhvalov/postgres-docker-highload-2018|Docker Highload]]   * [[https://www.slideshare.net/samokhvalov/postgres-docker-highload-2018|Docker Highload]]
   * [[https://tapoueh.org/tags/concurrency/|Postgresql concurrency]]   * [[https://tapoueh.org/tags/concurrency/|Postgresql concurrency]]
 +  * [[https://habr.com/ru/companies/tensor/articles/808931/|Неожиданные последствия запуска PostgreSQL в Docker: отключение JIT и разбор запросов / Хабр
  
 ==== Postgres.Pooler ==== ==== Postgres.Pooler ====
Line 1413: Line 1437:
   - https://learn.hashicorp.com/tutorials/consul/deployment-guide     - https://learn.hashicorp.com/tutorials/consul/deployment-guide  
 </WRAP> </WRAP>
 +<WRAP clear/>
 +
 +
 +===== Postgres Install =====
 +  * postgres install example in docker https://habr.com/ru/post/578744/
 +  * https://habr.com/ru/articles/735274/comments/ - postgresql docker shared_buffers effective_cache_size work_mem shm_size Random_page_cost ssd
 +
 +===== Postgres Additional info =====
 +  * https://shekhargulati.com/2022/07/08/my-notes-on-gitlabs-postgres-schema-design/
 +
  
  • postgresql.1662816148.txt.gz
  • Last modified: 2022/09/10 13:22
  • by admin