pg-encoding-migrator#

PowerShell 7+ script to safely migrate any PostgreSQL database to a different
encoding (e.g. WIN1251 to UTF8 with ICU locale provider). Windows-native.
Designed for PostgreSQL 15+.

Why#

PostgreSQL databases created with legacy encodings (WIN1251, LATIN1, etc.) cannot
be altered in-place. The only correct migration path is: dump with re-encoding,
create a new database, restore. This script automates that process safely with
full rollback support.

Features#

Zero data loss: source database is renamed, never dropped automatically
Auto-rollback on failure at any stage
On-the-fly re-encoding via pg_dump --encoding
Source encoding validation before migration starts
ANALYZE after restore for fresh query planner statistics
Optional privilege grant for application database user
Optional transcript log file
SQL written without BOM (avoids Windows encoding pitfalls)
PGPASSWORD passed via environment variable, cleared after use

Requirements#

Component	Version
PowerShell	7+
PostgreSQL	15+
OS	Windows

Quick Start#

Set-ExecutionPolicy Bypass -Scope Process -Force
.\Convert-PgEncoding.ps1 -DbName myapp -PgPassword "secret"

Parameters#

Parameter	Default	Description
`PgBin`	`C:\Program Files\PostgreSQL\18\bin`	Path to PostgreSQL bin directory
`PgData`	`C:\PostgreSQL`	Path to PostgreSQL data directory (sets PGDATA)
`PgUser`	`postgres`	PostgreSQL superuser
`PgPassword`	(empty)	Password (passed via PGPASSWORD, never CLI arg)
`PgPort`	`5432`	PostgreSQL port
`DbName`	`hmailserver`	Source database name
`GrantUser`	(empty)	App DB role to receive full privileges after restore
`BackupDir`	`C:\pg-migration\backup`	Directory for the dump file
`DumpFile`	`<DbName>_<SrcEncoding>.dump`	Dump file name (auto-generated if empty)
`SrcEncoding`	`WIN1251`	Expected source encoding (for validation + naming)
`TargetEncoding`	`UTF8`	Target encoding
`TargetLocale`	`ru-RU`	ICU locale for the new database
`LocaleProvider`	`icu`	Locale provider (icu or libc)
`LogFile`	(empty)	Optional transcript log path

Migration Stages#

[1/5] pg_dump  -- dump source DB with --encoding=UTF8
[2/5] RENAME   -- rename source DB to <name>_win1251 (backup)
[3/5] CREATE   -- create new DB with UTF8 + ICU locale
[4/5] RESTORE  -- pg_restore into new DB
[5/5] ANALYZE  -- update query planner statistics

Rollback#

If any stage fails after the rename:

DROP new database (if created)
RENAME backup database back to original name

The dump file is always preserved on disk for manual recovery.

Cleanup After Successful Migration#

# Drop the backup database once verified
psql -U postgres -c "DROP DATABASE myapp_win1251;"

# Remove the dump file
Remove-Item "C:\pg-migration\backup\myapp_win1251.dump"

License#

PolyForm Noncommercial 1.0.0 -- see LICENSE

Author#

Mikhail Deynekin -- https://deynekin.com