PostgreSQL: 入门指南
如何开始使用PostgreSQL
👋 欢迎使用 Stackhero 文档！
Stackhero 提供即用型 PostgreSQL 云 解决方案，具有多种优势，包括：
- 无限连接和数据传输。
- 包含 PgAdmin 网页界面。
- 包含许多 模块，如
PostGIS、TimescaleDB和PgVector。- 只需点击即可轻松完成 更新。
- 由专用私有 VM 提供的最佳 性能 和强大 安全性。
节省时间，简化生活：只需 5 分钟 即可试用 Stackhero 的 PostgreSQL 云托管 解决方案！
使用PostgreSQL的psql命令行工具
您可以使用官方的psql命令行工具管理PostgreSQL。它可以安装在您的计算机上，允许您远程管理PostgreSQL服务。或者，您可以通过Docker运行它。这种方法通常更受欢迎，因为它避免了直接在计算机上安装psql，并且更容易在不同版本之间切换。
要使用Docker访问psql，请运行以下命令（确保将18替换为您的PostgreSQL服务的主要版本号）：
docker run -v $(pwd):/mnt -it postgres:18-alpine /bin/bash
然后使用以下命令连接到您的PostgreSQL服务：
cd /mnt
psql \
--host=<XXXXXX>.stackhero-network.com \
--username=admin \
--port=<PORT> \
--dbname=admin
在PostgreSQL中创建用户和数据库
默认情况下，会创建一个具有管理权限的admin用户。为您计划托管的每个项目创建一个专用用户和数据库是一个好习惯。
使用PgAdmin网页界面
要使用PgAdmin网页界面，请使用HTTPS打开您的PostgreSQL域（例如，https://<XXXXXX>.stackhero-network.com）。使用admin作为用户名和您在服务配置中设置的密码登录（在您的Stackhero仪表板中可见）。
创建用户
-
进入
Servers/PostgreSQL，右键点击Login/Group Roles，选择Create/Login/Group Role：
使用PgAdmin在PostgreSQL中创建用户 -
设置登录名：
定义用户登录 -
设置一个安全的密码以避免暴力攻击：
定义用户密码 -
最后，确保只选择了“Can login”权限：
定义用户权限
点击“Save”按钮创建您的用户。
创建数据库
-
进入
Servers/postgresql，右键点击Databases，选择Create/Database...：
使用PgAdmin创建数据库
一个好的做法是为数据库和用户使用相同的名称。例如，如果您的项目名为“superWebsite”，请考虑创建一个名为“superWebsite”的用户和一个名为“superWebsite”的数据库。
-
设置数据库名称并选择所有者（您刚创建的用户）：
定义数据库名称和所有者
您的数据库现在已创建。
使用psql命令行工具
创建用户
要使用psql命令行工具在PostgreSQL上创建用户，请运行以下SQL查询：
CREATE ROLE "myProject" WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD 'secretPassword';
别忘了将
myProject替换为您的项目名称，并将secretPassword替换为安全密码。 另外，使用您的项目名称作为登录名和数据库名也是一个好习惯。如果您的项目名为“superWebsite”，请考虑创建一个名为“superWebsite”的用户和一个名为“superWebsite”的数据库。您可以使用以下命令行生成一个安全密码：
openssl rand -base64 24 | tr -d '\n' | cut -c1-32
创建数据库
要使用psql命令行工具在PostgreSQL上创建数据库，请运行以下SQL查询：
CREATE DATABASE "myProject"
WITH
OWNER = "myProject"
ENCODING = 'UTF8'
CONNECTION LIMIT = -1
IS_TEMPLATE = false;
一个好的做法是为数据库和用户使用相同的名称。例如，如果您的项目名为“superWebsite”，请考虑创建一个名为“superWebsite”的用户和一个名为“superWebsite”的数据库。
从计算机导入数据到PostgreSQL
从计算机导入数据到PostgreSQL实例的最简单方法之一是使用PostgreSQL命令行工具。在您的计算机上，运行以下命令（将<DB_NAME>替换为您的数据库名称，将data.pgsql替换为您的SQL文件名）：
psql \
--host=<XXXXXX>.stackhero-network.com \
--username=admin \
--port=<PORT> \
--dbname=<DB_NAME> \
< data.pgsql
从PostgreSQL导出数据到计算机
使用PostgreSQL命令行工具从PostgreSQL实例导出数据到计算机同样简单。
-
要导出整个数据库（将
<DB_NAME>替换为您的数据库名称）：pg_dump \ --host=<XXXXXX>.stackhero-network.com \ --username=admin \ --port=<PORT> \ --dbname=<DB_NAME> \ > data.pgsql -
要导出单个表（将
<DB_NAME>替换为您的数据库名称，将<TABLE_NAME>替换为您的表名）：pg_dump \ --host=<XXXXXX>.stackhero-network.com \ --username=admin \ --port=<PORT> \ --dbname=<DB_NAME> \ --table=<TABLE_NAME> \ > data.pgsql
使用Node.js连接PostgreSQL
要使用Node.js和pg包（也称为node-postgres）连接PostgreSQL，您可以参考以下示例。注意ssl参数，它对于确保TLS加密非常重要：
const { Client } = require('pg');
(async () => {
const pg = new Client({
host: '<XXXXXX>.stackhero-network.com',
port: <PORT>,
user: '<USERNAME>',
password: '<PASSWORD>',
database: '<DATABASE>',
ssl: {}
});
await pg.connect();
const result = await pg.query('SELECT 1');
await pg.end();
})().catch(error => {
console.error('');
console.error('🐞 发生错误！');
console.error(error);
process.exit(1);
});
使用Node.js和pg库（以及async/await）连接PostgreSQL的完整示例可在此Git仓库中找到：
https://github.com/stackhero-io/postgresqlGettingStarted.
使用Node.js/TypeORM连接PostgreSQL
要使用TypeORM从Node.js连接到PostgreSQL，请将ssl标志设置为true，如下例所示：
createConnection({
type: 'postgres',
host: '<XXXXXX>.stackhero-network.com',
port: <PORT>,
username: '<USERNAME>',
password: '<PASSWORD>',
database: '<DATABASE>',
extra: {
ssl: true
}
});
使用Django连接PostgreSQL
如果尚未安装，请安装psycopg模块，它将用于连接PostgreSQL：
pip install psycopg
在此初始步骤中，您将密码直接存储在settings.py文件中。此方法仅用于测试，因为它不安全。在本文档的后面，您将找到一个最佳实践示例。
打开settings.py文件并添加以下配置：
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'HOST': '<XXXXXX>.stackhero-network.com',
'PORT': <PORT>,
'OPTIONS': {
'sslmode': 'require',
},
'NAME': 'admin',
'USER': 'admin',
'PASSWORD': '<ADMIN_PASSWORD>'
}
}
注意：此示例不建议用于生产，仅用于测试目的！
一旦您的连接正常工作，您可以采用更安全的方法来存储凭据。以下示例使用django-environ并将凭据存储在.env文件中。
-
安装
django-environ：pip install django-environ -
打开
settings.py文件并按如下修改：import environ env = environ.Env() environ.Env.read_env() DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql', 'HOST': env('STACKHERO_POSTGRESQL_HOST'), 'PORT': <PORT>, 'OPTIONS': { 'sslmode': 'require', }, 'NAME': 'admin', 'USER': 'admin', 'PASSWORD': env('STACKHERO_POSTGRESQL_ADMIN_PASSWORD') } } -
在与
settings.py相同的目录中打开或创建.env文件并添加：STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD> -
最后，将
.env添加到您的.gitignore文件中，以确保您的凭据不会存储在Git仓库中：
echo ".env" >> .gitignore
激活PostgreSQL的PostGIS扩展
PostGIS扩展包含在我们的PostgreSQL服务中。您必须在计划使用的每个数据库上激活它。
要激活扩展，请连接到您的数据库并执行此查询：
CREATE EXTENSION postgis;
您可以通过检查其版本来验证PostGIS是否正常工作：
SELECT PostGIS_Full_Version();
或者，检索所有已安装的PostGIS扩展的列表：
SELECT * FROM pg_extension WHERE extname LIKE 'postgis%';
如果需要，您可以添加其他扩展。但是，强烈建议您不要激活不使用的扩展：
-- 启用PostGIS
CREATE EXTENSION postgis;
-- 启用栅格支持
CREATE EXTENSION postgis_raster;
-- 启用拓扑
CREATE EXTENSION postgis_topology;
-- Tiger需要模糊匹配
CREATE EXTENSION fuzzystrmatch;
-- 基于规则的标准化器
CREATE EXTENSION address_standardizer;
-- 示例规则数据集
CREATE EXTENSION address_standardizer_data_us;
-- 启用US Tiger地理编码器
CREATE EXTENSION postgis_tiger_geocoder;
注意：不要在数据库
postgres上激活PostGIS！
禁用PostgreSQL的PostGIS扩展
要从数据库中删除PostGIS，请连接到相关数据库并运行以下查询：
DROP EXTENSION postgis;
激活PgVector扩展
在Stackhero上激活PgVector扩展很简单。运行以下查询：
CREATE EXTENSION vector;
激活TimescaleDB扩展
在Stackhero上激活TimescaleDB扩展很简单。运行以下查询：
CREATE EXTENSION timescaledb;
跟踪SQL计划和执行的统计信息（pg_stat_statements）
要启用pg_stat_statements支持，首先通过Stackhero仪表板在您的PostgreSQL配置中启用它。然后，通过运行以下命令为当前数据库启用扩展：
CREATE EXTENSION pg_stat_statements;
有关更多信息，请参阅PostgreSQL官方文档。
使用Skyvia配置PostgreSQL
使用Skyvia与Stackhero for PostgreSQL非常简单。通过配置以下设置确保安全连接：
- 将
Protocol设置为SSL - 将
Port设置为您的PostgreSQL端口 - 通过将
SSL Mode设置为Require强制加密 - 选择
SSL TLS Protocol为1.2或更高
有关Skyvia配置的更多详细信息，请参阅Skyvia官方文档。
使用Stackhero为PostgreSQL配置Skyvia的示例