2.5 PostgreSQL初始数据库的创建与配置

2.5 初始数据库的创建与配置

2.5.1 明确初始数据库

2.5.1.1 初始数据库的概念

在 PostgreSQL 中，初始数据库（也称为模板数据库）是用于天生新数据库的特殊数据库。当你创建一个新的数据库时，你可以指定一个现有的数据库作为模板。新数据库将复制模板数据库的全部设置，包括数据、模式（schemas）、用户权限、索引等。
PostgreSQL 在安装过程中会主动创建两个初始数据库：template1 和 template0。template1 是默认的模板数据库，用于创建新数据库；template0 通常用于存储体系级的数据和设置，不发起直接用于创建用户数据库。
2.5.1.2 初始数据库的作用

初始数据库在 PostgreSQL 中扮演偏重要的角色：

• 快速创建：使用初始数据库可以快速创建具有预定义设置的新数据库，这有助于尺度化数据库环境并淘汰配置错误。
  
• 模板功能：管理员可以根据必要定制初始数据库，好比预加载常用的数据、函数、索引等，使得从这些模板创建的新数据库已经具备了一些基本功能。
  
• 权限管理：通过在初始数据库中设置得当的权限，可以控制用户对新创建数据库的访问和操作。
  
• 维护和升级：在升级 PostgreSQL 版本时，通常只必要升级初始数据库，然后从中创建新数据库，这样可以确保新数据库继承了全部最新的特性和改进。
  
• 备份和规复：在举行体系备份和规复时，备份初始数据库是一个好习惯，因为它们包含了创建新数据库所需的全部信息。
  

相识初始数据库的概念和作用对于 PostgreSQL 数据库管理员来说非常重要，因为它们是数据库创建和管理的底子。通过公道利用初始数据库，可以提高数据库管理的服从和一致性。
2.5.2 创建初始数据库

2.5.2.1 使用 postgres 命令行工具

在 PostgreSQL 中，可以使用 postgres 命令行工具创建初始数据库。这通常在数据库服务器启动时主动完成，但你也可以手动创建或修改初始数据库。

• 登录到 postgres 用户（假如必要）：
  
  1. sudo -i -u postgres

  复制代码
• 使用 initdb 命令初始化数据库：
  
  1. initdb -D /path/to/new_data_directory -E UTF8 -A trust

  复制代码
  这里 -D 指定了新数据库的数据目录，-E UTF8 设置了字符编码，-A trust 设置了默认的认证方法。
  
• 启动 PostgreSQL 服务：
  
  1. pg_ctl start -D /path/to/new_data_directory

  复制代码

2.5.2.2 使用 psql 命令行界面

你可以使用 psql 命令行界面来创建或修改初始数据库。

• 连接到 PostgreSQL 服务器：
  
  1. psql -U postgres -d template1

  复制代码
• 创建新的初始数据库：
  
  1. CREATE DATABASE new_template WITH TEMPLATE template1;

  复制代码
• 修改 pg_hba.conf 文件（假如必要）：
  在 pg_hba.conf 文件中添加或修改认证规则，以允许新初始数据库的连接。
  
• 重启 PostgreSQL 服务：
  
  1. pg_ctl restart -D /path/to/data_directory

  复制代码

2.5.2.3 使用图形界面工具

许多图形界面工具，如 pgAdmin 或 DataGrip，也可以用来创建初始数据库。

• 打开图形界面工具：
  启动你的 PostgreSQL 图形界面工具。
  
• 连接到 PostgreSQL 服务器：
  使用工具提供的连接领导连接到你的 PostgreSQL 服务器。
  
• 创建新的初始数据库：
  在工具中找到创建数据库的选项，通常在右键点击服务器或数据库列表中的“创建”或“新建”菜单中。
  
• 设置初始数据库的属性：
  在创建过程中，你可以选择使用现有的数据库作为模板，设置字符编码和其他选项。
  
• 保存并退出：
  完成创建后，保存设置并退出创建领导。
  

