PostgreSQL

      

系統管理:啟用、備份、即時觀測

啟用 Database Server
1. Shell> su postgres
(切換使用者至 postgres。此動作為執行PostgreSQL相關指令前所必須。)

2. Shell> /usr/bin/postgres -D /usr/local/pgsql/data (路徑也可能是 /var/lib/pgsql/data)
or Shell> pg_ctl -D /usr/local/pgsql/data start (路徑也可能是 /var/lib/pgsql/data)

備份與還原
單一資料庫備份:
Shell> pg_dump -U postgres database | bzip2 > database_YYYYMMDD.bz2 (備份資料庫 database 至 database_YYYYMMDD.bz2)
單一資料庫還原:
Shell> psql -U postgres database < infile (將名為 infile 的備份檔還原到資料庫 database)

單一資料表備份:
Shell> pg_dump -U postgres -t table_name database > table_name_YYYYMMDD.sql
單一資料表還原:
Shell> psql -U postgres -f table_name_YYYYMMDD.sql database (The file is the backup from the above command.)

全資料庫備份:
/usr/bin/pg_dumpall > cluster_YYYYMMDD.sql (備份整個 cluster 至 cluster_YYYYMMDD.sql)
全資料庫還原:
/usr/bin/psql -f infile postgres (將 infile 這個備份檔還原到 postgres 這個 cluster。原則上 cluster 皆為 postgres)

系統統計資訊(Statistics Collector)

官方文件:

觀測伺服器即時活動 (Statistics Collector)
SELECT * FROM pg_stat_activity;


SQL語言用法速查

SELECT * FROM table WHERE contidion AND WHERE column BETWEEN number1 AND number2 ORDER BY column DESC/ASC LIMIT count;

INSERT INTO table (column1, column2) VALUES ('string', number);


DELETE FROM table WHERE condition; (reference)

GROUP BY
SELECT kind, sum(len) AS total FROM films GROUP BY kind; (reference)
Note: The column "kind" in GROUP BY must appear/be used by SELECT.

SELECT DISTINCT (ON)

PostgreSQL多功能互動指令:psql


psql 在PostgreSQL中非常好用。以下介紹常用的一些指令用法。

0. 在開始使用 psql 前請記得二件事:
第一,psql 路徑原則上多半在 /usr/bin/psql,使用時請記得打全域路徑,例如:

/usr/bin/psql -d database (連線至名為 "database" 的資料庫)

第二,請將使用者轉換為 PostgreSQL 所屬的使用者,原則上 default 為 postgres (Shell> su - postgres)

1. 列出所有現在的資料庫
1) psql -h localhost (連到資料庫,一般單純情況在「本機」中連到 localhost 即可)
2) l

或者直接打以下指令亦可:
/usr/bin/psql -l

2. 刪除 database (刪除前請三思)
這個指令多半是資料庫備份回復錯誤時使用。直接刪掉再回復比較快。

1) 先連到 databse:
psql -h localhost
2) 以 SQL 語法刪除名為 dbname 的 database
DROP DATABASE dbname;

3. 修改使用者密碼
1) su postgres (轉換使用者)
2) /usr/bin/psql -h localhost (連線至database,可輸入IP Address,若為本機輸入localhost即可)
3) /password username (更改名為 "username" 的使用者密碼)

Index進階使用與效能分析

Indexes and ORDER BY:
Combining Multiple Indexes:

數值資料型態(Numeric Data Type)速查表

NameStorage SizeDescriptionRange
smallint2 bytessmall-range integer-32768 to +32767
integer4 bytesusual choice for integer-2147483648 to +2147483647
bigint8 byteslarge-range integer-9223372036854775808 to 9223372036854775807
decimalvariableuser-specified precision, exactno limit
numericvariableuser-specified precision, exactno limit
real4 bytesvariable-precision, inexact6 decimal digits precision
double precision8 bytesvariable-precision, inexact15 decimal digits precision
serial4 bytesautoincrementing integer1 to 2147483647
bigserial8 byteslarge autoincrementing integer1 to 9223372036854775807

Comparing Reliability and Speed

Why PostgreSQL Instead of MySQL 2009 / PostgreSQL Wiki (2009)

WordPress (部落格) with PostgreSQL Database

WordPress-Pg v1.2.1-1.0 (Release 2004.10.6)

Reference

15 Practical PostgreSQL Database Administration Commands

PostgreSQL的論壇文章
[ 發表文章 ]
Install a LAPP server: openSUSE 42 / Apahce 2.4/PostgreSQL 9.4/ PHP 5.6 mepoadm2015/06/04
Create a Table with Incremental Primary Key mepoadm2014/06/25
Backup and Transfer a LAMP(LAPP) System: Linux/Apache2/PostgreSQL/PHP mepoadm2013/08/09
參數設定與效能校調(Configuration):VACUUM / Automatic VACUUM HP2012/09/04
Built-in Replication - Re: [心得] 看來很多人要開始用 PostgreSQL - [新聞] 專利權大戰 社群網站無法倖免 / 2011.09 HP2011/09/06
心得:看來很多人要開始用 PostgreSQL - [新聞] 專利權大戰 社群網站無法倖免 2011.09 HP2011/09/05
Postgresql 和 MySQL 語法相異處整理:Create Table, KEY / INDEX, BIGINT / INT UNSIGNED HP2011/09/03
避免註冊會員「使用者名稱重複」(case-insensitive):Functional indexes & lower() search mepoadm2010/09/26
Performance Issue in PostgreSQL: Counting rows in a table HP2010/02/12
PostgreSQL的數值資料型態(Numeric Data Types)速查表 HP2009/10/09
[ 瀏覽文章 ]

PostgreSQL的分類地圖


Views: 8,849