• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      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:
      Admin bar avatar
      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 Cancel reply

      You must be logged in to post a comment.

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • thought behind whiteboard activity
      • Are you going to take your first ste
      • How to start working on an application?
      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      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

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now