A practical guide for converting a PostgreSQL database from SQL_ASCII encoding to UTF8 without taking the database offline. The post covers the core problem (pg_restore failing on invalid byte sequences), two SQL script approaches for replacing non-UTF8 characters, and two key pitfalls: trigger invocation during updates and table locking when disabling triggers via ALTER TABLE. The solution uses SET session_replication_role = replica to disable triggers without locking, and processes tables column-by-column rather than all at once to minimize downtime.
Table of contents
The Goal #The Problem #The Solution #The Triggers Problem #The Locking Problem #The Non-Locking, Non-Triggering Solution #Sample DB and Outputs #Sort: