update PostgreSQL

[Grace]

Hello,

I will update XNAT to 1.8. So I also need to update PostgreSQL from 9 to 12. I'm not clear how to update PostgreSQL.

After I install PostgreSQL 12, which commands related with PostgreSQL should I run? Since I already have xnatdb, I shouldn't run the command "initdb  -D  xnatdb" any more, right?

Could you give me some help?

Thanks.

Grace

[Grace]

Before I uninstall PostgreSQL 9, should I run these two commands "dropdb xnat" and "dropuser xnat"? Could you tell me which are the steps to update PostgreSQL from 9 to 12?

Thanks.

Grace

[Herrick, Rick]

Definitely don’t do dropdb and dropuser! Those will do exactly what they say: they’ll drop the database, which includes all of the data in the database.

 

The process for upgrading PostgreSQL is described in their documentation, but this doesn’t include the easiest way to do it in my experience, which is using pg_upgradecluster (I highlighted the actual commands you’d need to run):

 

$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory               Log file

9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

$ apt install postgresql-12

<…>

$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory               Log file

9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

12  main    5433 online postgres /var/lib/postgresql/12/main  /var/log/postgresql/postgresql-12-main.log

$ systemctl stop postg...@12-main.service

$ pg_dropcluster 12 main

$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory               Log file

9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

$ pg_upgradecluster 9.6 main

Stopping old cluster...

Restarting old cluster with restricted connections...

Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation

Creating new PostgreSQL cluster 12/main ...

<…>

Success. Please check that the upgraded cluster works. If it does,

you can remove the old cluster with

    pg_dropcluster 9.6 main

 

Ver Cluster Port Status Owner    Data directory               Log file

9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

Ver Cluster Port Status Owner    Data directory              Log file

12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

$ pg_dropcluster 9.6 main

$ apt --auto-remove remove postgresql-9.6

 

You’ll probably get a lot of warnings that look something like:

 

pg_dump: warning: WITH OIDS is not supported anymore (table ""xxxx"")

 

Don’t worry, those should get fixed when you start XNAT up again.

 

I would strongly recommend dumping your database before safekeeping before doing this!

 

$ pg_dump > xnat.sql

 

-- 

Rick Herrick

Sr. Programmer/Analyst

Neuroinformatics Research Group

Washington University School of Medicine

Phone: +1 (314) 273-1645

 

From: xnat_di...@googlegroups.com <xnat_di...@googlegroups.com> on behalf of Grace <xlix...@gmail.com>
Date: Monday, June 14, 2021 at 12:24 PM
To: xnat_discussion <xnat_di...@googlegroups.com>
Subject: [XNAT Discussion] Re: update PostgreSQL

* External Email - Caution *

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/e89e7a56-7c8a-4fad-a18a-8984c7fd1d59n%40googlegroups.com.

 

---

The materials in this message are private and may contain Protected Healthcare Information or other information of a sensitive nature. If you are not the intended recipient, be advised that any unauthorized use, disclosure, copying or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error, please immediately notify the sender via telephone or return mail.

[loren...@gmail.com]

Hi Rik,

I am having a hard time trying to upgrade PostgreSQL 10 -> 12 (Ubuntu 18.04), hope you can give me some insights on what I am doing wrong! 

Following your instructions in this thread seems a neat solution, but I get stuck in 'pg_upgradecluster 10 main' (before that everything goes smoothly, and looks like your pasted output, just changing 9.6->10). This happens when trying to start up the new cluster, I pasted the end of messages while running that (as sudo, otherwise there are permission issues) at the end of this email, but the reason seems to be: 

[10970] FATAL:  configuration file "/etc/postgresql/12/main/postgresql.conf" contains errors

I haven't touched that .conf file, and comparing it to /etc/postgresql/10/main/postgresql.conf, the only changes are 10->12 and the ports, which are matching the clusters correctly i.e. with v12 in 5432 and v10 in 5433: 

~$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file

10  main    5433 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

However two things are interesting in the log file /var/log/postgresql/postgresql-12-main.log: i) it seems to still be listening to port 5433 when starting v12, and ii) it seems to have changed the timezone somehow from BST to UTC - I  don't know if the last one matters, but the corresponding log file for v10 is all in UTC; in addition, chrony is installed and running, showing the correct time for the machine in BST (I also tried stopping chrony before upgrading but the result was the same). This is the full log file /var/log/postgresql/postgresql-12-main.log:  

