csvファイルをデータベースに取り込む方法_試行錯誤3（未解決）_WinSCPを使ってcsvファイルをVagrantにアップ_『OSS-DB標準教科書』5

はじめに

『オープンソースデータベース標準教科書 -PostgreSQL-（Ver.2.0.0）Kindle版』を使ってPostgreSQLの操作を学習中です。

この教科書の
4.演習　--4.2演習2:郵便番号データベース
を進めています。

つまずいたこと

DB(DBeaver/PostgreSQL)にCSVファイル（郵便番号データ）をインポートしDB内の「テーブル zip」のレコードデータにしたい。が、エラーになってインポートできない。

【これまでの経緯】
・csvファイルをデータベースに取り込む方法_試行錯誤1（未解決）『OSS-DB標準教科書』3
・csvファイルをデータベースに取り込む方法_試行錯誤2（未解決）『OSS-DB標準教科書』4

環境

ホストPC：Windows10
GUI DBツール：DBeaver
仮想化ソフト：VirtualBox（バージョン6.1.18）
ゲストPC：Vagrant(＝CentOS7＝仮想マシン）（バージョン6.1.18）
コンテナ型仮想環境：Docker
CUI DBクライアント：PostgreSQL

【メモ】
Vagrantのバージョン確認コマンド＝「vagrant -v」or「vagrant --version」

前提条件

前提条件＝必要なソフトがPCにインストールされ、上記環境が立ち上がっている状態。

↓立ち上げ方法（「【DBeaver】PostgreSQLへの再接続でエラーが出たので対処。「接続試行がタイムアウトしました----2-3.Dockerを起動」に書いたものと同。）

PowerShell

<PowerShellを立ち上げて>

(ディレクトリ移動)
cd C:\Users\***\dev\Vagrant-Docker-PostgreSQL-DBeaver-main

(カレントディレクトリ(現在の位置のディレクトリ)にあるファイルとサブディレクトリの一覧を表示)
dir

(Vagrant起動)
vagrant up

(Vagrantにssh接続)
vagrant ssh
<Vagrantを利用してCentOS7に入れた>

<次はDocker。Dockerディレクトリに入る>
(dockerに移動)
cd docker
(現在の作業ディレクトリを表示)
pwd
(ls＝「list segments」の略でディレクトリやファイルの情報を表示。la＝？？？)
ls -la

(Dockerコンテナを起動)
docker-compose up -d

（Dockerコンテナに入ってPostgreSQLを操作する）
docker exec -it postgres_container /bin/bash

<psql（CUI DB クライアント）に入る>
（psqlにログインする）
psql -U user first_tutorial
（↑　psql -U <ユーザー名> <データベース名>　）

（コード引用元：
限定公開YouTube動画「環境構築データベース PostgreSQL」(作成者=黒澤さん @kurosawa_kuro)）

するとPowerShellの#のディレクトリが「first_tutorial=#」という表示になります。
Dockerを通してPostgreSQLを操作する状態になりました！

このタイミングでDBeaverのfirst_tutorialを右クリックし「接続」をクリックし、
DBeaverでPostgreSQLに接続します。

WinSCPでcsvファイルをDBeaverにアップロードする

0-1.作業を始める前に、余談

試行錯誤1、試行錯誤2 を経て、黒澤さんから
「Dbeaverのインポート機能に問題があると仮定した方が良いかも」
とアドバイスいただきました。

以前黒澤さんより
「そもそもlinux(Vagrant)上にcsvファイルが格納されていないのが問題では？
こういうのでファイルをアップロードできる」
と教えていただいた記事

・【Windows10版】Virtualbox × Vagrant WinSCPクライアントでFTP接続してみた。@silversink8888

を見ながら、WinSCPでCSVファイルをアップロードしてみます。

0-2.WinSCPとは？用途は何？

・SCP
→ファイルを暗号化し、安全にファイルをやり取りする方法。SSH（Secure Shell）と呼ばれる機能によってセキュリティの高いファイル転送ができる。
→認証情報ややり取りされるデータのすべてを暗号化して通信することができる。
→どんなOSにも基本的に備わっている機能。

・WinSCP
→WindowsPCで簡単に使うことができるSCP。
（参照：下記）

MS-Windows上で動く オープンソースでグラフィカルなFTP、FTPS、SFTPクライアントプログラムです。旧来のSCPプロトコルもサポートします。このプログラムの主な機能は、ローカルコンピューターとリモートコンピュータ間で安全にファイルをコピーすることです。これに加え、WinSCPはスクリプトと基本的なファイルマネージャー機能を提供します。

（引用元：WinSCPとは-WinSCP公式HP）

・

ネットワーク越しに安全に情報をやり取りする手段の一つに、SCP（Secure CoPy）という方法があります。
SCPは自分のPCから相手のPC（あるいはサーバ）にファイル転送をする際、転送対象のファイルを暗号化することができるため、転送中にファイルの中身が読み取られることはありません。
そんなSCPはどんなOSにも基本的に備わっている機能ではありますが、そのまま利用するには少々わかりにくいこともあります。
WinSCPであれば、WindowsのPCでSCPを簡単に使うことができます。

（引用元：Windowsだけで使える！WinSCPの使い方【初心者向け】現役エンジニアが解説-TechAcademyマガジン2014/02/15記事）

・

SCPは、Secure Copy Protocolの略で、FTP（File Transfer Protocol）やHTTP（Hypertext Transfer Protocol）などと同じく、通信に使われるプロトコルの一種です。
SCPをFTPと同じ用途で使っているとのことですので、この2つで比較してみましょう。
FTPはその名の通り、ファイルを転送するための通信プロトコルですが、インターネット創世記から利用されている古いもので、ユーザ名やパスワードなど認証情報を含むすべての通信内容を暗号化せず転送するなど、多くのセキュリティ上の脆弱性が指摘されています。
一方、SCPはSSH（Secure Shell）と呼ばれる機能によってセキュリティの高いファイル転送ができる次世代のFTPと言えるかもしれません。
SCPは、認証情報ややり取りされるデータのすべてを暗号化して通信することができます。

（引用元：最近FTPではなくSCPというものを見かけます）-FELLOWS）

（その他参考：SCPとSFTPの違い-SKYARCH BROADCASTING 2014/7/23記事）

0-3.SCPとSFTPの違い

◆SCPとSFTPの共通点◆
・SCPもSFTPも、SSHの機能の１つとして提供される。
・SSHを利用して暗号化したファイル転送を行うことができる。
・「WinSCP」や「FileZilla」などのクライアントもリリースされており、これを利用すれば、Windowsとの間でファイル転送を行うこともできる。
・ファイルの内容はもちろん、ログイン時のアカウント情報を平文でなくSSHによって暗号化された情報としてネットワークに流すことができ、セキュアなファイル転送を実現。

-	SCP	SFTP
利用方法	CPコマンドに似せて利用できる	FTPでのファイルのやり取りに似た方法で利用できる
機能	比較的シンプルに作られており軽い	多機能
ファイル転送中断時の動作	再開が可能	再開不可能

※ファイルの内容は知られたくないが転送はしたいという場合、FTPなどを利用するよりもSCPもしくはSFTPを利用するのがセキュリティ上は好ましいと言える。

（参考：SCPとSFTP...LPI-Japan Linux豆知識049）

1.WinSCPをインストール

こちらの記事
「「WinSCP」使い方とインストール」-FREESoftconcierge 2020/12/13更新記事
を参考にして、WinSCPをWindowsPCにインストールしました。

2.サーバーに接続

1）デスクトップにできたWinSCPのショートカットキーをクリック
↓
2）このような画面が現れます。

「転送プロコトル」の選択肢を、どれを選んでよいのかがわからなかったのでそれぞれ意味を調べました。

---

・SFTP
→sshで暗号化された通信路を使って安全にファイルを送受信するプロトコル。
（ssh=（secure shell）ネットワークを通じて他のコンピュータに接続し、文字列による対話的な操作を行うことができるソフトウェアおよび通信プロトコルの一つで、送受信するデータをすべて暗号化して改竄や覗き見から保護することができる。SFTPはssh接続で形成された暗号化された伝送路を用いてファイルの送受信を行うもので、ssh上で「sftp」というコマンドを発行してファイルの指定や転送の開始などを指示する。コマンド体系などは著名なファイル転送プロトコルのFTP（File Transfer Protocol）によく似ており、同じような方法で利用できるが、詳細な仕様や実装はFTPそのものとは異なる。（引用元：IT用語辞典e-Words））
（プロコトル＝コンピューター同士の通信をする際の手順や規格のこと。情報を送り出す端末の選定、データの形式、パケットの構成、エラーの対処などを取り決めた通信の約束事（引用元：大塚商会））

・SCP
→上記と同（→ファイルを暗号化し、安全にファイルをやり取りする方法。SSH（Secure Shell）と呼ばれる機能によってセキュリティの高いファイル転送ができる。）

・FTP
→ファイルを送受信する時に使う決まりごと（通信プロトコル）。WEBサイトを公開するときに使用され、作成したHTMLなどのファイルをサーバーにアップロードするとき、また更新するときに使用される。
（参考：【初心者向け】FTPとは？仕組みから利用方法まで解説！）

・WebDAV
→サーバー上のファイルを読み取りや編集を、Webブラウザ上で行えるようにする仕組み。
（参考：WebDAVとは？その機能と使い方）

・Amazon S3
→AWSのストレージサービス。
（参考：AQS HP----Amazon S3）

---

えーっと・・・どれを選べば良いのだろうか？？結局選び方がわからない。
AmazonS 3では無いな。
見本ではデフォルトの「SFTP」となっているし、デフォルトで選択されているので、
とりあえず「SFTP」を選択します。

次は
・ホスト名
・ユーザー名
・パスワード
です。

今回やりたいことは、「WinSCPを用いてWindowsPCにあるcsvファイルをPostgreSQLに読み込ませる」ことです。
PostgreSQLは「 ホストPC > VirtualBox > Vagrant(CentOS7) > Docker > PostgreSQL 」というように「CentOS7の中のDockerの中」に入っています。
なので、このホスト名のところには、CenstOS7のIPアドレス（192.168.33.10）を入力します。
ポート番号も「5432」に変更。

ユーザー名＝CentOS7のユーザー名
パスワード＝CentOS7のパスワード
を入力したいのですが、ユーザー名&パスワード、それぞれ何だっけ？？

前提条件に書いた環境立ち上げの前に、必要なソフトをPCにインストールする段階がありました。
今回は黒澤さんがGitHubに用意してくださったファイルをPCにダウンロードして、
そのファイルをPowerShellから開いて→「vagrant up --provision」「vagrant ssh」で起動することで
Vagrantを通してCentOS7に入る、という方法を取りました。

黒澤さんのGitHubのWiki欄に「基本」としてvagrant up時のPowerShell画面がアップされていました。
コードの内容は下記の通り。

（コード引用：黒澤さん @kurosawa_kuro のGitHub「kurosawa-kuro/Vagrant-Docker-PostgreSQL-DBeaver…基本…Wiki」）

PowerShell

vagrant box add centos/7
vagrant box list
vagrant init centos/7
vagrant up
vagrant ssh

vagrant up 実行後に出たコマンドの一部が

default: SSH address: 127.0.0.1:2200
default: SSH username: vagrant
default: SSH auth method: private key

でした。

この「vagrant」をCentOS7のユーザー名とみなすのかな？
ユーザー名がvagrantだとしたら、パスワードもvagrantとしたのだろうか？？

/////////////////////////////////////////
転送プロコトル：SFTP
ホスト名：192.168.33.10（CenstOS7のIPアドレス）
ポート番号：5432（デフォルトの"22"から変更）
ユーザ名：vagrant
パスワード：vagrant
/////////////////////////////////////////
で試してみる。

（後日追記：↑なぜポート番号5432を選んだろう？DBeaverでPostgreSQLに接続する際に使っていたポート番号が5432だったからかな？多分そう。だが自分の行動が謎。）

上記を入力して「ログイン」をクリック。

↓

この画面が40秒ほど続いて・・・
↓
エラー。

3.やり直し！サーバーに接続-2-

↓こちらの記事を参考に進めてみます。
参考記事①＝「Vagrantで作成したVMにWinSCPでログインする」-@SRsawaguchi さんのQiita

/////////////////////////////////////////
転送プロトコル：SCP
ホスト名：localhostまたは127.0.0.1
ポート番号：Vagrantfileで設定したホスト側のポート番号
ユーザ名：vagrant
パスワード：空欄
/////////////////////////////////////////

参考記事①に従い、これ↑を入力する。
ホスト名とポート番号については、vagrant up したときの↓の赤枠部分が大切な気がする。

/////////////////////////////////////////
ホスト名：127.0.0.1
ポート番号：2222
/////////////////////////////////////////
と入力してみる。

↓
何やら出てきた。

↓
参考記事①に「秘密鍵の設定」という項があった。この設定をしていないから、
こんな画面が出たのかな？
一度キャンセルして、「秘密鍵の設定」をしてみる。
「キャンセル」をクリック。
↓
「エラー：ホストの鍵が証明されませんでした。」と出てきたので「OK」をクリック。
↓
「設定」をクリック
↓
秘密鍵欄に「C:\Users\***\dev\Vagrant-Docker-PostgreSQL-DBeaver-main.vagrant\machines\default\virtualbox\private_key」と入力→「OK」。
（「C:\Users\**\dev\Vagrant-Docker-PostgreSQL-DBeaver-main\」はVagrantfileがあるフォルダ。）

↓
こんなエラーが出た。「無視」をクリック。

↓
「保存」をクリックし「セッションの保存名」を「forOSSDB」と変更し、「OK」。
↓
また以前と同じ警告発生。

↓
「はい」
↓
エラー。

・・・なにがダメなんだろう？？

↓
↓
↓
今、PowerShellで入力してあるのは、「vagrant up」まで。
「# psql -U user first_tutorial」まで入力した状態で、
WinSCPを上記と同じ設定にして「ログイン」をクリックすれば、つなげるかな？
↓
やってみた。が、下記の通りのエラー。

「No supported authentication methods available (server sent:publickey.gssapi-keyec,gssapi-with-mic)
認証ログ (詳細はセッション ログを見て下さい):
Unable to use key file "C:\Users\***\dev\Vagrant-Docker-PostgreSQL-DBeaver-main.vagrant\machines\default\virtualbox\private_key" (OpenSSH SSH-2 private key (old PEM format))
ユーザ名"vagrant" を使用中」

今は解決できず&黒澤さんアドバイス

色々と助言もいただきながら試行錯誤しましたが、
「DBeaver(PostgreSQL)にcsvファイルを読み込む」ことが、結局できませんでした。
そうこうしている内に黒澤さんより
「WSL2でのpostgresの方が圧倒的に外部ファイルを扱いやすい」と、
WSL2を使ってみようという助言をいただきました。

ひとまず今の問題は置いておき、
WSL2に手を出してみようと思います。

2021/6/29追記：0-3.SCPとSFTPの違い