SUSE Manager/DB FAQ

From MicroFocusInternationalWiki
Jump to: navigation, search

SUSE Manager Main Page

Frequently asked questions about the Databases

Q: Where does the DB store its data?

A (oracle): /opt/apps/oracle/oradata/<sid>/

A (postgres): /var/lib/pgsql/data/

Q: Where does the DB store its archivelogs?

A (oracle): /opt/apps/oracle/oradata/<sid>/

A (postgres): /var/lib/pgsql/data/pg_xlog/

Q: Where does smdba store the database backup?

A (oracle): /opt/apps/oracle/flash_recovery_area/<uppercase SID>/

A (postgres): where specified with --backup-dir in the smdba command

Q: How can I set a daily backup of my database ?

A (oracle) : Just add this basic crontab job as root with # crontab -e :

0 0 * * * /usr/bin/smdba backup-hot

A (postgres) : After the first manual backup (see the documentation), just add this basic crontab job as root with # crontab -e :

0 0 * * * /usr/bin/smdba backup-hot

Q: Where to get generic information on how to use the smdba tool?

A: The smdba tool comes with manpage (type "man smdba") and extensive help output (type "smdba --help"). Each command has a sub-help for specific parameters. For example, in order to know valid parameters on how to restore a system from the backup, type "smdba backup-restore help".

Q: How to view size of the available backups?

A: SMDBA does not displays the size of them explicitly as they consists of several files in different places. Please list backups in a usual way and then measure them on the disk.

Q: How to delete specific or older backups?

A: SMDBA does not allows to delete a specific backup, but shifts them automatically, i.e. older backups get deleted automatically after each new backup.

Q: How to restore specific or the last backup?

A: Since SMDBA makes automatic hot backups, it allows to restore only from the last backup, merging the rest of the transaction log from the previous backups.

Q: What are those 0000000100000001000000FF files in my PostgreSQL data for?

This is the result of WAL (Write Ahead Log) or archive log subsystem. These are data integrity chunks that ensure changes to the main data files happens only after those changes have been written to permanent storage. First of all, write-ahead logging (WAL) helps to have a significantly less disk writes, i.e. data flush to the storage. So when there are many small transactions, WAL helps the IO perform better. SUSE Manager does exactly that: lots of small transactions when channels are synchronized, for example. Additionally, WAL enables support of on-line hot backup and PIT (Point In Time) recoveries.

Q: What is the deletion policy of archivelogs?

A: Archivelogs are managed by SMDBA only for the backup it does manage. No changes is done to the WAL files on the running database. Archive them on their own is up to the system administrator. WAL files that belongs to the backup are purged every time a new backup snapshot is taken.

There are two ways of managing WAL files on the PostgreSQL side: a) manually or b) automatically. In order to manage them in any way, SMDBA hot backups should be enabled.

To manage these files manually:

1. Take the backup snapshot.

2. Locate the checkpoint. For this, navigate to the PostgreSQL directory where these files are located. Example:

# ls /var/lib/pgsql/data/pg_xlog/*backup
000000010000000000000003.00000028.backup

3. Remove unused, old WAL files using exactly that checkpoint file the following way:

# pg_archivecleanup -d /var/lib/pgsql/data/pg_xlog/ 000000010000000000000003.00000028.backup 

pg_archivecleanup: keep WAL file "/var/lib/pgsql/data/pg_xlog//000000010000000000000003" and later
pg_archivecleanup: removing file "/var/lib/pgsql/data/pg_xlog//000000010000000000000002"
pg_archivecleanup: removing file "/var/lib/pgsql/data/pg_xlog//000000010000000000000001"


In order to manage these files automatically, configure your PostgreSQL accordingly, by adding the following line (change the values according your requirements):

archive_cleanup_command = 'pg_archivecleanup -d /var/lib/pgsql/data/pg_xlog/ %r 2>>/somewhere/to/your/cleanup.log'


Please note that by "automatically" it means every time when PostgreSQL restarts. If PostgreSQL is running without restart, the amount of WAL files will grow up.


More information is on the official PostgreSQL documentation about pg_archivecleanup tool and general WAL configuration.

Q: What is the deletion policy of backups?

A: Right now they are incremental automatic and shift with each new backup. New feature in the furture will allow a redundant policy.

Q: Can we change or set the deletion policy of archivelogs and backups?

A: This is a future feature, but as of today, the retention policy cannot be changed for the backups. Archivelogs are always reconciled automatically and unnecessary archivelogs are always purged.

Q: What version of PostgreSQL are my data files ?

A: You can manually check the version of your database files with:

   cat /var/lib/pgsql/data/PG_VERSION