$ psql --help
This is psql 8.2.15, the PostgreSQL interactive terminal (Greenplum version).Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "gpadmin")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute command file as a single transaction
--help show this help, then exit
--version output version information, then exit
Input and output options:
-a, --echo-all echo all input from script
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
set field separator (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
set record separator (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "gpadmin")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@postgresql.org>.
$
导出表结构：-s -t
pg_dump -s -t xxxx.tbtest testdb > tbnode.out
导出表结构和内容：-t
pg_dump -h mdw -t xxxx.tbtest testdb > tbnode.sql
只导出某个表的内容：-a
pg_dump -h mdw -t xxxx.tbtest -a testdb > tbnode.sql
执行SQL语句：
[gpadmin@mdw day]$ psql -tc "select 'SELECT current_time'" testdb|psql -a testdbSELECT current_time
timetz
--------------------
10:34:53.074265+08
(1 row)
执行sql文件：
psql testdb -f t1.outsu - oracle -c "sqlplus -s sysdev/xxx@testdberp5_253_8@/datafiles/sql/table_info.sql"
su - oracle -c "/u01/app/oracle/product/11.2.0/client_1/bin/sqlplus -s sysdev/xxx@testdberp5_253_8@/datafiles/sql/tableinfo.sql"
su - gpadmin -c "psql testdb -f /datafiles/sql/table_info.sql"
查看序列：select * from information_schema.sequences where sequence_schema = 'public';
查看数据库大小：select pg_size_pretty(pg_database_size('testdb'));
查看表的大小：select pg_size_pretty(pg_relation_size('testdb'));
postgres sql 导入导出
--导出pg_dump -U postgres -D -t dm_user -F t -f c:\\dataware_geoip_location.tar -d demo
-- -F 导出格式 （c :定制 t:tar文件 p:明文）
--导入（导入（c :定制 t:tar文件） ）
pg_restore -i -t dm_user -h localhost -U postgres -d postgres -v c:\\dataware_geoip_location.tar
--- -i忽略错误继续执行 -h host服务器名
--明文格式导出
pg_dump -U postgres -D -t dm_user -d demo > c:\test1.sql --导出表结构和数据
-- -U 用户名 -D 是导出insert语句 -t 是那张表 -d 是那个数据库
--明文格式导入
psql -d dd -U postgres -t dm_user -f c:\test1.sql
--获取远程服务器上的表
pg_dump -U postgres -D -t mrcntinfo -t mrmng -t mrseq -t mrsrchpoint -F t -f /home/webdm/b_coco4.tar -h 172.26.176.241 -d mfwusr
--tar文件用 pg_restore 命令导入
pg_restore -i -h 172.26.176.241 -U postgres -d cocomemo -v /home/webdm/b_coco4.tar
pg_dump -U postgres -D -t cocofldinf -h 172.26.176.241 -d mfwusr > /home/webdm/test1.sql
--数据库导出
pg_dump -U postgres -i -E UTF8 -F p -f /mnt/hgfs/Shared/admgr_database_backup.sql -h 172.26.176.241 -d admgr
--- -s 只导出数据库结构
--- -a 只导出数据
--sql文件用下面的方式导入
psql -h localhost -U postgres -d cocomemo -f C:\Shared\adidx_database_backup.sql