Need Howto Backup/Restore Postgres/postgresql DBs on UCS


#1

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?

Thanks Brian


#2

Hello,

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.

Hope this helps as a starting point.

Regards,
Frank Greif.


#3

I’ve tried this on both the master and on the ucs member server running our ODOO ERP installation, both fail from root, and also from su - postgres

root@odooerpdev1:/# pg_lclusters <— This is the UCS Member Server running our ODOO ERP Application
-bash: pg_lclusters: command not found

root@ucsmdc:~# pg_lclusters <–This is the UCS Master Domain Controller
-bash: pg_lclusters: command not found


#4

Hello Brian,

you mistyped the command: it must be ‘pg_lsclusters’.

Greetings,
Frank Greif.