> For the complete documentation index, see [llms.txt](https://easonwang.gitbook.io/web_advance/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://easonwang.gitbook.io/web_advance/shi-yong-postgresql.md).

# 使用PostgreSQL

## 使用PostgreSQL

下載頁面:<http://www.enterprisedb.com/products-services-training/pgdownload>

## 目前建議用 docker 快速架設

使用 docker-compose.yml

> 一樣要建立 volumn 避免重啟後資料消失

```
# Use postgres/example user/password credentials
version: '3.1'

services:

  db:
    image: postgres
    restart: always
    volumes:
      - "./dbdata:/var/lib/postgresql/data"
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: superuser
      POSTGRES_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
```

<https://hub.docker.com/_/postgres>

## 安裝

<https://www.godaddy.com/garage/how-to-install-postgresql-on-ubuntu-14-04/>

```
1.下載
apt-get install postgresql postgresql-contrib

2.然後設定開機自動啟動
sudo update-rc.d postgresql enable

3.啟動postgresql
sudo service postgresql start
```

之後輸入以下進入postgres使用者

```
sudo -u postgres -i
```

進入psql

```
psql
```

更改預設密碼，進入psql後輸入以下

```
\password postgres
```

### Mac安裝

```
brew install postgres
pg_ctl -D /usr/local/var/postgres start
createdb mydb
psql mydb // 進入DB
```

<https://medium.com/@Umesh_Kafle/postgresql-and-postgis-installation-in-mac-os-87fa98a6814d>

### 1.使用pgAdmin

<https://www.pgadmin.org/download/>

一個可視化的網頁瀏覽工具

> 如果使用本地連線時輸入localhost比較好，因為有時127.0.0.1無作用
>
> 如果出現can't connect application server，重新啟動一次即可。

**如要查看TABLE**

```
點選數據庫=>架構=>數據表
```

**如要查看row**

```
點選數據表下的table名稱，在點選最上方工具(T)下面的表格圖案
```

**執行SQL**

點選 Tools 然後按 Query Tool

### PgAdmin連線遠端主機的DB

1.設定pg\_hba.conf

```
sudo vim /etc/postgresql/9.5/main/pg_hba.conf

然後加入以下

host all all 0.0.0.0/0  md5
```

> 分別為DB, User, IP 與方法

2.設定postgresql.conf

```
listen_addresses = '*'
```

3.重啟：

```
sudo service postgresql restart
```

4.開啟遠端主機防火牆，port：5432

然後即可使用PgAdmin遠端連線。

可參考：<https://cloud.google.com/community/tutorials/setting-up-postgres>

### 2.使用SQL shell ( psql )

> 輸入psql <資料庫名稱>

```
記得每個指令結尾要加 ;
使用 \q 離開。
```

```
指令類似於 MySQL 的 SQL 指令
```

## 使用Log

> 預設只會存在psql 操作的log

log路徑：`/var/log/postgresql/postgresql-10-main.log`

1.首先要設定config: `/etc/postgresql/10/main/postgresql.conf`

2\.

```
- change the log_statement setting to 'all'.
- turned on the log_destination
- turn on the logging_collector
```

3.重啟config

```
進入psql後輸入
SELECT pg_reload_conf();
```

參考至:

<http://www.postgresql.org/docs/8.3/static/tutorial-table.html>

中文版:

<http://twpug.net/docs/postgresql-doc-8.0-zh_TW/tutorial-populate.html>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://easonwang.gitbook.io/web_advance/shi-yong-postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.