Databases
StackOverflow2013
Postsdbo
POHeroku PG Dump Export and PSQL Import -> Double Encoding of Extended ASCII Characters
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHeroku PG Dump Export and PSQL Import -> Double Encoding of Extended ASCII Characters
    primarykey
    data
    text
    <p>I've inherited a Heroku Rails application, which I am attempting to migrate into my dev environment.</p> <p>Heroku is running PG 9.1.9, Local is running PG 9.2.4. Rails 3.2.11, pg gem '0.13.2', Local OS Debian 7.</p> <p>Heroku database info:</p> <pre><code>$psql -l "dbname=abc... user=aaa etc" Name | Owner | Encoding | Collate | Ctype | Access ---------------+----------+-------------+-------------+-------------+----------------------- abcdef | abcdef | UTF8 | en_US.UTF-8 | en_US.UTF-8 | abcdef=CTc/abcdef </code></pre> <p>AFAIK, the production PG database uses UTF8 encoding.</p> <p>Exporting from Heroku:</p> <pre><code>pg_dump -C "dbname=abc... user=aaa etc" &gt; dump_plain.sql </code></pre> <p>I used the <strong>pg_dump -C</strong> option to ensure that the database is re-created based off the dump file, to avoid encoding issues if I were to create the target database by hand.</p> <p>The Heroku database</p> <pre><code>$head dump_plain.sql -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; </code></pre> <p>I then edit dump_plain.sql to change Database Name and Owner then import via:</p> <pre><code>psql &lt; dump_plain.sql </code></pre> <p>No errors reported.</p> <pre><code>$psql -l Name | Owner | Encoding | Collate | Ctype | Access ----------------+----------+----------+-------------+-------------+----------------------- imported_d | only_me | UTF8 | en_US.UTF-8 | en_US.UTF-8 | </code></pre> <p>PSQL reports that the encoding for both database is the same and Rail's database.yml, AFAIK, should be using the same encoding.</p> <p>Rails database.yml:</p> <pre><code>development: adapter: postgresql database: stm_test encoding: utf8 </code></pre> <p>For some reason, extended ASCII characters such as ellipses (...), are doubly encoded when running the Rails3 webapp off the freshly imported database.</p> <p>Screenshots to illustrate:</p> <p><strong>Double Encoding on Local Dev</strong> <img src="https://i.stack.imgur.com/iAQbR.jpg" alt="Double Encoding"></p> <p><strong>Correct Encoding on Heroku</strong> <img src="https://i.stack.imgur.com/1FJj5.jpg" alt="Shows Correctly on Production"></p> <p>This data is stored as a TEXT column:</p> <pre><code>CREATE TABLE templates ( id integer NOT NULL, screen_writer text, &lt;---- stored here action_list text ); </code></pre> <p>psql select snippet on LOCAL</p> <pre><code>label: "\"\xE2\x80\xA6I was proud of having\xE2\x80\xA6\"" </code></pre> <p>psql select snippet on Heroku (via heroku pg:psql > select ....)</p> <pre><code>label: "\"\xE2\x80\xA6I was proud of having\xE2\x80\xA6\"" </code></pre> <p>Viewing a json snippet:</p> <pre><code>production: "label":"\"\u2026I was proud of having\u2026\"" local: "label":"\"\u00e2\u0080\u00a6I was proud of having\u00e2\u0080\u00a6\"" </code></pre> <p>Note the additional \u00. Is the issue is related to the server's postgres client encoding settings?</p> <p>LOCALE settings on Local dev match Heroku's:</p> <pre><code>$locale LANG=en_US.UTF-8 LANGUAGE= LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL= </code></pre> <p>I feel I am missing a simple step and would be eternally grateful if anyone could nudge me in the right direction.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNazar
    1. USNazar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHeroku PG Dump Export and PSQL Import -> Double Encoding of Extended ASCII Characters
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you sure that web pages are different? I am wondering if your web server (I suppose you have two: one remote and one local) is putting the correct charset on the http response? If you save both pages, are them different on disk?
      singulars
      1. POHeroku PG Dump Export and PSQL Import -> Double Encoding of Extended ASCII Characters
      1. USeppesuig
    2. COHi @eppesuig. Thank you for the suggestion. Chrome's Network tab suggests that both web servers set encoding to utf-8 (Content-Type: application/json; charset=utf-8). I've updated the answer with a json sample from production and the local server after the restore.
      singulars
      1. POHeroku PG Dump Export and PSQL Import -> Double Encoding of Extended ASCII Characters
      1. USNazar
    3. COStill, it doesn't seem an error on the dbms side. I am not a ruby expert, but are you also sure that the encoding should be "utf8"? I google a little bit and found "encoding: unicode" instead. Is that the same?
      singulars
      1. POHeroku PG Dump Export and PSQL Import -> Double Encoding of Extended ASCII Characters
      1. USeppesuig
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload