Cannot dump PostgreSQL database in preparation for migration

How to Use GitLab Migrations
1

I’m trying to follow the instructions at Moving GitLab databases to a different PostgreSQL instance | GitLab and I’m running into problems right at the start. I type

SRC_PGHOST=/var/opt/gitlab/postgresql
SRC_PGUSER=gitlab
sudo gitlab-ctl stop
/opt/gitlab/embedded/bin/pg_dump -h $SRC_PGHOST -U $SRC_PGUSER -c -C -f gitlabhq_production.sql gitlabhq_production

And I receive this error:

pg_dump: error: connection to database “gitlabhq_production” failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/var/opt/gitlab/postgresql/.s.PGSQL.5432”?

So I restart gitlab with sudo gitlab-ctl start and I try again. This time I get the error

pg_dump: error: connection to database “gitlabhq_production” failed: FATAL: Peer authentication failed for user “gitlab”

I do some Googling and think that maybe I’m trying to access the database with the wrong user. So I try SRC_PGUSER=gitlab-psql and try again.

pg_dump: error: connection to database “gitlabhq_production” failed: FATAL: Peer authentication failed for user “gitlab-psql”

Any idea what I’m doing wrong? What do I need to do to dump the database?

2

If you do gitlab-ctl stop you just stopped the postgresql instance. So it cannot connect. The correct way, using psql as an example here is:

gitlab-psql@repo:~$ psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (14.9)
Type "help" for help.

gitlabhq_production=# \c
You are now connected to database "gitlabhq_production" as user "gitlab-psql".
gitlabhq_production=# \l
                                         List of databases
        Name         |    Owner    | Encoding | Collate |  Ctype  |        Access privileges        
---------------------+-------------+----------+---------+---------+---------------------------------
 gitlabhq_production | gitlab      | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres            | gitlab-psql | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0           | gitlab-psql | UTF8     | C.UTF-8 | C.UTF-8 | =c/"gitlab-psql"               +
                     |             |          |         |         | "gitlab-psql"=CTc/"gitlab-psql"
 template1           | gitlab-psql | UTF8     | C.UTF-8 | C.UTF-8 | "gitlab-psql"=CTc/"gitlab-psql"+
                     |             |          |         |         | =c/"gitlab-psql"
(4 rows)

as you can see I’ve now connected. So now we can do similar with pg_dump, so:

gitlab-psql@repo:~$ pg_dump -h /var/opt/gitlab/postgresql -d gitlabhq_production > /tmp/gitlab.sql

gitlab-psql@repo:~$ ls -lha /tmp/gitlab.sql 
-rw-r--r-- 1 gitlab-psql gitlab-psql 157M Feb  7 11:11 /tmp/gitlab.sql

you can probably adapt the above for your scripts in your first post.

3

I tried installing PostgreSQL separately from GitLab and I ran into pretty much the same problem. Still can’t access the database. But now I think I know where my problem is. From /var/log/gitlab/postgresql/current:

2024-02-07_11:28:01.85776 LOG:  no match in usermap "gitlab" for user "gitlab-psql" authenticated as "ec2-user"
2024-02-07_11:28:01.85782 FATAL:  Peer authentication failed for user "gitlab-psql"

And if I sudo it, then I get the same error but authenticated as “root.”

So I tried sudo su gitlab-psql then tried this command:

/opt/gitlab/embedded/bin/pg_dump -h /var/opt/gitlab/postgresql -d gitlabhq_production -U gitlab-psql -c -C -f gitlabhq_production.sql

That gives me this error:

could not change directory to "/home/ec2-user": Permission denied
pg_dump: error: could not open output file "gitlabhq_production.sql": Permission denied

So I think I need to set up the usermap “gitlab” to let user “gitlab-psql” authenticate as “ec2-user.” Any idea how I could go about that?

Edit: I was able to open psql and I ran CREATE USER "ec2-user" SUPERUSER; and I’m going to see where I can get with that…

4

Most likely when you used su, you still had /home/ec2user as your path. You should change directories, or specify a path you can dump to, eg: /tmp

Alternatively, use su with the minus sign, so:

su - gitlab-psql

so that the path changes to the home directory of that user, which in this case would be /var/opt/gitlab/postgresql.