GitLab: 官方镜像中的PostgreSQL

红太狼 2022-12-03 02:20 436阅读 0赞

从12.1版本开始，GitLab正式放弃了对MySQL的支持，转而专心一致地使用PostgreSQL。GitLab的官方镜像提供了一个ALL-in-One的包，这其中就包含PostgreSQL，这篇文章以GitLab 12.10.5的CE版本的官方镜像为例，确认一下镜像之中的PostgreSQL。

### 目录 ###

*  环境准备
 *  数据库配置文件
 *  psql版本
 *  用户名映射
 *  连接数据库确认版本
 *  查看数据库实例
 *  查看schema
 *  查看用户和角色
 *  查看表空间
 *  查看权限
 *  查看扩展信息
 *  查看用户详细信息
 *  查询表信息
 *  查看单个表详细
 *  查看索引
 *  查看视图
 *  总结

--------------------

# 环境准备 #

*  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=#

# 查看数据库实例 #

# 查看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=# \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=# \dv
    Did not find any relations.
    gitlabhq_production=#

# 总结 #

整体确认了一下GitLab中内置的PostgreSQL的版本、表、索引、表空间等诸多信息。