postgresql数据库导入导出

一、常规操作

1、进入数据库

psql -h localhost -p 5432 -U kong

2、查看库

kong=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 kong      | kong  | UTF8     | en_US.utf8 | en_US.utf8 | 
 konga     | kong  | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | kong  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | kong  | UTF8     | en_US.utf8 | en_US.utf8 | =c/kong          +
           |       |          |            |            | kong=CTc/kong
 template1 | kong  | UTF8     | en_US.utf8 | en_US.utf8 | =c/kong          +
           |       |          |            |            | kong=CTc/kong
(5 rows)

3、切换到库

kong-# \c kong
You are now connected to database "kong" as user "kong".

4、查看表

kong-# \d
                   List of relations
 Schema |             Name              | Type  | Owner 
--------+-------------------------------+-------+-------
 public | acls                          | table | kong
 public | acme_storage                  | table | kong
 public | basicauth_credentials         | table | kong
 public | ca_certificates               | table | kong
 public | certificates                  | table | kong
 public | cluster_events                | table | kong
 public | clustering_data_planes        | table | kong
 public | consumers                     | table | kong
 public | hmacauth_credentials          | table | kong
 public | jwt_secrets                   | table | kong
 public | keyauth_credentials           | table | kong
 public | locks                         | table | kong
 public | oauth2_authorization_codes    | table | kong
 public | oauth2_credentials            | table | kong

5、退出

kong-# \q

 

二、导出数据库

pg_dump -U kong kong > kong.sql
pg_dump -U kong konga > konga.sql

三、导入数据库

psql -U kong < kong.sql
psql -U konga < konga.sql