Comment modifier le codage des caractères d'une base de données postgres ?

Question

J'ai une base de données qui a été configurée avec le jeu de caractères par défaut SQL_ASCII. Je veux la faire passer en UNICODE. Existe-t-il un moyen simple de le faire ?

Answer 1

Tout d'abord, la réponse de Daniel est l'option correcte et sûre.

Pour le cas spécifique du passage de SQL_ASCII à autre chose, vous pouvez tricher et simplement consulter le catalogue pg_database pour réaffecter l'encodage de la base de données. Cela suppose que vous avez déjà stocké tous les caractères non-ASCII dans l'encodage attendu (ou que vous n'avez simplement pas utilisé de caractères non-ASCII).

Alors vous pouvez le faire :

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'thedb'

Cela ne changera pas la collation de la base de données, juste la façon dont les octets encodés sont convertis en caractères (donc maintenant length('£123') retournera 4 au lieu de 5). Si la base de données utilise la collation 'C', il ne devrait pas y avoir de changement dans l'ordre des chaînes ASCII. Cependant, vous devrez probablement reconstruire tous les index contenant des caractères non ASCII.

Caveat emptor. Le vidage et le rechargement permettent de vérifier que le contenu de votre base de données est bien dans l'encodage attendu, ce qui n'est pas le cas ici. Et s'il s'avère que vous avez des données mal codées dans la base de données, il sera difficile de les sauver. Donc, si vous le pouvez, videz et réinitialisez.

Answer 2

Pour modifier l'encodage de votre base de données :

1. Videz votre base de données
2. Laissez tomber votre base de données,
3. Créer une nouvelle base de données avec un encodage différent
4. Rechargez vos données.

Assurez-vous que l'encodage du client est correctement défini pendant tout cela.

Source : http://archives.postgresql.org/pgsql-novice/2006-03/msg00210.php

Answer 3

Vider une base de données avec un encodage spécifique et essayer de la restaurer sur une autre base de données avec un encodage différent peut entraîner une corruption des données. L'encodage des données doit être défini AVANT l'insertion de toute donnée dans la base de données.

Vérifiez ce : Lors de la copie de toute autre base de données, les paramètres d'encodage et de paramètres régionaux ne peuvent pas être modifiés par rapport à ceux de la base de données source, car cela pourrait entraîner la corruption des données.

Et ce : Certaines catégories de paramètres locaux doivent avoir leurs valeurs fixées lors de la création de la base de données. Vous pouvez utiliser des paramètres différents pour différentes bases de données, mais une fois qu'une base de données est créée, vous ne pouvez plus les modifier pour cette base de données. LC_COLLATE et LC_CTYPE sont ces catégories. Ils affectent l'ordre de tri des index, ils doivent donc être maintenus fixes, sinon les index sur les colonnes de texte seraient corrompus. ( Mais vous pouvez atténuer cette restriction en utilisant les collations, comme indiqué dans la Section 22.2. ) Les valeurs par défaut de ces catégories sont déterminées lors de l'exécution de initdb, et ces valeurs sont utilisées lors de la création de nouvelles bases de données, sauf indication contraire dans la commande CREATE DATABASE.

---

Je préfèrerais tout reconstruire depuis le début correctement avec un encodage local correct sur votre OS Debian comme expliqué aquí :

su root

Reconfigurez vos paramètres locaux :

dpkg-reconfigure locales

Choisissez votre locale (comme par exemple pour le français en Suisse : fr_CH.UTF8)

Désinstaller et nettoyer correctement postgresql :

apt-get --purge remove postgresql\*
rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres

Réinstaller postgresql :

aptitude install postgresql-9.1 postgresql-contrib-9.1 postgresql-doc-9.1

Maintenant, toute nouvelle base de données sera automatiquement créée avec un encodage correct, LC_TYPE (classification des caractères), et LC_COLLATE (ordre de tri des chaînes de caractères).

Answer 4

# dump into file
pg_dump myDB > /tmp/myDB.sql

# create an empty db with the right encoding (on older versions the escaped single quotes are needed!)
psql -c 'CREATE DATABASE "tempDB" WITH OWNER = "myself" LC_COLLATE = '\''de_DE.utf8'\'' TEMPLATE template0;'

# import in the new DB
psql -d tempDB -1 -f /tmp/myDB.sql

# rename databases
psql -c 'ALTER DATABASE "myDB" RENAME TO "myDB_wrong_encoding";' 
psql -c 'ALTER DATABASE "tempDB" RENAME TO "myDB";'

# see the result
psql myDB -c "SHOW LC_COLLATE"

Answer 5

La réponse de Daniel Kutik est correcte, mais elle peut être encore plus sûre, avec Renommer une base de données .

Donc, le moyen le plus sûr est :

1. Créez une nouvelle base de données avec un encodage et un nom différents.
2. Videz votre base de données
3. Restaurer le dump vers la nouvelle BD
4. Testez que votre application fonctionne correctement avec la nouvelle base de données.
5. Renommer l'ancienne base de données en quelque chose de significatif
6. Renommer la nouvelle BD
7. Testez à nouveau l'application
8. Abandonner l'ancienne base de données

En cas d'urgence, il suffit de renommer les bases de données.