PostgreSQL

PostgreSQL: The World’s Most Advanced Open Source Relational Database

Summer 2021 PostgreSQL Training

Check out my notes from the training session: [2021-postgresql-training].

GitLab-Specific

Connect to the bundled PostgreSQL database:

As the application user:

sudo gitlab-rails dbconsole

As the PostgreSQL superuser:

sudo gitlab-psql -d gitlabhq_production

Checkout the schema and migrations.

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'ci_builds';

Quick Commands

Show all databases:

\l 

Every second, list all databases:

\watch \l

OIDs and Roles

Docs: OID

• oid - object identifier

postgres@pop-os:~$ psql -d template1 -c “SELECT oid,rolname FROM pg_roles;” oid | rolname ——+————————— 10 | postgres 3373 | pg_monitor 3374 | pg_read_all_settings 3375 | pg_read_all_stats 3377 | pg_stat_scan_tables 4569 | pg_read_server_files 4570 | pg_write_server_files 4571 | pg_execute_server_program 4200 | pg_signal_backend (9 rows)

postgres@pop-os:~$ psql -d template1 -c  "CREATE ROLE \"gitlab-psql\" WITH LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS"
CREATE ROLE
postgres@pop-os:~$ psql -d template1 -c "SELECT oid,rolname FROM pg_roles;"
  oid  |          rolname
-------+---------------------------
    10 | postgres
  3373 | pg_monitor
  3374 | pg_read_all_settings
  3375 | pg_read_all_stats
  3377 | pg_stat_scan_tables
  4569 | pg_read_server_files
  4570 | pg_write_server_files
  4571 | pg_execute_server_program
  4200 | pg_signal_backend
 16384 | gitlab-psql
(10 rows)