Opened 3 years ago

Closed 16 months ago

#11583 closed Bug/Something is broken (fixed)

upgrade postgres on all moshes

Reported by: Jamie McClelland Owned by: Jamie McClelland
Priority: Urgent Component: Tech
Keywords: postgres backup Cc:
Sensitive: no

Description

Most of our moshes are now running postgres 9.4, whereas before the jessie upgrade they were running postgres 9.1.

Upon reboot, it appears that the old postgres 9.1 cluster is not started and the new postgres 9.4 is started, but using a non-default port.

That means that any postgres databases are not running, pending an upgrade to 9.4.

It also means we are getting errors when backups are run because the backup can't dump the postgres databases.

Change History (6)

comment:1 Changed 3 years ago by Jamie McClelland

Owner: set to Jamie McClelland
Status: newassigned

Here are the steps to fix (taken from #7770) for a typical mosh that has no postgres databases to begin with:

  • Become the postgres user and see which clusters are installed
    0 colin:~# su - postgres
    postgres@colin:~$ pg_lsclusters 
    Ver Cluster Port Status Owner    Data directory               Log file
    8.4 main    5432 down   postgres /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log
    9.1 main    5433 down   postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
    9.4 main    5434 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    postgres@colin:~$
    
  • Ensure all clusters are started
    postgres@colin:~$ pg_ctlcluster 8.4 main start
    Removed stale pid file.
    postgres@colin:~$ pg_ctlcluster 9.1 main start
    postgres@colin:~$
    
  • See if any of the clusters have any databases
    postgres@colin:~$ psql --port 5432 --list
                                 List of databases
       Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
    -----------+----------+-----------+---------+-------+-----------------------
     postgres  | postgres | SQL_ASCII | C       | C     | 
     template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
               |          |           |         |       | postgres=CTc/postgres
     template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
               |          |           |         |       | postgres=CTc/postgres
    (3 rows)
    
    postgres@colin:~$ psql --port 5433 --list
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    (3 rows)
    
    postgres@colin:~$ psql --port 5434 --list
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    (3 rows)
    
    postgres@colin:~$ 
    
  • At this point we don't really have anything to upgrade, but it doesn't hurt and if you do the upgrade process it will ensure that the newest version of postgres is running on the default port.
  • Drop the newest cluster
    postgres@colin:~$ pg_ctlcluster 9.4 main stop
    postgres@colin:~$ pg_dropcluster 9.4 main
    postgres@colin:~$
    
  • Upgrade from the oldest to the newest (we are skipping the in between one) - it upgrades to the newest available by default)
    postgres@colin:~$ pg_upgradecluster 8.4 main
    Stopping old cluster...
    Disabling connections to the old cluster during upgrade...
    Restarting old cluster with restricted connections...
    Creating new cluster 9.4/main ...
      config /etc/postgresql/9.4/main
      data   /var/lib/postgresql/9.4/main
      locale en_US.UTF-8
    Flags of /var/lib/postgresql/9.4/main set as ---------------C
      port   5434
    Disabling connections to the new cluster during upgrade...
    Roles, databases, schemas, ACLs...
    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...
    Re-enabling connections to the old cluster...
    Re-enabling connections to the new cluster...
    Copying old configuration files...
    Copying old start.conf...
    Copying old pg_ctl.conf...
    Copying old server.crt...
    Copying old server.key...
    Stopping target cluster...
    Stopping old cluster...
    Disabling automatic startup of old cluster...
    Configuring old cluster to use a different port (5434)...
    Starting target cluster on the original port...
    Success. Please check that the upgraded cluster works. If it does,
    you can remove the old cluster with
    
      pg_dropcluster 8.4 main
    postgres@colin:~$ 
    
  • Drop the old clusters
    postgres@colin:~$ pg_dropcluster 8.4 main
    postgres@colin:~$ pg_dropcluster 9.1 main
    Error: This cluster is still running. Stop it or supply the --stop option
    postgres@colin:~$ pg_ctlcluster 9.1 main stop
    postgres@colin:~$ pg_dropcluster 9.1 main
    postgres@colin:~$ 
    

comment:2 Changed 3 years ago by Jamie McClelland

All of the following moshes that had no active postgres databases have been upgrade to postgres 9.4 and the previous versions of postgres instances have been removed:

larkin.mayfirst.org
lewis.mayfirst.org
debs.mayfirst.org
tresca.mayfirst.org
magon.mayfirst.org
stokely.mayfirst.org
buffy.mayfirst.org
wolf.mayfirst.org
resnick.mayfirst.org
foucoult.mayfirst.org
menchu.mayfirst.org
eagle.mayfirst.org
slaapbeen.mayfirst.org
rushdie.mayfirst.org
bety.mayfirst.org
annapura.mayfirst.org
boggs.mayfirst.org
goldman.mayfirst.org
albizu.mayfirst.org
stoney.mayfirst.org
randolph.mayfirst.org
stone.mayfirst.org
annette.mayfirst.org
mandela.mayfirst.org
rivera.mayfirst.org
molina.mayfirst.org
binh.mayfirst.org
kahlo.mayfirst.org
kerr.mayfirst.org
kinoy.mayfirst.org
viewsic.mayfirst.org
Last edited 3 years ago by Jamie McClelland (previous) (diff)

comment:3 Changed 3 years ago by Jamie McClelland

The following are moshes that are running postgres databases that need to be upgraded to the lastest version of postgres:

  • viewsic
  • hammer
  • ossie
  • floriberto

And... there are still more moshes that need to be checked.

comment:4 Changed 3 years ago by JaimeV

All of the following moshes that had no active postgres databases have been upgraded to postgres 9.4 and the previous versions of postgres instances have been removed:

yippie malcolm june lucy lumumba rodolpho ekpo didier daza clara brown gaspar clara octavia ginsberg gilkey pauline

The following mosh's are only running v9.1 as they are still running wheezy: sojourner.mayfirst.org haring.mayfirst.org

The following mosh's are only running v8.4 as they are still running squeeze: jones hashmi

The following mosh's are only running v9.4: foucault marx hammer colin

This mosh is not running postgres: yser

These mosh's had active databases that were upgraded to version 9.4 proudhon julia dorothy chavez roe ossie chelsea floriberto

During this when attempting to upgrade the databases on rose I failed to realize that in this case someone had already configured version 9.4 to run on port 5432 and the previous postgres version on port 5433. I dumped the database and imported in the wrong direction and subsequently dropped the wrong cluster. I believe I have provoked data loss. I have attempted to recover files from backups but require more assistance.

Last edited 3 years ago by JaimeV (previous) (diff)

comment:5 Changed 3 years ago by Jamie McClelland

The problem on rose turned out to be fine - the database dropped was no longer needed.

Also... I have upgraded two servers that were missed in the previous round of upgrades: hammer and molina

comment:6 Changed 16 months ago by JaimeV

Resolution: fixed
Status: assignedclosed

This is done now. Postgres has been upgraded to 9.4 on all moshes and all older versions have been removed.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.