Cambiando la codificación de una base de datos sin perder información

Suele ocurrir que tienes una base de datos con codificación de caracteres en SQL_ASCII, y quieres pasarla a LATIN1 o a UTF-8, pero los datos que tiene la dichosa base de datos se mantienen en la codificación anterior. Eso no es ningún problema, siempre y cuando los datos que se guarden no tengan caracteres distintos al americano (esto es, sin ningún tipo de tilde o caracteres europeos especiales) o se estén guardando como HTML entities (en vez de ñ es &ntilde, por ejemplo) pero, ¿y si la base de datos no se usa únicamente para mantener una web?

Pues bien, es claro que podemos cambiar la codificación de caracteres de una base de datos (o al menos controlarla), mas no podemos hacer lo mismo con los datos. Lo que sí podemos hacer es crear un respaldo de los datos como un archivo de texto ANTES de hacer la conversión, crear una nueva base de datos con la nueva codificación y luego introducir los datos del archivo de texto. Es la secuencia habitual para hacer respaldos, pero aquí hay otro problema, y es que los datos respaldados conservan la codificación original... teniendo un respaldo como texto plano (en forma de consultas SQL), se reduce a un insignificante problema.

Veamos un ejemplo práctico, para este caso empleamos una base de datos ISO-8859-1 (LATIN1) bajo Postgresql 8.2 y vamos a trasladarla como una base de datos UTF-8 en Postgresql 8.3. La base de datos en cuestión solamente tiene una tabla llamada contenido:

Id	Nombre	Descripcion
1	accesorio	Accesorios que pueden dañar seriamente tu equipo
2	elemento	Útiles afinados para un mejor desempeño en el área de Ciencias Básicas
3	blog de contenido	Educación a distancia

Cuando realizamos un pg_dump a un archivo de texto, obtenemos:

pg_dump.exe -i -h localhost -p 5432 -U postgres -F p -C -v -f "C:\Users\raulerne\Desktop\dump_ejemplo.sql" prueba

-- Contenido de dump_ejemplo.sql

--
-- PostgreSQL database dump
--

-- Started on 2011-03-14 16:59:30

SET client_encoding = 'LATIN1';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1645 (class 1262 OID 38129)
-- Name: prueba; Type: DATABASE; Schema: -; Owner: raulerne
--

CREATE DATABASE prueba WITH TEMPLATE = template0 ENCODING = 'LATIN1';


ALTER DATABASE prueba OWNER TO raulerne;

\connect prueba

SET client_encoding = 'LATIN1';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1646 (class 0 OID 0)
-- Dependencies: 5
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- TOC entry 301 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


SET search_path = public, pg_catalog;

--
-- TOC entry 288 (class 1247 OID 16428)
-- Dependencies: 1306
-- Name: dblink_pkey_results; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE dblink_pkey_results AS (
"position" integer,
colname text
);


ALTER TYPE public.dblink_pkey_results OWNER TO postgres;

