PostgreSQL: 入門指南

如何開始使用 PostgreSQL

👋 歡迎來到 Stackhero 文件！

Stackhero 提供即用的 PostgreSQL 雲端 解決方案，帶來多項優勢，包括：

  • 無限連接和數據傳輸。
  • 包含 PgAdmin 網頁介面。
  • 許多 模組 如 PostGIS、TimescaleDB 和 PgVector 已包含。
  • 只需一鍵即可輕鬆完成 更新。
  • 由 專用私有 VM 提供的最佳 效能 和強大 安全性。

節省時間 並 簡化您的生活：只需 5 分鐘 即可嘗試 Stackhero 的 PostgreSQL 雲端託管 解決方案！

使用 PostgreSQL psql CLI

您可以使用官方的 psql CLI 管理 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 儀表板中查看）。

創建用戶

  1. 前往 Servers / PostgreSQL，右鍵點擊 Login/Group Roles，然後選擇 Create / Login/Group Role：

    使用 PgAdmin 在 PostgreSQL 中創建用戶使用 PgAdmin 在 PostgreSQL 中創建用戶

  2. 設置登錄名：

    定義用戶登錄定義用戶登錄

  3. 設置安全密碼以避免暴力攻擊：

    定義用戶密碼定義用戶密碼

  4. 最後，確保僅選擇了“Can login”權限：

    定義用戶權限定義用戶權限

點擊“Save”按鈕創建您的用戶。

創建數據庫

  1. 前往 Servers / postgresql，右鍵點擊 Databases，然後選擇 Create / Database...：

    使用 PgAdmin 創建數據庫使用 PgAdmin 創建數據庫

一個好的做法是為數據庫和用戶使用相同的名稱。例如，如果您的項目名為“superWebsite”，請考慮創建一個名為“superWebsite”的用戶和一個名為“superWebsite”的數據庫。

  1. 設置數據庫名稱並選擇擁有者（您剛創建的用戶）：

    定義數據庫名稱和擁有者定義數據庫名稱和擁有者

您的數據庫現在已創建。

使用 psql CLI

創建用戶

要使用 psql CLI 在 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 CLI 在 PostgreSQL 上創建數據庫，請運行以下 SQL 查詢：

CREATE DATABASE "myProject"
  WITH
  OWNER = "myProject"
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = false;

一個好的做法是為數據庫和用戶使用相同的名稱。例如，如果您的項目名為“superWebsite”，請考慮創建一個名為“superWebsite”的用戶和一個名為“superWebsite”的數據庫。

從電腦導入數據到 PostgreSQL

從電腦導入數據到您的 PostgreSQL 實例的最簡單方法之一是使用 PostgreSQL CLI。在您的電腦上運行以下命令（將 <DB_NAME> 替換為您的數據庫名稱，將 data.pgsql 替換為您的 SQL 文件名）：

psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  < data.pgsql

從 PostgreSQL 導出數據到電腦

使用 PostgreSQL CLI 從您的 PostgreSQL 實例導出數據到電腦同樣簡單。

  1. 要導出整個數據庫（將 <DB_NAME> 替換為您的數據庫名稱）：

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  > data.pgsql

  2. 要導出單個表（將 <DB_NAME> 替換為您的數據庫名稱，將 <TABLE_NAME> 替換為您的表名）：

    pg_dump \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  --table=<TABLE_NAME> \
  > data.pgsql

使用 PostgreSQL 與 Node.js

要使用 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);
});

使用 PostgreSQL 與 Node.js 和 pg 庫（以及 async/await）的完整示例可在此 Git 存儲庫中找到： https://github.com/stackhero-io/postgresqlGettingStarted.

使用 PostgreSQL 與 Node.js/TypeORM

要使用 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
  }
});

使用 PostgreSQL 與 Django

如果尚未安裝，請安裝 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 文件中。

  1. 安裝 django-environ：

    pip install django-environ

  2. 打開 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')
  }
}

  3. 在與 settings.py 相同的目錄中打開或創建 .env 文件並添加：

    STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com
STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD>

  4. 最後，將 .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;

-- 啟用美國 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 非常簡單。通過配置以下設置確保安全連接：

  1. 將 Protocol 設置為 SSL
  2. 將 Port 設置為您的 PostgreSQL 端口
  3. 通過將 SSL Mode 設置為 Require 強制加密
  4. 選擇 SSL TLS Protocol 為 1.2 或更高版本

有關 Skyvia 配置的更多詳細信息，請參閱 Skyvia 官方文檔。

使用 Stackhero 配置 Skyvia 的 PostgreSQL 示例使用 Stackhero 配置 Skyvia 的 PostgreSQL 示例

PostgreSQL 的其他文章

  1. ElephantSQL 停止運作的替代方案
    不用擔心 ElephantSQL 停止運作：Stackhero 在這裡！