Importing/Exporting DXi postgres databases (DRAFT)

Overview

If a postgres database exhibits errors, it may require repairs. The best practice is to back up the database and run the repairs locally rather than directly on the customer's system. SES and or SUS support will be required.

This article describes the steps required to backup and restore a postgres database on a DXi, but does not cover how to repair the database.

The database password is "postgres"

A couple of caveats to this process:

It's possible the customer could make changes to their system after we've dumped their database and returned their system to them. These changes would add functions to their database. Restoring a repaired database will delete those new functions.

A really messed-up customer database may not dump correctly.

Procedure

1) Stop the heartbeat service.

To ensure the database isn't changing, stop the hb service. After that's done, start the postgres service.

[root@DXi-Zero-2-3-0-3 ~]# service heartbeat stop

heartbeat: Stopping clustermanager
heartbeat: clustermanager stopped
[root@DXi-Zero-2-3-0-3 ~]#
[root@DXi-Zero-2-3-0-3 ~]# service postgresql start
Starting postgresql service: [ OK ]
[root@DXi-Zero-2-3-0-3 ~]#

mkdir /scratch/<sr-number>

2) Back up the customer's database.

[root@DXi-Zero-2-3-0-3 ~]# pg_dump -h 127.0.0.1 -U postgres policy > /scratch/<sr-number>/scheduler_policy_dump
Password:
[root@DXi-Zero-2-3-0-3 ~]# ls -l /scratch/<sr-number>/
total 124
-rw-rw-r-- 1 root root 36738 Jul 3 17:04 scheduler_policy_dump

After the backup is complete, proceed to the next step.

3) Stop the postgres service and restart the hb service on the customer's DXi.

[root@DXi-Zero-2-3-0-3 ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@DXi-Zero-2-3-0-3 ~]# service heartbeat start
heartbeat: Stopping clustermanager
heartbeat: clustermanager stopped
heartbeat: starting clustermanager
[root@DXi-Zero-2-3-0-3 ~]#

The customer's system can be returned to them while their database issues are being investigated.

4) Upload the dumped postgres database to the Quantum FTP site.

We can later download it and restore it locally.

On your local DXi backup your own postgres database for safe keeping.

[root@DXi-Zero-2-3-0-3 ~]# service heartbeat stop
heartbeat: Stopping clustermanager
heartbeat: clustermanager stopped
[root@DXi-Zero-2-3-0-3 ~]#
[root@DXi-Zero-2-3-0-3 ~]# service postgresql start
Starting postgresql service: [ OK ]
[root@DXi-Zero-2-3-0-3 ~]#

[root@DXi-Zero-2-3-0-3 ~]# pg_dump -h 127.0.0.1 -U postgres policy > /scratch/<sr-number>/scheduler_policy_dump.orig

Password:

[root@DXi-Zero-2-3-0-3 ~]#

5) Download the customer's database to /scratch/<sr-number> on your DXi.

A directory listing of /scratch shows the following:

[root@DXi-Zero-2-3-0-3 ~]# ls -l /scratch/<sr-number>/
total 124
-rw-rw-r-- 1 root root 36738 Jul 3 17:04 scheduler_policy_dump <<< customers postgres database dump >>>
-rw-rw-r-- 1 root root 34493 Jul 6 11:52 scheduler_policy_dump.orig <<< your postgres database dump >>>

6) Delete the db on your local DXi.

NOTE: Simply restoring a postgres database over the top of an existing one will result in a multitude of errors.

Delete the current database:

[root@DXi-Zero-2-3-0-3 ~]# dropdb -h 127.0.0.1 -U postgres policy

Password:

7) Create a new database on your local DXi.

[root@DXi-Zero-2-3-0-3 ~]# createdb -h 127.0.0.1 -U postgres policy

Password:

8) Restore the customer's database to your local DXi.

You will see text scroll up the screen as the tables are written into place. See the example below:

[root@DXi-Zero-2-3-0-3 ~]# psql -h 127.0.0.1 -U postgres policy < /scratch/<sr-number>/scheduler_policy_dump

Password for user postgres:

SET

CREATE LANGUAGE

ALTER LANGUAGE

SET

CREATE TABLE

ALTER TABLE

COMMENT

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

setval

--------

(1 row)

CREATE TABLE

ALTER TABLE

COMMENT

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

setval

--------

(1 row)

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COMMENT

CREATE TABLE

ALTER TABLE

COMMENT

CREATE TABLE

ALTER TABLE

COMMENT

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

setval

--------

(1 row)

CREATE TABLE

ALTER TABLE

COMMENT

CREATE TABLE

ALTER TABLE

COMMENT

CREATE TABLE

ALTER TABLE

COMMENT

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

setval

--------

(1 row)

CREATE TABLE

ALTER TABLE

COMMENT

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

setval

--------

(1 row)

CREATE TABLE

ALTER TABLE

COMMENT

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

setval

--------

(1 row)

ALTER TABLE

CREATE INDEX

ALTER TABLE

REVOKE

GRANT

[root@DXi-Zero-2-3-0-3 ~]#

9) Repair the customer's database.

Your DXi now has the customer's postgres database in place.
Carry out the postgres database manipulation/repairs.

10) After the necessary repairs are complete, dump the newly repaired database for restore into the customers DXi.

[root@DXi-Zero-2-3-0-3 ~]# pg_dump -h 127.0.0.1 -U postgres policy > /scratch/<sr-number>/scheduler_policy_dump.repaired
Password:
[root@DXi-Zero-2-3-0-3 ~]# ls -l /scratch/<sr-number>/

total 124
-rw-rw-r-- 1 root root 36738 Jul 3 17:04 scheduler_policy_dump <<< customers postgres database dump >>>
-rw-rw-r-- 1 root root 34493 Jul 6 11:52 scheduler_policy_dump.orig <<< your postgres database backup >>>

-rw-rw-r-- 1 root root 36418 Jul 6 14:02 scheduler_policy_dump.repaired <<< customers repaired postgres database >>>

11) Reinstate your own local postgres database by working through steps 6, 7, and 8 above.

After the reinstating is complete, stop the postgres service and start the hb service up again on your local DXi.

12) Copy the repaired postgres database back to the customer's DXi.

Restore the customer's repaired postgres database by again following steps 6, 7, and 8 above.