PostgreSQL Backup

    pg_basebackup is used to take base backups of a running PostgreSQL database cluster. We can take hot-backup and the backup can be used for...

   1.     point-in-time recovery as the starting point for a log shipping or 

   2.     streaming replication standby servers  

    It makes a binary copy of the database cluster files, while making sure the system is put in and out of backup mode automatically. Backups are always taken of the entire database cluster; it is not possible to back up individual databases or database objects.

    For individual database backups, a tool such as pg_dump must be used.

    The backup is made over a regular PostgreSQL connection, and uses the replication protocol. The connection must be made with a superuser or a user having REPLICATION permissions  and pg_hba.conf must explicitly permit the replication connection. The server must also be configured with max_wal_senders set high enough to leave at least one session available  for the backup and one for WAL streaming (if used).

    There can be multiple pg_basebackups running at the same time.

    pg_basebackup can make a base backup from not only the master but also the standby.

    Note that there are some limitations in an online backup from the standby:

    The backup history file is not created in the database cluster backed up.

    pg_basebackup cannot force the standby to switch to a new WAL file at the end of backup. When you are using -X none, if write activity on the primary is low, pg_basebackup may need to wait a long time for the last WAL file required for the backup to be switched and archived. In this case, it may be useful to run pg_switch_wal on the primary in order to trigger an immediate WAL file switch.

     If the standby is promoted to the master during online backup, the backup fails.

     All WAL records required for the backup must contain sufficient full-page writes, which requires you to enable full_page_writes on the master and not to use a tool like pg_compresslog as archive_command to remove full-page writes from WAL files.