Database - PostgreSQL

(最後更新 : 2016-05-01)

介紹

和 MySQL 一樣都是關聯型資料庫，一樣使用 SQL 來執行 query

正確念法為 post-gress-Q-L 或簡稱 postgres

安裝

安裝 Postgres

Ubuntu : todo…

Mac :

brew install postgres

初始化 Database

安裝完要始初化一個 Database, 而它的 owner 就是執行這指令的 User

initdb /usr/local/var/postgres -E utf8

Database 已存在

initdb: directory "/usr/local/var/postgres" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/usr/local/var/postgres" or run initdb
with an argument other than "/usr/local/var/postgres".

剛安裝完就已經有了，把它刪除再初始化一次 (??? 不確定是否有必要)

rm -r /usr/local/var/postgres

啟動 PostgreSQL

開機自動啟動

ln -sfv /usr/local/Cellar/postgresql/9.4.0/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents

啟動

postgres -D /usr/local/var/postgres
或
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

關閉

pg_ctl -D /usr/local/var/postgres stop -s -m fast

[Option] 安裝 Lunchy - 讓你方便的 start / stop 你的 postgres

gem install lunchy

啟動

lunchy start postgres

關閉

lunchy stop postgres

連到 console

以目前的 User 連線

psql

指定其他參數

psql -U username -d dbname -h 127.0.0.1

離開 console : \q 或 ctrl + d

Error `global/pg_filenode.map`

psql: FATAL:  could not open relation mapping file "global/pg_filenode.map": No such file or directory

可能是有些程序還沒刪乾淨，先刪除所有 pid 再重啟

killall postgres

Error `database does not exist`

psql: FATAL:  database "test" does not exist

建立 Database

createdb <user>

基本指令

command

建立 Database

createdb <user>

刪除 Database

dropdb <user>

console 指令

顯示已建立的 DB

command 下 :
psql -l

console 下 :
\l

連接 DB

\c dbname

顯示 Table

\d

Show the permission of table (it will list the permission of table that you designate e.g. SELECT,UPDATE,INSERT,DELETE

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='mytable'

Give permission of table to specific user

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username

Give permission of sequence to specific user

Insufficient privilege: 7 ERROR:  permission denied for sequence jobs_id_seq

GRANT USAGE, SELECT ON SEQUENCE jobs_id_seq TO username

Troubleshootings

database “XXX” is being accessed by other users

PG::ObjectInUse: ERROR:  database "example_development" is being accessed by other users
DETAIL:  There are 3 other sessions using the database.

config/initializers/postgresql_database_tasks.rb

module ActiveRecord
  module Tasks
    class PostgreSQLDatabaseTasks
      def drop
        establish_master_connection
        connection.select_all "select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='#{configuration['database']}' AND state='idle';"
        connection.drop_database configuration['database']
      end
    end
  end
end

重啟 Rails app, 並且 restart postgres, 建議手動把 postgres 的 pid 先殺光光再啟動

permission denied for sequence `field_name`

Error:

An exception occurred while executing 'SELECT NEXTVAL('jobs_id_seq')
Insufficient privilege: 7 ERROR:  permission denied for sequence jobs_id_seq

view privilege

table

select tablename from pg_tables where schemaname = 'public';

view

select table_name from information_schema.views where table_schema = 'public';

sequence

select sequence_name from information_schema.sequences where sequence_schema = 'public';

介紹

安裝