使用图形界面工具可以简化创建初始数据库的过程，特殊是对于不认识命令行的用户。不过，无论使用哪种方法，创建初始数据库时都应确保正确设置全部须要的参数和选项。
2.5.3 配置初始数据库

2.5.3.1 设置数据库全部者

在 PostgreSQL 中，数据库全部者（通常是 postgres 超等用户）对数据库拥有完全的控制权。在创建初始数据库时，可以指定全部者：

1. CREATE DATABASE template_name OWNER postgres;

复制代码

在 pgAdmin 或其他图形界面工具中，创建数据库时通常会有选项让你选择或指定全部者。
2.5.3.2 配置数据库编码

字符编码（Character Set Encoding）是数据库中存储文本数据的方式。在 PostgreSQL 中，默认的编码是 UTF8，它支持多语言文本。在创建初始数据库时，可以指定编码：

1. CREATE DATABASE template_name WITH ENCODING 'UTF8';

复制代码

假如你必要使用其他编码，可以在创建数据库时指定。不过，发起使用 UTF8 编码，因为它是最通用的编码方式。
2.5.3.3 配置数据库模板

配置数据库模板是创建初始数据库的重要步调，因为模板数据库将作为创建新数据库的底子。以下是一些常用的配置：

• 模板数据库的权限：确保模板数据库具有得当的权限设置，以便新数据库可以正确地继承这些权限。
  
  1. GRANT ALL PRIVILEGES ON DATABASE template_name TO role_name;

  复制代码
• 预加载数据和函数：可以在模板数据库中预加载常用的数据和函数，这样每次创建新数据库时，这些数据和函数就会主动包含在内。
  
• 预定义模式：在模板数据库中创建常用的模式（schemas），这样新数据库就会包含这些模式。
  
  1. CREATE SCHEMA my_schema;

  复制代码
• 预定义扩展：假如某些 PostgreSQL 扩展在新数据库中常常使用，可以在模板数据库中预先安装这些扩展。
  
  1. CREATE EXTENSION postgis WITH SCHEMA public;

  复制代码
• 优化配置参数：根据模板数据库的预期用途，调整 postgresql.conf 文件中的相干参数，如 shared_buffers、work_mem 等。
  

配置好初始数据库后，每次创建新数据库时，只需指定使用该模板，新数据库就会继承模板的全部设置和内容。这使得数据库的创建和管理更加高效和一致。
2.5.4 角色和权限管理

2.5.4.1 创建和管理角色

在 PostgreSQL 中，角色（Roles）是用户和组的抽象，可以用于管理数据库权限。

• 创建角色：
  
  1. CREATE ROLE role_name WITH LOGIN PASSWORD 'password';

  复制代码
  这将创建一个新角色，并设置其密码。
  
• 管理角色属性：
  可以设置角色的各种属性，如是否允许角色创建数据库等：
  
  1. ALTER ROLE role_name CREATEDB;
  2. ALTER ROLE role_name NOSUPERUSER;

  复制代码
• 删除角色：
  假如不再必要某个角色，可以将其删除：
  
  1. DROP ROLE role_name;

  复制代码

2.5.4.2 配置角色权限

角色权限用于控制角色对数据库对象的访问和操作。

• 授予权限：
  授予角色对数据库、表、函数等的权限：
  
  1. GRANT SELECT ON TABLE table_name TO role_name;
  2. GRANT USAGE ON SCHEMA schema_name TO role_name;
  3. GRANT EXECUTE ON FUNCTION function_name TO role_name;

  复制代码
• 撤销权限：
  撤销已授予角色的权限：
  
  1. REVOKE SELECT ON TABLE table_name FROM role_name;

  复制代码
• 管理权限：
  使用 \du 和 \l 命令在 psql 中查看角色和数据库的权限。
  

2.5.4.3 角色的权限继承

在 PostgreSQL 中，角色可以继承其他角色的权限，这有助于简化权限管理。

