In the Filesystem Hierarchy Standard, `/var/lib/ is stated as (in italic the most important part):
5.8.1 Purpose
This hierarchy holds state information pertaining to an application or
the system. State information is data that programs modify while they
run, and that pertains to one specific host. Users must never need to
modify files in /var/lib to configure a package's operation.
State information is generally used to preserve the condition of an
application (or a group of inter-related applications) between
invocations and between different instances of the same application.
State information should generally remain valid after a reboot, should
not be logging output, and should not be spooled data.
An application (or a group of inter-related applications) must use a
subdirectory of /var/lib for its data. There is one
required subdirectory, /var/lib/misc, which is intended for state
files that don't need a subdirectory; the other subdirectories should
only be present if the application in question is included in the
distribution.
/var/lib/ is the location that must be used for all distribution
packaging support. Different distributions may use different names, of
course.
In short: /var/lib/ is for data that is used locally.
So it makes perfect sense to put a database's data into /var/lib/{mysql|postgress}/ directory but... the FHS is a standard created mostly for use by distributions. As a user you are free to put your data wherever you want and it is mostly a matter of opinion.
You are misunderstanding the word "local". /usr/local/bin/ is not for system software but for your own software (basically anything with "local" in must never be touched by the system. As explained by FHS:
/usr/local/
4.9.1 Purpose
The /usr/local hierarchy is for use by the system administrator when
installing software locally. It needs to be safe from being
overwritten when the system software is updated. It may be used for
programs and data that are shareable amongst a group of hosts, but not
found in /usr. Locally installed software must be placed within
/usr/local rather than /usr unless it is being installed to replace or
upgrade software in /usr.
An executable installed from system software should never go to anything local.
Now for /usr/lib/.
4.7.1 Purpose
/usr/lib includes object files, libraries, and internal binaries that
are not intended to be executed directly by users or shell
scripts. Applications may use a single subdirectory under
/usr/lib. If an application uses a subdirectory, all
architecture-dependent data exclusively used by the application must
be placed within that subdirectory.
postgressql is probably a daemon started at boot? If so it makes sense to put it here. You are not suppose to use the command yourself but start a service. The files in /usr/lib/ tend to have their own user and group and/or a daemon that restricts access to /var/lib (only mysqld can access /var/lib/mysql/ for instance; this will be the same for postgressql)