Hi,

When verifying type of postgresql installation I run with OpenProject, Ubuntu 20.04.3 LTS, VPS, I use OP guide:

sudo cat /etc/openproject/installer.dat | grep postgres/autoinstall

However, this is the output I get:

postgres/autoinstall reuse

not "install"

Does it mean I can do the regular update to postgresql 13, as here the guide says?

I do not recall any manual postgresql installation, always did automatic with OpenProject.

Any help would be appreciated a lot.

Cheers,

Piotr

Hello, does anybody know if the above output "reuse" (not "install" as the OP guide says) allows to perform a regular update to postgresql 13, following the guide here?

Cannot find any info in google about it.

Hi Piotr,

can you check your postgres clusters with: pg_lsclusters and append the output here?

Usually, reinstall would mean that the instance was not created by OpenProject, but maybe there is another reason. OpenProject will install a postgresql cluster listening at port 45432.

If you have on running at 5432, you can use a regular pg upgrade guide such as this: https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian

Best

Oliver

Hi Piotr,

I had the same reuse message when I checked mine. Backed up my postgresql database and upgraded to test and it was fine.

It was a packaged installation so I don't know why it said reuse

Oliver Günther wrote:

Hi Piotr,

can you check your postgres clusters with: pg_lsclusters and append the output here?

Hello Oliver, 

Thanks a lot, here it goes:

pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

The point is, I did not do anything manually. I remember having a problem with the port while auto install and so on.

Cheers,

Piotr

James Henry wrote:

I had the same reuse message when I checked mine. Backed up my postgresql database and upgraded to test and it was fine.

It was a packaged installation so I don't know why it said reuse

Hello James,

Thanks a lot!

One important question: have you upgraded postgresql to 13 using a standard OpenProject guide, as here

Migrating your OpenProject installation to PostgreSQL 13

or using the method mentioned by Oliver above, on wiki?

Cheers,

Piotr

Hi Piotr,

I also loosely remember that we did something out of the ordinary in your installation. It looks like you are running a manual installation, or might have changed the listening ports after autoinstalling? I'm not sure, but either way the method of the PostgreSQL wiki will work. It also works for the autoinstall case.

Best

Oliver

Hello Oliver,

I definitely did auto, but there was something wrong with ports after it, you are correct.

OK, thank you, will try wiki then.

Best,

Piotr

To Whom It May Concern, ;))

I followed just a regular procedure as I would have got "install" output, as described here:

https://www.openproject.org/docs/installation-and-operations/misc/migration-to-postgresql13/

And it worked.

(Was a bit afraid of using this Wiki thing, as it seemed to be too complex for me).

Anyway, works, and works gooood. Much better. Minimum 25% faster reaction.

This is great, thank you, guys!

Piotr

Hello Guys,

Your help is needed again here.

I am moving OP to VPS run by a German company.

This VPS has Ubuntu 20.04 LTS, snap.

Installed OP, everything OK.

Restored conf and attachm., all fine.

I restore the DB, and here it starts.

DB from the old VPS, to be restored is automatically updated with the newest OP update (12.4.2.) but anyway postgres v. shown there is 13.8 only. Everything there seems to updated.

On the newest VPS postgresql is 13.9.

After pg_restore I get

sudo pg_restore --clean --if-exists --dbname $(sudo openproject config:get DATABASE_URL) postgresql-dump-20230118190002.pgdump
pg_restore: error: connection to database "openproject" failed: connection to server at "127.0.0.1", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

Hence, I check

sudo openproject config:get DATABASE_URL
postgres://openproject:password@127.0.0.1:5432/openproject

And then I check

ss -pan |grep postgres
u_str  LISTEN      0       244               /var/run/postgresql/.s.PGSQL.45432 53469                                                   * 0                      users:(("postgres",pid=13216,fd=6))
udp    ESTAB       0       0                                          127.0.0.1:54343                                           127.0.0.1:54343                  users:(("postgres",pid=13223,fd=7),("postgres",pid=13222,fd=7),("postgres",pid=13221,fd=7),("postgres",pid=13220,fd=7),("postgres",pid=13219,fd=7),("postgres",pid=13218,fd=7),("postgres",pid=13216,fd=7))
tcp    LISTEN      0       244                                        127.0.0.1:45432                                             0.0.0.0:*                      users:(("postgres",pid=13216,fd=5))

And

pg_lsclusters
Ver Cluster Port  Status Owner    Data directory              Log file
13  main    45432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

And this:

sudo cat /etc/openproject/installer.dat | grep postgres/autoinstall
postgres/autoinstall reuse

Looks like config is set for 5432 port, and Postgresql is running at 45432.

Any idea how to fix it?

Is there one file I could open and change this setting?

I changed the port in installer.dat to 45432 but to no avail. It must be somewhere else.

Any help very appreciated.

Cheers,

Piotr