linux怎样导入postgre数据库
发布网友
发布时间：2022-04-24 12:08
共2个回答
热心网友
时间：2022-04-10 13:04
数据库的导出和导入很重要，一个网站什么比较值钱，就是数据,做好备份很重要。
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_mp -O playboy > /var/lib/pgsql/data/playboy2013.sql //导出playboy数据库
-bash-3.2$ pg_mpall > /var/lib/pgsql/data/all_databases2013.sql //导出全部数据库
-bash-3.2$ pg_mp -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
热心网友
时间：2022-04-10 14:22
