PostgreSQL
COPY, importación y exportación
Carga y descarga masiva con COPY, formatos CSV y texto, permisos, staging, validación y control transaccional para mover grandes volúmenes de datos.
- Última actualización
- Actualizada
- Nivel
- Aplicación
PostgreSQL
Carga y descarga masiva con COPY, formatos CSV y texto, permisos, staging, validación y control transaccional para mover grandes volúmenes de datos.
COPY es el canal de alto rendimiento para mover filas entre PostgreSQL y un stream. Su velocidad no elimina la necesidad de validar formato, constraints, atomicidad, errores y capacidad de recuperación.
INSERT fila por fila
→ muchos round trips y parse/execute repetido
COPY
→ stream continuo de filas
→ menos overhead por registroCOPY del servidor lee o escribe archivos accesibles por el proceso PostgreSQL. \copy de psql usa archivos del cliente y envía el stream por la conexión.
Servidor:
COPY staging_products(sku, name, price)
FROM '/var/lib/postgresql/import/products.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');Cliente:
\copy staging_products(sku,name,price) FROM './products.csv' CSV HEADEREn una aplicación, el driver suele ofrecer COPY protocol mediante streams.
COPY (
SELECT id, sku, name, price
FROM products
WHERE business_id = 10
ORDER BY id
) TO STDOUT WITH (FORMAT csv, HEADER true);No necesitas exportar una tabla completa; puede usarse una query.
Nunca cargues datos externos grandes directamente a tablas centrales sin estrategia:
COPY → staging table
→ validar y normalizar
→ detectar errores/duplicados
→ INSERT/MERGE a tablas finales
→ registrar resultadoEjemplo:
CREATE TEMP TABLE staging_products (
row_number bigint GENERATED ALWAYS AS IDENTITY,
sku text,
name text,
price_text text
) ON COMMIT DROP;Se usan tipos permisivos en staging para conservar filas inválidas y reportarlas. Después:
SELECT row_number, price_text
FROM staging_products
WHERE price_text !~ '^\d+(\.\d{1,2})?$';COPY FROM es un statement: si una fila produce error ordinario, la operación falla y se revierte dentro de la transacción.
Esto protege consistencia, pero una carga enorme puede perder todo el progreso por una fila defectuosa.
Estrategias:
No ignores silenciosamente filas sin auditar.
COPY FROM ejecuta constraints y triggers de fila ordinarios. Consecuencias:
Deshabilitar constraints/triggers requiere privilegios y puede dejar datos inválidos. Prefiere staging y validación.
Define:
Por defecto, un campo vacío y NULL no siempre significan lo mismo. Configura explícitamente:
WITH (FORMAT csv, HEADER true, NULL '', FORCE_NULL (optional_column))La semántica debe probarse con datos reales.
FORMAT binary evita parsing textual y puede ser rápido, pero es menos portable entre tipos/versiones/herramientas. Es útil entre sistemas PostgreSQL compatibles y procesos controlados, no como archivo humano estable.
Si omites una columna, se aplica default. Si incluyes IDs explícitos, la sequence no se ajusta automáticamente. Después de una importación debes sincronizarla cuidadosamente.
Generated columns no deben recibir valores directos.
Factores:
maintenance_work_mem para índices posteriores.Para carga inicial controlada puede ser mejor:
Pero en producción activa necesitas compatibilidad y disponibilidad.
Una staging reconstruible puede ser UNLOGGED:
CREATE UNLOGGED TABLE import_staging (...);Reduce WAL, pero se trunca tras crash y no se replica como tabla normal. Solo úsala cuando el proceso puede reintentarse desde el archivo original.
Existe COPY ... FREEZE en condiciones específicas para tablas recién creadas/truncadas dentro de la transacción. Es una optimización especializada para cargas iniciales; revisa restricciones y versión. No es un flag general para evitar vacuum.
COPY ... FROM '/path' y PROGRAM son operaciones privilegiadas porque acceden al sistema operativo o ejecutan comandos. El runtime role no debe tener esos privilegios.
Usa STDIN/STDOUT desde la aplicación para datos de usuario.
Nunca construyas COPY PROGRAM con input no confiable.
Define una key de importación:
import_batch_id uuid,
source_row_number bigintY constraints para evitar repetir la misma fila. El merge final puede usar ON CONFLICT, pero recuerda que una actualización acumulativa no es idempotente automáticamente.
Un buen import produce:
No respondas solo “import failed”.
Usa streaming y backpressure. No cargues todo el CSV en memoria. Controla:
Una exportación de varias consultas puede usar repeatable read para compartir snapshot.
COPY y \copy.\copy usa archivos del cliente.Roles, ownership y permisos convierte el acceso a la base en un modelo explícito de confianza mínima.