Cacher is the code snippet organizer for pro developers

# RECREATE A DATABASE WITH NEW `LC_CTYPE`

# Backup 1
psql -U postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'idwell' AND pid <> pg_backend_pid();" && psql -U postgres -c 'CREATE DATABASE idwell_backup TEMPLATE idwell;'

# Backup 2
pg_dump -U postgres idwell > /tmp/dump.sql

# Backup 3
pg_dumpall -U postgres > /tmp/dump2.sql

# Create
psql -U postgres -c "CREATE DATABASE idwell_new LC_CTYPE 'C.UTF-8' TEMPLATE template0"

# Restore to new
psql -U postgres idwell_new < /tmp/dump.sql

# Backup 4
psql -U postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'idwell' AND pid <> pg_backend_pid();" && psql -U postgres -c 'ALTER DATABASE idwell RENAME TO idwell_old'

# Final replacement
psql -U postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'idwell_new' AND pid <> pg_backend_pid();" && psql -U postgres -c 'ALTER DATABASE idwell_new RENAME TO idwell'

SELECT UPPER('абв');
SELECT UPPER('абв' COLLATE "C.UTF-8");
SELECT * FROM pg_collation;

$ locale -a