PostgreSQL - Offirmo-team/wiki GitHub Wiki

Voir aussi base de données, SQL…

Introduction

https://www.postgresql.org/

Une base de données "couteau suisse". Fonctionalités:

• transactions
• no-sql-like: JSON
  • https://www.postgresql.org/docs/current/functions-json.html
  • https://www.postgresql.org/docs/current/datatype-json.html
• full text search http://rachbelaid.com/postgres-full-text-search-is-good-enough/
• queue https://adriano.fyi/posts/2023-09-24-choose-postgres-queue-technology/
• time series https://www.timescale.com/

Récent:

• 2022/07 https://shekhargulati.com/2022/07/08/my-notes-on-gitlabs-postgres-schema-design/
• 2019 https://modern-sql.com/blog/2019-02/postgresql-11

Sécurité

http://www.ibm.com/developerworks/library/os-postgresecurity/

Installation

Local

• docker https://hub.docker.com/_/postgres
• postgresapp https://postgresapp.com/
• Ubuntu

sudo apt-get install postgresql
sudo apt-get install libpq-dev

cloud

• as a service
  • AWS
  • https://www.enterprisedb.com/
• heroku, inline addon or:

heroku addons:add heroku-postgresql:dev
heroku pg:promote HEROKU_POSTGRESQL_CHARCOAL_URL

• https://elements.heroku.com/addons/heroku-postgresql
• https://devcenter.heroku.com/articles/heroku-postgresql#version-support
• external connexion https://devcenter.heroku.com/articles/heroku-postgresql#external-connections-ingress

config

sudo -u postgres psql
SHOW config_file;
-> /etc/postgresql/9.3/main/postgresql.conf
-> /etc/postgresql/9.3/main/pg_hba.conf

Utilisation

nommage

• valid chars in table names
  • see SQL standard
  • https://dba.stackexchange.com/questions/45589/what-are-the-valid-formats-of-a-postgresql-schema-name

Commandes

relancer

sudo /etc/init.d/postgresql restart

requêtage

Voir SQL

Schéma

• https://dba.stackexchange.com/questions/45589/what-are-the-valid-formats-of-a-postgresql-schema-name

types de donnée

• https://www.postgresql.org/docs/11/sql-createtable.html
• https://www.postgresql.org/docs/11/datatype.html

Clients

Commun

"Connection string" postgres://someuser:somepassword@somehost:381/somedatabase

Clients graphiques

• officiel https://www.pgadmin.org/
• https://www.jetbrains.com/datagrip/
• all https://wiki.postgresql.org/wiki/PostgreSQL_Clients
  • https://severalnines.com/blog/top-gui-tools-postgresql

connecteurs

node.js

https://medium.com/dailyjs/postgresql-with-nodejs-d0dcedba5884

• [driver] "de facto standard" node-postgres https://node-postgres.com/
  • https://github.com/brianc/node-postgres
• "query builder" knex
  • +++ cheatsheet https://devhints.io/knex
  • arrays: https://stackoverflow.com/questions/50118196/how-to-insert-array-data-type-using-knex-and-potsgres
  • array append https://github.com/tgriesser/knex/issues/879
  • full text search https://github.com/tgriesser/knex/wiki/Recipes#adding-a-full-text-index-for-postgresql
• ORM bookshelf
• Not-an-ORM Massive JS https://massivejs.org/
  • This is not an ORM, it's a bit more than a query tool https://github.com/robconery/massive-js
  • http://www.craigkerstiens.com/2015/12/08/massive-json/
  • http://www.craigkerstiens.com/2015/11/30/massive-node-postgres-an-overview-and-intro/
  • https://massive-js.readthedocs.io/en/v2/
• ship hold (+ experimental) https://medium.com/dailyjs/postgresql-with-nodejs-d0dcedba5884
• http://rob.conery.io/2015/03/13/bringing-the-power-of-postgres-to-nodejs/
  • et astuces http://rob.conery.io/category/postgres/
• rdb Object Relational Mapper for postgres https://www.npmjs.org/package/rdb

Avancé

NoSQL

• http://www.postgresql.org/docs/9.4/static/datatype-json.html
• http://www.postgresql.org/docs/9.4/static/functions-json.html

SELECT
  articles.metadata->'foo'->'date' AS date
WHERE
  articles.metadata #>> '{foo,date}' = '2015-01-28'

full text search

http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/

Problèmes rencontrés

KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Combo of version problem node + pg. For now, use a clean node 12: nvm use