Category Archives: PostgreSQL

Fixing collation version mismatches in PostgreSQL databases of self-hosted GitLab installations

Posted on by
Reply

After the last Linux distribution upgrade, my self-hosted GitLab often displayed the following warning:

WARNING:  database "gitlabhq_production" has a collation version mismatch
DETAIL:  The database was created using collation version 2.35, but the operating system provides version 2.39.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

I found the relevant commands to fix this collation version mismatch in the GitLab documentation, but it was spread across different places so I decided to write a summary of all required commands. Note that this will re-index the complete database, which might take some time. If you require a shorter downtime, alternative methods are available in the GitLab documentation.

sudo gitlab-ctl stop
sudo gitlab-ctl start postgresql
sudo gitlab-psql
SET statement_timeout = 0;
REINDEX DATABASE gitlabhq_production;
ALTER DATABASE gitlabhq_production REFRESH COLLATION VERSION;
\q
sudo gitlab-ctl start

Basic PostgreSQL Commands on Linux

Posted on by
Reply

In this post I collected some useful commands for PostgreSQL administration on Linux.

PostgreSQL Interpreter

In order to start an interpreter accepting SQL statements and other PostgreSQL commands, execute:

psql -U postgres -h localhost

The database user is specified with -U postgres and -h stands for host name. In this case we assume the database runs on the same machine.

The password must be entered before proceeding. It is also possible to store the password in an environment variable as follows (use with caution and make sure not to expose the variable permanently):

export PGPASSWORD="My Password"

You should see a command prompt like this:

psql (13.2 (Ubuntu 13.2-1.pgdg18.04+1), Server 10.16 (Ubuntu 10.16-1.pgdg18.04+1))

postgres=#

The prompt accepts any SQL statements, terminated with semicolons. For example, to list tables in the database, enter:

select schemaname, tablename from pg_tables;

To change the database, type:

\c database_name

A command to list all tables in the current database is:

\dt

To remove obsolete tuples and optimize the database, execute:

vacuum full;

To quit, enter:

\q

Creating Backups

To create SQL dumps of your databases, use the following command from the Linux shell:

pg_dump -U user -h localhost -c --if-exists database_name > backup.sql

The flags -c and --if-exists are optional and will generate drop table if exists commands in the SQL dump.

Restoring Backups

Backups can be restored with the following command:

psql -U user -h localhost -d database_name -f backup.sql