Ok, first let me apologize if this is the wrong board to post this, I only picked this one because it impacts several apps we have on UCS not just a single one.
We have installed several application, KIX4OTRS, Odoo, etc. All appear to use postgres/postgresql databases to store their configurations and backend data. We’d like to have nightly dumps of these databases so they get caught in the nightly Bareos backups we have. In searching google we have found several bash scripts that work on our other NON-UCS postgresql databases, however these same script fail with login as postgres user. We have tried the users created by the applications when installed, again these failed. We even tried root and this too failed. Is there a default postgres password installed?
How do we go about dumping our postgres/postgresql databases for backup/restore purposes?
The default database cluster is owned by the local ‘postgres’ user. You can verify that:
root@ucs4-master:~# pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
9.1 main 5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
The UCS system has a templating mechanism that creates and maintains the cluster configuration. Particularly, the corresponding pg_hba.conf file always contains the tuple
# Database administrative login by UNIX sockets
local all postgres ident
which grants full access to the postgres user, using local unix domain sockets. Doing
su - postgres
should be enough to have the full palette of pg_* tools, for instance pg_dump or pg_dumpall.
This holds true for all applications that place their databases into the default cluster. If pg_lsclusters (as root) lists more than one cluster you have to consult the documentation of the corresponding application.