Re: Creating a hot copy of PostgreSQL database

On Sun, Jul 20, 2014 at 12:28:27PM -0700, Shreesha wrote:
> Hello All,
> I was looking for an option of taking the hot copy of the pgsql database
> periodically or with manual trigger. In order to accomplish the same, I
> need to make sure that all the active transactions are halted for some time.
> In MySQL I could do this with FLUSH with READ LOCK. Once the backup is
> taken, I could UNLOCK the tables to continue the normal course of database
> operations.
>
> How can I do this in PostgreSQL database?
>
> Any information on this regard would be highly appreciated.
>
> Thanks,
> Shreesha.
>
> P.S.
> I happened to explore Backup and Restore options provided by PostgreSQL.
> a) Taking SQL dump would result in a slower operation and our system
> wouldn't be able to leverage the performance delay caused by this operation.
> b) File system level cache wouldn't be feasible as I don't want my the
> database server to be shutdown for taking the database backup and restore
> operation.
> c) In order to accomplish the archival of WAL logs, I would anyway want to
> halt the database which brings me to the same problem again.

Hi Shreesha,

I think the following excerpt from the section in the documentation on
"File System Level Backup" may be what you need. This is for version 9.3:

If simultaneous snapshots are not possible, one option is to shut down the
database server long enough to establish all the frozen snapshots. Another
option is to perform a continuous archiving base backup (Section 24.3.2)
because such backups are immune to file system changes during the backup.
This requires enabling continuous archiving just during the backup process;
restore is done using continuous archive recovery (Section 24.3.4).

Regards,
Ken