Problem with postgres upgrade

I"m trying to help someone who refused to upgrade to PG13 and now has an expired SSL cert.

It may be made more difficult because of Apt-get update fails inside container yarn repo not signed - #5 by pfaffman, but I modified the pg12 and pg13 templates so that they should work, but I can’t get it to upgrade and I can’t get it to rebuild with the pg12 template.

Here’s what happens when I tried to do the upgrade:

                                     
fixing permissions on existing directory /var/lib/postgresql/12/main ... ok          
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100                                                                                                              
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC                              
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
                                     
Success. You can now start the database server using:
                                                                                                                                                       
    pg_ctlcluster 12 main start                                                                                                                        
                                                                           
update-alternatives: warning: forcing reinstallation of alternative /usr/share/postgresql/13/man/man1/postmaster.1.gz because link group postmaster.1.g
z is broken                                                                                                                                            
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.             
Processing triggers for postgresql-common (246.pgdg110+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:                                                                                                                    
Stopping PostgreSQL 12 database server: main.                                                                                                          
Stopping PostgreSQL 13 database server: main.                                                                                                          
Performing Consistency Checks               -----------------------------                                                                                                                          
Checking cluster versions                                   ok                                                                                         
Checking database user is the install user                  ok                                                                                         
Checking database connection settings                       ok                                                                                         
Checking for prepared transactions                          ok                                                                                         
Checking for system-defined composite types in user tables  ok                                                                                         
Checking for reg* data types in user tables                 ok                                                                                         
Checking for contrib/isn with bigint-passing mismatch       ok                                                                                         
Creating dump of global objects                             ok                                                                                         
Creating dump of database schemas                                                                                                                      
  discourse                                                                                                                                            
                                                                                                                                                       
*failure*                                                                                                                                              

Consult the last few lines of "pg_upgrade_dump_16566.log" for
the probable cause of the failure.
Failure, exiting
-------------------------------------------------------------------------------------
UPGRADE OF POSTGRES FAILED

Please visit https://meta.discourse.org/t/postgresql-13-update/172563 for support.

You can run ./launcher start app to restart your app in the meanwhile

Trying to rebuild without the upgrade has stuff like the below when it tries to migrate the database:

PG::InsufficientPrivilege: ERROR:  permission denied to create extension "unaccent"                                                                    

I think I’ll have to revert to a clean install and the latest backup, which is several days old.

Can’t you add the below to hooks to create the unaccent extension if it’s missing?

after_postgres:
    - exec: su postgres -c 'psql discourse -c "create extension if not exists unaccent;"'
3 Likes

Sounds like a plan! I’ll check it out on the morning. Thanks

1 Like

Looks like that did it! I had to add it to the pg12 and postgres.

Well, it did it enough to get it running again with PG12, but I never got the PG13 upgrade to work. I made a backup. moved the postgres stuff to a backup directory, rebuilt, and restored the backup.

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.