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 :
Check for new version:
We have installed version 9.4 Checking status of running cluster:
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:
Package's part is finished. next step is to migrate our database to the new version.
Check active DB cluster:
Only one old (9.4 ) is active. It is a time to upgrade DB. I've stopped all clients which are using the database.
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:
That are all. I'm goint enable partituoning at this database next day
I've used idea from this URL:
aptitude update Get: 1 http://ftp.debian.org/debian jessie-updates InRelease [145 kB] Get: 2 http://security.debian.org stretch/updates InRelease [62.9 kB] Get: 3 http://security.debian.org jessie/updates InRelease [63.1 kB]
Check for new version:
dpkg-query -l postgresql* Desired=Unknown/Install/Remove/Purge/Hold | 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
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
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
Comments
Post a Comment