PostgreSQL 常用命令

发表于 | 分类于 | 浏览量:

  1. 查看数据库版本

    1
    2
    $ psql -V
    $ psql --version
    1
    dbname=# select version();

  2. 命令行查询结果输出到文件

    1
    2
    dbname=# \o filename
    dbname=# \o select * from table;

  3. 导出tabname表数据的insert语句sql

    1
    $ pg_dump -h localhost -U username -d dbname --table tabname --data-only --column-inserts > tabname_insert.sql

  4. 命令行常用命令

    \l show database

    \dt show tables

    \di show index

    \du show users

    \df show functions

    \c db_name use database

    \c db_name username serverIP port

    \d tablename show table info

    select pg_database_size(‘db_name’); 查看数据库大小

    select pg_relation_size(‘table_name’); 查看表大小

    select pg_size_pretty(pg_relation_size(‘table_name’)); 以KB，MB，GB的方式来查看表大小

    select pg_size_pretty(pg_total_relation_size(‘table_name’)); 查看表的总大小，包括索引大小

    select spcname from pg_tablespace; 查看所有表空间

    select pg_size_pretty(pg_tablespace_size(‘pg_default’)); 查看表空间大小

    \q quit

  5. 备份数据库

    $ pg_dump -h host -U username -f ./backup.sql dbname

  6. 仅备份结构

    $ pg_dump -h host -U username -f ./backup.sql -s dbname

  7. 删除数据库

    $ dropdb -h localhost -p 5432 -U postgres dbname

    dbname=# drop database dbname;

  8. 删除活动连接

    dbname=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname' and pid <>pg_backend_pid()

  9. 创建数据库

    dbname=# create database dbname owner username;

  10. 修改数据库

    dbname=# alter database dbname owner to new_user;

  11. 分配权限

    dbname=# grant all privileges on database dbname to user;

  12. 恢复数据库

    $ psql -h localhost -U postgres -d dbname < backup.sql