---
title: SBIR WRB (07-30-2021)
tags: sbir-star
robots: noindex, nofollow
---

<!-- 研究主題類別: 市場調查、通訊模組、無線技術 -->
PostgreSQL資料庫 (07-30-2021)
===

[toc]

### PostgreSQL 12

:::info
- ip addr: 192.168.1.117
- port: 5432
- username/password: postgres/postgres
- database: thingsboard
:::
> Prepare another Ubuntu 20.04.2 VM running up PostgreSQL 12 (bridged mode, ip addr: 192.168.1.117)

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql-12
sudo service postgresql status

> Set the password for main postgresql user

sudo su - postgres
psql
\password
\q

> Connect to the database to create **thingsboard** DB

psql -U postgres -d postgres -h 127.0.0.1 -W
CREATE DATABASE thingsboard;
\q

> Configure ThingsBoard to use external database (change localhost to 192.168.1.117)
* TB configuration file, ./application/src/main/resources/thingsboard.yml
* TB env variable file, ./application//src/main/conf/thingsboard.conf

# Edit thingsboard.yml
440 # SQL DAO Configuration
441 spring:
442   data:
443     jpa:
444       repositories:
445         enabled: "true"
446   jpa:
447     open-in-view: "false"
448     hibernate:
449       ddl-auto: "none"
450     database-platform: "${SPRING_JPA_DATABASE_PLATFORM:org.hibernate.dialect.PostgreSQLDialect}"
451   datasource:
452     driverClassName: "${SPRING_DRIVER_CLASS_NAME:org.postgresql.Driver}"
453     url: "${SPRING_DATASOURCE_URL:jdbc:postgresql://localhost:5432/thingsboard}"
454     username: "${SPRING_DATASOURCE_USERNAME:postgres}"
455     password: "${SPRING_DATASOURCE_PASSWORD:postgres}"
456     hikari:
457       maximumPoolSize: "${SPRING_DATASOURCE_MAXIMUM_POOL_SIZE:16}"

> Enable remote access to your PostgreSQL serv
* Edit /etc/postgresql/12/main/postgresql.conf

# line 58
- #listen_addresses = 'localhost'
+ listen_addresses = '*' 

sudo service postgresql restart

* Edit /etc/postgresql/12/main/pg_hba.conf

# The user postgres can access all databases from a trusted location (192.168.1.106) without a password
host    all             postgres            192.168.1.106            trust

* Using UFW to manage firewall rules

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432


---
＊**見證人**

---