切換和連接

$ sudo -u postgres psql

列出所有數(shù)據(jù)庫

postgres=# \l

連接到名為 postgres 的數(shù)據(jù)庫

postgres=# \c postgres

斷開

postgres=# \q
postgres=# \!

在 PostgreSQL 控制臺中運(yùn)行

顯示版本

SHOW SERVER_VERSION;

顯示系統(tǒng)狀態(tài)

\conninfo

顯示環(huán)境變量

SHOW ALL;

列出用戶

SELECT rolname FROM pg_roles;

顯示當(dāng)前用戶

SELECT current_user;

顯示當(dāng)前用戶的權(quán)限

\du

顯示當(dāng)前數(shù)據(jù)庫

SELECT current_database();

顯示數(shù)據(jù)庫中的所有表

\dt

列出函數(shù)

\df <schema>

列出數(shù)據(jù)庫

\l

連接到數(shù)據(jù)庫

\c <database_name>

顯示當(dāng)前數(shù)據(jù)庫

SELECT current_database();

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE <database_name> WITH OWNER <username>;

刪除數(shù)據(jù)庫

DROP DATABASE IF EXISTS <database_name>;

重命名數(shù)據(jù)庫

ALTER DATABASE <old_name> RENAME TO <new_name>;

列出當(dāng)前數(shù)據(jù)庫中的表

\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

全局列表

\dt *.*.
SELECT * FROM pg_catalog.pg_tables

列表表架構(gòu)

\d <table_name>
\d+ <table_name>
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

創(chuàng)建表

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);

創(chuàng)建表，主鍵自增

CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);

刪除表

DROP TABLE IF EXISTS <table_name> CASCADE;

成為 postgres 用戶，如果您有權(quán)限錯(cuò)誤

sudo su - postgres
psql

授予 對數(shù)據(jù)庫的所有權(quán)限

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

授予數(shù)據(jù)庫連接權(quán)限

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

授予架構(gòu)權(quán)限

GRANT USAGE ON SCHEMA public TO <user_name>;

授予函數(shù)權(quán)限

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

授予在所有表上選擇、更新、插入、刪除的權(quán)限

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

在表上授予權(quán)限

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

授予對表的選擇權(quán)限

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

添加欄目

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

更新欄

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];

刪除列

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

將列更新為自增主鍵

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;

使用自動遞增的主鍵插入表中

INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);
INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

選擇 所有數(shù)據(jù)

SELECT * FROM <table_name>;

讀取一行數(shù)據(jù)

SELECT * FROM <table_name> LIMIT 1;

搜索數(shù)據(jù)

SELECT * FROM <table_name> WHERE <column_name> = <value>;

插入 數(shù)據(jù)

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

更新 數(shù)據(jù)

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;

刪除 所有數(shù)據(jù)

DELETE FROM <table_name>;

刪除特定數(shù)據(jù)

DELETE FROM <table_name>
WHERE <column_name> = <value>;

列出 Schemas

\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;

創(chuàng)建架構(gòu)

CREATE SCHEMA IF NOT EXISTS <schema_name>;

刪除模式

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

• \lo_export LOBOID FILE
• \lo_import FILE [COMMENT]
• \lo_list
• \lo_unlink LOBOID

使用 pg_dumpall 備份所有數(shù)據(jù)庫

$ pg_dumpall -U postgres > all.sql

使用 pg_dump 備份數(shù)據(jù)庫

$ pg_dump -d mydb -f mydb_backup.sql

• ? -a ? 只轉(zhuǎn)儲數(shù)據(jù)，而不是模式(schema)
• ? -s ? 只轉(zhuǎn)儲模式，不轉(zhuǎn)儲數(shù)據(jù)
• ? -c ? 在重新創(chuàng)建之前刪除數(shù)據(jù)庫
• ? -C ? 還原前創(chuàng)建數(shù)據(jù)庫
• ? -t ? 僅轉(zhuǎn)儲命名表
• ? -F ? 格式(c：自定義，d：目錄，t：tar)

使用 pg_dump -? 獲取完整的選項(xiàng)列表

使用 psql 恢復(fù)數(shù)據(jù)庫

$ psql -U user mydb < mydb_backup.sql

使用 pg_restore 恢復(fù)數(shù)據(jù)庫

$ pg_restore -d mydb mydb_backup.sql -c

• ? -U ? 指定數(shù)據(jù)庫用戶
• ? -c ? 在重新創(chuàng)建之前刪除數(shù)據(jù)庫
• ? -C ? 還原前創(chuàng)建數(shù)據(jù)庫
• ? -e ? 如果遇到錯(cuò)誤退出
• ? -F ? 格式(c:自定義，d:目錄，t:tar，p:純文本sql(默認(rèn)))

使用 pg_restore -? 獲取完整的選項(xiàng)列表

獲取 postgresql.conf 的位置

$ psql -U postgres -c 'SHOW config_file'

附加到 postgresql.conf

listen_addresses = '*'

附加到 pg_hba.conf(與 postgresql.conf 相同的位置)

host  all  all  0.0.0.0/0  md5
host  all  all  ::/0       md5

重啟 PostgreSQL 服務(wù)器

$ sudo systemctl restart postgresql

將表格導(dǎo)出為 CSV 文件

\copy table TO '<path>' CSV
\copy table(col1,col1) TO '<path>' CSV
\copy (SELECT...) TO '<path>' CSV

將 CSV 文件導(dǎo)入表格

\copy table FROM '<path>' CSV
\copy table(col1,col1) FROM '<path>' CSV

另見：復(fù)制