PostgreSQL Postgresql interview questions
PostgreSQL Technologies

Step by step to perform Enterprise PostgreSQL (or) EDB PostgreSQL Patching:

Please download .run files for source and target versions of Patch(just for safety purpose).

Source(existing) version file :
Latest version (Patching) :

EDB PostgreSQL Patching Steps :

Step 1 : Take backup of PostgreSQL server existing binaries, for safety purpose. (check owner,if root run below command as root)

Syntax : tar -zcvf /9.5AS_Backup.tar.gz /edbas95/9.5AS/
Example: tar -zcvf /u01/patches/backup_9.5AS/9.5AS_Backup.tar.gz /edbas95/9.5AS/

Step 2 : Run below pre-checks (just in case if we want to verify any of these details later)

==> Check replication status : select usename,client_addr, state,backend_start, sent_location, write_location,flush_location, replay_location from pg_stat_replication;
==> Check replication lag(in bytes) : select pg_xlog_location_diff(sent_location, replay_location) as “replay_lag(In Bytes)”,client_addr from pg_stat_replication;
==> Check version : select version();
==> Check data directory location : show data_directory;
==> Check config file location : show config_file;
==> Check hba file location : show hba_file;
==> Check port number : show port;
==> Check PostgreSQL server is Master (or) Slave : select pg_is_in_recovery();

Step 3 : Comment the crontab on the effected server(s) before stopping edb PostgreSQL server
Crontab -e

Step 4 : Stop the PostgreSQL server and make sure no processes using the home that is going to be patched(here: /edbas95/9.5AS)
$ pg_ctl status
$ pg_ctl stop
$ pg_ctl status

$ ps -ef|grep -i postgres:
$ ps -ef|grep -i postgres

Step 5 : Go to Patch file (.run) location and as root(admin user), Run the actual patch command. Patching logs will be generated in /tmp location.

Syntax :

./ −−mode unattended −−prefix BINARIES_LOCATION −−serverport PORT_NUMBER −−workload_profile oltp −−server_utilization 100 −−datadir DATA_DIRECTORY −−superpassword ‘Edb_Postgres_Pa$$word’

PS : Above options may vary as per environment requirements.

Example :

./ −−mode unattended −−prefix /edbas95/ −−serverport 5432 −−workload_profile oltp −−server_utilization 100 −−datadir /u01/pgsql/ −−superpassword ‘pa$$word’

Output :
Info: An existing Postgres Plus Advanced Server installation has been found at /edbas95/9.5AS. This installation will be upgraded.
In order to upgrade, we may need to restart the server. All connections to the server will need to be reestablished after the completion of the installation process.

Patch logs generated in /tmp:

Step 6 : After patching, EDB PostgreSQL server will be started automatically, if you want you can do one more restart just to crosscheck everything is looking good. Connect to the server and check the patched version is reflecting or not.

$ psql -U postgres
psql> select version();

Step 7 : Verify the replication is working fine by creating a test table(in case if you have Master-Slave setup). You can use the same steps to patch Master as well as slave, replication will be resumed once the PostgreSQL server is online.

Step 8 : Uncomment the crontab, as the Enterprise PostgreSQL patching activity is completed.

If you want to rollback / revert to old(source) patch version, you can execute above steps (Step : 5) with source patch version file(.run).

Comments Rating 0 (0 reviews)

About the author


Add Comment

Click here to post a comment