• 角色成员：
  将一个角色添加为另一个角色的成员，从而继承权限：
  
  1. GRANT role_name TO another_role;

  复制代码
• 查看角色成员：
  查看角色的成员列表：
  
  1. \roles

  复制代码
• 继承设置：
  在创建角色时，可以设置角色是否应该继承其他角色的权限：
  
  1. CREATE ROLE role_name WITH LOGIN PASSWORD 'password' INHERIT;

  复制代码

通过公道配置角色和权限，可以确保数据库的安全性，同时提高管理服从。在设置权限时，发起遵循最小权限原则，即只授予角色完成其任务所必需的最小权限。
2.5.5 数据库模板和角色模板

2.5.5.1 使用模板数据库

模板数据库在 PostgreSQL 中用于创建新的数据库，它们包含了新数据库应该继承的全部设置和对象。

• 使用默认模板：
  PostgreSQL 安装后会自带两个模板数据库：template1 和 template0。通常，template1 被用作默认的模板数据库。
  
• 创建新数据库：
  创建新数据库时，可以指定模板：
  
  1. CREATE DATABASE new_database_name TEMPLATE template1;

  复制代码
• 修改默认模板：
  假如必要，可以修改默认模板数据库中的设置或对象，这些更改将影响全部使用该模板创建的新数据库。
  

2.5.5.2 创建自定义模板

创建自定义模板数据库可以让你快速部署具有特定配置和预加载数据的新数据库。

• 创建模板数据库：
  创建一个新的模板数据库：
  
  1. CREATE DATABASE custom_template WITH TEMPLATE template1;

  复制代码
• 配置模板：
  在新创建的模板数据库中，添加所需的扩展、模式、函数等：
  
  1. CREATE EXTENSION postgis;
  2. CREATE SCHEMA my_schema;

  复制代码
• 设置模板为默认：
  假如你想将新的模板数据库设置为默认模板，可以在 postgresql.conf 文件中设置：
  
  1. default_text_search_config = 'pg_catalog.english'

  复制代码

2.5.5.3 角色模板的应用

角色模板不是 PostgreSQL 的一个直接概念，但可以通过角色和权限管理来模拟。

• 创建角色：
  创建一个包含特定权限的角色，这个角色可以作为其他新角色的模板：
  
  1. CREATE ROLE role_template WITH LOGIN PASSWORD 'password';
  2. GRANT CONNECT ON DATABASE database_name TO role_template;

  复制代码
• 复制角色权限：
  创建新角色时，可以复制已有角色的权限：
  
  1. CREATE ROLE new_role WITH LOGIN PASSWORD 'password';
  2. GRANT ALL PRIVILEGES ON DATABASE database_name TO new_role;

  复制代码
• 角色继承：
  使用 GRANT 和 REVOKE 命令来管理角色之间的权限继承关系。
  

通过使用模板数据库和角色，可以尺度化数据库环境，确保新创建的数据库和角色具有一致的配置和权限设置。这有助于简化数据库管理并提高数据安全性。
2.5.6 多语言支持和地区设置

2.5.6.1 配置数据库语言

PostgreSQL 支持多种语言，可以在数据库级别配置语言设置。

• 设置数据库语言：
  在创建数据库时，可以指定语言：
  
  1. CREATE DATABASE mydatabase LC_CTYPE 'en_US.utf8' LC_COLLATE 'en_US.utf8';

  复制代码
  这将设置数据库的字符类型和排序规则为美国英语。
  
• 更改现有数据库的语言：
  可以使用 ALTER DATABASE 命令更改现有数据库的语言设置：
  
  1. ALTER DATABASE mydatabase SET LC_CTYPE TO 'en_US.utf8';
  2. ALTER DATABASE mydatabase SET LC_COLLATE TO 'en_US.utf8';

  复制代码

2.5.6.2 地区设置的配置

地区设置（Locale）是指特定的文化习惯和语言环境，它影响日期、时间、数字和货币的格式。

