Jak zmienić kodowanie znaków w bazie danych postgres?

Po pierwsze, odpowiedź Daniela jest prawidłową, bezpieczną opcją.

W konkretnym przypadku zmiany z SQL_ASCII na coś innego, możesz oszukać i po prostu szturchnąć katalog pg_database, aby ponownie przypisać kodowanie bazy danych. Zakłada to, że zapisałeś już znaki inne niż ASCII w oczekiwanym kodowaniu (lub po prostu nie użyłeś znaków innych niż ASCII).

Wtedy możesz zrobić:

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

Nie zmieni to zestawienia bazy danych, tylko jak zakodowane bajty są konwertowane na znaki (więc teraz length('£123') zwróci 4 zamiast 5). Jeśli baza danych używa kompilacji "C", nie powinno być żadnych zmian w kolejności dla łańcuchów ASCII. Prawdopodobnie będziesz musiał odbudować wszystkie indeksy zawierające znaki inne niż ASCII.

Caveat emptor. Dumping i przeładowanie pozwala sprawdzić, czy zawartość bazy danych jest rzeczywiście w oczekiwanym kodowaniu, a to nie. a jeśli okaże się, że masz w bazie jakieś źle zakodowane dane, to to będzie trudne. Więc jeśli to możliwe, porzuć i ponownie rozpocznij.

Zrzucenie bazy danych z określonym kodowaniem i próba przywrócenia jej w innej bazie danych z innym kodowaniem może spowodować uszkodzenie danych. Kodowanie danych musi być ustawione przed włożeniem jakichkolwiek danych do bazy danych.

Sprawdź to : podczas kopiowania jakiejkolwiek innej bazy danych, ustawienia kodowania i ustawień regionalnych nie mogą zostać zmienione w stosunku do ustawień źródłowych bazy danych, ponieważ może to spowodować uszkodzenie danych.

I to : niektóre kategorie locale muszą ustalać ich wartości podczas tworzenia bazy danych. Możesz używać różnych ustawień dla różnych baz danych, ale po utworzeniu bazy danych nie możesz już ich zmieniać dla tej bazy danych. LC_COLLATE i LC_CTYPE są tymi kategoriami. wpływają na kolejność sortowania indeksów, więc muszą być stałe, inaczej indeksy w kolumnach tekstowych ulegną uszkodzeniu. (ale możesz złagodzić to ograniczenie za pomocą zestawień, jak omówiono w sekcji 22.2.) wartości domyślne dla kategorie te są określane podczas uruchamiania initdb, a wartości te są używane podczas tworzenia nowych baz danych, chyba że w poleceniu CREATE DATABASE podano inaczej.

Wolałbym odbudować wszystko od początku poprawnie z poprawnym lokalnym kodowaniem na Twoim systemie operacyjnym debian, jak wyjaśniono tutaj :

su root

Zmień ustawienia lokalne:

dpkg-reconfigure locales

Wybierz lokalizację (jak na przykład francuski w Szwajcarii : fr_CH.UTF8)

Odinstaluj i wyczyść poprawnie 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

Ponownie zainstaluj postgresql :

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

Teraz każda nowa baza danych zostanie automatycznie utworzona z poprawnym kodowaniem, LC_TYPE (Klasyfikacja znaków) i LC_COLLATE (kolejność sortowania łańcuchów).

Odpowiedź Daniela Kutika jest poprawna, ale może być jeszcze bezpieczniejsza, z zmiana nazwy bazy danych.

Więc, naprawdę bezpieczny sposób jest:

1. Utwórz nową bazę danych z innym kodowaniem i nazwą
2. zrzuć swoją bazę danych
3. Przywróć zrzut do nowego DB
4. sprawdź, czy aplikacja działa poprawnie z nowym DB
5. Zmień nazwę starego DB na coś znaczącego
6. Zmień nazwę nowego DB
7. Test aplikacji ponownie
8. Drop the old baza danych

W nagłych wypadkach wystarczy zmienić nazwę DBs z powrotem

# 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"