EDB SQL Patch
Suggest editsOverview
There are occasions when a potentially complex set of changes need to be made to system-defined objects in an already existing database. edb_sqlpatch
uses patch files supplied with EDB Postgres Advanced Server and can use them, as appropriate, to fix upgraded databases.
General use
The edb_sqlpatch
command uses current connection settings to connect a database (or databases) presented as the command's arguments or to all databases if the -a
flag is used.
We recommend that the -a
flag always be used to always check all databases to ensure none are missed from the checking and patching processes.
With no other flags given, edb_sqlpatch
checks the given database (or all databases) to determine which patches are applicable, which patches have previously been applied and which patches need to be applied.
This will report if any of the databases need patching.
edb_sqlpatch
only applies the patches if the -f
(or --fix
) flag is given. To apply patches to all the databases that need them, use -f
in combination with -a
.
Use edb_sqlpatch -L
or edb_sqlpatch --list
to obtain a list of available patches. Specific patches can be applied using the patch name as an argument to the -P
or --patch
flag.
Use with backups and replicas
Backups
If you have a database restored from a backup made before a system was patched, you need to run edb_sqlpatch
against the restored backup to ensure that the patches are applied to the restored system.
Streaming replicas
If the master system has been patched, there should be no need to run edb_sqlpatch
on the replicas as the patching is replicated. You can run edb_sqlpatch
on a replica to verify that.
Logical replicas
Logical replicas may not replicate all changes to system objects. Therefore, each logical replica should have edb_sqlpatch -a
run against it to ensure that it is fully and correctly patched. Do not run edb_sqlpatch
concurrently on different replicas. If edb_sqlpatch
reports un-applied fixes, use edb_sqlpatch -f -a
to apply them to the replica and repeat the process on all the replicas.
PGD clusters
PGD clusters are configured to replicate all changes. PGD clusters should have edb_sqlpatch -f -a
run on each node including all witness nodes. edb_sqlpatch should not be run in a mixed version cluster. Each node should be fully upgraded to the latest minor release version of EPAS before any of the nodes have edb_sqlpatch run against them. In addition, edb_sqlpatch should only be run on a single node at a time. Do not run edb_sqlpatch
concurrently on different nodes.
Command line options
Usage:
Target options:
short option | long option | description |
---|---|---|
-a | --all | patch all databases |
-d=DBNAME | --database=DBNAME | database to patch |
Patching options:
short option | long option | description |
---|---|---|
-L | --list | list available patches |
-f | --fix | apply the patches, don't just check |
-P=PATCHNAME | --patch=PATCHNAME | specify specific patch to apply |
-S=PATCHNAME | --source=PATCHNAME | specify non-default patch source directory |
Connection options:
short option | long option | description |
---|---|---|
-h=HOSTNAME | --host=HOSTNAME | database server host or socket directory |
-p=PORT | --port=PORT | database server port |
-U=USERNAME | --username=USERNAME | user name to connect as |
-w | --no-password | never prompt for password |
-W | --password | force password prompt |
--maintenance-db=DBNAME | alternate maintenance database |
Other options:
short option | long option | description |
---|---|---|
-v | --verbose | write a lot of output |
-V | --version | output version information, then exit |
-? | --help | show this help, then exit |
Examples
Checking and fixing all databases
First check all databases using the -a
flag.
We can see patches need to be applied to the databases. We can apply those patches by using the -af
flag.
The databases are now patched.
Checking a single existing/upgraded database
When a database has been upgraded, patches may need to be applied to the database to complete the remediation of any problems.
The number of patches that have been applied (and the number needing to be applies) varies according to the version of the database, and how many patches are applicable to the current database.
The -v
flag increases verbosity and lists the patches being evaluated for application:
Fixing a single existing/upgraded database
The -f
flag signals that you want edb_sqlpatch
to apply patches to the database.
By using the -a
flag and not specifying a database, edb_sqlpatch
apply patches to all databases it can connect to:
Notice that the edb database was patched in the preceding command, and so edb_sqlpatch
confirms that the patches were applied. The template database is skipped because, as the command explains, it does not allow connections.
Checking a fresh database installation
In this scenario, all patches will already be applied so none need to be applied.
The number of patches that have been applied varies according to the version of the database.
Could this page be better? Report a problem or suggest an addition!