• 配置服务器地区设置：
  在 postgresql.conf 文件中，可以设置服务器的默认地区：
  
  1. lc_time = 'en_US.utf8'

  复制代码
• 配置客户端地区设置：
  客户端可以连接时指定地区设置，比方在 psql 命令行中：
  
  1. psql -U username -d mydatabase -c "SET lc_time TO 'de_DE.utf8';"

  复制代码

2.5.6.3 字符集和排序规则

字符集（Character Set）和排序规则（Collation）是地区设置的重要构成部门。

• 字符集：
  PostgreSQL 默认使用 UTF8 字符集，它支持多语言文本。在创建数据库或模板时可以指定字符集：
  
  1. CREATE DATABASE mydatabase WITH ENCODING 'UTF8';

  复制代码
• 排序规则：
  排序规则定义了字符串比较和排序的规则。可以为每个数据库或模式设置排序规则：
  
  1. CREATE DATABASE mydatabase LC_COLLATE 'de_DE.utf8';
  2. CREATE SCHEMA myschema LC_COLLATE 'de_DE.utf8';

  复制代码
• 更改排序规则：
  假如必要，可以更改现有数据库或模式的排序规则：
  
  1. ALTER DATABASE mydatabase SET LC_COLLATE TO 'fr_FR.utf8';
  2. ALTER SCHEMA myschema SET LC_COLLATE TO 'fr_FR.utf8';

  复制代码

正确配置多语言支持和地区设置对于国际化应用程序非常重要，它们确保了数据的正确显示和处置惩罚。在配置这些设置时，应考虑应用程序的需求和目的用户群体。
2.5.7 连接和认证设置

2.5.7.1 配置连接参数

PostgreSQL 提供了多个参数来配置客户端连接。

• 最大连接数：
  在 postgresql.conf 文件中，max_connections 参数控制了数据库可以同时处置惩罚的客户端连接数。
  
  1. max_connections = 100

  复制代码
• 超时设置：
  connection_timeout 参数定义了客户端连接实验的超时时间。
  
  1. connection_timeout = 60s

  复制代码
• 资源限制：
  可以使用 work_mem 和 maintenance_work_mem 参数来限制查询和维护任务的内存使用。
  

2.5.7.2 设置客户端认证

客户端认证决定了哪些用户可以连接到数据库以及怎样验证他们的身份。

• 设置密码：
  使用 ALTER USER 命令为用户设置或更改密码。
  
  1. ALTER USER username PASSWORD 'newpassword';

  复制代码
• 角色创建：
  使用 CREATE ROLE 命令创建新角色，并指定是否允许登录。
  
  1. CREATE ROLE rolename WITH LOGIN PASSWORD 'password';

  复制代码
• 角色权限：
  使用 GRANT 和 REVOKE 命令管理角色的权限。
  

2.5.7.3 使用 pg_hba.conf 管理访问控制

pg_hba.conf 文件控制客户端对数据库的访问权限。

• 信任连接：
  允许来自特定 IP 地点的连接无需密码。
  
  1. host all all 127.0.0.1/32 trust

  复制代码
• 密码认证：
  要求来自特定 IP 地点的连接使用密码。
  
  1. host all all 192.168.1.0/24 md5

  复制代码
• SSL 连接：
  要求使用 SSL 加密连接。
  
  1. hostssl all all 0.0.0.0/0 ssl

  复制代码
• 配置文件重载：
  修改 pg_hba.conf 后，可以通过发送 SIGHUP 信号到 PostgreSQL 主进程来重载配置，而无需重启服务。
  
  1. pg_ctl reload

  复制代码
• 注意事项：
  pg_hba.conf 文件中的规则是按顺序检查的，因此排列顺序很重要。通常，更具体的规则应该放在文件的前面。
  

通过这些设置，可以确保 PostgreSQL 数据库的连接和认证既安全又高效。在配置连接和认证时，应根据实际的安全需求和环境举行得当的调整，并定期检察和更新安全策略。
2.5.8 备份和规复初始数据库

