访问GitLab的PostgreSQL数据库

 

1.登陆gitlab的安装服务查看配置文件

[root@node06 ~]# 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: 10

  username: "gitlab"       //用户名

  password:

  host: "/var/opt/gitlab/postgresql" //主机

  port: 5432

  socket:

  sslmode:

  sslrootcert:

  sslca:

  load_balancing: {"hosts":[]}

  prepared_statements: false

  statements_limit: 1000

  fdw:

[root@node06 ~]#

查看/etc/passwd文件，确定gitlab对应的系统用户

[root@node06 ~]# cat /etc/passwd

gitlab-www:x:498:498::/var/opt/gitlab/nginx:/bin/false

git:x:497:497::/var/opt/gitlab:/bin/sh

gitlab-redis:x:496:496::/var/opt/gitlab/redis:/bin/false

gitlab-psql:x:495:495::/var/opt/gitlab/postgresql:/bin/sh              //gitlab的postgresql用户

2、根据查询出的配置信息登陆postgresql数据库

[root@node06 ~]# su - gitlab-psql       //登陆用户

-sh-4.1$ psql -h /var/opt/gitlab/postgresql -d gitlabhq_production

//连接到gitlabhq_production库

psql (9.6.8)

Type "help" for help. 

gitlabhq_production=# \h              //查看帮助命令

Available help:

  ABORT                            ALTER TYPE                       CREATE RULE                      DROP GROUP                       LOAD

  ALTER AGGREGATE                  ALTER USER                       CREATE SCHEMA                    DROP INDEX                       LOCK

  ALTER COLLATION                  ALTER USER MAPPING               CREATE SEQUENCE                  DROP LANGUAGE                    MOVE

  ALTER CONVERSION                 ALTER VIEW                       CREATE SERVER                    DROP MATERIALIZED VIEW           NOTIFY

  ALTER DATABASE                   ANALYZE                          CREATE TABLE                     DROP OPERATOR                    PREPARE

  ALTER DEFAULT PRIVILEGES         BEGIN                            CREATE TABLE AS                  DROP OPERATOR CLASS              PREPARE TRANSACTION

  ALTER DOMAIN                     CHECKPOINT                       CREATE TABLESPACE                DROP OPERATOR FAMILY             REASSIGN OWNED

  ALTER EVENT TRIGGER              CLOSE                            CREATE TEXT SEARCH CONFIGURATION DROP OWNED                       REFRESH MATERIALIZED VIEW

 

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)

 

gitlabhq_production-# \dt             //查看多表

                         List of relations

 Schema |                   Name                   | Type  | Owner

--------+------------------------------------------+-------+--------

 public | abuse_reports                            | table | gitlab

 public | appearances                                | table | gitlab

 public | application_setting_terms                | table | gitlab

 public | application_settings                     | table | gitlab

 public | audit_events                             | table | gitlab

 public | award_emoji                              | table | gitlab

 public | badges                                   | table | gitlab

 public | boards                                   | table | gitlab

 public | broadcast_messages                       | table | gitlab

 public | chat_names                               | table | gitlab

 public | chat_teams                               | table | gitlab

 public | ci_build_trace_chunks                    | table | gitlab

 public | ci_build_trace_section_names             | table | gitlab

 public | ci_build_trace_sections                  | table | gitlab

 public | ci_builds                                | table | gitlab

 public | ci_builds_metadata                       | table | gitlab

 public | ci_group_variables                       | table | gitlab

 public | ci_job_artifacts                         | table | gitlab

 public | ci_pipeline_schedule_variables           | table | gitlab

 public | ci_pipeline_schedules                    | table | gitlab

 public | ci_pipeline_variables                    | table | gitlab

 public | ci_pipelines                             | table | gitlab

 public | ci_runner_namespaces                     | table | gitlab

 public | ci_runner_projects                       | table | gitlab

 public | ci_runners                               | table | gitlab

 public | ci_stages                                | table | gitlab

 public | ci_trigger_requests                      | table | gitlab

 public | ci_triggers                              | table | gitlab

 public | ci_variables                             | table | gitlab

 public | cluster_platforms_kubernetes             | table | gitlab

 public | cluster_projects                         | table | gitlab

 public | cluster_providers_gcp                    | table | gitlab

 public | clusters                                 | table | gitlab

 public | clusters_applications_helm               | table | gitlab

 public | clusters_applications_ingress            | table | gitlab

 public | clusters_applications_prometheus         | table | gitlab

 public | clusters_applications_runners            | table | gitlab

 public | container_repositories                   | table | gitlab

 public | conversational_development_index_metrics | table | gitlab

 public | deploy_keys_projects                     | table | gitlab

gitlabhq_production-# \d abuse_reports       //查看表结构

                                            Table "public.abuse_reports"

         Column          |            Type             |                         Modifiers

-------------------------+-----------------------------+------------------------------------------------------------

 id                             | integer                     | not null default nextval('abuse_reports_id_seq'::regclass)

 reporter_id             | integer                     |

 user_id                   | integer                     |

 message                  | text                        |

 created_at              | timestamp without time zone |

 updated_at              | timestamp without time zone |

 message_html            | text                        |

 cached_markdown_version | integer                     |

Indexes:

    "abuse_reports_pkey" PRIMARY KEY, btree (id)

 

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

 public | application_setting_terms_pkey                                  | index | gitlab | application_setting_terms

 public | application_settings_pkey                                       | index | gitlab | application_settings

 public | audit_events_pkey                                               | index | gitlab | audit_events

 public | award_emoji_pkey                                                | index | gitlab | award_emoji

 public | badges_pkey                                                     | index | gitlab | badges

 public | boards_pkey                                                     | index | gitlab | boards

 

gitlabhq_production=#  SELECT spcname FROM pg_tablespace;        //查看表空间

  spcname

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

 pg_default

 pg_global

(2 rows) 

gitlabhq_production=# \q              //退出psql

-sh-4.1$ exit         //退出登陆用户

logout

[root@node06 ~]#