PostgreSQL

https://liapoc.com/outside-postgresql.html

https://qiita.com/nanbuwks/items/846cf3536a82a2798555

https://zenn.dev/hdmt/articles/8b242a8c78f7f1


# tips

## IPアドレスの確認

```bash
hostname -I
```

## pgAdmin 4

https://www.sraoss.co.jp/tech-blog/pgsql/pgadmin4/

https://www.pgadmin.org/download/pgadmin-4-apt/

`apt` でインストールする場合

```bash
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
sudo apt install pgadmin4
```

### 操作方法

https://itsakura.com/pgadmin4-db-create

### バックアップ

https://harionote.net/article/55

SQLとしてエクスポート

- General
    - Format: Plain
    - Encoding: UTF8
- Data Options
    - Do not save
        - Owner: on
        - Privileges: on
- Query Options
    - Use INSERT Commands: on

## pandas

https://tanuhack.com/pandas-postgres-readto/

## SQLite3からPostgreSQLへのデータ移行

https://zenn.dev/thr/articles/ce99ffd1f32605


---
# Windows

以下のリンクからインストーラーをダウンロード

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

インストールの流れについては以下を参照のこと

https://qiita.com/tom-sato/items/037b8f8cb4b326710f71

`psql` で文字化けする場合は，以下のサイトを参考に文字コードをUTF-8に変更する

https://qiita.com/Ryokhu/items/37b79a48b625b77c7b39#設定画面からutf-8に設定する方法


# サーバーにPostgreSQLをインストール (Ubuntu)

```bash
sudo apt update && sudo apt install -y postgresql
```

PostgreSQLのサービスが起動しているかを確認

```bash
systemctl status postgresql.service
```

:::note info
PostgreSQLが起動している場合は，`Active: active (exited)` と表示される
:::

## 動作確認

デフォルトユーザー「postgres」にログインして，`psql` を起動

```bash
sudo -u postgres psql
```

#### バージョン確認

```console
postgres=# SELECT version();
```
```plaintext
                                                             version                
---------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 (Ubuntu 16.4-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
(1 row)
```

表示を閉じる場合は `\q` を入力

#### データベースの確認

```console
postgres=# \l
```
```plaintext
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
(3 rows)
```

こちらも，表示を閉じる場合は `\q` を入力

#### パスワードを設定

```console
postgres=# ALTER ROLE postgres WITH password '<パスワード>';
```

#### データベースを作成

```console
postgres=# CREATE DATABASE <データベース名>;
```

#### データベースを削除

```console
postgres=# DROP DATABASE <データベース名>;
```

#### ログアウト

```console
postgres=# \q
```

# 外部からの接続を許可（サーバー側）

Ubuntu 24の場合，PostgreSQLのバージョンは `16`
`/etc/postgresql/16/main/postgresql.conf` を書き換える
Windowsの場合は `C:\Program Files\PostgreSQL\16\data\postgresql.conf`

```bash
sudo vi /etc/postgresql/<PostgreSQLのバージョン>/main/postgresql.conf
```

```diff_conf
  #------------------------------------------------------------------------------
  # CONNECTIONS AND AUTHENTICATION
  #------------------------------------------------------------------------------

  # - Connection Settings -

- listen_addresses = 'localhost'    # what IP address(es) to listen on;
+ listen_addresses = '*'            # what IP address(es) to listen on;
                                    # comma-separated list of addresses;
                                    # defaults to 'localhost'; use '*' for all
                                    # (change requires restart)
  port = 5432                       # (change requires restart)
```

`/etc/postgresql/16/main/pg_hba.conf` を書き換える
Windowsの場合は `C:\Program Files\PostgreSQL\16\data\pg_hba.conf`

```bash
sudo vi /etc/postgresql/<PostgreSQLのバージョン>/main/pg_hba.conf
```

許可したいクライアントのIPアドレスを
`host <データベース名> <ユーザー名> <クライアントのIPアドレス>/24 <md5>`
で追加

