Re: Creating a hot copy of PostgreSQL database

Thanks for your thoughts Daniel. Really appreciate it. :-)

Though taking the pg_dump file for backup and restoring it sounds feasible,
this is a CPU extensive operation and the backup process is taking long
time. This was the same case earlier with mysqldump as well.
With this performance concern, we ended up writing a new module which does
the copy operation.

I was wondering if pg_basebackup will be of any help for me in this regard.
Can you please shed some light on how this can be used for recovery? Any
examples(apart from the one in the documentation) on this usage especially
with the 'xlog switch' would really help. I didn't find much usage examples
of this utility over the internet. That's why thought of posting it here.

Thanks.

On Mon, Jul 21, 2014 at 5:47 PM, Daniel Staal <DStaal(at)usa(dot)net> wrote:

> --As of July 21, 2014 2:39:32 PM -0700, Shreesha is alleged to have said:
>
> @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.
>>
>
> --As for the rest, it is mine.
>
> I understand that you are thinking 'copy the actual database files', but
> might I suggest looking at pg_dump? It does essentially the above - the
> 'directory' format would even output one file per table. It does not lock
> the database while it's in progress - but it does use Postgres's standard
> concurrency control. (Meaning that it essentially will output a snapshot
> of the database in time: While writes, deletes, etc. will work while
> pg_dump is being run, the backup will contain only the data at the point
> the backup started.) If you need further data integrity you can even use
> the `--serializable-deferrable` switch. (Though note in nearly all cases
> it's overkill; read the docs.)
>
> The output would be more portable and manipulateable as well: You could do
> things like restore only one table, or even possibly restore to other
> databases. (From SQL format.) It would also output a platform-independent
> dump, while your procedure above might not work with even a minor
> difference in compiler between the original and restore database. (I'm not
> sure how sensitive Postgres is to that type of thing, but the bare files
> are not defined to be usable by anything except the exact binary that wrote
> them.)
>
> Daniel T. Staal
>
> ---------------------------------------------------------------
> This email copyright the author. Unless otherwise noted, you
> are expressly allowed to retransmit, quote, or otherwise use
> the contents for non-commercial purposes. This copyright will
> expire 5 years after the author's death, or in 30 years,
> whichever is longer, unless such a period is in excess of
> local copyright law.
> ---------------------------------------------------------------
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

--
~Shreesha.