Synchronous Streaming Replicating is set up between a master and a hot standby database.
It seems like a good idea to perform daily backups on the standby database to avoid any performance issues on the master database.
The backup of the standby db was successfully restored using pg_basebackup. Archived WAL files were transferred locally and the restore_command was added in recovery.conf to point to them – the following errors were recorded in the log files whilst starting up the database:
cp: cannot stat ‘/archivedir/wal_arch/00000002.history’: No such file or directory
2016-05-12 17:33:52 BST LOG: starting archive recovery
cp: cannot stat ‘/archivedir/wal_arch/0000000100000000000000E7’: No such file or directory
2016-05-12 17:33:52 BST LOG: redo starts at 0/E7000CA0
2016-05-12 17:33:52 BST LOG: consistent recovery state reached at 0/E7000D80
2016-05-12 17:33:52 BST LOG: invalid resource manager ID 100 at 0/E7000D80
2016-05-12 17:33:52 BST LOG: redo done at 0/E7000D48
cp: cannot stat ‘/archivedir/wal_arch/0000000100000000000000E7’: No such file or directory
cp: cannot stat ‘/archivedir/wal_arch/00000002.history’: No such file or directory
2016-05-12 17:33:52 BST LOG: selected new timeline ID: 2
cp: cannot stat ‘/archivedir/wal_arch/00000001.history’: No such file or directory
2016-05-12 17:33:52 BST LOG: archive recovery complete
2016-05-12 17:33:52 BST LOG: MultiXact member wraparound protections are now enabled
2016-05-12 17:33:52 BST LOG: database system is ready to accept connections
2016-05-12 17:33:52 BST LOG: autovacuum launcher started
Is it a good idea to backup the standby database instead of the master?
Best Answer
Yes, it can be a good idea. In fact, allowing this was a major feature in the release of PostgreSQL 9.2.