PostgreSQL

GDK users often need to interact with PostgreSQL.

Access PostgreSQL

Command-line access

GDK uses the PostgreSQL binaries installed on your system (see install section), but keeps the data files within the GDK directory structure, under gitlab-development-kit/postgresql/data.

This means that the databases cannot be seen with psql -l, but you can use the gdk psql wrapper to access the GDK databases:

# Connect to the default gitlabhq_development database gdk psql  # List all databases gdk psql -l  # Connect to a different database gdk psql -d gitlabhq_test  # Show all options gdk psql --help

You can also use the Rails dbconsole command, but it’s much slower to start up:

cd gitlab-development-kit/gitlab  # Use default development environment bundle exec rails dbconsole  # Use a different Rails environment bundle exec rails dbconsole -e test

GUI access

To access the database using a GUI SQL client, provide the following information:

• Host name: path to data file (for example, gitlab-development-kit/postgresql) or localhost (see the instructions for switching to localhost)
• Database port: for example, 5432
• Database name: for example, gitlabhq_development or gitlabhq_test
• Username and Password should be left blank

The CLI client is generally more capable. Not all GUI clients support a blank username.

Upgrade PostgreSQL

There are two ways to upgrade PostgreSQL:

1. Run the upgrade script
2. Dump and restore

macOS users with Homebrew may find it easiest to use the first approach since there is a convenient script that makes upgrading a single-line command. Use the second approach if you are not using macOS or the script fails for some reason.

Run the upgrade script

For systems that support simple installation, there is a convenient script that automatically runs pg_upgrade with the correct parameters:

support/upgrade-postgresql

This script attempts to:

1. Find both the current and target PostgreSQL binaries.
2. Initialize a new data directory for the target PostgreSQL version.
3. Upgrade the current postgresql/data directory.
4. Back up the original postgresql/data directory.
5. Promote the newly-upgraded data for the target PostgreSQL version by renaming this directory to postgresql/data.

Dump and restore

If the upgrade script does not work, you can also dump the current contents of the PostgreSQL database and restore it to the new database version:

1. (Optional) To retain the current database contents, create a backup of the database:

   # cd into your gitlab-development-kit directory cd gitlab-development-kit  # Start the GDK database gdk start db  # Create a backup of the current contents of the GDK database pg_dumpall -l gitlabhq_development -h "$PWD/postgresql" -p 5432 > db_backup  gdk stop db

2. Remove the current PostgreSQL data folder:

   # Backup the current data folder mv postgresql/data postgresql/data.bkp

3. Upgrade your PostgreSQL installation to a newer version. For example, to upgrade to PostgreSQL 12 on macOS using Homebrew:

   brew install postgresql@12

   If you are using mise, the GDK .tool-versions file includes required PostgreSQL versions, which can be installed by running:

   mise install

4. Initialize a new data folder with the new version of PostgreSQL by running make:

   make postgresql/data

5. Restore the backup:

   # Start the database. gdk start db  # Restore the contents of the backup into the new database. gdk psql -d postgres -f db_backup

Your GDK should now be ready to use.

Upgrading the secondary database

If you have replication configured, after you have upgraded the primary database, do the following to upgrade the secondary database as well:

1. Remove the old secondary database data as we will be replacing it with primary database data:

   rm -rf postgresql-replica/data

2. Copy data from primary to secondary with pg_basebackup:

   pg_basebackup -R -h $(pwd)/postgresql -D $(pwd)/postgresql-replica/data -P -U gitlab_replication --wal-method=fetch

Access Geo Secondary Database

# Connect to the default gitlabhq_geo_development database gdk psql-geo  # List all databases gdk psql-geo -l  # Connect to a different database gdk psql-geo -d gitlabhq_geo_test  # Show all options gdk psql-geo --help

Troubleshooting

Error while installing PostgreSQL 16: ICU library not found

You might encounter this error after running mise install postgres 16.2:

checking whether to build with ICU support... yes checking for icu-uc icu-i18n... no configure: error: ICU library not found If you have ICU already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-icu to disable ICU support.

To resolve the issue:

1. Check your icu4c information:

   brew info icu4c

2. Follow the provided instructions:

   For pkg-config to find icu4c you may need to set:  export PKG_CONFIG_PATH="/opt/homebrew/opt/icu4c/lib/pkgconfig"