PostgreSQL 数据库编码问题
PostgreSQL 数据库编码问题
资料一：postgres模板数据库
CREATE DATABASE 实际上是通过拷贝一个现有的数据库进行工作的。缺省时，它拷贝名为 template1 的标准系统数据库。所以该数据库是创建新数据库的"模板"。如果你给 template1 增加对象，这些对象将被拷贝到随后创建的用户数据库中。这样的行为允许节点对数据库中的标准套件进行修改。比如，如果你把过程语言 PL/pgSQL 安装到 template1 里，那么你在创建用户数据库的时候它们就会自动可得，而不需要额外的动作。
系统里还有名为 template0 的第二个标准系统数据库，这个数据库包含和 template1 初始时一样的数据内容，也就是说，只包含标准的 PostgreSQL 对象。在 initdb 之后，我们不应该对 template0 做任何修改。通过告诉 CREATE DATABASE 使用 template0 而不是 template1 进行拷贝，你可以创建一个"纯净"的用户数据库，它不会包含任何 template1 里所特有的东西。这一点在恢复 pg_dump 转储的时候是非常方便的：转储脚本应该在一个纯净的数据库中恢复以确保我们正确创建了被转储出的数据库内容，而不和任何现在可能已经存在于 template1 中的附加物相冲突。
要通过拷贝 template0 的方法创建一个数据库，可使用下列方法之一：
CREATE DATABASE dbname TEMPLATE template0;
createdb -T template0 dbname
前者用于 SQL 环境，后者用于 shell 环境。
我们可以创建额外的模板数据库，而且实际上我们可以在一个集群中通过将 CREATE DATABASE 的模板声明为相应的数据库名拷贝任何数据库。不过，我们必需明白，这个功能并非一般性的"COPY DATABASE"工具。实际上，在拷贝操作的过程中，源数据库必需是空闲状态(没有正在处理的数据修改事务)。如果在 CREATE DATABASE 开始的时候存在其它连接，那么操作将会失败，否则在 CREATE DATABASE 的执行过程中新连接都将被锁定，直到拷贝完成。
在 pg_database 里有两个有用的标志可以用于每个数据库：datistemplate 和 datallowconn 字段。datistemplate 表示该数据库是准备用作 CREATE DATABASE 模板的。如果设置了这个标志，那么该数据库可以由任何有 CREATEDB 权限的用户克隆；如果没有设置，那么只有超级用户和该数据库的所有者可以克隆它。如果 datallowconn 为假，那么将不允许与该数据库发生任何新的连接(不过现有的会话不会因为把该标志设置为假而被杀死)。template0 数据库通常被标记为 datallowconn = false 以避免对它的修改。template0 和 template1 都应该总是标记为 datistemplate = true 。
模板库的两个标志 datistemplate 和 datallowconn的设置方法见文末。再补充一点：datallowconn为假时，允许修改编码。
【注意】template1 和 template0 没有任何特殊的状态，除了 template1 是 CREATE DATABASE 的缺省源数据库名之外。比如，我们可以删除 template1 然后从 template0 中创建它而不会有任何不良效果。如果我们不小心在 template1 里加了一堆垃圾，那么我们就会建议做这样的操作。
资料二：PostgreSQL 数据库编码问题
参考网址
了解 PostgreSQL的人肯定听过 template1 和 template0，这两个作为模板库,在建库的时候会用到，但这两者是有很大差别的，曾经一段时间对这两个模板库的使用比较模糊，今天再次查看了文档，决定通过实验记录下来。
关于默认模板库
默认模板库为 template1
postgres=# create database db1;
CREATE DATABASE
备注：建库时如果不指定 TEMPLATE 属性，默认用的是 template1 模板库.
手工指定模板库
postgres=# create database db2 template template0;
CREATE DATABASE
备注：也可以指定模板库为 template0
template1 和 template0 的区别？
数据库初始化之后, 就有了 template0, template1 库，开始时这两个库的内容是一样的，但这两个库有啥异同呢？
template1 可以连接并创建对象，template0 不可以连接
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# create table tmp_1( id int4);
CREATE TABLE
template1=# \c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept
备注：当然可以通过其它方法连接 template0 库。正因为 template1 可以创建对像，相比 template0 ，被称为非干净数据库，而 template0被称为干净的数据库。
使用 template1 模板库建库时不可指定新的 encoding 和 locale，而 template0 可以
template1=# create database db3 TEMPLATE template0 ENCODING 'SQL_ASCII' ;
CREATE DATABASE
template1=# create database db4 TEMPLATE template1 ENCODING 'SQL_ASCII' ;
ERROR: new encoding (SQL_ASCII) is incompatible with the encoding of the template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as template.
template0 库和 template1 都不可删除
postgres=# drop database template0;
ERROR: cannot drop a template database
postgres=# drop database template1;
ERROR: cannot drop a template database
备注：当然有方法删除 template1 库，而且这个操作并不危险，需要修改系统表，这里不演示了。
关于复制数据库
之前简单介绍了 template0 和 template1 的异同，有必要介绍通过模板库复制库的操作，例如这里已经有个 francs 库了，现在想复制一个 francs1 库，内容和francs 库一样。
复制库
postgres=# \c francs
You are now connected to database "francs" as user "postgres".
francs=# select count(*) from pg_stat_user_tables ;
count 41 (1 row)
postgres=# create database francs1 TEMPLATE francs ;
CREATE DATABASE
postgres=# \c francs1 francs
You are now connected to database "francs1" as user "francs".
francs1=> select count(*) from pg_stat_user_tables ;
count 41 (1 row)
备注：这种方法在复制数据库时提供了方便, 也可以定制自己的数据库模板,但是这么操作有个前提，复制时源库不可以连接，复制过程中也不允许连接源库, 否则会报以下错误：
ERROR: source database "francs" is being accessed by other users
DETAIL: There is 1 other session using the database.
根据需要创建符合自己编码方式的数据库
CREATE DATABASE fintest
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
TABLESPACE = pg_default
template = template0
CONNECTION LIMIT = -1;
检查 /etc/profile 文件。应该在里面加入：
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
export LC_CTYPE=en_US.UTF-8
整体的环境变量如果设置好了也不会出现后面的事。postgres用户也不会变成别的字符集。
postgres不仅创建的数据库字符集是LATIN1 ,连postgres和template 0 和template1 都是LATIN1.
解决代码：
# 方法一：用 template0 重新复制一份 template1
# 使 template0 能连接，与 template1 一样。
update pg_database set datallowconn = TRUE where datname = 'template0';
# 验证，如果能连接就继续，否则最好不要继续，回头仔细检查代码是否错误，实在不行就使用方法二
\c template0
# 解除 template1 的模板标志并删除
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
# 创建 UTF8 编码的 template1 ，并将 Ctype 和 Collate 都设置为 en_US.UTF-8，Ctype 用于分类，Collate 用于排序
create database template1 with encoding = 'UTF8' LC_CTYPE = 'en_US.UTF-8' LC_COLLATE = 'en_US.UTF-8' template = template0;
# 检查 是否能连接
\c template1
# 关闭 template0 的连接功能
update pg_database set datallowconn = FALSE where datname = 'template0';
# 方法二：直接修改数据库编码，Ctype 和 Collate 只能在创建时设置，故此法对中文的排序和分类可能有影响
update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'your_database';
作者：只有香如故
链接：https://www.jianshu.com/p/62893363b0d2
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
