Skip to main content

Upgrade postgres 9.4 to 9.6 at Debian linux

I'm using virtual server based on Debian Linux for some my free projects.  One of them is based on Postgres with PostGIS extension.   This windy rainy day is a good time for executing some upgrade. Also I would like to add partman extension for partituoning.

Checking new version. 

Update package list :
aptitude update 
Get: 1 jessie-updates InRelease [145 kB]                                                                                           
Get: 2 stretch/updates InRelease [62.9 kB]                                                                                    
Get: 3 jessie/updates InRelease [63.1 kB] 

 Check for new version:

dpkg-query -l postgresql* 
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                                  Version                 Architecture            Description
un  postgresql-7.4                        <none>                  <none>                  (no description available)
un  postgresql-8.0                        <none>                  <none>                  (no description available)
un  postgresql-9.1                        <none>                  <none>                  (no description available)
ii  postgresql-9.4                        9.4.12-0+deb8u1         amd64                   object-relational SQL database, version 9.4 server
un  postgresql-9.4-postgis-2.0-scripts    <none>                  <none>                  (no description available)
ii  postgresql-9.4-postgis-2.1            2.1.8+dfsg-4            amd64                   Geographic objects support for PostgreSQL 9.4
un  postgresql-9.4-postgis-2.1-scripts    <none>                  <none>                  (no description available)
ii  postgresql-9.4-postgis-scripts        2.1.8+dfsg-4            all                     Geographic objects support for PostgreSQL 9.4 -- scripts
un  postgresql-client                     <none>                  <none>                  (no description available)
ii  postgresql-client-9.4                 9.4.12-0+deb8u1         amd64                   front-end programs for PostgreSQL 9.4
ii  postgresql-client-common              181                     all                     manager for multiple PostgreSQL client versions
ii  postgresql-common                     181                     all                     PostgreSQL database-cluster manager
ii  postgresql-contrib-9.4                9.4.12-0+deb8u1         amd64                   additional facilities for PostgreSQL
un  postgresql-doc-9.4                    <none>                  <none>                  (no description available)
un  postgresql-doc-9.6                    <none>                  <none>                  (no description available)

We have installed version 9.4 Checking status of running cluster:

root@sergiy:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Host has one cluster version 9.4. At this moment we can install new one Postgres using new claster with a new version.  I'm including few new packages:

oot@sergiy:~# aptitude search postgresql-9.6
p   postgresql-9.6                                                                - object-relational SQL database, version 9.6 server                                      
p   postgresql-9.6-asn1oid                  

root@sergiy:~# aptitude install postgresql-9.6  postgresql-9.6-postgis-scripts    postgresql-9.6-postgis-2.3  postgresql-9.6-partman 
The following NEW packages will be installed:
  ghostscript{a} i965-va-driver{a} libaec0{a} libarmadillo7{a} libass5{a} libasyncns0{a} libavc1394-0{a} libavcodec57{a} libavdevice57{a} libavfilter6{a} 
3 packages upgraded, 138 newly installed, 1 to remove and 108 not upgraded.
Need to get 84.3 MB of archives. After unpacking 370 MB will be used.
Do you want to continue? [Y/n/?] y

Setting up postgresql-9.6 (9.6.4-0+deb9u1) ...
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433
update-alternatives: using /usr/share/postgresql/9.6/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
[ ok ] Starting PostgreSQL 9.6 database server: main.

Package's part is finished. next step is to migrate our database to the new version.

Database upgrade. 

The first step is dropping our new DB claster created by an installer. 

pg_dropcluster 9.6 main --stop 

Check active DB cluster:

root@sergiy:~# pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Only one old (9.4 ) is active.  It is a time to upgrade DB. I've stopped all clients which are using the database.

root@sergiy:~# pg_upgradecluster 9.4 main 
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database geocar...
Analyzing database geocar...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
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...
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 the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

  pg_dropcluster 9.4 main
I would like to wait some time to be sure that 9.6 is OK  for me.  I 'm going to delete old cluster next week.  Check if new cluster uses old port 5432 by next command:

root@sergiy:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5433 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

That are all. I'm goint enable partituoning at this database next day
I've used idea from this URL:


Popular posts from this blog

Small script for one of the plarium game

few notes about google games.
Goggle informed that access to the all games be closed after June, 30. I played "Pirates: Tides of fortune" and decided to limit my game time using small script. Detail is below.
Some history
First internet games were updated network games only. 15 years ago internet connection requires a lot of money and nobody had a problem with lagging of the other players. Usually games uses personal communication protocol. Warbirds, Aces High and Eve online use this way.
Next part use a browser and trivial HTTP protocol. One of the popular game in this area is travian . Travian player uses browser and every browser (PC, cell phone, tablet) can be used for playing. Of course, popularity of the game is related to graphics. Trivial HTTP does not have good power in this and other technology is used for this side. One of them is Flash . Unfortunately flash requires a lot of CPU : "Pirates" was near to froze at my old celeron with 512M…

Small python script for monitoring MySQL performance

I have few services which use MySQL as database server. I would like to have information about load in PNG  image  or  in Cacti app.
MySQL   has  performance information at 'SHOW STATUS' command.

Values  which  are monitored : 
 threads_running, threads_connected, thread_cached, slow_queries
 Of course,  it is really easy to add more variables.

Connection to MySQL is accomplished by MySQLdb  module. Typical example of usage is below :
import MySQLdb mydb = MySQLdb.connect(host = 'hostname', user = 'username', password = 'secret', database = 'mysatabase' ) mycursor = mydb.cursor() mycursor.execute('SQL command') sqlresult = cur.fetchall()
Storing data in rrd file is aviable via rrdtools package. This one is present in debian and Centos OS. example of creating file is below: import rrdtool rrdtool.create("myfile.rrd" , "DS:value1:datatype:hea…