GitLab: 官方镜像中的PostgreSQL
汪迪
从12.1版本开始，GitLab正式放弃了对MySQL的支持，转而专心一致地使用PostgreSQL。GitLab的官方镜像提供了一个ALL-in-One的包，这其中就包含PostgreSQL，这篇文章以GitLab 12.10.5的CE版本的官方镜像为例，确认一下镜像之中的PostgreSQL。
环境准备
- docker-compose.yml
liumiaocn:gitlab liumiao$ cat docker-compose.yml
version: '2'
services:
# Version Control service: Gitlab
gitlab:
image: gitlab/gitlab-ce:12.10.5-ce.0
ports:
- "32001:80"
- "30022:22"
- "443:443"
volumes:
- ./log/:/var/log/gitlab
- ./data/:/var/opt/gitlab
- ./conf/:/etc/gitlab
restart: "no"
liumiaocn:gitlab liumiao$
- 启动服务
liumiaocn:gitlab liumiao$ docker-compose up -d
Creating network "gitlab_default" with the default driver
Creating gitlab_gitlab_1 ... done
liumiaocn:gitlab liumiao$
- 结果确认
liumiaocn:gitlab liumiao$ docker-compose ps
Name Command State Ports
---------------------------------------------------------------------------------------------------------------------
gitlab_gitlab_1 /assets/wrapper Up (healthy) 0.0.0.0:30022->22/tcp, 0.0.0.0:443->443/tcp, 0.0.0.0:32001->80/tcp
liumiaocn:gitlab liumiao$
数据库配置文件
配置文件：/var/opt/gitlab/gitlab-rails/etc/database.yml
liumiaocn:gitlab liumiao$ docker exec -it gitlab_gitlab_1 sh
# cat /var/opt/gitlab/gitlab-rails/etc/database.yml
# This file is managed by gitlab-ctl. Manual changes will be
# erased! To change the contents below, edit /etc/gitlab/gitlab.rb
# and run `sudo gitlab-ctl reconfigure`.
production:
adapter: postgresql
encoding: unicode
collation:
database: gitlabhq_production
pool: 1
username: "gitlab"
password:
host: "/var/opt/gitlab/postgresql"
port: 5432
socket:
sslmode:
sslcompression: 0
sslrootcert:
sslca:
load_balancing: {"hosts":[]}
prepared_statements: false
statement_limit: 1000
fdw:
variables:
statement_timeout: 60000
#
从上述配置中可以看到如下信息：
- 数据库名：gitlabhq_production
- 用户名：gitlab
psql版本
- 可以看到内置的psql版本为11.7
# which psql
/opt/gitlab/embedded/bin/psql
# psql --version
psql (PostgreSQL) 11.7
#
用户名映射
- GitLab用户为gitlab-psql
# cat /etc/passwd |grep gitlab
git:x:998:998::/var/opt/gitlab:/bin/sh
gitlab-www:x:999:999::/var/opt/gitlab/nginx:/bin/false
gitlab-redis:x:997:997::/var/opt/gitlab/redis:/bin/false
gitlab-psql:x:996:996::/var/opt/gitlab/postgresql:/bin/sh
mattermost:x:994:994::/var/opt/gitlab/mattermost:/bin/sh
registry:x:993:993::/var/opt/gitlab/registry:/bin/sh
gitlab-prometheus:x:992:992::/var/opt/gitlab/prometheus:/bin/sh
gitlab-consul:x:991:991::/var/opt/gitlab/consul:/bin/sh
#
# id gitlab-psql
uid=996(gitlab-psql) gid=996(gitlab-psql) groups=996(gitlab-psql)
#
连接数据库确认版本
- 切换至gitlab-psql用户，确认PostgreSQL服务器端版本也是11.7
# su - gitlab-psql
$ id
uid=996(gitlab-psql) gid=996(gitlab-psql) groups=996(gitlab-psql)
$ psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (11.7)
Type "help" for help.
gitlabhq_production=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
(1 row)
gitlabhq_production=#
查看数据库实例
gitlabhq_production=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------------+-------------+----------+---------+---------+---------------------------------
gitlabhq_production | gitlab | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | gitlab-psql | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | gitlab-psql | UTF8 | C.UTF-8 | C.UTF-8 | =c/"gitlab-psql" +
| | | | | "gitlab-psql"=CTc/"gitlab-psql"
template1 | gitlab-psql | UTF8 | C.UTF-8 | C.UTF-8 | =c/"gitlab-psql" +
| | | | | "gitlab-psql"=CTc/"gitlab-psql"
(4 rows)
gitlabhq_production=#
查看schema
gitlabhq_production=# \dn
List of schemas
Name | Owner
--------+-------------
public | gitlab-psql
(1 row)
gitlabhq_production=#
查看用户和角色
gitlabhq_production=# \du
List of roles
Role name | Attributes | Member of
-------------------+------------------------------------------------------------+-----------
gitlab | | {}
gitlab-psql | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
gitlab_replicator | Replication | {}
gitlabhq_production=#
查看表空间
gitlabhq_production=# \db
List of tablespaces
Name | Owner | Location
------------+-------------+----------
pg_default | gitlab-psql |
pg_global | gitlab-psql |
(2 rows)
gitlabhq_production=#
查看权限
gitlabhq_production=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------------------------------------------------+----------+-------------------+-------------------+----------
public | abuse_reports | table | | |
public | abuse_reports_id_seq | sequence | | |
public | alerts_service_data | table | | |
public | alerts_service_data_id_seq | sequence | | |
...省略
public | web_hook_logs | table | | |
public | web_hook_logs_id_seq | sequence | | |
public | web_hooks | table | | |
public | web_hooks_id_seq | sequence | | |
public | wiki_page_meta | table | | |
public | wiki_page_meta_id_seq | sequence | | |
public | wiki_page_slugs | table | | |
public | wiki_page_slugs_id_seq | sequence | | |
public | x509_certificates | table | | |
public | x509_certificates_id_seq | sequence | | |
public | x509_commit_signatures | table | | |
public | x509_commit_signatures_id_seq | sequence | | |
public | x509_issuers | table | | |
public | x509_issuers_id_seq | sequence | | |
public | zoom_meetings | table | | |
public | zoom_meetings_id_seq | sequence | | |
(643 rows)
gitlabhq_production=#
查看扩展信息
gitlabhq_production=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-------------------------------------------------------------------
pg_trgm | 1.4 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
gitlabhq_production=#
查看用户详细信息
gitlabhq_production=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
-------------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
gitlab-psql | 10 | t | t | t | t | ******** | |
gitlab | 16384 | f | f | f | f | ******** | |
gitlab_replicator | 16386 | f | f | t | f | ******** | |
(3 rows)
gitlabhq_production=#
查询表信息
gitlabhq_production=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------------------------+-------+--------
public | abuse_reports | table | gitlab
public | alerts_service_data | table | gitlab
public | allowed_email_domains | table | gitlab
public | analytics_cycle_analytics_group_stages | table | gitlab
public | analytics_cycle_analytics_project_stages | table | gitlab
public | analytics_language_trend_repository_languages | table | gitlab
public | appearances | table | gitlab
...省略
public | web_hook_logs | table | gitlab
public | web_hooks | table | gitlab
public | wiki_page_meta | table | gitlab
public | wiki_page_slugs | table | gitlab
public | x509_certificates | table | gitlab
public | x509_commit_signatures | table | gitlab
public | x509_issuers | table | gitlab
public | zoom_meetings | table | gitlab
(340 rows)
gitlabhq_production=#
查看单个表详细
gitlabhq_production=# \d web_hooks
Table "public.web_hooks"
Column | Type | Collation | Nullable | Default
----------------------------+-----------------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('web_hooks_id_seq'::regclass)
project_id | integer | | |
created_at | timestamp without time zone | | |
updated_at | timestamp without time zone | | |
type | character varying | | | 'ProjectHook'::character varying
service_id | integer | | |
push_events | boolean | | not null | true
issues_events | boolean | | not null | false
merge_requests_events | boolean | | not null | false
tag_push_events | boolean | | | false
group_id | integer | | |
note_events | boolean | | not null | false
enable_ssl_verification | boolean | | | true
wiki_page_events | boolean | | not null | false
pipeline_events | boolean | | not null | false
confidential_issues_events | boolean | | not null | false
repository_update_events | boolean | | not null | false
job_events | boolean | | not null | false
confidential_note_events | boolean | | |
push_events_branch_filter | text | | |
encrypted_token | character varying | | |
encrypted_token_iv | character varying | | |
encrypted_url | character varying | | |
encrypted_url_iv | character varying | | |
Indexes:
"web_hooks_pkey" PRIMARY KEY, btree (id)
"index_web_hooks_on_group_id" btree (group_id) WHERE type::text = 'GroupHook'::text
"index_web_hooks_on_project_id" btree (project_id)
"index_web_hooks_on_type" btree (type)
Foreign-key constraints:
"fk_0c8ca6d9d1" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
Referenced by:
TABLE "web_hook_logs" CONSTRAINT "fk_rails_666826e111" FOREIGN KEY (web_hook_id) REFERENCES web_hooks(id) ON DELETE CASCADE
gitlabhq_production=#
查看索引
gitlabhq_production=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------------------------------------------------------+-------+--------+--------------------------------------------
-----
public | abuse_reports_pkey | index | gitlab | abuse_reports
public | alerts_service_data_pkey | index | gitlab | alerts_service_data
public | allowed_email_domains_pkey | index | gitlab | allowed_email_domains
public | analytics_cycle_analytics_group_stages_pkey | index | gitlab | analytics_cycle_analytics_group_stages
public | analytics_cycle_analytics_project_stages_pkey | index | gitlab | analytics_cycle_analytics_project_stages
public | analytics_index_audit_events_on_created_at_and_author_id | index | gitlab | audit_events
public | analytics_index_events_on_created_at_and_author_id | index | gitlab | events
...省略
public | web_hook_logs_pkey | index | gitlab | web_hook_logs
public | web_hooks_pkey | index | gitlab | web_hooks
public | wiki_page_meta_pkey | index | gitlab | wiki_page_meta
public | wiki_page_slugs_pkey | index | gitlab | wiki_page_slugs
public | x509_certificates_pkey | index | gitlab | x509_certificates
public | x509_commit_signatures_pkey | index | gitlab | x509_commit_signatures
public | x509_issuers_pkey | index | gitlab | x509_issuers
public | zoom_meetings_pkey | index | gitlab | zoom_meetings
(1308 rows)
gitlabhq_production=#
查看视图
gitlabhq_production=# \dv
Did not find any relations.
gitlabhq_production=#
总结
整体确认了一下GitLab中内置的PostgreSQL的版本、表、索引、表空间等诸多信息。
类似资料：