VPSを借りる [PostgreSQL] #4

PostgreSQL

※How to Install PostgreSQL on CentOS 8

DOMAIN=yourdomain

PORT=5432
DATABASE=yourdb
USERNAME=yourname
PASSWORD=yourpass

sudo dnf install @postgresql postgresql-contrib

sudo postgresql-setup --initdb --unit postgresql
sudo systemctl enable --now postgresql

sudo -u postgres -i psql -c "ALTER ROLE postgres WITH PASSWORD '$PASSWORD';"

sudo -u postgres -i psql -c "CREATE ROLE $USERNAME WITH LOGIN PASSWORD '$PASSWORD';"
sudo -u postgres -i psql -c "CREATE DATABASE $DATABASE;"
sudo -u postgres -i psql -c "GRANT ALL PRIVILEGES ON DATABASE $DATABASE TO $USERNAME;"

sudo -u postgres tee /var/lib/pgsql/.pgpass << EOF
*:$PORT:*:postgres:$PASSWORD
*:$PORT:$DATABASE:$USERNAME:$PASSWORD
EOF

sudo chmod 600 /var/lib/pgsql/.pgpass

sudo cp -p /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.origin
sudo sed -i -e "s/^local\s.*all\s.*all\s.*peer/local   all             all                                     md5/g" /var/lib/pgsql/data/pg_hba.conf
sudo diff -u /var/lib/pgsql/data/pg_hba.conf.origin /var/lib/pgsql/data/pg_hba.conf

sudo systemctl restart postgresql

sudo -u postgres -i psql -U $USERNAME -d $DATABASE -c "CREATE TABLE staff (id CHAR(4) NOT NULL, name TEXT NOT NULL, age INTEGER, PRIMARY KEY (id));"
sudo -u postgres -i psql -U $USERNAME -d $DATABASE -c "INSERT INTO staff VALUES (1, 'Yamada Taro', 26);"
sudo -u postgres -i psql -U $USERNAME -d $DATABASE -c "INSERT INTO staff VALUES (2, 'Suzuki Hanako', 21);"
sudo -u postgres -i psql -U $USERNAME -d $DATABASE -c "SELECT * FROM staff;"

sudo dnf install php-pgsql

sudo tee /var/www/$DOMAIN/public_html/dbtest.php << EOF
<?php
  \$db = pg_connect("dbname=$DATABASE user=$USERNAME password=$PASSWORD");
  if(!\$db){
    echo "Error: Unable to open database.<br />\n";
    exit;
  } else {
    echo "Opened database successfully.<br />\n";
  }

  \$result = pg_query(\$db, "SELECT * FROM staff"); 
  if(!\$result) {
    echo "Error: Unable to select table.<br />\n";
    exit;
  }

  while (\$row = pg_fetch_row(\$result)) {
    echo "\$row[0], \$row[1], \$row[2]<br />\n";
  }

  pg_close(\$db);
?>
EOF

curl https://$DOMAIN/dbtest.php

VPSを借りる [VPS、SSH、Nginx] #1
VPSを借りる [HTTPS] #2
VPSを借りる [PHP] #3