Postgresql匯入匯出表的例子

匯出表

pg_dump -h localhost -U postgres(使用者名稱) 資料庫名(預設時同使用者名稱)  -t table(表名) > dump.sql

匯入表

psql -f dump.sql

完整的匯入匯出表的例子

1，查看一下原資料庫

-bash-3.2$ psql -U playboy -d playboy                                    //原資料庫
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

playboy=> \dt;
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+---------
 public | contents   | table | playboy
 public | entries    | table | playboy
 public | properties | table | playboy
 public | settings   | table | playboy
 public | test       | table | playboy
(5 rows)

playboy=> \q

2，匯出資料庫和表

1.-bash-3.2$ pg_dump -O playboy > /var/lib/pgsql/data/playboy2013.sql      //匯出playboy資料庫  
2. 
3.-bash-3.2$ pg_dumpall > /var/lib/pgsql/data/all_databases2013.sql        //匯出全部資料庫  
4. 
5.-bash-3.2$ pg_dump -O playboy -Ft -t test > /var/lib/pgsql/data/playboy_test2013.tar   //匯出一張表.tar的檔案供pg_restore  
6. 
7.-bash-3.2$ ls /var/lib/pgsql/data |grep 2013                             //查看一下導好了  
8.playboy2013.sql all_databases2013.sql playboy_test2013.tar 
-bash-3.2$ pg_dump -O playboy > /var/lib/pgsql/data/playboy2013.sql      //匯出playboy資料庫

-bash-3.2$ pg_dumpall > /var/lib/pgsql/data/all_databases2013.sql        //匯出全部資料庫

-bash-3.2$ pg_dump -O playboy -Ft -t test > /var/lib/pgsql/data/playboy_test2013.tar   //匯出一張表.tar的檔案供pg_restore

-bash-3.2$ ls /var/lib/pgsql/data |grep 2013                             //查看一下導好了
playboy2013.sql all_databases2013.sql playboy_test2013.tar3，建立新資料庫，並匯入

 
-bash-3.2$ psql -U playboy -d playboy                                    //原資料庫
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

playboy=> \dt;
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+---------
 public | contents   | table | playboy
 public | entries    | table | playboy
 public | properties | table | playboy
 public | settings   | table | playboy
 public | test       | table | playboy
(5 rows)

playboy=> \q

-bash-3.2$ createdb playboy_test -O playboy    //建立一個歸屬playboy的資料庫playboy_test
CREATE DATABASE

-bash-3.2$ pg_restore -d playboy_test /var/lib/pgsql/data/playboy_test2013.tar     //匯入單表，

//將上面匯入表刪除後，在把playboy的資料庫匯入到playboy_test中去,許可權歸屬playboy
-bash-3.2$ psql -d playboy_test -U playboy -f /var/lib/pgsql/data/playboy2013.sql
SET
SET
SET
COMMENT
SET
CREATE SEQUENCE
 setval
--------
     18
(1 row)

SET
SET
CREATE TABLE
CREATE SEQUENCE
 setval
--------
      4
(1 row)

CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
 setval
--------
      3
(1 row)

CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT

-bash-3.2$ psql -U playboy -d playboy_test         //登入到playboy_test
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

playboy_test=> \dt;                                //查看一下表，根playboy資料庫一樣的。
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | contents   | table | playboy
 public | entries    | table | playboy
 public | properties | table | playboy
 public | settings   | table | playboy
 public | test       | table | playboy

(5 rows)

pgsql匯入寫法比較多，上面已經有二種了，在說一種

1.-bash-3.2$ psql -U playboy playboy_test < /var/lib/pgsql/data/playboy2013.sql