Re: Creating a hot copy of PostgreSQL database - Mailing list pgsql-novice

Re-using a backup script for MySQL to make Postgres backups might not be a good idea. Besides, it shouldn't take much
effortto create a new script for filesystem level backup anyways. Of course, it depends on what filesystem you're
using.If your filesystem provides snapshots option, then it will be as simple as: 
1. issue pg_start_backup('label');
2. take snapshot of the filesystem
3. issue pg_stop_backup();

If not, you can look at other backup options like using pg_basebackup with pitr, or pg_dump.

On Mon, Jul 21, 2014 at 02:39:32PM -0700, Shreesha wrote:
> @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.albe@wien.gv.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.