How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?
Add a comment
|
3 Answers
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:
create database dump:
pg_dump your_database > your_database.sqlthis will save your database in sql format, in encoding you currently have.
delete database (or rename it):
DROP DATABASE your_databaseif 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;create database with new encoding:
CREATE DATABASE your_database WITH ENCODING 'UNICODE' TEMPLATE=template0;import data from dump created before:
PGCLIENTENCODING=YOUR_OLD_ENCODING psql -f your_database.sql your_databaseyou 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.
6 Comments
daniels
i have a cpanel account and if i backup the database i will be able to restore only the content and not to recreate database with new encoding
rombarcz
drop = delete, here's link to cpanel docs: cpanel.net/support/docs/11/cpanel/databases_delete_post.html
Svish
PostgreSQL newbie here... How can you do this using SQL and/or the psql command-line tool? For example if the database and server is currently set to SQL_ASCII, but I want it to be UTF-8?
rombarcz
newbie or not, you must know your tools, use pg_dump for database dumping, psql -f for importing previously dumped data. delete/create database you can use many different tools, sql (DROP DATABASE/CREATE DATABASE [postgresql.org/docs/9.0/static/sql-createdatabase.html]), phpPgAdmin, pgAdmin (visual tools). READ tool documentation before performing any action.
Sash
Found this link for PostgresSQL archives.postgresql.org/pgsql-novice/2006-03/msg00210.php
|
You can change encoding on the fly without dump/restore:
update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'database_name'
5 Comments
Bobort
Now this is a useful answer. "Create database with new encoding" is an incredibly unhelpful instruction when the very question is "How do I change the encoding on a database?"
rombarcz
Is this documented anywhere in postgres manual? playing with postgresql internals and just updating reference informations may lead to serious damages. If this indeed rewrite all indexes and necessary data it is great! But if not you may end up with restoring from your backups.
Victor
@Bobort, you are right.. but there are some caveats on changing "on the fly" stackoverflow.com/a/5091083/903998.. it appears that the right choice is to start with a fresh db (postgres)
Abhipso Ghosh
It is giving me this error:
-bash: syntax error near unexpected token ('`
aldo
this will break the existing utf-8 text
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