postgresql

1. postgresql

1. postgresql

1.1. In praise of PostgreSQL

https://www.reddit.com/r/programming/comments/oynrdh/in_praise_of_postgresql/

1.2. Hands-on with PostgreSQL Authorization - Part 2 - Row-Level Security

https://www.tangramvision.com/blog/hands-on-with-postgresql-authorization-part-2-row-level-security

1.3. citusdata/citus: Distributed PostgreSQL as an extension (Columnar database)

1.3.1. https://www.citusdata.com/blog/2019/02/08/the-most-useful-postgres-extension-pg-stat-statements/

1.4. Tricks

1.4.1. conexiones sencillas con url

psql "postgres://postgres:postgres@localhost:5432/postgres"

1.4.2. Importar a una tabla desde un csv

Mucho cuidado porque el orden de los campos importa

  psql "$POSTGRES_DATABASE_URI" -c "\copy table_name from 'file.csv' delimiter ',' csv header;"

  file="table.csv"; tail -n +2 $file | psql -d mydb -U postgres -W -h localhost -p 5432 -c
  "COPY landing_tbl ( $(head -n 1 $file) ) FROM STDIN CSV;"

El tail -n +2 se salta el encabezado del csv, el head -n 1 final.csv
nos saca el encabezado para la lista de campos

1.4.3. Update con join

  update new_table
  set field = b.field
  from backup b
  where  b.created_at = new_table.created_at and b.id = new_table.id and
  b.field is not null

1.4.4. Contraseña postgres

PostgreSQL por defecto no tiene contraseña y no se puede acceder a
ella. Hay que hacer:
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
psql -d postgres -U postgres -W -h localhost -p 5432 para comprobar
que funciona

1.4.5. updates csv

Tener una tabla temporal intermedia para la copia. Al añadir columnas
a la tabla final, no se rompe el proceso que insertaba en esa tabla, y
si cambias el CSV, tienes que cambiar la temporal
Aprovechar ese paso intermedio para intentar no perder registros, p.e.
haciendo un
upsert en
la tabla final desde la temporal

Script de upsert SQL

  begin;
  create temporary table temp_mytable as
  select :fields_list
  from :dest_table
  where 1 <> 1;
  \copy temp_mytable from ':filename' delimiter ',' csv;
  insert into :dest_table(:fields_list)
  select
  :fields_list
  from temp_mytable
  on conflict (:pkey_fields_list)
  do update set
  :update_set;
  commit;

1.4.6. backups

https://www.postgresql.org/docs/current/backup.html

# Primero hay que levantar un proxy a la BBDD para conectarnos a ella en localhost:54321 (según el ejemplo)
psql "postgres://postgres:postgres@localhost:5432/postgres" -c "\copy tablename to tablename.csv delimiter ',' csv header"
pg_dump "postgres://postgres:postgres@localhost:5432/postgres" >  out.sql
psql "postgres://postgres:postgres@localhost:5432/postgres" < out.sql

export PGPASSWORD=yourpassword
pg_dump --verbose -h 127.0.0.1 -U db_user -p 5432 --format=custom --no-owner --no-acl -f database_name.dump database_name
pg_restore --verbose -p 5432 -U db_user -d database_name -h 127.0.0.1 --no-acl --no-owner -W database_name.dump

Separar DDL y SQL INSERT (schema-only y data-only)
http://www.postgresqlnow.com/get-ddl/
https://stackoverflow.com/questions/2857989/using-pg-dump-to-only-get-insert-statements-from-one-table-within-database

https://dba.stackexchange.com/questions/23000/disable-constraints-before-using-pg-restore-exe
-disable-triggers option to pg_restore
Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed
SET CONSTRAINTS ALL DEFERRED; → Ponerlo al principio de un sql

1.4.6.1. huge tables

https://medium.com/@dmitry.romanoff/postgresql-how-to-copy-a-huge-table-in-chunks-from-one-db-instance-to-another-d0d9087124d7

pg_dump dbname | split -d -b 2G - filename_
# Reload with:
cat filename* | psql dbname

1.4.6.2. table by table dump

