Creating a hot copy of PostgreSQL database

From: Shreesha <shreesha1988(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Creating a hot copy of PostgreSQL database
Date: 2014-07-20 19:28:27
Message-ID: CAPBNhTywBq9jqMuFaxrW0fDV-StjNDbOSw_ouskaVz5TM0meCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message ktm@rice.edu 2014-07-20 21:36:14 Re: Creating a hot copy of PostgreSQL database
Previous Message Paul Linehan 2014-07-20 17:08:11 Re: Help with a JOIN.