Back up and restore Service Portal PostgreSQL databases

In some cases, it is needed to make a backup and restore of one of the Service Portal databases.

To get a list of SMSP databases, you can run the following commands:

# sudo -u postgres psql #connect to the DB server
\l #list databases
\q #quit

The following figure shows an example.

Simple procedure

When patching some services like IdM, new fields may be created. In the event that you restore the service, you may also need to restore the database. This depends on the patch instructions.

To backup a DB (in this example, idmdb) from the command line:

# sudo -u postgres pg_dump idmdb > /opt/hp/patches/postgres_imdb.backup_`date +%F_%H-%M-%S`

To backup of the entire Postgres instance (all DBs):

# sudo -u postgres pg_dumpall > /opt/hp/patches/postgres_backup_`date +%F_%H-%M-%S`

To restore a DB (in this example, idmdb):

# sudo -u postgres dropdb idmdb
# sudo -u postgres createdb -T template0 idmdb
# sudo -u postgres psql idmdb < /opt/hp/patches/postgres_imdb.backup_2015-12-14_12-23-14

Extended procedure

Stop SMSP and Operations Orchestration (OO) to kill all Database (DB) activity:

# propel stop
# systemctl stop central

Backup all Databases:

# sudo -u postgres pg_dumpall > /opt/hp/patches/postgres_backup_`date +%F_%H-%M-%S`

Log in as the postgres user into the DB server, get all active databases which are not templates and output the different drop commands into the file dd.sql:

# sudo -u postgres psql -c "select 'drop database '||datname||';'from pg_database where datistemplate=false" >> dd.sql

Edit dd.sql and remove the first two lines and the last one in order to keep only the drop commands.

Drop all databases by using the dd.sql file:

# sudo -u postgres psql -d postgres -f dd.sql

Restore all databases:

# sudo -u postgres psql -f postgres_backup_<date when backup is taken>;

As the users/roles do not change, we did not drop them and warnings or errors will be displayed. You could drop them (except for the postgres user) using this command and SQL script (du.sql):

sudo -u postgres psql -c "select 'DROP USER '||usename||';'from pg_user where usename<>'postgres'" >> du.sql

Restart Service Portal and OO:

# systemctl start central
# propel start