• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • Upgrade PostgreSQL from 9.5 to 11.3

      Upgrade PostgreSQL from 9.5 to 11.3

      • Posted by Nijamutheen J
      • Categories Blog
      • Date July 18, 2019
      • Comments 0 comment

      In this blog we are going to upgrade the postgresql server from 9.5 to 11.3 .

      We are upgraded the postgres server by using pg_upgrade utility as well as logical backup method ,

      you can follow anyone  of the method .

       

       

      Collect the server details before proceed  upgrade activity.

       s/no. PostgreSQL VERSION 9.5 PostgreSQL VERSION 11.3
      DATA DIRECTORY /data_9.5 /data_11.3
      BIN path /opt/postgresql/9.5 /opt/postgresql/11.3
      PORT 50000 5432

       

      PREREQUEST :
      ===========

      Step 1. Check the application backend connection if any application connected , Disconnect the application from DB server.

      Below command will be helpful to checking backend connection :

      template1=# select datname, count(*) from pg_stat_activity group by datname;
        datname  | count 
      -----------+-------
       testdb    |     1
       template1 |     0
      (2 rows)
       
      template1=# select datname, numbackends from pg_stat_database;
        datname  | numbackends 
      -----------+-------------
       testdb    |           1
       template1 |           0
       template0 |           0
      (3 rows)

      Comment the application details in pg_hba.conf and restart the old cluster before taking backup,

      # host    all             all             54.19.98.56/32          trust
      # host    all             all             55.19.97.86/32          trust

      Step 2. Take the full (physical/logical) backup of existing postgres (9.5) server.

      /opt/postgresql/9.5.6/bin/pg_dumpall" -p 50000 -U pgstain -h localhost -w > "/backup/db_bkp_`date +%d%b%y%H%M%S`.dmp"

      Step 3. Below query helpful to find the changed parameter (no default) value of postgresql.conf file .

      SELECT name,setting,source FROM pg_settings
      WHERE source NOT IN
      ('default','client','override','environment variable'); 
      
                  name            |                          setting                          |       source
      ----------------------------+-----------------------------------------------------------+--------------------
       archive_command            | gzip < %p > /Archive/%f | configuration file
       archive_mode               | on                                                        | configuration file
       DateStyle                  | ISO, MDY                                                  | configuration file
       default_text_search_config | pg_catalog.english                                        | configuration file
       dynamic_shared_memory_type | posix                                                     | configuration file
       lc_messages                | en_US.UTF-8                                               | configuration file
       lc_monetary                | en_US.UTF-8                                               | configuration file
       lc_numeric                 | en_US.UTF-8                                               | configuration file
       lc_time                    | en_US.UTF-8                                               | configuration file
       listen_addresses           | *                                                         | configuration file
       log_directory              | log                                                       | configuration file
       log_filename               | postgresql-%a.log                                         | configuration file
       log_rotation_age           | 1440                                                      | configuration file
       log_timezone               | PRC                                                       | configuration file
       log_truncate_on_rotation   | on                                                        | configuration file
       logging_collector          | on                                                        | configuration file
       max_connections            | 203                                                       | configuration file
       max_wal_size               | 1024                                                      | configuration file
       min_wal_size               | 80                                                        | configuration file
       port                       | 64001                                                     | configuration file
       shared_buffers             | 16384                                                     | configuration file
       TimeZone                   | PRC                                                       | configuration file
      (22 rows)

      Step 4. Backup the application details (pg_hba.conf) of old postgres server.

      For example
      i have taken sample application details they are.

      host    all             all             54.19.98.56/32          trust
      host    all             all             55.19.97.86/32          trust

      Step 5. Issue the ” \l+ ” & Check the database sizes .

       

      IMPLEMENTATION :
      ===============

      Postgres database installation 3 types, they are

      1. GRAPHICAL Installation  (./ postgresql.run) .
      2. BINAY INSTALLATION  (RPM) .
      3. SOURCE CODE INSTALLATION  (configure —) .

      Follow the below steps to install postgres database , we have given steps for rpm installation & source code installation types for study purposes, you can install anyone type as per your comfortable.

      Binary Installation :
      =============
      Step 1. As root user install the below packages .

      rpm -ivh postgresql-infrastructure-1-6.noarch.rpm
      rpm -ivh postgresql113-libs-11.3-1.x86_64.rpm
      rpm -ivh postgresql113-server-11.3-1.x86_64.rpm 
      rpm -ivh postgresql113-contrib-11.3-1.x86_64.rp
      rpm -ivh postgresql113-devel-11.3-1.x86_64.rpm 
      rpm -ivh postgresql113-docs-11.3-1.x86_64.rpm
      rpm -ivh postgresql113-plperl-11.3-1.x86_64.rpm 
      rpm -ivh postgresql113-plpython-11.3-1.x86_64.rpm 
      rpm -ivh postgresql113-pltcl-11.3-1.x86_64.rpm
      rpm -ivh postgresql113-server-11.3-1.x86_64.rpm
      rpm -ivh postgresql113-test-11.3-1.x86_64.rpm

      Step 2. Now create a postgres user and directory to be used as data directory for initializing database cluster. Owner of this data directory should be postgres user and permissions should be 700 and also set path for postgresql binaries for our ease.

      # useradd pgstain
      # passwd pgstain
      # mkdir -p /data_11.3/
      # chown -R pgstain. /data_11.3/

      Step 3. Initialize the postgres cluster as user pgstain .

      $ /opt/postgresql/11.3/bin/initdb -D /data_11.3/ -U pgstain

      ->>> Where -D is location for this database cluster or we can say it is data directory where we want to initialize database cluster, -U for database superuser name.

      Step 4. Start the new cluster as user pgstain .

      /opt/postgresql/11.3/bin/pg_ctl -D /data_11.3/ restart

       

      Source code installation :
      ==================

      1. First install required prerequisites such as gcc, readline-devel and zlib-devel using package manager as shown.

      # yum install gcc zlib-devel readline-devel     [On RHEL/CentOS]
      # apt install gcc zlib1g-dev libreadline6-dev   [On Debian/Ubuntu]
      # Zypper in gcc zlib1g-dev libreadline6-dev     [On SUSE Linux   ]

      2. Download the source code tar file from the official postgres website using the following wget command directly on system.

      # wget https://ftp.postgresql.org/pub/source/v11.3/postgresql-11.3.tar.gz

      3. Use tar command to extract the downloaded tarball file. New directory named postgresql-11.3 will be created.

      # tar -xvzf postgresql-11.3.tar.gz
      
      # ll
      Sample Output
      total 26328
      drwxr-xr-x 2 pgstain pgstain     4096 Sep 21  2014 bin
      -rwxr-xr-x 1 pgstain pgstain      347 Nov  7  2017 init-user-db.sh
      drwxr-xr-x 6 pgstain pgstain     4096 May  7 04:59 postgresql-11.3
      -rw-r--r-- 1 pgstain pgstain 25868246 Jul  8 12:04 postgresql-11.3.tar.gz
      drwxr-xr-x 2 pgstain pgstain     4096 May 17  2017 public_html

      4. Next step for installation procedure is to configure the downloaded source code by choosing the options according to your needs.

      # cd postgresql-11.3
      # ls -l
      total 764
      -rw-r--r--  1 pgstain pgstain    730 May  7 04:46 .dir-locals.el
      -rw-r--r--  1 pgstain pgstain   1622 May  7 04:46 .gitattributes
      -rw-r--r--  1 pgstain pgstain    504 May  7 04:46 .gitignore
      -rw-r--r--  1 pgstain pgstain   1192 May  7 04:46 COPYRIGHT
      -rw-r--r--  1 pgstain pgstain   3848 May  7 04:46 GNUmakefile.in
      -rw-r--r--  1 pgstain pgstain    284 May  7 04:46 HISTORY
      -rw-r--r--  1 pgstain pgstain  74257 May  7 04:59 INSTALL
      -rw-r--r--  1 pgstain pgstain   1682 May  7 04:46 Makefile
      -rw-r--r--  1 pgstain pgstain   1212 May  7 04:46 README
      -rw-r--r--  1 pgstain pgstain    522 May  7 04:46 aclocal.m4
      drwxr-xr-x  2 pgstain pgstain   4096 May  7 04:58 config
      -rwxr-xr-x  1 pgstain pgstain 561752 May  7 04:46 configure
      -rw-r--r--  1 pgstain pgstain  84451 May  7 04:46 configure.in
      drwxr-xr-x 56 pgstain pgstain   4096 May  7 04:58 contrib
      drwxr-xr-x  3 pgstain pgstain   4096 May  7 04:58 doc
      drwxr-xr-x 16 pgstain pgstain   4096 May  7 04:59 src

      Note : use ./configure –help to get help about various options.

      5. Now create a directory where you want to install postgres files and use prefix option with configure.

      # mkdir /opt/postgresql/11.3/
      # ./configure --prefix=/opt/postgresql/11.3/   
      (or)  
      #./configure --prefix=/opt/postgresql/11.3/  --without-readline

      :- To Know more the out put click below link

      Sample OutPuts

      6. After configuring, next we will start to build postgreSQL using following make command.

      # make          (or) # make world  (additional modules (contrib), type instead PostgreSQL, contrib, and documentation
      # make install   (or) # make install-world (if you want contribution extension)

      7. Postgresql 11 has been installed in “/opt/postgresql/11.3/” directory. now create a postgres user and directory to be used as data directory for initializing database cluster. Owner of this data directory should be postgres user and permissions should be 700 and also set path for postgresql binaries for our ease.

      # useradd pgstain
      # passwd pgstain
      # mkdir -p /data_11.3/
      # chown -R pgstain.pgstain /data_11.3/

      8. Now initialize database using the following command as postgres user before using any postgres commands.

      # su pgstain
      $ /opt/postgresql/11.3/bin/initdb -D /data_11.3/ -U pgstain

      Where -D is location for this database cluster or we can say it is data directory where we want to initialize database cluster, -U for database superuser name.

      For more info and options we can refer initdb –help.

      9. After initializing database, start the database cluster or if you need to change port or listen address for server, edit the postgresql.conf file in data directory of database server.

      change the port & listen_address as per the application and restart the postgres database.

      $  /opt/postgresql/11.3/bin/pg_ctl -D /data_11.3/ -l logfile start

      10. After starting database, verify the status of postgres server process by using following commands.

      $ ps -ef |grep -i postgres
      $ netstat -apn |grep -i 5432

      We can see that database cluster is running fine, and startup logs can be found at location specified with -l option while starting database cluster.

      11. Now connect to database cluster and create database by using following commands.

      $ psql -p 5432
      postgres=# \l   --- to list all databases in cluster
      postgres=# \q   --- to quit form postgres console

      12. Create the environment variable file then only you can able to access the postgres utility directly on home path without going to bin path.

      $ cat /home/pgstain/.profile
      export PATH=/opt/postgresql/11.3/bin:$PATH
      export PGHOME=/opt/postgresql/11.3
      export PGDATA=/data_11.3
      export LD_LIBRARY_PATH=/opt/postgresql/11.3/lib
      export PGDATABASE=pgstain
      export PGUSER=pgstain
      export PGPORT=5432
      export PGLOCALEDIR=/opt/postgresql/11.3/share/locale
      export MANPATH=$MANPATH:/opt/postgresql/11.3/share/man

      POSTGRES UPGRADATION :
      =====================

      Step 1. Stop the old Postgres Server & start the new postgres server.

      $ /opt/postgresql/9.5.5/bin/pg_ctl stop -D /data_9.5

      Step 2. Restore the backed up file into new cluster server.

      $ /opt/postgresql/11.3/bin/psql -U pgstain -p 5432 -d postgres -f /backup/db_bkp_11Jul19112808.dmp
                                                       
         (OR)
      
      {  
      1. Stop the both server 9.5 as well as 11.3
      2. Below command will check the postgres upgrade compatibility.
       /opt/postgresql/11.3/bin/pg_upgrade -d /data_9.5 -D /data_11.3 -b /opt/postgresql/9.5.5/bin -B /opt/postgresql/11.3/bin -o 64000 -O 5432 -c
      3. Below command will upgrade the postgres server.
      /opt/postgresql/11.3/bin/pg_upgrade -d /data_9.5 -D /data_11.3 -b /opt/postgresql/9.5.5/bin -B /opt/postgresql/11.3/bin -o 64000 -O 5432
      
      }

      Step 7. Change the postgresql.conf file as per “prerequest” step 2.

      $ cat postgresql.conf
      listen_addresses = '*'         
      port = 50000  
      
      ## To keep 7 days of logs, one log file per day named postgresql-Mon.log, postgresql-Tue.log, etc, and automatically overwrite last week's log .
      
      logging_collector=on
      log_filename = 'postgresql-%a.log'
      log_directory = 'log'
      log_rotation_age = 1d 
      log_truncate_on_rotation=on  
      
      ## To enabling archive logs
      
      archive_mode=on
      archive_command = 'gzip < %p > /Archive/%f'
      .....
      ...
      .etc

      Step 8. put the Web/Apps details on pg_ha.conf files as per pre request step 3 .

      $ cat pg_hba.conf
      
      # TYPE  DATABASE        USER            ADDRESS                 METHOD
      
      # "local" is for Unix domain socket connections only
      local   all             all                                     trust
      # IPv4 local connections:
      host    all             all             127.0.0.1/32           trust
      # IPv6 local connections:
      host    all             all             ::1/128                 trust
      # Allow replication connections from localhost, by a user with the
      # replication privilege.
      #local   replication     pgstain                                trust
      #host    replication     pgstain        127.0.0.1/32            trust
      #host    replication     pgstain        ::1/128                 trust
      host    all             all             127.0.0.1/32            trust
      host    all             all             54.19.98.56/32          trust
      host    all             all             55.19.97.86/32          trust

      Step 9. Restart the new server to reflect all parameters.

      $ /opt/postgresql/11.3/bin/pg_ctl -D /data_11.3  restart

      Step 10. Change the backup script as per new server path

      "/opt/postgresql/11.3/bin/pg_dumpall" -p 50000 -U pgstain -w > "/backup/db_bkp_`date +%d%b%y%H%M%S`.dmp"
      find /backup/ -mtime +3 -exec rm -rf {} \;
      find /Archive/ -type f -mtime +3  -exec rm -rf {} \;

       

      POSTREQUEST :
      ============

      1. Issue the below query again. to get parameter is updated/changed details and compare it with old paramer ( Pre request step 2) .

      SELECT name,setting,source FROM pg_settings
      WHERE source NOT IN
      ('default','client','override','environment variable');

      2. Check the database size again ” \l+ ” .

      3. Wait for web/Apps data validation, Once you get confirmation uninstall the old postgres version & delete the old data directories.

      Connection checking after restored:

      pgstain@SCDCB0000181:/Archive> ps -ef|grep postgres
      pgstain  40583     1  0 22:16 ?        00:00:00 /opt/postgresql/11.3/bin/postgres -D /data_11.3
      pgstain  40585 40583  0 22:16 ?        00:00:01 postgres: checkpointer
      pgstain  40586 40583  0 22:16 ?        00:00:00 postgres: background writer
      pgstain  40587 40583  0 22:16 ?        00:00:03 postgres: walwriter
      pgstain  40588 40583  0 22:16 ?        00:00:00 postgres: autovacuum launcher
      pgstain  40589 40583  0 22:16 ?        00:00:00 postgres: archiver   last was 00000001000000020000002D
      pgstain  40590 40583  0 22:16 ?        00:00:00 postgres: stats collector
      pgstain  40591 40583  0 22:16 ?        00:00:00 postgres: logical replication launcher
      root     42056  2324  6 22:28 ?        00:00:10 /usr/sbin/save -LL -s scdca0000044.adcchina.corpintra.net -g Backup53/P0978_D_B1_LIN_FI_02/backup/P0978_D_B1_LIN_FI_02 -a *policy action jobid=174165 -a *policy name=Backup53 -a *policy workflow name=P0978_D_B1_LIN_FI_02 -a *policy action name=backup -y Tue Jul 30 23:59:59 GMT+0800 2019 -w Tue Jul 30 23:59:59 GMT+0800 2019 -m SCDCB0000181-bn1.cn.bg.corpintra.net -b ADCC1 B1 DDBOOST POOL -t 1563200881 -o MODIFIED_ASOF_TIME:timeval=1563200880;RENAMED_DIRECTORIES:index_lookup=on;BACKUPTIME:lookup_range=1563114637:1563200881;REQUESTED_LEVEL:level=incr; -l incr -q -W 78 -N /srv/postgresql/var/inst1/log /srv/postgresql/var/inst1/log
      root     42098  2324  0 22:30 ?        00:00:00 /usr/sbin/save -LL -s scdca0000044.adcchina.corpintra.net -g Backup53/P0978_D_B1_LIN_FI_02/backup/P0978_D_B1_LIN_FI_02 -a *policy action jobid=174165 -a *policy name=Backup53 -a *policy workflow name=P0978_D_B1_LIN_FI_02 -a *policy action name=backup -y Tue Jul 30 23:59:59 GMT+0800 2019 -w Tue Jul 30 23:59:59 GMT+0800 2019 -m SCDCB0000181-bn1.cn.bg.corpintra.net -b ADCC1 B1 DDBOOST POOL -t 1563200994 -o RENAMED_DIRECTORIES:index_lookup=on;BACKUPTIME:lookup_range=1563114642:1563200994;REQUESTED_LEVEL:level=incr; -l incr -q -W 78 -N /srv/postgresql/data /srv/postgresql/data
      pgstain  42173 41182  0 22:31 pts/1    00:00:00 grep --color=auto postgres

       

      4. Check the application backend connection if application connected disconnect the application from DB server 

      Below command will be helpful to checking backend connection :
       template1=# select datname, count(*) from pg_stat_activity group by datname;
        datname  | count 
      -----------+-------
       testdb    |     8
       template1 |     1
      (2 rows)
       
      template1=# select datname, numbackends from pg_stat_database;
        datname  | numbackends 
      -----------+-------------
       testdb    |           8
       template1 |           1
       template0 |           0
      (3 rows)

       

       

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      ./configure --prefix=/opt/postgresql/11.3/
      July 18, 2019

      Next post

      Not able to enable postgresql archive log ?
      July 21, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Don’t See What You’re Looking For? We’ll Customize a Package to Fit Your Business Office space!
      14May2022
      Need an Address, Phone Number, And Virtual Office In Chennai?
      14May2022
      Rayafeel Technologies can provide you with ideal meeting solutions
      14May2022
      Virtual Offices VS. Coworking Spaces: What’s The Difference?
      14May2022
      SQL Manager for Oracle Type 6 – 10
      13May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?