Hello List, I have done some searching and looking at all the documentation I could find, and I have found a (what i consider) a bug in pg_dump pg_restore that I wanted to bounce off the list to find their solutions. I have a database which gets updated from many locations and it looks like we have gotten some character encoding mis matches in the system. I am going through and fixing these as this is my problem, not postgres's, however, in finding this bug in our data, I found a situation with postgres. If you have bad character encodings in your data, you can not backup and restore that data using pg_dump and pg_restore, on Windows Server 2003, using compress or plain text modes. Both modes fail on the bad encoding when you try to restore. I found this out while doing a test run on our backup and restore process, this data had crept in over a recent period and got into the database somehow - I am finding out how :) I see this as a bug because the data got in there and postgres accepted it on the copy in, but then refuses to accept it on the restore, but I wanted to find out what the list thought. The database is 8.2, running on Windows Server 2003. The database is UTF8 as reported by \l in psql. Now I have managed to do a backup and test restore, but I did this by writing a ruby script that went through the plain text version of the backup line by line and split out the good from the bad by attempting to parse each line through Iconv. It was not a fast process, but it did result in me finding the 120 lines that contained bad data and get them out of the file's COPY tables. I then had to go and individually correct each of these fields to handle the incorrect encodings. Now, the problem is, I should be able to restore my backup, regardless of this sort of problem. The problem characters were all in text fields and so should be able to be restored. How are other admins handling such a situation? What backup tools do you use? I know on Oracle I can do a Binary hot backup which just bypasses the whole problem. But as it stands right now, I will have to keep this script handy or constantly double and triple check encodings. Note, I know the bad stuff shouldn't get in there to begin with, but that is not the point here. I should be able to restore to the state I made the backup in. Anyway, comments? Ideas? I have attached some of the encodings below if anyone is interested. Regards Mikel http://lindsaar.net/ "\201L J\303\203\342\200\234ZSEF U." "\201N\t\\N\tHU\t2084\t20" "DON\303\203\302\201T U." "\201ROS\t\\N\tHU\t3580\t" "\215HE 25\t\\N\t\\N\tHAM" "\215GER\t\\N\t\\N\t2001-" "\201SHINDEN 955-2\t\\" "\215NBUCH\t\\N\tDE\t710" ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
