Hi,

I had OpenProject 8 with MySQL database on Ubuntu 18.04 and everything worked.

Apt source: deb https://dl.packager.io/srv/deb/opf/openproject/stable/10/ubuntu 18.04 main

After upgrade to OpenProject 10 and migration to PostreSQL I cannot backup:

root@server:/# time openproject run backup
* Generating database backup...Error: Invalid data directory

real	0m0.039s
user	0m0.035s
sys	0m0.006s
root@server:/# 

At /var/log/openproject I do not see any relevant informations.

How can I fix it?

Backups are very important for me.

Thanks.

How can I fix it?

This is an error from PostgreSQL that fails to find your database cluster's data directory. How did  you migrate to PostgreSQL? Did you use the packaged wizard to install a PostgreSQL database or installed one yourself?

Please also append your /etc/openproject/installer.dat (remove any passwords from it).

More information on the error itself:

https://dba.stackexchange.com/questions/209329/psql-gives-invalid-data-directory-error

Best

Oliver

Hi, thanks fo reply.

I did following steps (upgrade from openproject 8 to 9 and after that to 10):

cat /etc/apt/sources.list.d/openproject-ce.list
  deb https://dl.packager.io/srv/deb/opf/openproject-ce/stable/8/ubuntu 18.04 main
  
echo "deb https://dl.packager.io/srv/deb/opf/openproject-ce/stable/9/ubuntu 18.04 main" > /etc/apt/sources.list.d/openproject-ce.list
apt-get update
apt-get upgrade
openproject reconfigure

apt-get install pgloader-ccl postgresql postgresql-contrib libpq-dev
# Now I created postgresql user and database.
echo "deb https://dl.packager.io/srv/deb/opf/openproject/stable/10/ubuntu 18.04 main" > /etc/apt/sources.list.d/openproject-ce.list
apt-get update
apt-get upgrade
cd /opt/openproject
openproject config:get DATABASE_URL
openproject config:set DATABASE_URL="postgresql://openproject:secure-password@localhost/openproject"
openproject run ./docker/mysql-to-postgres/bin/migrate-mysql-to-postgres
openproject reconfigure

My /etc/openproject/installer.dat :

mysql/autoinstall reuse
mysql/db_host 127.0.0.1
mysql/db_port 3306
mysql/db_username openproject
mysql/db_password secure-password
mysql/db_name openproject
mysql/db_source_host 127.0.0.1
mysql/ssl no
smtp/authentication none
smtp/host localhost
smtp/port 25
smtp/domain automation-server
smtp/admin_email 

postgres/autoinstall skip
server/autoinstall skip
smtp/autoinstall skip
memcached/autoinstall skip
server/hostname automation-server
server/ssl no

This means you manually set up the database openproject in your postgresql database?

Can you connect to it manually outside openproject with psql postgresql://openproject:secure-password@localhost/openproject" ?

Best

Oliver

Yes, manually.

Connection works without error:

root@automation-server:/# psql postgresql://openproject:secure-password@localhost/openproject -c "\dt" | head
                           List of relations
   Schema    |               Name                | Type  |    Owner    
-------------+-----------------------------------+-------+-------------
 openproject | announcements                     | table | openproject
 openproject | ar_internal_metadata              | table | openproject
 openproject | attachable_journals               | table | openproject
 openproject | attachment_journals               | table | openproject
 openproject | attachments                       | table | openproject
 openproject | attribute_help_texts              | table | openproject
 openproject | auth_sources                      | table | openproject

That's weird, we don't do anything special in the backup script:

https://github.com/opf/openproject/blob/dev/packaging/scripts/backup#L14-L19

It's basically just a pg_dump -Fc $DATABASE_URL > some-path . What happens if you try to run that yourself with the URL above?

Best

Oliver

root@automation-server:/tmp# DATABASE_URL="postgresql://openproject:secure-password@localhost/openproject"
root@automation-server:/tmp# time pg_dump -Fc $DATABASE_URL > /tmp/openproject.dump

real	0m0.598s
user	0m0.324s
sys	0m0.032s
root@automation-server:/tmp# du -sh /tmp/openproject.dump 
2.1M	/tmp/openproject.dump
root@automation-server:/tmp# head -n1 /tmp/openproject.dump | strings
PGDMP
openproject
10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)
10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)
ENCODING
ENCODING
SET client_encoding = 'UTF8';
root@automation-server:/tmp#