IPアドレスを制限しない場合は，`0.0.0.0/0` を追加
（許可したいIPアドレスを１つずつ追加するほうが安全）

```diff_conf
  # TYPE  DATABASE        USER            ADDRESS                 METHOD

  # "local" is for Unix domain socket connections only
  local   all             all                                     peer
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            scram-sha-256
+ host    all             all             192.168.50.10/24        md5
```

PostgreSQLを再起動し，設定変更を反映させる

```bash
sudo service postgresql restart
```

## ポート開放

### Windows

以下のサイトを参考にして，PostgreSQL用に**5432**番（デフォルトの場合）を開放

https://support.borndigital.co.jp/hc/ja/articles/360002711593-Windows10で特定のポートを開放する

### Ubuntu

https://qiita.com/siida36/items/be21d361cf80d664859c

https://vpslife.server-memo.net/ubuntu_ufw_start/

現在開放されているポートを確認

```bash
sudo ufw status
```
```plaintext
Status: active

To                         Action      From
--                         ------      ----
22                         ALLOW       Anywhere
22 (v6)                    ALLOW       Anywhere (v6)
```

:::note warn
`Status: inactive` と出力された場合は，Firewallが無効化されている
:::

上の例ではSSH用の22番のみ開放されているため，PostgreSQL用の**5432**番（デフォルトの場合）を開放
下の例では，PHP用にApacheの80番も開放

```bash
sudo ufw allow 5432  # PostgreSQL
sudo ufw allow 80    # Apache
sudo ufw reload
sudo ufw status
```

ポートが開放されたか確認

```bash
sudo ufw status
```
```plaintext
Status: active

To                         Action      From
--                         ------      ----
22                         ALLOW       Anywhere
5432                       ALLOW       Anywhere
80                         ALLOW       Anywhere
22 (v6)                    ALLOW       Anywhere (v6)
5432 (v6)                  ALLOW       Anywhere (v6)
80 (v6)                    ALLOW       Anywhere (v6)
```


# 外部からの接続確認（クライアント側）

クライアント用PostgreSQLをインストール

```bash
sudo apt install -y postgresql-client
```

```bash
psql -h <サーバーPCのIPアドレス> -U <ユーザー名> -d <データベース名>
```

## PHPによるPostgreSQLへの接続確認

https://qiita.com/uneyamauneko/items/4e9e8d4b18eff3c950e9

### Apacheのインストール

```bash
sudo apt install -y apache2
```

バージョン確認

```bash
pache2ctl -V
```
```plaintext:結果
Server version: Apache/2.4.58 (Ubuntu)
```

再起動後もApacheを起動させる

```bash
sudo systemctl enable apache2
```

### PHPのインストール

```bash
sudo apt install -y php
```

バージョン確認

```bash
php -v
```
```plaintext
PHP 8.3.6 (cli) (built: Jun 13 2024 15:23:20) (NTS)
```

`/var/www/html/` に `psql-test.php` を作成

```bash
sudo vi /var/www/html/psql-test.php
```

```php:/var/www/html/psql-test.php
<?php
phpinfo();
?>
```

ブラウザーで `localhost/psql-test.php` にアクセス

### PostgreSQLへの接続確認

```bash
sudo apt install -y php-pgsql
```

`/var/www/html/psql-test.php` を編集

```bash
sudo vi /var/www/html/psql-test.php
```

```php:/var/www/html/psql-test.php
<html>
<head><title>PHP TEST</title></head>
<body>

<?php
ini_set("display_errors", 1);
error_reporting(E_ALL);

$conn = "host=<サーバーPCのIPアドレス> port=<ポート番号> user=<ユーザー名> password=<パスワード> dbname=<データベース名>";
$link = pg_connect($conn);
if (!$link) {
    die('接続失敗です。'.pg_last_error());
}

echo('接続に成功しました。<br>');

// PostgreSQLに対する処理

$close_flag = pg_close($link);

if ($close_flag){
    echo('切断に成功しました。<br>');
}

?>
</body>
</html>
```