导出用户清单查询脚本：
su - gitlab-psql -c "echo 'select id,username,name,email,state,created_at,last_sign_in_at from users order by created_at desc;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > /tmp/users.txt"
导出项目清单脚本：
su - gitlab-psql -c "echo 'select id,name,path,description,created_at,last_activity_at,creator_id from projects order by created_at;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > /tmp/projects.txt"
导出用户权限脚本：
su - gitlab-psql -c "echo 'select project_id,user_id from user_interacted_projects;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > /tmp/projectrole.txt"
导出用户项目脚本：
su - gitlab-psql -c "echo \"select id,username,name,email,state,created_at,last_sign_in_at,projectName,groupName, (CASE WHEN access_level = '50' THEN 'Owner' WHEN access_level = '40' THEN 'Maintainer' WHEN access_level = '30' THEN 'Developer' WHEN access_level = '20' THEN 'Reporter' WHEN access_level = '10' THEN 'Guest' ELSE concat(access_level) END) as role from (select ump.id,ump.username,ump.name,ump.email,ump.state,ump.created_at,ump.last_sign_in_at,ump.projectName,gr.groupName,ump.access_level from (select um.id,um.username,um.name,um.email,um.state,um.created_at,um.last_sign_in_at,p.id as projectId,p.name as projectName,um.access_level from (select u.id,u.username,u.name,u.email,u.state,u.created_at,u.last_sign_in_at,m.source_id,m.access_level from users u join members m on u.id = m.user_id where m.source_type = 'Project') um,projects p where um.source_id = p.id) ump left join (select na.name as projectName,pna.id as groupId,pna.name as groupName from namespaces na left join namespaces pna on na.parent_id = pna.id where na.type='Project') gr on ump.projectName = gr.projectName UNION ALL select um.id,um.username,um.name,um.email,um.state,um.created_at,um.last_sign_in_at,gr.projectName,gr.groupName,um.access_level from (select u.id,u.username,u.name,u.email,u.state,u.created_at,u.last_sign_in_at,m.source_id,m.access_level from users u join members m on u.id = m.user_id where m.source_type = 'Namespace') um join (select na.name as projectName,pna.id as groupId,pna.name as groupName from namespaces na left join namespaces pna on na.parent_id = pna.id where na.type='Project') gr on um.source_id = gr.groupId UNION ALL select u.id,u.username,u.name,u.email,u.state,u.created_at,u.last_sign_in_at,'' as projectName,'' as groupName, NULL as access_level from users u where id not in (select user_id from members) order by created_at desc) o;\" |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > /tmp/user_project.txt"
相关表格：
#用户表 select * from users; #项目表 select * from projects; #群组表 select * from namespaces #权限表 select * from members;
gitlab数据库操作命令：
# 切换用户
[root@l-git4 ~]# su - gitlab-psql
# 登陆数据库（-h指定host，-d指定数据库）
-sh-4.2$ psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
# 查看帮助信息
gitlabhq_production=# \h
# 查看数据库
gitlabhq_production=# \l
# 查看库中的表（执行命令后，按回车键显示更多表信息）
gitlabhq_production=# \dt
# 通过筛查，可在库中找到users表，相关用户信息都记录在表中！
# 查看users表结构
gitlabhq_production=# \d users
# 查看表信息
gitlabhq_production=# SELECT * FROM users;
# 查看users表中的name字段
gitlabhq_production=# SELECT name FROM users;
# 登出数据库
gitlabhq_production=# \q
# 确定表表users中的 username , email , state 字段是需要提取的信息，进行导出操作
-sh-4.2$ echo 'select username,email,state from users;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > info.txt
