From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Shreesha *EXTERN*" <shreesha1988(at)gmail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Creating a hot copy of PostgreSQL database |
Date: | 2014-07-21 07:38:27 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17D1D5B6@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Shreesha wrote:
> 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.
What do you mean by that? How can you use the cache for creating a copy?
> 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.
I don't see your point c)
The database server will automatically archive WALs any way you want, and there
is no need to stop or halt the database for that.
To create a copy of a PostgreSQL database cluster, take an online backup and restore it
to some point in time after the end of the backup using the WAL archives.
I am sure that you can automate this if necessary.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2014-07-21 07:52:34 | Re: JBDC LDAP support |
Previous Message | ktm@rice.edu | 2014-07-20 21:36:14 | Re: Creating a hot copy of PostgreSQL database |