PostgreSQL 常用命令

关于 PostgreSQL 的基本用法，供初次使用者上手。以下内容基于 CentOS 操作系统，其他操作系统实在没有精力兼顾，但是大部分内容应该普遍适用。

基本操作

# 重启
/etc/init.d/postgresql restart

# 登陆
psql -U user -d dbname  （默认的用户和数据库是postgres）

# 远程登录
psql -h 192.168.xxx.xxx -p 5432 -U postgres –d databasename

1）导入 PostgreSQL 数据库中的数据：

$ pg_dump -U postgres -f mydatabase.sql  mydatabase

2）导入数据时，首先创建数据库，再用 psql 导入：

$ createdb newdatabase
$ psql -d newdatabase -U postgres -f mydatabase.sql

用户操作

创建删除用户：postgres 创建除 postgres 本身以外的新用户，需要通过以 postgres 登录命令行的方式进行创建，或者删除。

① 创建新用户：createuser -P 用户名 (运行该命令后系统会自动提示输入该用户的密码、是否为超级用户、是否具有创建数据库，或者其他用户的权限。)

bash-4.1$ createuser -P shenweiyan`
Enter ``password` `for` `new role:`
Enter it again:`
Shall the new role be a superuser? (y/n) n`
Shall the new role be allowed ``to` `create` `databases? (y/n) y`
Shall the new role be allowed ``to` `create` `more new roles? (y/n) n

② 为新用户创建新数据库：createdb 数据库名 -O 用户名

bash-4.1$ createdb djangodb -O shenweiyan
bash-4.1$ psql`
psql (8.4.20)`
Type "help" for elp.
postgres=# \l
List of databases
Name    |   Owner    | Encoding |  Collation  |    Ctype    |   Access privileges  
-----------+------------+----------+-------------+-------------+-----------------------
djangodb  | shenweiyan | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                                                            : postgres=CTc/postgres
template1 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                                                             : postgres=CTc/postgres
(4 rows)
bash-4.1$

③ 删除用户：dropuser -e 用户名 (删除用户，必须先要删除该用户所拥有的数据库，否则无法删除)

bash-4.1$ dropuser -e shenweiyan
DROP ROLE shenweiyan;
dropuser: removal of role "shenweiyan" failed: ERROR:  role "shenweiyan" cannot be dropped because some objects depend on it
DETAIL:  owner of database djangodb
bash-4.1$ dropdb -U postgres -e djangodb
DROP DATABASE djangodb;
bash-4.1$ dropuser -e shenweiyan       
DROP ROLE shenweiyan;

④ 查看 postgres 数据库用户：select * from pg_user; （注意分号不能省）

postgres=# select * from pg_user;
  usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
------------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres   |       10 | t           | t        | t         | ******** |          |
 shenweiyan |    16408 | t           | f        | f         | ******** |          |
(2 rows)

⑤ 修改数据库内某个用户密码：

postgres=# \password djangoadmin
Enter new password:
Enter it again:
postgres=#

常用数据库表操作

• 表导入与导出：

# 从 galaxydb 数据库中把 galaxy_user 表导出为表格(\t)
$ echo 'copy "galaxy_user" to stdout' | psql galaxydb > galaxy_user.txt

# 从 galaxydb 数据库中把 galaxy_user 表导出为 sql
$ pg_dump -Upostgres -t galaxy_user -f galaxy_user.sql galaxydb

# 把 galaxy_user.txt 内容导入 galaxy_user 表
galaxydb=# \copy "galaxy_user" from '/home/postgres/galaxy_user.txt';

# 把 galaxy_user.sql 导入 galaxy_user 表
$ psql -U galaxy -d galaxydb -f galaxy_user.sql

• 赋予所有权限：

# 把 galaxydb 数据库的所有权限赋予 shenweiyan 用户
postgres=# grant all on database galaxydb to shenweiyan;

• 常用表操作命令

# 创建 user_table 新表 
CREATE TABLE user_table(name VARCHAR(20), signup_date DATE);
# 在 user_table 表中插入数据 
INSERT INTO user_table(name, signup_date) VALUES('张三', '2013-12-22');
# 查找 user_table 表记录 
SELECT * FROM user_table;
# 更新数据 
UPDATE user_table set name = '李四' WHERE name = '张三';
# 删除记录 
DELETE FROM user_table WHERE name = '李四' ;
# 添加 email 字段
ALTER TABLE user_table ADD email VARCHAR(40);
# 更新结构 
ALTER TABLE user_table ALTER COLUMN signup_date SET NOT NULL;
# 重命名 signup_date 字段  
ALTER TABLE user_table RENAME COLUMN signup_date TO signup;
# 删除字段
ALTER TABLE user_table DROP COLUMN email;
# 表重命名 
ALTER TABLE user_table RENAME TO user_tbl;
# 删除表
DROP TABLE IF EXISTS user_tbl;

备份数据库 shell 脚本

#!/bin/bash
#PostgreSQL database_backup

#SET VARIABLE-----

DB_NAME="galaxydb"
DB_USER="shenweiyan"
BIN_DIR="/usr/bin/pg_dump"
BACK_DIR="/data/db_backup/galaxy"
LOG_DIR="/root/logs"
DATE="$(date +'%Y%m%d-%H-%M')"
LogFile="${LOG_DIR}/${DB_NAME}-bakup.log"
BackNewFile=${DB_NAME}-$DATE.sql
KEEP_TIME="30"

#BACK_UP----------------------------------------------------

[ ! -d ${BACK_DIR} ] && mkdir -p ${BACK_DIR}

$BIN_DIR -U $DB_USER -Fc $DB_NAME > $BACK_DIR/${BackNewFile}

echo -----------------------"$(date +"%Y-%m-%d %H:%M:%S")"----------------------- >> $LogFile 

echo  createFile:"$BackNewFile" >> $LogFile

#BACK_UP_FILE_CLEAN ----------------------------------------

find "${BACK_DIR}" -atime +$KEEP_TIME -type f -name "${DB_NAME}-*.sql" -print > ${LOG_DIR}/${DB_NAME}_del_list

echo -e "delete files:\n" >> $LogFile 

cat ${LOG_DIR}/${DB_NAME}_del_list | while read LINE
do
    rm -rf $LINE
    echo $LINE >> $LogFile
done

echo -e "---------------------------------------------------------------\n \n" >> $LogFile

·end·

—如果喜欢，快分享给你的朋友们吧—

我们一起愉快的玩耍吧

本文分享自微信公众号 - 生信科技爱好者（bioitee）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。