pgsql导出到mysql_postgresql 导出 导入 数据库

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

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