PostgeSQL Backup

[mamosorre84]

Hi,

how can we backup with app-consistent option a Postgre DB in a SUSE 12 VM?

The DB is configured in "archive level".

Do you have some pre-post scripts to suggest?

What about log transaction?

Thank you

Marco

[Mike Resseler]

Hi Marco,

There are some scripts out there (example: https://wiki.postgresql.org/wiki/Automa ... p_on_Linux). As far as I know you will need to stop the services and start after the backup like a MySQL database. Veeam itself cannot do this like we can with SQL and Oracle

Cheers
Mike

[mamosorre84]

Hi Mike,

thank you for the quick reply.

Does this script stop DB services? Are logs truncated?

I've found this kb https://www.postgresql.org/docs/9.6/sta ... ASE-BACKUP , in particular:


25.3.3.2. Making an exclusive low level backup

The process for an exclusive backup is mostly the same as for a non-exclusive one, but it differs in a few key steps. It does not allow more than one concurrent backup to run, and there can be some issues on the server if it crashes during the backup. Prior to PostgreSQL 9.6, this was the only low-level method available, but it is now recommended that all users upgrade their scripts to use non-exclusive backups if possible.

Ensure that WAL archiving is enabled and working.

Connect to the server (it does not matter which database) as a user with rights to run pg_start_backup (superuser, or a user who has been granted EXECUTE on the function) and issue the command:

SELECT pg_start_backup('label');

where label is any string you want to use to uniquely identify this backup operation. pg_start_backup creates a backup label file, called backup_label, in the cluster directory with information about your backup, including the start time and label string. The function also creates a tablespace map file, called tablespace_map, in the cluster directory with information about tablespace symbolic links in pg_tblspc/ if one or more such link is present. Both files are critical to the integrity of the backup, should you need to restore from it.

By default, pg_start_backup can take a long time to finish. This is because it performs a checkpoint, and the I/O required for the checkpoint will be spread out over a significant period of time, by default half your inter-checkpoint interval (see the configuration parameter checkpoint_completion_target). This is usually what you want, because it minimizes the impact on query processing. If you want to start the backup as soon as possible, use:

SELECT pg_start_backup('label', true);

This forces the checkpoint to be done as quickly as possible.

Perform the backup, using any convenient file-system-backup tool such as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the database while you do this. See Section 25.3.3.3 for things to consider during this backup.

Again connect to the database as a user with rights to run pg_stop_backup (superuser, or a user who has been granted EXECUTE on the function), and issue the command:

SELECT pg_stop_backup();

This terminates the backup mode and performs an automatic switch to the next WAL segment. The reason for the switch is to arrange for the last WAL segment file written during the backup interval to be ready to archive.

Once the WAL segment files active during the backup are archived, you are done. The file identified by pg_stop_backup's result is the last segment that is required to form a complete set of backup files. If archive_mode is enabled, pg_stop_backup does not return until the last segment has been archived. Archiving of these files happens automatically since you have already configured archive_command. In most cases this happens quickly, but you are advised to monitor your archive system to ensure there are no delays. If the archive process has fallen behind because of failures of the archive command, it will keep retrying until the archive succeeds and the backup is complete. If you wish to place a time limit on the execution of pg_stop_backup, set an appropriate statement_timeout value, but make note that if pg_stop_backup terminates because of this your backup may not be valid.

I think this script does not stop DB services..is it correct?

Thank you

Marco

[Mike Resseler]

No, that script is an example of taking backups for the database but it indeed does not stop / start. That is indeed something you need to do also the moment you take a backup of the VM

[mamosorre84]

Ok..thank you!