2.5.8.1 备份初始数据库的重要性

备份初始数据库对于数据库管理和灾难规复至关重要。由于初始数据库用作创建新数据库的模板，任何对其的更改都会影响全部新创建的数据库。因此，保持初始数据库的备份可以在以下环境下掩护数据和配置：

• 数据丢失：在数据破坏或丢失的环境下，可以从备份中规复初始数据库。
  
• 错误配置：假如对初始数据库的配置更改导致问题，可以使用备份来规复到先前的状态。
  
• 版本升级：在升级 PostgreSQL 版本之前备份初始数据库，可以在升级过程中出现问题时回退。
  

2.5.8.2 使用 pg_dump 备份数据库

pg_dump 是 PostgreSQL 提供的一个用于备份数据库的工具。

• 备份整个数据库：
  
  1. pg_dump -U username -d database_name > backup_filename.sql

  复制代码
  这将创建一个包含数据库内容的 SQL 文件。
  
• 备份初始数据库：
  
  1. pg_dump -U username -d template1 > template1_backup.sql

  复制代码
  由于 template1 是一个特殊的数据库，通常用于创建新数据库，因此备份它特殊重要。
  
• 备份选项：
  pg_dump 支持多种选项，如 --schema-only 仅备份模式结构，--data-only 仅备份数据，--no-owner 不包含全部权信息等。
  

2.5.8.3 规复数据库的方法

假如必要从备份中规复数据库，可以使用 psql 工具或 pg_restore。

• 使用 psql 规复：
  将备份文件导入到现有的或新创建的数据库中：
  
  1. psql -U username -d database_name < backup_filename.sql

  复制代码
• 使用 pg_restore 规复：
  pg_restore 工具用于从备份文件中规复数据库：
  
  1. pg_restore -U username -d database_name < backup_filename.sql

  复制代码
  或者，假如你使用的是自定义格式的备份，可以使用：
  
  1. pg_restore -U username -d database_name backup_filename.backup

  复制代码
• 注意事项：
  在规复数据库之前，确保目的数据库不存在或已经清空，以避免数据冲突。别的，假如备份是在差别版本的 PostgreSQL 中创建的，可能必要考虑版本兼容性问题。
  

通过定期备份初始数据库并认识规复过程，可以确保在出现问题时快速规复服务，淘汰数据丢失的风险。
2.5.9 维护和优化初始数据库

2.5.9.1 数据库的常规维护任务

维护初始数据库是确保其性能和稳定性的关键。以下是一些常规的维护任务：

• 定期备份：
  定期备份初始数据库，以防数据丢失或破坏。
  
• VACUUM 操作：
  定期运行 VACUUM 命令来清理数据库中的死tuple，接纳空间，并更新统计信息。
  
  1. VACUUM ANALYZE;

  复制代码
• 日志文件管理：
  定期检查和维护日志文件，确保日志文件不会占用过多的磁盘空间。
  
• 更新统计信息：
  更新数据库的统计信息，以帮助查询优化器天生更有用的查询筹划。
  
• 检查磁盘空间：
  监控磁盘空间使用环境，确保有足够的空间供数据库操作。
  

2.5.9.2 性能监控和调优

性能监控和调优是保持初始数据库高效运行的重要部门。

• 监控工具：
  使用 pg_stat_statements 或其他监控工具来跟踪数据库的性能。
  
• 查询优化：
  分析慢查询并优化它们，可能通过调整索引、改进查询逻辑或增加内存资源。
  
• 配置参数调整：
  根据性能监控的结果，调整 postgresql.conf 中的配置参数，如 shared_buffers、work_mem 等。
  
• 硬件资源：
  根据必要升级硬件资源，如增加内存或使用更快的存储设备。
  

2.5.9.3 清理和重建数据库

在某些环境下，可能必要清理或重建初始数据库：

