Infrastructure at your Service

Daniel Westermann

Patching PostgreSQL to a new minor release

If you are used to patch Oracle databases you probably know how to use opatch to apply PSUs. How does PostgreSQL handle this? Do we need to patch the existing binaries to apply security fixes? The answer is: No.

Lets say you want to patch PostgreSQL from version 9.4.1 to version 9.4.5. What do you need to do?

For this little demo I’ll create a new database and a sample table in my 9.4.1 instance:

([email protected][local]:5432) [postgres] > select version();
                                                         version                                                          
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

Time: 0.483 ms
([email protected][local]:5432) [postgres] > create database patch;
CREATE DATABASE
Time: 2533.745 ms
([email protected][local]:5432) [postgres] > \c patch
You are now connected to database "patch" as user "postgres".
([email protected][local]:5432)  > create table test ( a int );
CREATE TABLE
Time: 2.430 ms
([email protected][local]:5432)  > insert into test (a) values ( generate_series(1,100));
INSERT 0 100
Time: 0.959 ms

If I now want to bring this version to 9.4.5 the first step I’ll need to do is to install the 9.4.5 binaries in a separate path. The binaries for my 9.4.1 installation are located here:

[email protected]:/home/postgres/ [PG1] ps -ef | grep PG1
postgres  2645     1  0 10:51 ?        00:00:00 /u01/app/postgres/product/94/db_1/bin/postgres -D /u02/pgdata/PG1
postgres 14439 11550  0 11:04 pts/1    00:00:00 grep --color=auto PG1

I already installed the 9.4.5 binaries here:

[email protected]:/home/postgres/ [PG1] ls /u01/app/postgres/product/94/db_5
bin  include  lib  share

The only tasks I need to do from here on are a) stop the 9.4.1 version:

[email protected]:/home/postgres/ [PG1] which pg_ctl
/u01/app/postgres/product/94/db_1/bin/pg_ctl
[email protected]:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1 stop -m fast
waiting for server to shut down.... done
server stopped
[email protected]:/home/postgres/ [PG1] ps -ef | grep PG1
postgres 14452 11550  0 11:06 pts/1    00:00:00 grep --color=auto PG1
[email protected]:/home/postgres/ [PG1] 

Once the old version is down I just can b) restart with the new binaries:

[email protected]:/home/postgres/ [PG1] which pg_ctl
/u01/app/postgres/product/94/db_5/bin/pg_ctl
[email protected]:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1 start
server starting
[email protected]:/home/postgres/ [PG1] LOG:  database system was shut down at 2015-12-01 11:06:31 CET
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

That’s it. The new version is now 9.4.5:

([email protected][local]:5432) [postgres] > select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

Time: 20.725 ms
([email protected][local]:5432) [postgres] > \c patch
You are now connected to database "patch" as user "postgres".
([email protected][local]:5432)  > select count(*) from test;
 count 
-------
   100
(1 row)

Time: 104.297 ms

Usually, for minor versions, you can just install the new binaries and start the instance from there. But anyway, be sure to read the release notes before doing it.

3 Comments

  • umar iqbal says:

    Quick question . do i have to move or copy data directory or i can use old data directory path after patch as i notice you use same old data directly after patching. does pg_ctl automatically detect that its patch and show you new version . any environment variable needed to be modified? is this process same for upgrading from 9.4 to 9.5 or 9.6 . will appreciate answer.

  • Pawan Sharma says:

    Hi Daniel,

    As I have checked you just stop the pg_ctl process with older binary and started with new binary location.

    [email protected]:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1 start
    server starting
    [email protected]:/home/postgres/ [PG1] LOG: database system was shut down at 2015-12-01 11:06:31 CET
    LOG: MultiXact member wraparound protections are now enabled
    LOG: database system is ready to accept connections
    LOG: autovacuum launcher started

    I have one question, how its auto switch to new binary location.
    Please share the steps for repatch to the order version, if there is some issue current patch.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure