Re: Creating a hot copy of PostgreSQL database

@Albe Laurenz:
'cache' was a typo. I meant file system level backup as mentioned here - (
http://www.postgresql.org/docs/9.3/static/backup-file.html)
It would be really helpful if you can give steps for automating the copy of
PostgreSQL database cluster.

To give a clear picture of what I am currently trying to do, Please find
below:
The system is using MySQL database and currently, we are doing backup
ourselves with the help of a module which does the following:
1) LOCK TABLES which will internally create a global read lock for all the
tables.
2) FLUSH TABLES
3) Iterate through all the tables in each database and create a copy of
those files in destination location
4) UNLOCK the tables.
Repeat these steps for every database.

We are migrating from MySQL to PostgreSQL and I was trying to figure out
the corresponding alternative in pg database.
I wanted to know if there is any way I can reuse the current module for
creating the database backup. So following are my concerns:
Is there way to create a global read lock on all the tables in a database
and ensure that all the active transactions are halted till UNLOCK? if so,
how?

Even upon going through the documentation, I couldn't get this concrete
thing here. Appreciate your help.

On Mon, Jul 21, 2014 at 12:38 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

> 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
>

--
~Shreesha.