[Postgres] - Create Postgres app on fly.io
Free 方案可以免費部署 2 個 App。
- VM size: `shared-cpu-1x`
- Memory: 256 mb
在操作 fly.io 的過程，都依靠 flyctl 這個 command line tool 來處理，所以這邊要先 Install
以 MacOS 為例:
1
|
curl -L https://fly.io/install.sh | sh
|
官網: Install
在部署前要先 Login
依照提示很簡單的 Login 進去
登入成功如圖:
flyctl postgres create
- App Name: phil-postgres-test
- Select region: Japan
- Select configuration: Development - Single node, 1x shared CPU, 256MB RAM, 1GB disk
接著等待 Deploy 程序完成，會給我們基本 DB connection 的資訊
會給以下這些資訊:
Creating postgres cluster phil-postgres-test in organization personal
Postgres cluster phil-postgres-test created
Username: postgres
Password: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hostname: phil-postgres-test.internal
Proxy Port: 5432
Postgres Port: 5433
Save your credentials in a secure place -- you won't be able to see them again!
Any app within the xxxxxxxxxx organization can connect to postgres using the above credentials and the hostname "phil-postgres-test.internal."
For example: postgres://postgres:<Password>@phil-postgres-test.internal:5432
Now that you've set up postgres, here's what you need to understand: https://fly.io/docs/reference/postgres-whats-next/
等等連接 DB 時需要用到
1
2
3
|
flyctl postgres connect -a <postgres-app-name>
# ex
flyctl postgres connect -a phil-postgres-test
|
成功!
1
2
3
4
|
# proxy local port 15432 到遠端的 5432
flyctl proxy 15432:5432 -a <postgres-app-name>
# ex
flyctl proxy 15432:5432 -a phil-postgres-test
|
接著
1
|
psql postgres://postgres:<Password>@localhost:15432
|
或是
1
2
|
psql -h localhost -p 15432 -U postgres
# 接著輸入密碼
|
成功!
- 看目前 connection info
\conninfo
1
2
3
|
\conninfo
# You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "15432".
|
- 看 current user
SELECT current_user;
1
2
3
4
5
6
|
SELECT current_user;
# current_user
# --------------
# postgres
# (1 row)
|
- 看目前 active 的 postgres connection
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT pid as process_id,
usename as username,
datname as database_name,
client_hostname,
application_name,
backend_start,
state,
wait_event,
state_change
FROM pg_stat_activity WHERE state = 'active';
# process_id | username | database_name | client_hostname | application_name | backend_start | state | wait_event | state_change
# ------------+----------+---------------+-----------------+------------------+------------------------------+--------+------------+-------------------------------
# 17794 | postgres | postgres | | psql | 2022-10-11 16:16:07.46128+00 | active | | 2022-10-11 16:42:35.424708+00
(1 row)
|
- 列出所有 available 的 databases
\l
\l
- Connect to 指定 DB
\c <DB 名> <user 名>
1
2
3
4
5
|
# 如果 user 名不給，會使用 current user
\c postgres
# psql (14.5, server 14.4 (Debian 14.4-1.pgdg110+1))
# You are now connected to database "postgres" as user "postgres".
|
- 列出所有 users 和 他們的 roles
\du
1
2
3
4
5
6
7
|
\du
# Role name | Attributes | Member of
# ------------+------------------------------------------------------------+-----------
# flypgadmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
# postgres | Superuser | {}
# repluser | Replication | {}
|
1
2
3
4
|
\s
# 將 command history 存進 file
\s <file name>
|
- 開啟 query execution time
\timing
1
2
3
4
5
6
7
8
|
\timing
# Timing is on.
# 再下一次 command 就可以關閉
\timing
# Timing is off.
|
- Help 並指定特定 command
\h <要查詢的相關 command>
1
2
3
4
|
\h <要查詢的相關 command>
# ex
\h ALTER TABLE
|
Ref:
- The End -