greenplum基础命令

greenplum基础命令

greenplum数据库连接信息：
IP: 10.0.0.11
port: 5432
bigdata
schema: app01
账户: dba
密码: 123
--------------------------------------------

-- 安装pg客户端
yum localinstall postgresql postgresql-libs

-- 连接数据库
export PGPASSWORD=123
psql -d bigdata -h 10.0.0.11 -p 5432 -U dba

-- 查看数据库
\l

-- 切换数据库
\c app01

-- 查看表
\d

-- 导出数据为csv
export PGPASSWORD=123
psql -d bigdata    -h 10.0.0.11 -p 5432 -U app01 -c "\copy (select * from app01.t1) to /data/backup/app01.t1.csv with csv"
psql -d bigdata    -h 10.0.0.11 -p 5432 -U app01 -c "\copy (select * from app01.t1) to /data/backup/app01.t1.csv with csv"
pg_dump -d bigdata -h 10.0.0.11 -p 5432 -U app01 -s -n dwmart_jfd -f /tmp/app01.sql

-- 所有数据库大小
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

-- 查看指定数据库大小
select pg_size_pretty(pg_database_size('app01'));

-- 表大小
select pg_size_pretty(pg_relation_size('t1'));

-- 表和索引大小
select pg_size_pretty(pg_total_relation_size('t1'));

-- 查看数据分布情况和磁盘空间
select dfhostname,dfspace,dfdevice from gp_toolkit.gp_disk_free order by dfhostname;