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)
1.
2.

官方文件：
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html

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

More info: http://www.postgresql.org/docs/8.1/static/monitoring-stats.html

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);

UPDATE table SET column = expression FROM fromlist WHERE condition;

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)
http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-DISTINCT

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:
http://www.postgresql.org/docs/8.3/static/indexes-ordering.html
Combining Multiple Indexes:
http://www.postgresql.org/docs/8.3/static/indexes-bitmap-scans.html

數值資料型態(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)
http://wordpress-pg.sourceforge.net/

Reference

15 Practical PostgreSQL Database Administration Commands
http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands/
psql: http://www.postgresql.org/docs/8.3/static/app-psql.html
Backup and Restore: http://www.postgresql.org/docs/8.3/static/backup.html
Numeric Types: http://www.postgresql.org/docs/8.3/static/datatype-numeric.html

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: 9,357
Login | Edit this page | PostgreSQL mailing list
Developed by MEPO Humanity Technology - http://mepo.cc