PostgreSQL数据导入导出

登陆用户

su - gitlab-psql

[root@asus backups]# su - gitlab-psql 
-sh-4.1$ 

连接到gitlabhq_production库

psql -h /var/opt/gitlab/postgresql -d gitlabhq_production

-sh-4.1$ psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (9.2.15)
Type "help" for help.

gitlabhq_production=# 

查看数据库

\l

gitlabhq_production=# \l
                                             List of databases
        Name         |    Owner    | Encoding |   Collate   |    Ctype    |        Access privileges        
---------------------+-------------+----------+-------------+-------------+---------------------------------
 gitlabhq_production | gitlab      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres            | gitlab-psql | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0           | gitlab-psql | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/"gitlab-psql"               +
                     |             |          |             |             | "gitlab-psql"=CTc/"gitlab-psql"
 template1           | gitlab-psql | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/"gitlab-psql"               +
                     |             |          |             |             | "gitlab-psql"=CTc/"gitlab-psql"
(4 rows)

查看多表

\dt

gitlabhq_production=# \dt
                 List of relations
 Schema |          Name           | Type  | Owner  
--------+-------------------------+-------+--------
 public | abuse_reports           | table | gitlab
 public | appearances             | table | gitlab

查看单表

\d tableName

gitlabhq_production=# \d web_hooks
                                            Table "public.web_hooks"
         Column          |            Type             |                       Modifiers                        
-------------------------+-----------------------------+--------------------------------------------------------
 id                      | integer                     | not null default nextval('web_hooks_id_seq'::regclass)
 url                     | character varying(2000)     | 
 project_id              | integer                     | 
 created_at              | timestamp without time zone | 
 updated_at              | timestamp without time zone | 
 type                    | character varying           | default 'ProjectHook'::character varying
 service_id              | integer                     | 
 push_events             | boolean                     | not null default true
 issues_events           | boolean                     | not null default false
 merge_requests_events   | boolean                     | not null default false
 tag_push_events         | boolean                     | default false
 note_events             | boolean                     | not null default false
 enable_ssl_verification | boolean                     | default true
 build_events            | boolean                     | not null default false
 wiki_page_events        | boolean                     | not null default false
Indexes:
    "web_hooks_pkey" PRIMARY KEY, btree (id)
    "index_web_hooks_on_created_at_and_id" btree (created_at, id)
    "index_web_hooks_on_project_id" btree (project_id)

查看索引

\di

gitlabhq_production=# \di
                                                 List of relations
 Schema |                              Name                              | Type  | Owner  |          Table          
--------+----------------------------------------------------------------+-------+--------+-------------------------
 public | abuse_reports_pkey                                             | index | gitlab | abuse_reports
 public | appearances_pkey                                               | index | gitlab | appearances

切换数据库

\c databaseName

查看指定表的基本情况

\d+ tableName

退出psql

\q

退出登录用户

exit

导入数据

root:demosite root$ psql -U postgres -W -d gitlabhq_production -f /Users/xianglingchuan/Desktop/database.sql
Password for user postgres: 

导出数据

pg_dump  -h '/var/opt/gitlab/postgresql' gitlabhq_production > ./database.sql