Re: Creating a hot copy of PostgreSQL database - Mailing list pgsql-novice
| From | Shreesha |
|---|---|
| Subject | Re: Creating a hot copy of PostgreSQL database |
| Date | |
| Msg-id | CAPBNhTzb7oDNhtofBUPwwEsVNSyQQGpebf=sDUT6Mn8P00_M0w@mail.gmail.com Whole thread Raw |
| In response to | Creating a hot copy of PostgreSQL database (Shreesha <shreesha1988@gmail.com>) |
| Responses |
Re: Creating a hot copy of PostgreSQL database
|
| List | pgsql-novice |
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@usa.net> wrote:
--As of July 21, 2014 2:39:32 PM -0700, Shreesha is alleged to have said:--As for the rest, it is mine.@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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
~Shreesha.
pgsql-novice by date: