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. <\/p>\n
Lets say you want to patch PostgreSQL from version 9.4.1 to version 9.4.5. What do you need to do? <\/p>\n
For this little demo I’ll create a new database and a sample table in my 9.4.1 instance:<\/p>\n
\n(postgres@[local]:5432) [postgres] > select version();\n version \n--------------------------------------------------------------------------------------------------------------------------\n 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\n(1 row)\n\nTime: 0.483 ms\n(postgres@[local]:5432) [postgres] > create database patch;\nCREATE DATABASE\nTime: 2533.745 ms\n(postgres@[local]:5432) [postgres] > c patch\nYou are now connected to database \"patch\" as user \"postgres\".\n(postgres@[local]:5432) > create table test ( a int );\nCREATE TABLE\nTime: 2.430 ms\n(postgres@[local]:5432) > insert into test (a) values ( generate_series(1,100));\nINSERT 0 100\nTime: 0.959 ms\n<\/pre>\nIf 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:<\/p>\n
\npostgres@oel7:\/home\/postgres\/ [PG1] ps -ef | grep PG1\npostgres 2645 1 0 10:51 ? 00:00:00 \/u01\/app\/postgres\/product\/94\/db_1\/bin\/postgres -D \/u02\/pgdata\/PG1\npostgres 14439 11550 0 11:04 pts\/1 00:00:00 grep --color=auto PG1\n<\/pre>\nI already installed the 9.4.5 binaries here:<\/p>\n
\npostgres@oel7:\/home\/postgres\/ [PG1] ls \/u01\/app\/postgres\/product\/94\/db_5\nbin include lib share\n<\/pre>\nThe only tasks I need to do from here on are a) stop the 9.4.1 version:<\/p>\n
\npostgres@oel7:\/home\/postgres\/ [PG1] which pg_ctl\n\/u01\/app\/postgres\/product\/94\/db_1\/bin\/pg_ctl\npostgres@oel7:\/home\/postgres\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1 stop -m fast\nwaiting for server to shut down.... done\nserver stopped\npostgres@oel7:\/home\/postgres\/ [PG1] ps -ef | grep PG1\npostgres 14452 11550 0 11:06 pts\/1 00:00:00 grep --color=auto PG1\npostgres@oel7:\/home\/postgres\/ [PG1] \n<\/pre>\nOnce the old version is down I just can b) restart with the new binaries:<\/p>\n
\npostgres@oel7:\/home\/postgres\/ [PG1] which pg_ctl\n\/u01\/app\/postgres\/product\/94\/db_5\/bin\/pg_ctl\npostgres@oel7:\/home\/postgres\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1 start\nserver starting\npostgres@oel7:\/home\/postgres\/ [PG1] LOG: database system was shut down at 2015-12-01 11:06:31 CET\nLOG: MultiXact member wraparound protections are now enabled\nLOG: database system is ready to accept connections\nLOG: autovacuum launcher started\n<\/pre>\nThat’s it. The new version is now 9.4.5:<\/p>\n
\n(postgres@[local]:5432) [postgres] > select version();\n version \n--------------------------------------------------------------------------------------------------------------\n 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\n(1 row)\n\nTime: 20.725 ms\n(postgres@[local]:5432) [postgres] > c patch\nYou are now connected to database \"patch\" as user \"postgres\".\n(postgres@[local]:5432) > select count(*) from test;\n count \n-------\n 100\n(1 row)\n\nTime: 104.297 ms\n<\/pre>\n