• 清理数据库：
  假如数据库中存在过多的无用数据或配置错误，可以清理这些数据或重置配置。
  
• 重建数据库：
  假如数据库破坏或必要彻底重置，可以从备份中重建数据库，或创建一个新的初始数据库。
  
• 使用模板：
  假如初始数据库必要重置，可以使用一个干净的模板数据库来创建它。
  
• 测试变更：
  在生产环境之外测试全部的维护和优化变更，以确保它们不会对现有体系产生负面影响。
  

通过这些维护和优化步伐，可以确保初始数据库的健康状态，从而为全部基于它创建的新数据库提供一个稳定和高效的起点。
2.5.10 常见问题息争决方案

2.5.10.1 常见数据库创建问题

当创建数据库时，可能会遇到一些问题，以下是一些常见的问题及其解决方案：

• 权限问题：
  
  
  
  • 确保执行创建数据库操作的用户具有足够的权限。
    
  • 使用超等用户或具有 CREATE 权限的用户来创建数据库。
    
  
  
• 名称冲突：
  
  
  
  • 确保所选的数据库名称在体系中是唯一的，没有其他数据库使用该名称。
    
  
  
• 磁盘空间不足：
  
  
  
  • 检查服务器的磁盘空间，确保有足够的空间来创建新数据库。
    
  • 清理不须要的文件或增加磁盘空间。
    
  
  
• 连接数限制：
  
  
  
  • 假如到达 max_connections 的限制，可能无法创建新数据库。
    
  • 增加 max_connections 参数的值或关闭一些不须要的连接。
    
  
  

2.5.10.2 配置错误息争决方法

配置错误可能导致数据库运行不正常，以下是一些常见的配置问题及其解决方法：

• 配置文件语法错误：
  
  
  
  • 细致检查 postgresql.conf 和 pg_hba.conf 文件的语法，确保没有拼写或格式错误。
    
  
  
• 参数设置不当：
  
  
  
  • 根据服务器的硬件配置和工作负载调整参数，如 shared_buffers、work_mem 等。
    
  • 参考 PostgreSQL 官方文档中的参数阐明和推荐值。
    
  
  
• 配置文件未重载或重启：
  
  
  
  • 对于某些参数的更改，必要重载配置文件或重启数据库服务才能生效。
    
  • 使用 pg_ctl reload
    重载配置文件，或重启数据库服务。
    
  
  
• 文件权限和全部权问题：
  
  
  
  • 确保 PostgreSQL 数据目录和配置文件的权限设置正确，全部权归 postgres 用户。
    
  
  

2.5.10.3 性能问题诊断和优化

性能问题可能由多种因素引起，以下是一些诊断和优化的方法：

• 慢查询分析：
  
  
  
  • 使用 EXPLAIN 或 EXPLAIN ANALYZE 命令分析慢查询的执行筹划。
    
  • 优化查询语句或调整索引以提高查询性能。
    
  
  
• 资源监控：
  
  
  
  • 监控数据库的 CPU、内存和 I/O 使用环境，确定是否存在资源瓶颈。
    
  • 根据监控结果调整硬件资源或数据库配置。
    
  
  
• 索引优化：
  
  
  
  • 定期检察索引的使用环境，删除不须要的索引，添加缺失的索引。
    
  • 使用 pg_stat_statements 扩展来分析查询和索引的使用环境。
    
  
  
• 配置参数调优：
  
  
  
  • 根据数据库的工作负载和性能监控结果调整配置参数。
    
  • 举行基准测试，以验证配置更改对性能的影响。
    
  
  
• 定期维护：
  
  
  
  • 定期执行 VACUUM 和 ANALYZE 命令来维护数据库性能。
    
  • 清理不再必要的数据和日志文件。
    
  
  

通过这些方法，可以有用地诊断息争决 PostgreSQL 数据库的常见问题，保持数据库的健康和高效运行。在处置惩罚问题时，发起参考 PostgreSQL 的官方文档和社区资源，以获取更多的帮助和信息。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。