PostgeSQL Backup

Availability for the Always-On Enterprise

PostgeSQL Backup

Veeam Logoby mamosorre84 » Tue Jun 06, 2017 9:01 am

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
mamosorre84
Enthusiast
 
Posts: 44
Liked: 5 times
Joined: Mon Oct 24, 2016 3:56 pm
Location: Ancona - Italy
Full Name: Marco Sorrentino

Re: PostgeSQL Backup

Veeam Logoby Mike Resseler » Tue Jun 06, 2017 9:33 am

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
Mike Resseler
Veeam Software
 
Posts: 3876
Liked: 429 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: PostgeSQL Backup

Veeam Logoby mamosorre84 » Tue Jun 06, 2017 10:06 am

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
mamosorre84
Enthusiast
 
Posts: 44
Liked: 5 times
Joined: Mon Oct 24, 2016 3:56 pm
Location: Ancona - Italy
Full Name: Marco Sorrentino

Re: PostgeSQL Backup

Veeam Logoby Mike Resseler » Tue Jun 06, 2017 10:21 am

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
Mike Resseler
Veeam Software
 
Posts: 3876
Liked: 429 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: PostgeSQL Backup

Veeam Logoby mamosorre84 » Tue Jun 06, 2017 10:51 am

Ok..thank you!
mamosorre84
Enthusiast
 
Posts: 44
Liked: 5 times
Joined: Mon Oct 24, 2016 3:56 pm
Location: Ancona - Italy
Full Name: Marco Sorrentino

[MERGED] Qlik Sense Backup

Veeam Logoby gnanasekar » Fri Feb 02, 2018 12:47 pm

We run nightly Veeam backups of our Qlik Sense environment.

The backups consist of:

The entire VM (VMDK file)
Individual Windows folders

Reading through the documentation provided by Qlik, it is suggested to execute a "pg_dump" for the postgreSQL repository database.

In our instance, considering we backup our entire Server nightly, is this a necessary step?

Does anyone else's backup process differ from the Qlik documentation?

I have been searching for the answer for the long time i referred this links https://nunsys.com/veeam-backup-virtualizacion/ | Qlikview


Thanks!
gnanasekar
Lurker
 
Posts: 2
Liked: never
Joined: Thu Dec 14, 2017 6:15 am
Full Name: gnanasekar

Re: PostgeSQL Backup

Veeam Logoby PTide » Sun Feb 04, 2018 10:19 pm

Hi gnanasekar,

pg_dump will create a standalone dump file of the database in a consistent state. The actual database will have to be restored separately after you restore the VM. There is also another way of backing up postgresql in transaction-consistent manner that involves preparation of the database. Please review this thread.

Thank you.
PTide
Veeam Software
 
Posts: 3493
Liked: 289 times
Joined: Tue May 19, 2015 1:46 pm


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], Google [Bot], mih, Yahoo [Bot] and 1 guest