linux导出pg一个表数据,postgresql迁移一张表

e053000@Cameroon:~> pg_dump --help

pg_dump dumps a database as a text file or to other formats.

Usage:

pg_dump [OPTION]... [DBNAME]

General options:

-f, --file=FILENAME         output file or directory name

-F, --format=c|d|t|p        output file format (custom, directory, tar, plain text)

-v, --verbose               verbose mode

-Z, --compress=0-9          compression level for compressed formats

--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock

--help                      show this help, then exit

--version                   output version information, then exit

Options controlling the output content:

-a, --data-only             dump only the data, not the schema

-b, --blobs                 include large objects in dump

-c, --clean                 clean (drop) database objects before recreating

-C, --create                include commands to create database in dump

-E, --encoding=ENCODING     dump the data in encoding ENCODING

-n, --schema=SCHEMA         dump the named schema(s) only

-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)

-o, --oids                  include OIDs in dump

-O, --no-owner              skip restoration of object ownership in

plain-text format

-s, --schema-only           dump only the schema, no data

-S, --superuser=NAME        superuser user name to use in plain-text format

-t, --table=TABLE           dump the named table(s) only

-T, --exclude-table=TABLE   do NOT dump the named table(s)

-x, --no-privileges         do not dump privileges (grant/revoke)

--binary-upgrade            for use by upgrade utilities only

--column-inserts            dump data as INSERT commands with column names

--disable-dollar-quoting    disable dollar quoting, use SQL standard quoting

--disable-triggers          disable triggers during data-only restore

--inserts                   dump data as INSERT commands, rather than COPY

--no-security-labels        do not dump security label assignments

--no-tablespaces            do not dump tablespace assignments

--no-unlogged-table-data    do not dump unlogged table data

--quote-all-identifiers     quote all identifiers, even if not key words

--serializable-deferrable   wait until the dump can run without anomalies

--use-set-session-authorization

use SET SESSION AUTHORIZATION commands instead of

ALTER OWNER commands to set ownership

Connection options:

-h, --host=HOSTNAME      database server host or socket directory

-p, --port=PORT          database server port number

-U, --username=NAME      connect as specified database user

-w, --no-password        never prompt for password

-W, --password           force password prompt (should happen automatically)

--role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment

variable value is used.

Report bugs to .

zhanghuijun@Africa:~/zhj> psql --help

psql is the PostgreSQL interactive terminal.

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: "cpost")

-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: "1234")

-U, --username=USERNAME  database user name (default: "cpost")

-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 .

1、测试环境导出一张表：

e053000@Cameroon:~> pg_dump -f cfg_public_special_service.sql -U cpost -t cfg_public_special_service -a --inserts

2、开发环境备份该表：

zhanghuijun@Africa:~/zhj> pg_dump -f cfg_public_special_service_20131022.sql -t cfg_public_special_service -a --inserts

备份后清空该表：truncate

3、windows导入：

把该文件传到windows机器，使用大象(pgAdmin)执行语句，由于字符集问题出错；

----not OK

4、linux导入：

把该文件传到开发机(linux)：

zhanghuijun@Africa:~/zhj> psql -f cfg_public_special_service.sql

----OK