| 48 | | == 系統 == |
| | 48 | == 資料庫 == |
| | 49 | |
| | 50 | === 資料庫列表 === |
| | 51 | |
| | 52 | {{{ |
| | 53 | ~$ sudo su - postgres |
| | 54 | ~$ psql |
| | 55 | postgres=# \l |
| | 56 | List of databases |
| | 57 | Name | Owner | Encoding | Collate | Ctype | Access privileges |
| | 58 | -----------+----------+----------+-------------+-------------+----------------------- |
| | 59 | postgres | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | |
| | 60 | template0 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres + |
| | 61 | | | | | | postgres=CTc/postgres |
| | 62 | template1 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres + |
| | 63 | | | | | | postgres=CTc/postgres |
| | 64 | (3 rows) |
| | 65 | |
| | 66 | }}} |
| | 67 | |
| | 68 | === 新增資料庫 === |
| | 69 | |
| | 70 | {{{ |
| | 71 | ~$ sudo su - postgres |
| | 72 | -bash-4.2$ createdb 資料庫名稱 |
| | 73 | }}} |
| | 74 | |
| | 75 | === 允許使用者可以管理指定資料庫 === |
| | 76 | |
| | 77 | {{{ |
| | 78 | ~$ sudo su - postgres |
| | 79 | -bash-4.2$ psql -d postgres ### 使用 postgres 資料庫 |
| | 80 | postgres=# ALTER USER 使用者名稱 WITH ENCRYPTED PASSWORD '密碼'; |
| | 81 | postgres=# GRANT ALL PRIVILEGES ON DATABASE 資料庫名稱 TO 使用者名稱; |
| | 82 | }}} |
| | 83 | |
| | 84 | === 資料庫備份 === |
| | 85 | |
| | 86 | * 一般慣用 `pg_dump` 來進行備份 |
| | 87 | * 差別在於備份的格式，例如 `-Fd` 等同於 `--format=d` 是將備份資料存放於子目錄下，`-f 路徑` 指定存放路徑 |
| | 88 | {{{ |
| | 89 | pg_dump -Fd -f 存放路徑 資料庫名稱 |
| | 90 | }}} |
| | 91 | * 而 `-Fc` 等同於 `--format=c` 則是採用 PostgreSQL 自訂的格式 |
| | 92 | {{{ |
| | 93 | pg_dump --verbose --format=c --dbname=資料庫名稱 -f 存放路徑 |
| | 94 | }}} |
| | 95 | * 支援格式請參閱 man page 內容，'''需要注意的是 `pg_restore` 只支援 `c`, `d`, `t` 三種''' |
| | 96 | {{{ |
| | 97 | -F format |
| | 98 | --format=format |
| | 99 | Selects the format of the output. format can be one of the following: |
| | 100 | |
| | 101 | p |
| | 102 | plain |
| | 103 | Output a plain-text SQL script file (the default). |
| | 104 | |
| | 105 | c |
| | 106 | custom |
| | 107 | Output a custom-format archive suitable for input into pg_restore. |
| | 108 | Together with the directory output format, this is the most flexible |
| | 109 | output format in that it allows manual selection and reordering of |
| | 110 | archived items during restore. This format is also compressed by |
| | 111 | default. |
| | 112 | |
| | 113 | d |
| | 114 | directory |
| | 115 | Output a directory-format archive suitable for input into |
| | 116 | pg_restore. This will create a directory with one file for each |
| | 117 | table and blob being dumped, plus a so-called Table of Contents file |
| | 118 | describing the dumped objects in a machine-readable format that |
| | 119 | pg_restore can read. A directory format archive can be manipulated |
| | 120 | with standard Unix tools; for example, files in an uncompressed |
| | 121 | archive can be compressed with the gzip tool. This format is |
| | 122 | compressed by default and also supports parallel dumps. |
| | 123 | |
| | 124 | t |
| | 125 | tar |
| | 126 | Output a tar-format archive suitable for input into pg_restore. The |
| | 127 | tar format is compatible with the directory format: extracting a |
| | 128 | tar-format archive produces a valid directory-format archive. |
| | 129 | However, the tar format does not support compression. Also, when |
| | 130 | using tar format the relative order of table data items cannot be |
| | 131 | changed during restore. |
| | 132 | }}} |
| | 133 | |
| | 134 | === 資料庫還原 === |
| | 135 | |
| | 136 | * 一般慣用 `pg_restore` 來進行備份，須注意格式只支援 `c`, `d`, `t` 三種。 |
| | 137 | {{{ |
| | 138 | pg_restore --verbose --clean --if-exist --format=格式 --dbname=資料庫名稱 備份檔案名稱 |
| | 139 | }}} |
| | 140 | * 使用 `c` 格式，偶爾會遇到版本號碼不相容的問題 |
| | 141 | {{{ |
| | 142 | -bash-4.2$ pg_restore --verbose --clean --if-exist --format=c --dbname=資料庫名稱 /tmp/20180410.backup |
| | 143 | pg_restore: [archiver] unsupported version (1.13) in file header |
| | 144 | }}} |
| | 145 | |
| | 146 | |
| | 147 | == 系統效能 == |
| 210 | | |
| 211 | | |
| 212 | | == 資料庫 == |
| 213 | | |
| 214 | | === 資料庫列表 === |
| 215 | | |
| 216 | | {{{ |
| 217 | | ~$ sudo su - postgres |
| 218 | | ~$ psql |
| 219 | | postgres=# \l |
| 220 | | List of databases |
| 221 | | Name | Owner | Encoding | Collate | Ctype | Access privileges |
| 222 | | -----------+----------+----------+-------------+-------------+----------------------- |
| 223 | | postgres | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | |
| 224 | | template0 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres + |
| 225 | | | | | | | postgres=CTc/postgres |
| 226 | | template1 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres + |
| 227 | | | | | | | postgres=CTc/postgres |
| 228 | | (3 rows) |
| 229 | | |
| 230 | | }}} |
| 231 | | |
| 232 | | === 新增資料庫 === |
| 233 | | |
| 234 | | {{{ |
| 235 | | ~$ sudo su - postgres |
| 236 | | -bash-4.2$ createdb 資料庫名稱 |
| 237 | | }}} |
| 238 | | |
| 239 | | === 允許使用者可以管理指定資料庫 === |
| 240 | | |
| 241 | | {{{ |
| 242 | | ~$ sudo su - postgres |
| 243 | | -bash-4.2$ psql -d postgres ### 使用 postgres 資料庫 |
| 244 | | postgres=# ALTER USER 使用者名稱 WITH ENCRYPTED PASSWORD '密碼'; |
| 245 | | postgres=# GRANT ALL PRIVILEGES ON DATABASE 資料庫名稱 TO 使用者名稱; |
| 246 | | }}} |
| 247 | | |
| 248 | | === 資料庫備份 === |
| 249 | | |
| 250 | | * 一般慣用 `pg_dump` 來進行備份 |
| 251 | | * 差別在於備份的格式，例如 `-Fd` 等同於 `--format=d` 是將備份資料存放於子目錄下，`-f 路徑` 指定存放路徑 |
| 252 | | {{{ |
| 253 | | pg_dump -Fd -f 存放路徑 資料庫名稱 |
| 254 | | }}} |
| 255 | | * 而 `-Fc` 等同於 `--format=c` 則是採用 PostgreSQL 自訂的格式 |
| 256 | | {{{ |
| 257 | | pg_dump --verbose --format=c --dbname=資料庫名稱 -f 存放路徑 |
| 258 | | }}} |
| 259 | | * 支援格式請參閱 man page 內容，'''需要注意的是 `pg_restore` 只支援 `c`, `d`, `t` 三種''' |
| 260 | | {{{ |
| 261 | | -F format |
| 262 | | --format=format |
| 263 | | Selects the format of the output. format can be one of the following: |
| 264 | | |
| 265 | | p |
| 266 | | plain |
| 267 | | Output a plain-text SQL script file (the default). |
| 268 | | |
| 269 | | c |
| 270 | | custom |
| 271 | | Output a custom-format archive suitable for input into pg_restore. |
| 272 | | Together with the directory output format, this is the most flexible |
| 273 | | output format in that it allows manual selection and reordering of |
| 274 | | archived items during restore. This format is also compressed by |
| 275 | | default. |
| 276 | | |
| 277 | | d |
| 278 | | directory |
| 279 | | Output a directory-format archive suitable for input into |
| 280 | | pg_restore. This will create a directory with one file for each |
| 281 | | table and blob being dumped, plus a so-called Table of Contents file |
| 282 | | describing the dumped objects in a machine-readable format that |
| 283 | | pg_restore can read. A directory format archive can be manipulated |
| 284 | | with standard Unix tools; for example, files in an uncompressed |
| 285 | | archive can be compressed with the gzip tool. This format is |
| 286 | | compressed by default and also supports parallel dumps. |
| 287 | | |
| 288 | | t |
| 289 | | tar |
| 290 | | Output a tar-format archive suitable for input into pg_restore. The |
| 291 | | tar format is compatible with the directory format: extracting a |
| 292 | | tar-format archive produces a valid directory-format archive. |
| 293 | | However, the tar format does not support compression. Also, when |
| 294 | | using tar format the relative order of table data items cannot be |
| 295 | | changed during restore. |
| 296 | | }}} |
