PostgreSQL: はじめに

PostgreSQLの始め方

👋 Stackheroのドキュメントへようこそ！

Stackheroは、数多くの利点を提供するPostgreSQLクラウドソリューションを提供しています。主な利点は以下の通りです：

無制限の接続とデータ転送。

PgAdminウェブUIが含まれています。

PostGIS、TimescaleDB、PgVectorなどの多くのモジュールが含まれています。

ワンクリックで簡単にアップデート。

プライベートで専用のVMによる最適なパフォーマンスと強力なセキュリティ。

時間を節約し、生活を簡素化しましょう：StackheroのPostgreSQLクラウドホスティングソリューションを試すのにわずか5分しかかかりません！

PostgreSQL `psql` CLIの使用

PostgreSQLは公式のpsql CLIで管理できます。これはコンピュータにインストール可能で、リモートで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 Web UIの使用

PgAdmin Web UIを使用するには、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」という名前のユーザーとデータベースを作成することを検討してください。

データベース名を設定し、所有者（先ほど作成したユーザー）を選択します：

データベース名と所有者を定義

データベースが作成されました。

`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」という名前のユーザーとデータベースを作成することを検討してください。

このコマンドラインで安全なパスワードを生成できます：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」という名前のユーザーとデータベースを作成することを検討してください。

コンピュータからPostgreSQLへのデータのインポート

コンピュータからPostgreSQLインスタンスにデータをインポートする最も簡単な方法の1つは、PostgreSQL CLIを使用することです。コンピュータで次のコマンドを実行します（<DB_NAME>をデータベース名に、data.pgsqlをSQLファイル名に置き換えてください）：

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

PostgreSQLからコンピュータへのデータのエクスポート

PostgreSQLインスタンスからコンピュータへのデータのエクスポートも、PostgreSQL CLIを使用して同様に簡単です。

データベース全体をエクスポートするには（<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に接続するには、次の例を考慮してください。TLS暗号化を確保するために重要なssl引数に注意してください：

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の使用

Node.jsからTypeORMを使用してPostgreSQLに接続するには、以下の例のようにsslフラグをtrueに設定します：

createConnection({
  type: 'postgres',
  host: '<XXXXXX>.stackhero-network.com',
  port: <PORT>,
  username: '<USERNAME>',
  password: '<PASSWORD>',
  database: '<DATABASE>',
  extra: {
    ssl: true
  }
});

DjangoでのPostgreSQLの使用

まだインストールされていない場合は、PostgreSQLに接続するために使用されるpsycopgモジュールをインストールします：

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 Geocoderを有効化
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 for PostgreSQLでのSkyvia設定の例

PostgreSQL: はじめに

👋 Stackheroのドキュメントへようこそ！

PostgreSQL psql CLIの使用

PostgreSQLでのユーザーとデータベースの作成

PgAdmin Web UIの使用

ユーザーの作成

データベースの作成

psql CLIの使用

ユーザーの作成

データベースの作成

コンピュータからPostgreSQLへのデータのインポート

PostgreSQLからコンピュータへのデータのエクスポート

Node.jsでのPostgreSQLの使用

Node.js/TypeORMでのPostgreSQLの使用

DjangoでのPostgreSQLの使用

PostgreSQL PostGIS拡張機能の有効化

PostgreSQL PostGIS拡張機能の無効化

PgVector拡張機能の有効化

TimescaleDB拡張機能の有効化

SQL計画と実行の統計追跡（pg_stat_statements）

SkyviaでのPostgreSQLの設定

PostgreSQL に関するその他の記事

PostgreSQL `psql` CLIの使用

`psql` CLIの使用