psql "postgresql://username:password:127.0.0.1:5432/database" -c "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY table_name;" | tail +3 | head -n -2 | xargs -I {} bash -c 'export PGPASSWORD="password"; pg_dump -h 127.0.0.1 -p 8090 -U application -c --format=plain -t "{}" database > "database_tables/{}.sql"'

1.4.6.3. Month by month table dump

latest=$(psql "postgresql://username:password@127.0.0.1:8090/asuma" -c "SELECT to_char(max(estamp), 'yyyy-mm-dd') from bigtable; " --csv | tail +2)
oldest=$(psql "postgresql://username:password@127.0.0.1:8090/asuma" -c "SELECT to_char(min(estamp), 'yyyy-mm-dd') from bigtable; " --csv | tail +2)
d=$oldest
while [ "$d" \< "$latest" ]; do
    echo "$d -- $n"
    n=$(date -I -d "$d + 1 month")
    d=$n
done

1.4.7. Crear usuario y dar permisos

Crear usuario solo lectura en postgres
https://www.aptible.com/docs/kb-create-read-only-database-user-postgresql

  GRANT SELECT ON TABLE public.elnombredelatabla TO el_usuario;
  revoke references ON TABLE public.nombredelatabla from el_usuarios;
  SELECT *
  FROM   information_schema.table_privileges
  where table_name = 'nombre_tabla'

GRANT CONNECT on DATABASE asuma TO developer;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO developer;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO developer;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO developer;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO developer;

REVOKE CREATE ON SCHEMA public FROM developer;

Creating a Read-Only Postgres User
Si se hacen desde DBeaver en una conexión de PROD hay que salir de la aplicación para que haga COMMIT;
https://stackoverflow.com/questions/67276391/why-am-i-getting-a-permission-denied-error-for-schema-public-on-pgadmin-4

CREATE DATABASE EXAMPLE_DB;
CREATE USER EXAMPLE_USER WITH ENCRYPTED PASSWORD 'Sup3rS3cret';
GRANT ALL PRIVILEGES ON DATABASE EXAMPLE_DB TO EXAMPLE_USER;
\c EXAMPLE_DB postgres
-- You are now connected to database "EXAMPLE_DB" as user "postgres".
GRANT ALL ON SCHEMA public TO EXAMPLE_USER;
-- Para crear tablas tienes que darle este otro permiso
grant create on schema PUBLIC to example_user;
grant create on database example_db to example_user;

1.4.8. secuencias de postgres

https://wiki.postgresql.org/wiki/Fixing_Sequences

1.4.9. Monitorización de Locks

https://wiki.postgresql.org/wiki/Lock_Monitoring

  select relation::regclass, * from pg_locks where not granted;

A veces se quedan colgando procesos y no tienes ni idea de por qué

1.4.10. Locks

https://medium.com/compass-true-north/postgresql-lessons-we-learned-the-hard-way-663ddf666e4

1.4.11. Ver procesos abiertos

  select *
  from pg_stat_activity
  where datname = 'databasename';

https://tableplus.com/blog/2018/08/postgresql-how-to-find-and-kill-hanging-query.html

  SELECT pg_cancel_backend(PID);
  SELECT pg_terminate_backend(PID); -- Mas fuerte

1.4.12. Sacar estadísticas de una columna en postgres

percentile_disc son los percentiles (0.5 es la mediana)

    min(err),
    percentile_disc(0.1) within group(order by err),
    percentile_disc(0.25) within group(order by err),
    percentile_disc(0.5) within group(order by err),
    avg(err),
    percentile_disc(0.75) within group(order by err),
    percentile_disc(0.9) within group(order by err),
    max(err),
    variance(err)

SELECT id, content, 1 - (embedding <=> %s) AS cosine_similarity
               FROM items
               ORDER BY cosine_similarity DESC LIMIT 5

1.6.1. GitHub - pgvector/pgvector: Open-source vector similarity search for Postgres

1.7. aquametalabs/aquameta: Web development platform built entirely in PostgreSQL

1.8. https://livebook.manning.com/book/postgis-in-action-third-edition/chapter-8/153

1.9. https://postgis.net/docs/geometry_overlaps.html

Operadores de PostGIS que te hace un cruce por bounding box para hacer un cruce grueso primero, luego puedes afinar más