--
-- TOC entry 35 (class 1255 OID 16419)
-- Dependencies: 5
-- Name: dblink(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink(text, text) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_record'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink(text, text) OWNER TO postgres;

--
-- TOC entry 36 (class 1255 OID 16420)
-- Dependencies: 5
-- Name: dblink(text, text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink(text, text, boolean) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_record'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink(text, text, boolean) OWNER TO postgres;

--
-- TOC entry 37 (class 1255 OID 16421)
-- Dependencies: 5
-- Name: dblink(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink(text) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_record'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink(text) OWNER TO postgres;

--
-- TOC entry 38 (class 1255 OID 16422)
-- Dependencies: 5
-- Name: dblink(text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink(text, boolean) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_record'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink(text, boolean) OWNER TO postgres;

--
-- TOC entry 45 (class 1255 OID 16431)
-- Dependencies: 5
-- Name: dblink_build_sql_delete(text, int2vector, integer, text[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_build_sql_delete(text, int2vector, integer, text[]) RETURNS text
AS '$libdir/dblink', 'dblink_build_sql_delete'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_build_sql_delete(text, int2vector, integer, text[]) OWNER TO postgres;

--
-- TOC entry 44 (class 1255 OID 16430)
-- Dependencies: 5
-- Name: dblink_build_sql_insert(text, int2vector, integer, text[], text[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_build_sql_insert(text, int2vector, integer, text[], text[]) RETURNS text
AS '$libdir/dblink', 'dblink_build_sql_insert'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_build_sql_insert(text, int2vector, integer, text[], text[]) OWNER TO postgres;

--
-- TOC entry 46 (class 1255 OID 16432)
-- Dependencies: 5
-- Name: dblink_build_sql_update(text, int2vector, integer, text[], text[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_build_sql_update(text, int2vector, integer, text[], text[]) RETURNS text
AS '$libdir/dblink', 'dblink_build_sql_update'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_build_sql_update(text, int2vector, integer, text[], text[]) OWNER TO postgres;

--
-- TOC entry 53 (class 1255 OID 16439)
-- Dependencies: 5
-- Name: dblink_cancel_query(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_cancel_query(text) RETURNS text
AS '$libdir/dblink', 'dblink_cancel_query'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_cancel_query(text) OWNER TO postgres;

--
-- TOC entry 30 (class 1255 OID 16415)
-- Dependencies: 5
-- Name: dblink_close(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_close(text) RETURNS text
AS '$libdir/dblink', 'dblink_close'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_close(text) OWNER TO postgres;

--
-- TOC entry 32 (class 1255 OID 16416)
-- Dependencies: 5
-- Name: dblink_close(text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_close(text, boolean) RETURNS text
AS '$libdir/dblink', 'dblink_close'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_close(text, boolean) OWNER TO postgres;

--
-- TOC entry 33 (class 1255 OID 16417)
-- Dependencies: 5
-- Name: dblink_close(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_close(text, text) RETURNS text
AS '$libdir/dblink', 'dblink_close'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_close(text, text) OWNER TO postgres;

--
-- TOC entry 34 (class 1255 OID 16418)
-- Dependencies: 5
-- Name: dblink_close(text, text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_close(text, text, boolean) RETURNS text
AS '$libdir/dblink', 'dblink_close'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_close(text, text, boolean) OWNER TO postgres;

--
-- TOC entry 18 (class 1255 OID 16403)
-- Dependencies: 5
-- Name: dblink_connect(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_connect(text) RETURNS text
AS '$libdir/dblink', 'dblink_connect'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_connect(text) OWNER TO postgres;

--
-- TOC entry 19 (class 1255 OID 16404)
-- Dependencies: 5
-- Name: dblink_connect(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_connect(text, text) RETURNS text
AS '$libdir/dblink', 'dblink_connect'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_connect(text, text) OWNER TO postgres;

--
-- TOC entry 47 (class 1255 OID 16433)
-- Dependencies: 5
-- Name: dblink_current_query(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_current_query() RETURNS text
AS '$libdir/dblink', 'dblink_current_query'
LANGUAGE c;


ALTER FUNCTION public.dblink_current_query() OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 16405)
-- Dependencies: 5
-- Name: dblink_disconnect(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_disconnect() RETURNS text
AS '$libdir/dblink', 'dblink_disconnect'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_disconnect() OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 16406)
-- Dependencies: 5
-- Name: dblink_disconnect(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_disconnect(text) RETURNS text
AS '$libdir/dblink', 'dblink_disconnect'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_disconnect(text) OWNER TO postgres;

--
-- TOC entry 54 (class 1255 OID 16440)
-- Dependencies: 5
-- Name: dblink_error_message(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_error_message(text) RETURNS text
AS '$libdir/dblink', 'dblink_error_message'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_error_message(text) OWNER TO postgres;

--
-- TOC entry 39 (class 1255 OID 16423)
-- Dependencies: 5
-- Name: dblink_exec(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_exec(text, text) RETURNS text
AS '$libdir/dblink', 'dblink_exec'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_exec(text, text) OWNER TO postgres;

--
-- TOC entry 40 (class 1255 OID 16424)
-- Dependencies: 5
-- Name: dblink_exec(text, text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_exec(text, text, boolean) RETURNS text
AS '$libdir/dblink', 'dblink_exec'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_exec(text, text, boolean) OWNER TO postgres;

--
-- TOC entry 41 (class 1255 OID 16425)
-- Dependencies: 5
-- Name: dblink_exec(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_exec(text) RETURNS text
AS '$libdir/dblink', 'dblink_exec'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_exec(text) OWNER TO postgres;

--
-- TOC entry 42 (class 1255 OID 16426)
-- Dependencies: 5
-- Name: dblink_exec(text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_exec(text, boolean) RETURNS text
AS '$libdir/dblink', 'dblink_exec'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_exec(text, boolean) OWNER TO postgres;

--
-- TOC entry 26 (class 1255 OID 16411)
-- Dependencies: 5
-- Name: dblink_fetch(text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_fetch(text, integer) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_fetch'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_fetch(text, integer) OWNER TO postgres;

--
-- TOC entry 27 (class 1255 OID 16412)
-- Dependencies: 5
-- Name: dblink_fetch(text, integer, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_fetch(text, integer, boolean) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_fetch'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_fetch(text, integer, boolean) OWNER TO postgres;

--
-- TOC entry 28 (class 1255 OID 16413)
-- Dependencies: 5
-- Name: dblink_fetch(text, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_fetch(text, text, integer) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_fetch'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_fetch(text, text, integer) OWNER TO postgres;

--
-- TOC entry 29 (class 1255 OID 16414)
-- Dependencies: 5
-- Name: dblink_fetch(text, text, integer, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_fetch(text, text, integer, boolean) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_fetch'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_fetch(text, text, integer, boolean) OWNER TO postgres;

--
-- TOC entry 52 (class 1255 OID 16438)
-- Dependencies: 5
-- Name: dblink_get_connections(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_get_connections() RETURNS text[]
AS '$libdir/dblink', 'dblink_get_connections'
LANGUAGE c;


ALTER FUNCTION public.dblink_get_connections() OWNER TO postgres;

--
-- TOC entry 43 (class 1255 OID 16429)
-- Dependencies: 288 5
-- Name: dblink_get_pkey(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_get_pkey(text) RETURNS SETOF dblink_pkey_results
AS '$libdir/dblink', 'dblink_get_pkey'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_get_pkey(text) OWNER TO postgres;

--
-- TOC entry 50 (class 1255 OID 16436)
-- Dependencies: 5
-- Name: dblink_get_result(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_get_result(text) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_get_result(text) OWNER TO postgres;

--
-- TOC entry 51 (class 1255 OID 16437)
-- Dependencies: 5
-- Name: dblink_get_result(text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_get_result(text, boolean) RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_get_result(text, boolean) OWNER TO postgres;

--
-- TOC entry 49 (class 1255 OID 16435)
-- Dependencies: 5
-- Name: dblink_is_busy(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_is_busy(text) RETURNS integer
AS '$libdir/dblink', 'dblink_is_busy'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_is_busy(text) OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 16407)
-- Dependencies: 5
-- Name: dblink_open(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_open(text, text) RETURNS text
AS '$libdir/dblink', 'dblink_open'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_open(text, text) OWNER TO postgres;

--
-- TOC entry 23 (class 1255 OID 16408)
-- Dependencies: 5
-- Name: dblink_open(text, text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_open(text, text, boolean) RETURNS text
AS '$libdir/dblink', 'dblink_open'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_open(text, text, boolean) OWNER TO postgres;

--
-- TOC entry 24 (class 1255 OID 16409)
-- Dependencies: 5
-- Name: dblink_open(text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_open(text, text, text) RETURNS text
AS '$libdir/dblink', 'dblink_open'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_open(text, text, text) OWNER TO postgres;

--
-- TOC entry 25 (class 1255 OID 16410)
-- Dependencies: 5
-- Name: dblink_open(text, text, text, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_open(text, text, text, boolean) RETURNS text
AS '$libdir/dblink', 'dblink_open'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_open(text, text, text, boolean) OWNER TO postgres;

--
-- TOC entry 48 (class 1255 OID 16434)
-- Dependencies: 5
-- Name: dblink_send_query(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dblink_send_query(text, text) RETURNS integer
AS '$libdir/dblink', 'dblink_send_query'
LANGUAGE c STRICT;


ALTER FUNCTION public.dblink_send_query(text, text) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1308 (class 1259 OID 38132)
-- Dependencies: 5
-- Name: contenido; Type: TABLE; Schema: public; Owner: raulerne; Tablespace:
--

CREATE TABLE contenido (
id integer NOT NULL,
nombre character varying(30) NOT NULL,
descripcion text
);


ALTER TABLE public.contenido OWNER TO raulerne;

--
-- TOC entry 1307 (class 1259 OID 38130)
-- Dependencies: 1308 5
-- Name: contenido_id_seq; Type: SEQUENCE; Schema: public; Owner: raulerne
--

CREATE SEQUENCE contenido_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE public.contenido_id_seq OWNER TO raulerne;

--
-- TOC entry 1648 (class 0 OID 0)
-- Dependencies: 1307
-- Name: contenido_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: raulerne
--

ALTER SEQUENCE contenido_id_seq OWNED BY contenido.id;


--
-- TOC entry 1649 (class 0 OID 0)
-- Dependencies: 1307
-- Name: contenido_id_seq; Type: SEQUENCE SET; Schema: public; Owner: raulerne
--

SELECT pg_catalog.setval('contenido_id_seq', 3, true);


--
-- TOC entry 1639 (class 2604 OID 38134)
-- Dependencies: 1308 1307 1308
-- Name: id; Type: DEFAULT; Schema: public; Owner: raulerne
--

ALTER TABLE contenido ALTER COLUMN id SET DEFAULT nextval('contenido_id_seq'::regclass);


--
-- TOC entry 1642 (class 0 OID 38132)
-- Dependencies: 1308
-- Data for Name: contenido; Type: TABLE DATA; Schema: public; Owner: raulerne
--

COPY contenido (id, nombre, descripcion) FROM stdin;
1 accesorio Accesorios que pueden dañar seriamente tu equipo
2 elemento Útiles afinados para un mejor desempeño en el área de Ciencias Básicas
3 blog de contenido Educación a distancia
\.


--
-- TOC entry 1641 (class 2606 OID 38139)
-- Dependencies: 1308 1308
-- Name: contenido_pkey; Type: CONSTRAINT; Schema: public; Owner: raulerne; Tablespace:
--

ALTER TABLE ONLY contenido
ADD CONSTRAINT contenido_pkey PRIMARY KEY (id);


--
-- TOC entry 1647 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-03-14 16:59:30

--
-- PostgreSQL database dump complete
--

Bastante extenso, ¿verdad?

El truco está en cambiar el encoding del cliente (la línea que aparece con SET client_encoding) y de la base de datos (las líneas con CREATE DATABASE), y pasar el archivo a un programa o script que sea capaz de convertir caracteres de una codificación a otra. Si trabajas en GNU/Linux, lo puedes hacer con iconv, también puedes encontrar varios buscando en Google; aquí te comparto uno hecho en PHP, creado específicamente para pasar un archivo de ISO-8859-1 (LATIN1 o SQL_ASCII) a UTF-8:

$nombre_archivo_latin1 = $_SERVER['argv'][1];
$nombre_archivo_utf8 = $_SERVER['argv'][1] . '.converted';

$contenido_archivo_latin1 = '';
$contenido_archivo_utf8 = '';

if(file_exists($nombre_archivo_latin1))
{
  $filestream_latin1 = fopen($nombre_archivo_latin1, 'r');
  $contenido_archivo_latin1 = fread($filestream_latin1, filesize($nombre_archivo_latin1));
  $contenido_archivo_utf8 = iconv('ISO-8859-1', 'UTF8', $contenido_archivo_latin1);
  $filestream_utf8 = fopen($nombre_archivo_utf8, 'w');
  $status = fputs($filestream_utf8, $contenido_archivo_utf8);
  if($status)
  {
        echo 'Hecho :)', PHP_EOL;
  }
  else
  {
        fwrite(STDERR, 'No se pudo generar el archivo con codificacion UTF-8
');
  }
}
else
{
  fwrite(STDERR, 'El archivo especificado no existe
');
}
?>

El script funciona únicamente con el cliente de PHP. En la consola, escribe php nombre_script.php nombre_dump.sql; se creará un archivo nombre_dump.sql.converted, que es el mismo respaldo que hicimos con pg_dump, solo que ahora tiene la codificación UTF-8 que necesitábamos. Solo nos resta restaurar el nuevo respaldo en la base de datos en Postgresql 8.3 con psql -U nombre_usuario -d base_de_datos_utf8 < nombre_dump.sql.converted y ya hemos restaurado nuestra base de datos correctamente.

Espero que les sirva de ayuda algún día; los comentarios son bienvenidos.