2021-06-28 18:26:05.731 BST [3612] LOG:  starting PostgreSQL 12.7 (Ubuntu 12.7-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0,64-bit

2021-06-28 18:26:05.732 BST [3612] LOG:  listening on IPv4 address "127.0.0.1", port 5433

2021-06-28 18:26:05.733 BST [3612] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"

2021-06-28 18:26:05.754 BST [3613] LOG:  database system was shut down at 2021-06-28 18:26:04 BST

2021-06-28 18:26:05.763 BST [3612] LOG:  database system is ready to accept connections

2021-06-28 18:26:48.348 BST [3612] LOG:  received fast shutdown request

2021-06-28 18:26:48.352 BST [3612] LOG:  aborting any active transactions

2021-06-28 18:26:48.362 BST [3612] LOG:  background worker "logical replication launcher" (PID 3619) exited with exit code 1

2021-06-28 18:26:48.363 BST [3614] LOG:  shutting down

2021-06-28 18:26:49.105 BST [3612] LOG:  database system is shut down

2021-06-28 17:26:50.092 UTC [3738] LOG:  tables declared WITH OIDS are not supported

2021-06-28 17:26:50.092 UTC [3738] FATAL:  configuration file "/etc/postgresql/12/main/postgresql.conf" contains errors

pg_ctl: could not start server

Examine the log output.

Something else I noticed is that when installing (sudo apt-get install postgresql-12) the locale and encoding are: 

The database cluster will be initialized with locale "C.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

But when upgrading (sudo pg_upgradecluster 10 main) the line about the encoding is missing: 

The database cluster will be initialized with locale "C.UTF-8".

The default text search configuration will be set to "english".

And in both cases I can see a line like: 

selecting default time zone ... Europe/London

So I don't know what to try next. Any ideas? 

Many thanks,

Lorena 

*******

~$ sudo pg_upgradecluster 10 main

... 

Fixing hardcoded library paths for stored procedures...

Upgrading database xnat...

pg_dump: warning: WITH OIDS is not supported anymore (table ""xs_par_table"")

pg_dump: warning: WITH OIDS is not supported anymore (table ""dependencies_saved_ddl"")

Analyzing database xnat...

Fixing hardcoded library paths for stored procedures...

Upgrading database postgres...

Analyzing database postgres...

Fixing hardcoded library paths for stored procedures...

Upgrading database template1...

Analyzing database template1...

Copying old configuration files...

Copying old start.conf...

Copying old pg_ctl.conf...

Stopping target cluster...

Stopping old cluster...

Disabling automatic startup of old cluster...

Configuring old cluster to use a different port (5433)...

Starting target cluster on port 5432...

Job for postg...@12-main.service failed because the service did not take the steps required by its unit configuration.

See "systemctl status postg...@12-main.service" and "journalctl -xe" for details.

Error: Could not start target cluster; please check configuration and log files

[Herrick, Rick]

Sorry, I have no idea what’s going on there. The procedure I wrote out earlier is the limit of my expertise in this area 😊

 

You can try the pg_dump export/psql import method as a work-around:

 

1. Dump your existing database with pg_dump
2. Uninstall the old version of PostgreSQL
3. Install the new version of PostgreSQL
4. Import your database with psql

 

The psql command for the import is simple, just add --file=filename.sql, with the caveat that you may need to add command-line options for database host, user, etc.

 

$ pg_dump > xnat.sql

$ psql --file=xnat.sql

 

Before doing this, you should back up your database in any way possible, including copying/archiving the various PostgreSQL data and configuration folders.

 

-- 

Rick Herrick

XNAT Architect/Developer

Computational Imaging Laboratory

To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/35f93972-9ee9-491c-8391-5cdcfb6720fbn%40googlegroups.com.

[loren...@gmail.com]

Hi Rik,

Thank you! I think I have a magnet for weird errors... 

I implemented something similar but slightly simpler, by installing the two servers, then doing a dump from one to another as suggested at the end of 18.6.1 here, explicitly: 

~$ 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

~$ sudo apt-get install postgresql-12

~$ 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

12  main    5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

~$ su - postgres

postgres:~$ pg_dumpall -p 5432 | psql -d postgres -p 5433

postgres:~$ exit

~$ sudo systemctl stop postg...@10-main.service

~$ sudo pg_dropcluster 10 main

~$ Edit /etc/postgresql/12/main/postgresql.conf  > change: port = 5433 > to: port = 5432

~$ sudo systemctl restart postg...@12-main.service

~$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file

12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

The only difference I see is in their connections, do I need to change pg_hba.conf? 

~$ sudo netstat -tulpen

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode      PID/Program name    

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      112        16284      971/postgres        -> this one disappears after dropping v10

tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      112        28972      3735/postgres   -> this one changes to 5432 for v12 at the end             

tcp6       0      0 :::5432                 :::*                    LISTEN      112        16285      971/postgres                 -> this one disappears after dropping v10

I checked various things in XNAT after upgrading, and it all seems to be working fine: change of email, change of password, created new user, deleting a subject, uploading and viewing images... If there is anything else or any other way I should check please let me know!

Best,

Lorena 

[Herrick, Rick]

I think if you can start XNAT and log in, that means the connection between the database and XNAT is working fine. If XNAT can’t connect to the database it becomes very unhappy very quickly and won’t start up, but if it does you should be good.

To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/b601d501-0b9d-4e95-800b-35fe1c74adacn%40googlegroups.com.