DataStreamを設定する方法

Table of Contents

demo siteを作成

compute engineを立ち上げる

省略

compute engineを設定

SSHをクリックして、以下をまず実行

sudo apt update
sudo apt install apache2 php libapache2-mod-php -y
sudo apt install postgresql postgresql-contrib -y

JavaScript

sudo apt-get install php-pgsql

JavaScript

sudo systemctl restart apache2

JavaScript

sql databaseを設定

sudo -u postgres psql

JavaScript

databaseとテーブルを作成

CREATE DATABASE myappdb;

SQL

ユーザーとパスワードを作成

CREATE USER gaozhan WITH PASSWORD 'postgres_pwd';

SQL

ユーザーに権限を付与する

GRANT ALL PRIVILEGES ON DATABASE myappdb TO gaozhan;
GRANT ALL PRIVILEGES ON TABLE users TO gaozhan;
GRANT USAGE, SELECT, UPDATE ON public.users_id_seq TO gaozhan;

ALTER ROLE gaozhan WITH REPLICATION;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gaozhan;
GRANT USAGE ON SCHEMA public TO gaozhan;
ALTER DEFAULT PRIVILEGES IN SCHEMA public  
GRANT SELECT ON TABLES TO gaozhan;

SQL

作成したDBに遷移する

\\c myappdb

ShellScript

パブリケーションを作成

CREATE PUBLICATION datastream_publication_all_tables FOR ALL TABLES;

SQL

リプリケーションを作成

SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('datastream_replication_slot_myappab', 'pgoutput');

SQL

テーブルを作成する

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    gender VARCHAR(50),
    company VARCHAR(100)
);

SQL

データベースを閉じる

\\q

SQL

データベースを再起動

sudo systemctl restart postgresql

ShellScript

web siteを作成

front

以下のコードを使う

sudo nano index.html

ShellScript

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Simple Data Collection Form</title>
</head>
<body>
    <form action="form-handler.php" method="post">
        <label for="name">Name:</label>
        <input type="text" id="name" name="name"><br>
        <label for="age">Age:</label>
        <input type="number" id="age" name="age"><br>
        <label for="gender">Gender:</label>
        <select id="gender" name="gender">
            <option value="male">Male</option>
            <option value="female">Female</option>
            <option value="other">Other</option>
        </select><br>
        <label for="company">Company:</label>
        <input type="text" id="company" name="company"><br>
        <input type="submit" value="Submit">
    </form>
</body>
</html>

JavaScript

sudo touch form-handler.php
sudo nano form-handler.php

ShellScript

backend

以下をコピーする

form-handler.php

<?php
// Display all errors, good for development
ini_set('display_errors', 1);
error_reporting(E_ALL);

// Database credentials
$host = "localhost";
$dbname = "myappdb";
$user = "gaozhan";
$password = "postgres_pwd";

// Connect to the database
try {
    $conn = new PDO("pgsql:host=$host;dbname=$dbname", $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
} catch (PDOException $e) {
    die("Could not connect to the database $dbname :" . $e->getMessage());
}

// Only proceed if the form data is being posted
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = $_POST['name'];
    $age = $_POST['age'];
    $gender = $_POST['gender'];
    $company = $_POST['company'];

    // Prepare the SQL statement
    $stmt = $conn->prepare("INSERT INTO users (name, age, gender, company) VALUES (:name, :age, :gender, :company)");

    // Bind parameters
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':age', $age);
    $stmt->bindParam(':gender', $gender);
    $stmt->bindParam(':company', $company);
    
    // Execute and check if the statement was successful
    if ($stmt->execute()) {
        // Redirect to a confirmation page or back to the form page with a success message
        header('Location: success.html');
        exit();
    } else {
        // Output error information
        $errorInfo = $stmt->errorInfo();
        die("Error entering data: " . $errorInfo[2]);
    }
}

// Close the connection
$conn = null;
?>

JavaScript

thanks page

sudo touch success.html
sudo nano success.html

ShellScript

以下をコピーする

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Form Submission Successful</title>
</head>
<body>
    <p>Thank you for submitting the form. We have received your data.</p>
</body>
</html>

JavaScript

databaseにホワイトリストを追加する

sudo nano /etc/postgresql/13/main/pg_hba.conf

ShellScript

以下のIPをホワイトリストに追加する

host    myappdb         gaozhan         34.72.28.29/32          md5
host    myappdb         gaozhan         34.67.234.134/32        md5
host    myappdb         gaozhan         34.67.6.157/32          md5
host    myappdb         gaozhan         34.72.239.218/32        md5
host    myappdb         gaozhan         34.71.242.81/32         md5

ShellScript

こちらでGCP各リージョンのIPを確認できる

https://cloud.google.com/datastream/docs/ip-allowlists-and-regions?hl=ja

共有を許可する

sudo nano /etc/postgresql/13/main/postgresql.conf

ShellScript

以下のように修正

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

wal_level = logical                     # minimal, replica, or logical
                                        # (change requires restart)

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

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