Importación fallida con `could not create unique index`

[Paulus_Schoutsen]

TL;DR: Cometimos un error en una actualización y estamos buscando ayuda

---

Con Home Assistant utilizamos Discourse para impulsar nuestra comunidad. Lo ejecutamos con el método discourse_docker en una instancia EC2 de AWS.

Al ser un proyecto de código abierto, el mantenimiento del foro quedó en el olvido y terminamos con una versión antigua, actualizada por última vez a principios de 2019.

Para empeorar las cosas, en una actualización anterior habíamos fijado Postgres en la versión 9.5 porque no teníamos el espacio en disco necesario para actualizar a Postgres 10. Nunca resolvimos ese problema.

También hicimos un cambio una vez en la plantilla de Cloudflare y lo cometimos al repositorio, lo que impidió que la rama discourse_docker se actualizara a la última versión.

Ayer decidimos proceder con la actualización…

Cuando se estaba migrando la base de datos, nos encontramos con un problema: se utilizó una sintaxis incompatible con la versión 9.5:

== 20200429095034 AddTopicThumbnailInformation: migrating =====================
-- execute("ALTER TABLE posts\nADD COLUMN IF NOT EXISTS image_upload_id bigint\n")

Nos dimos cuenta rápidamente del problema de tener fijada la versión 9.5. Así que decidimos migrar a Postgres 10. Eso no funcionó y recibimos el siguiente error:

