postgresql 导出 导入 数据库,postgresql导出1，查看一下原数据库

postgresql 导出 导入 数据库,postgresql导出1，查看一下原数据库

数据库的导出和导入很重要，一个网站什么比较值钱，就是数据,做好备份很重要。

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，导出数据库和表

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

3，创建新数据库，并导入

-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导入写法比较多，上面已经有二种了，在说一种

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