tag:blogger.com,1999:blog-3669809752172683097.post8266779593055314972..comments2024-02-08T04:04:28.385-08:00Comments on Cyclopedia Square: Convert a PostgreSQL Database from LATIN1 to UTF8Bryanhttp://www.blogger.com/profile/11394436715172971234noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-3669809752172683097.post-46027879917986946292012-09-06T09:12:48.290-07:002012-09-06T09:12:48.290-07:00You won't lose any data in the database. pg_d...You won't lose any data in the database. pg_dump dumps *everything*. You can examine the file produced by the pg_dump command with a text editor and see what is in there if you aren't sure you trust it. You can (and probably should) also do all this on a test database first, just to make sure.Bryanhttps://www.blogger.com/profile/11394436715172971234noreply@blogger.comtag:blogger.com,1999:blog-3669809752172683097.post-37983587295768177962012-09-06T01:21:43.600-07:002012-09-06T01:21:43.600-07:00Hi guys, I am having a similar problem with a post...Hi guys, I am having a similar problem with a postgresql database which was created with latin1 encoding.<br />I am about to convert the database to utf-8 but I don't want to loose the data already stored in the database.<br />Can I try the following and still have my website intact? Please advice<br />1. pg_dump yourdb > yourdb.psql<br />2. dropdb yourdb<br />3. createdb -E utf-8 yourdb<br />4. psql yourdb < yourdb.psqlAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3669809752172683097.post-18426632256061258822012-06-12T23:42:09.755-07:002012-06-12T23:42:09.755-07:00Thank you for the fixThank you for the fixAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3669809752172683097.post-89103110914893515642009-05-19T04:31:00.000-07:002009-05-19T04:31:00.000-07:00I had the same issue with my DB. Following your in...I had the same issue with my DB. Following your instructions I managed to convert the encoding quickly. <br /><br />In addition I executed the following to convert my htmls to UTF-8: <br /><br />ksh# for i in *<br />> do iconv -f CP1251 -t UTF-8 $i | tee $i<br />> done<br /><br />and<br /><br />ksh# for i in *<br />>do cat $i | sed s/windows-1251/utf-8/ | tee $i<br />>done<br /><br /><br />10x a lotUnknownhttps://www.blogger.com/profile/01090773109208547194noreply@blogger.comtag:blogger.com,1999:blog-3669809752172683097.post-83675176038928892742008-11-04T17:32:00.000-08:002008-11-04T17:32:00.000-08:00Good catch! Fortunately I only made that mistake ...Good catch! Fortunately I only made that mistake in the blog, not on the command line.Bryanhttps://www.blogger.com/profile/11394436715172971234noreply@blogger.comtag:blogger.com,1999:blog-3669809752172683097.post-48469564056369211652008-11-04T03:08:00.000-08:002008-11-04T03:08:00.000-08:00psql -U ... bryan < "CREATE DATABASE bryan...<I><BR/>psql -U ... bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = '<B>UTF-8</B>';"<BR/></I><BR/><BR/>forgot to fix it.. :/Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3669809752172683097.post-1544163088766485902008-11-04T03:07:00.000-08:002008-11-04T03:07:00.000-08:00psql -U ... bryan < "CREATE DATABASE bryan...<I><BR/>psql -U ... bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'LATIN1';"<BR/></I><BR/><BR/>should be <BR/><BR/><I><BR/>psql -U ... bryan < "CREATE DATABASE bryan WITH TEMPLATE = template0 OWNER = bryan ENCODING = 'LATIN1';"<BR/></I><BR/><BR/>Also, you don't need to convert the sql dump to UTF-8. Postgresql knows what encoding it currently working on, so if you restore a latin1 encoded database into a utf-8 database it will automatically do the right thing.<BR/><BR/>In other words, with all clients disconnected:<BR/><BR/>1. pg_dump yourdb > yourdb.sql<BR/>2. dropdb yourdb<BR/>3. createdb -E utf-8 yourdb<BR/>4. psql yourdb < yourdb.sqlAnonymousnoreply@blogger.com