I, [2020-06-12T00:30:55.448351 #1]  INFO -- : Upgrading PostgreSQL from version 9.5 to 10
WARNING: Upgrading PostgresSQL would require an addtional 89M of disk space
Please free up some space, or expand your disk, before continuing.

Teníamos 47 GB disponibles, así que eso fue extraño. Luego nos dimos cuenta de que discourse_docker estaba desactualizado, así que lo actualizamos a la última versión. ¡Sorpresa! Acababa de llegar Postgres 12.

Después de ejecutar rebuild nuevamente, esta vez obtuvimos este error:

I, [2020-06-12T00:41:17.378129 #1]  INFO -- : Upgrading PostgreSQL from version 9.5 to 12
WARNING: Upgrading PostgresSQL would require an addtional 92G of disk space
Please free up some space, or expand your disk, before continuing.

Eso es un poco más de espacio, pero vale. Simplemente aumentemos nuestro espacio en disco a 300 GB y ejecutemos de nuevo.

Esta vez, pg_upgrade falló durante la migración:

Restoring database schemas in the new cluster
  template1
  discourse

*failure* Consult the last few lines of "pg_upgrade_dump_16384.log" for the probable cause of the failure. Failure, exiting

Cuando revisamos el archivo pg_upgrade_dump_16384.log, vimos el siguiente error:

pg_restore: creating VIEW "postgres_exporter.pg_stat_activity"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 721; 1259 678554 VIEW pg_stat_activity postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_activity.waiting does not exist
LINE 27:     "pg_stat_activity"."waiting",
             ^
    Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('678556'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('678555'::pg_catalog.oid);


-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('678554'::pg_catalog.oid);

CREATE VIEW "postgres_exporter"."pg_stat_activity" AS
 SELECT "pg_stat_activity"."datid",
    "pg_stat_activity"."datname",
    "pg_stat_activity"."pid",
    "pg_stat_activity"."usesysid",
    "pg_stat_activity"."usename",
    "pg_stat_activity"."application_name",
    "pg_stat_activity"."client_addr",
    "pg_stat_activity"."client_hostname",
    "pg_stat_activity"."client_port",
    "pg_stat_activity"."backend_start",
    "pg_stat_activity"."xact_start",
    "pg_stat_activity"."query_start",
    "pg_stat_activity"."state_change",
    "pg_stat_activity"."waiting",
    "pg_stat_activity"."state",
    "pg_stat_activity"."backend_xid",
    "pg_stat_activity"."backend_xmin",
    "pg_stat_activity"."query"
   FROM "pg_stat_activity";

¡Oh, vaya!

Así que en este punto decidimos dar un par de pasos atrás. ¿Podríamos simplemente poner los foros en funcionamiento nuevamente y dejarlos en modo de solo lectura mientras resolvemos este asunto de las copias de seguridad? Logramos hacerlo corrigiendo algunos problemas de permisos tanto para postgres como para redis, y los foros volvieron a estar en línea con la versión antigua. No todo funciona; por ejemplo, al ir a Administración → Usuarios → Grupos, obtenemos este error:

NoMethodError (undefined method `automatic_membership_retroactive' for #<Group:0x00007fcaca3045e8>)
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activemodel-6.0.1/lib/active_model/attribute_methods.rb:431:in `method_missing'

Pero lo demás parece funcionar.

En este punto, decidimos que, como tuvimos que cambiar los propietarios (chown) para volver a una instancia funcional, deberíamos simplemente iniciar una nueva instancia e importar nuestra copia de seguridad.

Así que iniciamos una nueva instancia EC2, seguimos las instrucciones de inicio de discourse_docker y comenzamos nuestra importación. Luego nos encontramos con un problema extraño: no pudo crear un índice porque los datos no cumplían con los requisitos de unicidad del índice:

ERROR:  could not create unique index "index_incoming_domains_on_name_and_https_and_port"
DETAIL:  Key (name, https, port)=(homeassistant.home, f, 8123) is duplicated.
EXCEPTION: psql failed: DETAIL:  Key (name, https, port)=(homeassistant.home, f, 8123) is duplicated.
/var/www/discourse/lib/backup_restore/database_restorer.rb:95:in `restore_dump'

Pero cuando entramos en la consola de Rails de nuestra instancia en ejecución, no había duplicados:

[7] pry(main)> IncomingDomain.where(name: "homeassistant.home")
=> [#<IncomingDomain:0x000055e5cabc3760 id: 8648, name: "homeassistant.home", https: false, port: 8123>]

Así es como estamos ahora. Y estamos un poco perdidos.

• Tenemos una instancia en funcionamiento con una base de datos defectuosa en comparación con el código Ruby, que no puede migrar a versiones más recientes de Postgres.
• Tenemos una copia de seguridad que no se puede importar en una nueva instancia.

Exploramos la posibilidad de mudarnos a un Discourse alojado de pago, pero dado que tenemos 3 millones de visitas a páginas y un millón de publicaciones, los precios empresariales son un compromiso demasiado grande para nosotros.

Así que necesitamos encontrar una salida; preferiblemente podríamos importar nuestra copia de seguridad, pero también funcionaría migrar nuestra instancia antigua.

¿Alguien tiene alguna idea? No nos importa pagar a alguien para que nos ayude.

[Falco]

Creo que el camino más sencillo para ti es obtener una copia de seguridad funcional e importarla en una nueva instancia, como intentaste la última vez.

Intentemos solucionar los datos duplicados:

# Conéctate por SSH a la máquina
cd /var/discourse
./launcher enter app
su postgres
psql
\connect discourse
SELECT * FROM incoming_domains WHERE name LIKE '%homeassistant.home%';

# Esto debería imprimir varias líneas
# Utiliza sentencias SQL DELETE para solucionarlo
# y sal con \q

¿Puedes probar lo anterior y pedir más ayuda si te quedas atascado?

[Paulus_Schoutsen]

¿Tendría que limpiar también IncomingLink e IncomingReferrer, dado que referrer apunta a IncomingDomain y IncomingLink apunta a IncomingReferrer?

Ejecutando la consulta ahora e intentaré importar otra copia de seguridad. Consultar directamente vía postgres me da resultados diferentes a usar Rails. Pero supongo que podría ser por un default scope.

[robbiet480 (Robbie Trencheny)]

Hemos intentado importar otra copia de seguridad y falló debido a un índice corrupto. Hemos reindexado todos los índices únicos en la instancia original y ahora estamos experimentando este problema con un puñado de usuarios.

Mantendremos informados.

[Paulus_Schoutsen]

¡Bien, lo logramos y ya estamos de nuevo en línea! Gracias por los consejos, @Falco.

Para ayudar a otras personas con sus problemas, aquí les dejamos un desglose de lo que hicimos.

Teníamos varios índices corruptos que provocaron que la importación fallara. Logramos solucionarlo eliminando manualmente los duplicados. También teníamos 8 usuarios con un username_lower duplicado (demasiados “mike” y “marco”). Los renombramos actualizando tanto username como username_lower. A partir de los datos de los usuarios, notamos que la primera corrupción ocurrió en diciembre de 2019.

En lugar de seguir el ciclo de “hacer copia de seguridad” → “restaurar copia de seguridad” → “fallar por duplicados” → “arreglar”, decidimos reindexar todos los índices. Encontramos todos los índices con restricciones de unicidad con la siguiente consulta:

select idx.relname as index_name, 
       insp.nspname as index_schema,
       tbl.relname as table_name,
       tnsp.nspname as table_schema
from pg_index pgi
  join pg_class idx on idx.oid = pgi.indexrelid
  join pg_namespace insp on insp.oid = idx.relnamespace
  join pg_class tbl on tbl.oid = pgi.indrelid
  join pg_namespace tnsp on tnsp.oid = tbl.relnamespace
where pgi.indisunique --<< solo índices únicos
  and tnsp.nspname = 'public'

Una vez que todos los índices funcionaron, pudimos hacer una copia de seguridad e importarla correctamente en la nueva instancia. Las migraciones se ejecutaron como se esperaba, intercambiamos las instancias y volvimos a estar operativos ¡Salud por la resistencia de Discourse!

Gracias de nuevo, @Falco.

Que tengan un buen fin de semana

[Paulus_Schoutsen]

Un último consejo para quienes depuran problemas de corrupción de datos. Inicialmente, cuando nuestra importación falló por datos duplicados, entré a la consola de Rails y busqué los datos que causaron que el índice no se creara.

Sin embargo, al consultar usando los campos indexados, Postgres utilizó el índice dañado para generar los resultados. ¡Así que mi consulta inicial mostró 1 resultado, y más tarde, al eliminar esa entrada, mostró 0 resultados!

Las consultas que realizan escaneos completos de la tabla, ¡eso es lo que hay!