登陆用户
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