# PostgreSQL Las dos maneras de llegar a PostgreSQL, sentencias INSERT que se pasan a psql y un destino que conecta, el esquema que declaran los dos y por qué la cláusula de conflicto actualiza en vez de no hacer nada. Source: https://docs.jmrp.io/ghchronicle/es/sinks/postgres/ Dos destinos llevan los puntos fechados de ghchronicle a PostgreSQL: uno escribe SQL en un fichero para cargarlo después, el otro conecta e inserta. ```yaml sinks: sql: dialect: postgres path: /var/lib/ghchronicle/points.sql max_bytes: 67108864 keep: 5 ``` Sentencias INSERT en el dialecto de PostgreSQL, escritas a un fichero rotatorio o, con `path: "-"`, a la salida estándar. ```sh ghchronicle -config config.yaml -once | psql "$DATABASE_URL" ``` Esta es una de las dos maneras de llegar a PostgreSQL, y la otra se conecta: ```yaml sinks: postgres: dsn: ${DATABASE_URL} batch: 1000 ``` `sinks.postgres` escribe las mismas tablas en una base de datos que está corriendo, en una ida y vuelta por lote, con los valores enviados como parámetros en vez de renderizados dentro de la sentencia. Nada más cambia: el esquema de abajo es el que escriben las dos, a propósito, porque los dashboards que publica este proyecto consultan esas tablas y una segunda forma convertiría a uno de los dos en una mentira. Cuál quieres es una pregunta sobre cuándo ocurre la carga. El fichero es la respuesta cuando la base está donde esto no llega, cuando las sentencias se van a leer o a retener antes de ejecutarse, o cuando quien las lee no es PostgreSQL: son SQL corriente y otro motor puede tomarlas. La conexión es la respuesta cuando la base está ahí mismo, y te ahorra el temporizador que mete un fichero por psql. Hay una diferencia más, y está en Grafana y no aquí. El sink que conecta sabe cuál es el servidor porque lo marca, así que `-publish-dashboard` puede construir el datasource a partir del DSN; el de fichero no puede, porque nunca se conecta y en su configuración no hay host, ni puerto, ni usuario. ## El esquema es el contrato - Una tabla por medida, con su nombre: `gh_repo`, `gh_traffic`, `gh_workflow_run`. - `time TIMESTAMPTZ NOT NULL`, la fecha en que ocurrió la cosa. - Una columna `TEXT NOT NULL DEFAULT ''` por etiqueta, con cadena vacía donde el punto no tenía valor. - Una columna por campo, tipada a partir del valor: `BIGINT` para un entero, `DOUBLE PRECISION` para un flotante, `BOOLEAN`, `TEXT`, y `TIMESTAMPTZ` para un campo que es en sí una hora. - `PRIMARY KEY (time, )`. - Todo identificador va entre comillas dobles, porque `user`, `type` y `state` son aquí nombres de etiqueta y allí palabras reservadas. Esa clave primaria es la clave de serie de InfluxDB escrita como restricción, y es lo que hace que una reescritura de la ventana de tráfico de catorce días converja en vez de acumular. ```sql CREATE TABLE IF NOT EXISTS "gh_traffic" ("time" TIMESTAMPTZ NOT NULL, "full_name" TEXT NOT NULL DEFAULT '', "kind" TEXT NOT NULL DEFAULT '', "owner" TEXT NOT NULL DEFAULT '', "repo" TEXT NOT NULL DEFAULT '', PRIMARY KEY ("time", "full_name", "kind", "owner", "repo")); ALTER TABLE "gh_traffic" ADD COLUMN IF NOT EXISTS "count" BIGINT; ALTER TABLE "gh_traffic" ADD COLUMN IF NOT EXISTS "uniques" BIGINT; ALTER TABLE "gh_traffic" ADD COLUMN IF NOT EXISTS "url" TEXT; INSERT INTO "gh_traffic" ("time", "full_name", "kind", "owner", "repo", "count", "uniques", "url") VALUES ('2026-09-07T00:00:00Z'::timestamptz, 'acme/telemetry', 'views', 'acme', 'telemetry', 41, 12, 'https://github.com/acme/telemetry/graphs/traffic') ON CONFLICT ("time", "full_name", "kind", "owner", "repo") DO UPDATE SET "count" = EXCLUDED."count", "uniques" = EXCLUDED."uniques", "url" = EXCLUDED."url"; ``` > **DO UPDATE, no DO NOTHING** > > La fila de tráfico de hoy se reescribe con un recuento mayor en cada pasada. > Una fila congelada en su primer valor sería justo el error que todo el diseño > del punto fechado existe para evitar. ## Cómo llegan las declaraciones El `CREATE TABLE IF NOT EXISTS` se emite la primera vez que se ve una medida en un fichero, o en el proceso de un sink que conecta, con el tiempo y las etiquetas de las que está hecha la clave. Cada campo de la unión que lleva ese lote lo sigue como `ALTER TABLE ... ADD COLUMN IF NOT EXISTS`, y lo mismo un campo que aparece en un lote posterior. Los campos nunca van en el `CREATE TABLE`, porque esa sentencia no le hace nada a una tabla que creó una versión anterior: un campo que esa versión no escribía llegaría al `INSERT` sin columna, y PostgreSQL rechaza la sentencia y el lote que la rodea. Añadir la columna de un campo si falta significa lo mismo para una tabla nueva y para una antigua. Un fichero rotado empieza otra vez sus declaraciones, así que cualquier fichero se puede reproducir por su cuenta. Así que un fichero reproducido en una base de datos que ya tiene sus tablas hace que psql imprima un aviso por cada declaración que encuentra su trabajo hecho, uno por el `CREATE TABLE` y uno por cada columna que la tabla ya tiene: ```text NOTICE: relation "gh_traffic" already exists, skipping NOTICE: column "count" of relation "gh_traffic" already exists, skipping ``` Esos son de esperar. Una línea `ERROR` no lo es. El sink que conecta no envía todos esos `ALTER TABLE`. Pregunta al catálogo qué columnas tiene ya una tabla la primera vez que su proceso se encuentra con ella, y añade solo las que le faltan. PostgreSQL toma el bloqueo exclusivo de un `ALTER TABLE` antes de comprobar `IF NOT EXISTS`, así que uno por campo en cada arranque esperaba a cada consulta de Grafana que leía la tabla y retenía todas las que llegaban detrás. **Medido contra PostgreSQL 18.6** con un lector que mantenía una tabla abierta: `CREATE TABLE IF NOT EXISTS` respondió en menos de un milisegundo, mientras que un `ADD COLUMN IF NOT EXISTS` de una columna que ya existía esperó hasta que un `lock_timeout` de un segundo lo rechazó, y un `SELECT` detrás de él esperó tres segundos. Una sentencia que el servidor rechaza se envía otra vez en la escritura siguiente en vez de darse por hecha. Una etiqueta es otra cosa, porque forma parte de la clave. Una vista por primera vez después de declarar la tabla, en el mismo fichero o en el mismo proceso, no puede unirse a la clave primaria sin reescribirla, así que se convierte en una columna normal. Eso solo ocurre cuando un colector cambia su conjunto de etiquetas entre pasadas. Una tabla que creó una versión anterior es otro asunto, y una versión que cambia las etiquetas de una medida cambia lo que se carga en ella. **Medido contra PostgreSQL 18.6** el 2026-09-27: - Una etiqueta que añade una versión posterior no recibe columna de ninguno de los dos destinos, así que cada `INSERT` de esa medida se rechaza con `column ... does not exist`, y en el destino que conecta, con él las demás filas del mismo lote. - Una etiqueta que una versión posterior deja de escribir, como 2.6.1 dejó de escribir `is_answer` en `gh_discussion_comment`, deja el `ON CONFLICT` del fichero nombrando una clave que la tabla no tiene. psql rechaza cada una de esas sentencias con "there is no unique or exclusion constraint matching the ON CONFLICT specification", y el resto del fichero se carga. El destino que conecta lee del catálogo la clave propia de la tabla y choca contra esa, así que sigue escribiendo, y sus filas nuevas quedan junto a las viejas con la cadena vacía en la columna que ya no se escribe, las dos formas que guarda también InfluxDB. La salida limpia de cualquiera de los dos casos es borrar la tabla y dejar que las pasadas la vuelvan a llenar, y un relleno histórico lo anterior, que es lo que hace `-migrate -yes` con los cambios que el binario conoce: mira más abajo. Un colector con el destino que conecta sigue escribiendo tras el borrado. Su primera escritura después se rechaza con `relation ... does not exist`, porque el destino declara una tabla una vez por proceso; entonces olvida las tablas de ese lote, las declara de nuevo y reenvía el lote una vez, lo que crea la tabla de cero. **Medido contra PostgreSQL 18.6**: antes de esto, todas las escrituras siguientes de la tabla, y el resto de su lote, se rechazaban hasta reiniciar. [Cómo leer las tablas](https://docs.jmrp.io/ghchronicle/es/collectors/measurements/#cómo-leer-las-tablas) enseña cómo reconstruir una clave a mano. ## Qué hace aquí una migración Cuando una versión cambia aquello por lo que se identifican las filas de una medida, [`-migrate`](https://docs.jmrp.io/ghchronicle/es/install/upgrading/#migraciones) pregunta a la base de datos del destino que conecta si alguna fila tiene un valor en la columna de la etiqueta antigua, en el esquema en el que escribe el destino, y aplicar el cambio renombra esa única tabla en ese esquema, lo que conserva todas sus filas, índices y claves: ```sql ALTER TABLE ""."gh_discussion_comment" RENAME TO "gh_discussion_comment-20261001T091004"; ``` El nombre es la medida, un guion y el instante en UTC, así que hay que ponerlo entre comillas. El renombrado toma el mismo bloqueo exclusivo que un borrado, que espera detrás de cada consulta de Grafana que lee la tabla, así que se envía con un `lock_timeout` de 5 segundos, tres veces; una tabla que sigue ocupada más tiempo deja el cambio pendiente con la razón del bloqueo. La siguiente escritura del destino crea la tabla con el nombre antiguo y la clave nueva, cuyo índice toma el nombre `gh_discussion_comment_pkey1` junto al de la copia. **Medido contra PostgreSQL 18.6**: el renombrado tardó 7 ms, no tocó ningún otro esquema, y un destino que había escrito la tabla antes siguió escribiendo después. ghchronicle borra la copia cuando lleva 24 horas guardada: tras una pasada del servicio, en el siguiente arranque de cualquier ejecución o en el siguiente `-migrate -yes`, cada uno con su propio `lock_timeout`. `-uninstall data` la lista con las demás tablas `gh_`. Hasta entonces, deshacer el cambio son dos sentencias: ```sql DROP TABLE "gh_discussion_comment"; ALTER TABLE "gh_discussion_comment-20261001T091004" RENAME TO "gh_discussion_comment"; ``` Al fichero SQL no se le puede preguntar, así que decide el registro del fichero de estado de la versión que lo escribió primero, y aplicar el cambio escribe el borrado en el fichero, después de todo lo que ya tiene: ```sql DROP TABLE IF EXISTS "gh_discussion_comment"; ``` Después el destino olvida la tabla, así que las siguientes filas de esa medida la declaran de nuevo tras el borrado. Reproducido en orden, la base de datos pierde la tabla con la forma antigua y gana la de la forma nueva. **Medido con psql contra PostgreSQL 18.6**: sin el borrado, las filas nuevas se rechazan contra la clave antigua; con él, el fichero carga entero. El borrado llega a donde se reproduzca el fichero, donde no se aparta nada, así que un arranque nunca lo aplica por su cuenta; `-migrate -yes` sí. Un fichero reproducido solo, sin el que lleva el borrado, sigue encontrando la tabla antigua, y una rotación que borra ese fichero antes de reproducirlo se lleva el borrado con él. Con `path: "-"` el borrado y las filas releídas van a la salida estándar, y `-migrate -yes` imprime su plan y su informe en la salida de errores, así que la salida estándar es solo el SQL y admite la misma tubería que `-once`: ```sh ghchronicle -config config.yaml -migrate -yes | psql "$DATABASE_URL" ``` **Medido con psql contra PostgreSQL 18.6**: por esa tubería, la tabla que hizo la 2.6.0 se borró y se volvió a crear con la clave nueva, con los comentarios releídos. Antes, el plan iba primero por el mismo flujo, psql leía su primera línea como el comienzo de una sentencia y perdía el borrado con ella, y el destino conservaba la tabla antigua mientras el fichero de estado registraba el cambio como aplicado. Ejecutado sin la tubería, el SQL va a la terminal y a ningún otro sitio, y el cambio igualmente se registra como aplicado. ## TimescaleDB Convierte cada tabla en hypertable una vez exista. La clave primaria ya incluye `time`, que es la única condición que le pone TimescaleDB. ```sql SELECT create_hypertable('gh_traffic', 'time', if_not_exists => TRUE); SELECT create_hypertable('gh_workflow_run', 'time', if_not_exists => TRUE); ``` Nada del dashboard cambia. ## Cómo montarlo 1. Elige el destino. El que conecta necesita `sinks.postgres.dsn` y una base de datos a la que esta máquina llegue, y declara sus tablas en su primera escritura. El de fichero necesita `sinks.sql.path`, un fichero o `-` para la salida estándar. 2. Con el destino de fichero, carga lo que escribió. El que conecta no tiene nada que cargar. ```sh psql "$DATABASE_URL" -f /var/lib/ghchronicle/points.sql ``` O, para la disposición en flujo, ejecuta el colector con `-once` desde un planificador y encadénalo directamente. 3. Apunta el datasource de PostgreSQL de Grafana a la base de datos e importa `ghchronicle-postgres.json`, o, con el destino que conecta, deja que `-publish-dashboard` cree el datasource a partir del DSN y publique el dashboard. ```sql SELECT time, "count" FROM gh_traffic WHERE kind = 'views' AND repo = $repo ``` `dashboards/ghchronicle-postgres.json` tiene los mismos 154 paneles que el de InfluxDB, con cada consulta traducida a PostgreSQL contra este esquema. La traducción compara el texto por sus bytes, como InfluxDB: cada columna de texto por la que una consulta ordena, o de la que toma el menor o el mayor valor, lleva `COLLATE "C"`. Si se deja a la base de datos, el orden es el de su intercalación, y en un PostgreSQL construido sobre glibc cuya base se creó con una configuración regional como `en_US.UTF-8` esa intercalación deja a un lado mayúsculas y signos de puntuación en su primera pasada. **Medido contra PostgreSQL 18.6 en Debian**, cuya base se crea como `en_US.utf8`: antes de esto, "(ghost)" iba después de "alice" y "VALID" después de "unsigned", y tres paneles listaban sus filas en otro orden que el dashboard de InfluxDB. La imagen Alpine coincidía solo porque musl compara bytes se llame como se llame la configuración regional. En la base de datos no cambia nada: sus columnas conservan la intercalación con que se crearon, y solo las consultas del dashboard dicen cómo comparar. ## Por dónde seguir - [Elegir almacén](https://docs.jmrp.io/ghchronicle/es/sinks/) compara PostgreSQL con los demás, y lleva el registro de escrituras que todos comparten. - [Los dashboards](https://docs.jmrp.io/ghchronicle/es/dashboards/) dice cuál de los cinco se dibuja contra cada almacén, y en qué se convierte un panel que un almacén no puede responder.