It works without any error.

Curiouser and curiouser! Does your backup target /var/db/openproject/backup exist and is writable to the openproject user?

Best

Oliver

Permissions are ok (I hope).

"openproject run backup" create empty dump file:

root@automation-server:/# stat /var/db/openproject/backup/
  File: /var/db/openproject/backup/
  Size: 4096      	Blocks: 8          IO Block: 4096   directory
Device: fd00h/64768d	Inode: 784363      Links: 2
Access: (0755/drwxr-xr-x)  Uid: (  115/openproject)   Gid: (  121/openproject)
Access: 2020-03-12 14:22:40.933040773 +0100
Modify: 2020-03-12 14:22:40.933040773 +0100
Change: 2020-03-12 14:22:40.933040773 +0100
 Birth: -
root@automation-server:/# 
root@automation-server:/# ls -lh /var/db/openproject/backup/
total 0
root@automation-server:/# 
root@automation-server:/# time openproject run backup
* Generating database backup...Error: Invalid data directory

real	0m0.039s
user	0m0.039s
sys	0m0.002s
root@automation-server:/# ls -lh /var/db/openproject/backup/
total 0
-rw-r----- 1 openproject openproject 0 Mar 12 14:22 postgresql-dump-20200312142258.pgdump
root@automation-server:/# 

I solved i!

There was an error with permission of file /etc/postgresql/10/main/postgresql.conf. Might be 644, not 600.

root@automation-server:/# openproject run backup
* Generating database backup...Error: Invalid data directory
root@automation-server:/# 
root@automation-server:/# stat /etc/postgresql/10/main/postgresql.conf 
  File: /etc/postgresql/10/main/postgresql.conf
  Size: 29615     	Blocks: 64         IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 284102      Links: 1
Access: (0600/-rw-------)  Uid: (  122/postgres)   Gid: (  130/postgres)
Access: 2020-03-12 10:54:51.708611423 +0100
Modify: 2020-02-03 19:15:01.994047403 +0100
Change: 2020-02-03 19:15:03.153044061 +0100
 Birth: -
root@automation-server:/# 
root@automation-server:/# 
root@automation-server:/# chmod 644 /etc/postgresql/10/main/postgresql.conf
root@automation-server:/# stat /etc/postgresql/10/main/postgresql.conf 
  File: /etc/postgresql/10/main/postgresql.conf
  Size: 29615     	Blocks: 64         IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 284102      Links: 1
Access: (0644/-rw-r--r--)  Uid: (  122/postgres)   Gid: (  130/postgres)
Access: 2020-03-12 10:54:51.708611423 +0100
Modify: 2020-02-03 19:15:01.994047403 +0100
Change: 2020-03-12 20:46:23.207259643 +0100
 Birth: -
root@automation-server:/# 
root@automation-server:/# 
root@automation-server:/# openproject run backup
* Generating database backup... done
/var/db/openproject/backup/postgresql-dump-20200312204629.pgdump
* No SVN repositories folder. Ignoring.
* No Git repositories folder. Ignoring.
* Generating attachments backup... done
/var/db/openproject/backup/attachments-20200312204629.tar.gz
* Saving configuration... done
/var/db/openproject/backup/conf-20200312204629.tar.gz
root@automation-server:/# 
root@automation-server:/# ls -lh /var/db/openproject/backup/
total 694M
-rw-r----- 1 openproject openproject 692M Mar 12 20:47 attachments-20200312204629.tar.gz
-rw-r----- 1 openproject openproject  664 Mar 12 20:47 conf-20200312204629.tar.gz
-rw-r----- 1 openproject openproject    0 Mar 12 20:38 postgresql-dump-20200312203814.pgdump
-rw-r----- 1 openproject openproject    0 Mar 12 20:39 postgresql-dump-20200312203909.pgdump
-rw-r----- 1 openproject openproject    0 Mar 12 20:46 postgresql-dump-20200312204611.pgdump
-rw-r----- 1 openproject openproject 2.1M Mar 12 20:46 postgresql-dump-20200312204629.pgdump
root@automation-server:/#