How can I change database encoding for a PostgreSQ

Question

How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?

Answer 1

To expand on the answers given, you can use these commands to accomplish your task.

// Backup the database to outfile
pg_dump dbname > outfile

// Terminate all connections to the database
psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='dbname';"

// Delete the database
psql -c "DROP DATABASE dbname;"

//Re-create the database using the encoding keyword
psql -c "CREATE DATABASE dbname ENCODING='UTF8';"

//Import the saved data
psql -f outfile

Dumping database: http://www.postgresql.org/docs/9.4/static/backup-dump.html

Creating database: http://www.postgresql.org/docs/9.4/static/sql-createdatabase.html

This is a list of all encodings available for version 9.4: http://www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED

Answer 2

In short, you cannot do this with only phpPgAdmin or SQL without risking existing data corruption. You have to export all data, create database with correct encoding and restore exported data.

This is how you should proceed:

1. create database dump:

   pg_dump your_database > your_database.sql

   this will save your database in sql format, in encoding you currently have.

2. delete database (or rename it):

   DROP DATABASE your_database

   if you have enough storage to do it I recommend leaving old database until you make sure everything is OK with new one, rename it:

   ALTER DATABASE your_database RENAME TO your_database_backup;

3. create database with new encoding:

   CREATE DATABASE your_database WITH ENCODING 'UNICODE' TEMPLATE=template0;

4. import data from dump created before:

   PGCLIENTENCODING=YOUR_OLD_ENCODING psql -f your_database.sql your_database

   you need to set psql client encoding to one you had in old database.

Changing encoding on-the-fly isn't possible as it would require rewriting most of internal database data which is almost equal to recreating db way I described.

It is possible to just alter internal postgres informations about database and any new data after this alteration will be saved correctly, however your existing data might get corrupted.

Answer 3

You can change encoding on the fly without dump/restore:

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