1

I have a mac os machine on which I took a backup of a table with the following command

psql -h localhost -U prices_root -d prices_deb -c "\copy 
(SELECT feed_item_id, pubdate, link, guid, title, summary, content, author, feed_id from feed_items)   
TO 'prices_deb_feed_items_dump.csv' with (format csv,header true, delimiter '^');"

My feed_items table has the following structure

enter image description here

My Mac machine has stopped working and I created the same database again on windows with the same user password etc. I tried to restore the data in that table from the CSV data dump using the following command

psql -h localhost -U ch_v3_root -d ch_v3_dev -c "\copy 
feed_items(feed_item_id,pubdate,link,guid,title,summary,content,author,feed_id) 
FROM 'prices_deb_feed_items_dump.csv' with (format csv,header true, delimiter '^');"

It gives me this error

ERROR:  character with byte sequence 0x9d in encoding "WIN1252" has no equivalent in encoding "UTF8"
CONTEXT:  COPY feed_items, line 21

I would really appreciate if someone can help me restore this database table. When I tested the backup restore on mac it was working well. On windows I keep getting this error

PirateApp
  • 5,433
  • 4
  • 57
  • 90
  • 1
    Is it possible you had database encoding WIN1252 on Mac and now created db with UTF8 encoding ? – Julius Tuskenis Apr 02 '21 at 07:37
  • not sure about mac but i changed it on utf8 using the command set server_encoding to utf8 on my windows machine – PirateApp Apr 02 '21 at 07:41
  • 1
    have you tried creating DB with encoding WIN1252 on windows machine and then restoring your backup to it? – Julius Tuskenis Apr 02 '21 at 08:08
  • does this encoding apply per database or to all the databases? i ll try it now and update – PirateApp Apr 02 '21 at 08:17
  • 1
    I guess the problem is that your old database encoding was not WIN1252, but something else, but pg_restore believes it was. I finds 9D in the backup and as 9D has no character in WIN1252 raises an error. So the best way to solve the problem is finding out what your DB encoding was on Mac. – Julius Tuskenis Apr 02 '21 at 08:20
  • @JuliusTuskenis my mac is dead but i have the harddrive with the raw database files, i am not even sure if i can import those into a new mac but even if i do is it possible to determine the encoding used? – PirateApp Apr 02 '21 at 08:22
  • 1
    You can define encoding to your specific database using `CREATE DATABASE db_name ENCODING=encoding_name;` – Julius Tuskenis Apr 02 '21 at 08:23
  • I think you should try to determine encoding from the content of your csv file and then use the Laurenz Albe answer. – Julius Tuskenis Apr 02 '21 at 09:54
  • if it isnt too much tio ask is there any way to determine the encoding from the content of the postgres csv dump? – PirateApp Apr 02 '21 at 11:08
  • 1
    tools like notepad++ might help, but no guarantees: https://softwareengineering.stackexchange.com/questions/187169/how-to-detect-the-encoding-of-a-file . Also take a look at https://stackoverflow.com/questions/3710374/get-encoding-of-a-file-in-windows – Julius Tuskenis Apr 02 '21 at 11:23

1 Answers1

3

You need to use the same client encoding that you had when you exported the data from your MacOS machine. For example, if it is Windows-1251, on the Windows command line you could do

set PGCLIENTENCODING=WIN1251
psql ... -c "\copy ..."
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263