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 | CAPBNhTx6m5rjBWYEqG=ntCT5_5eaBYMuf3R=SQgS6u=9VNc6WQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Creating a hot copy of PostgreSQL database (Albe Laurenz <laurenz.albe@wien.gv.at>) |
| Responses |
Re: Creating a hot copy of PostgreSQL database
Re: Creating a hot copy of PostgreSQL database |
| List | pgsql-novice |
@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:What do you mean by that? How can you use the cache for creating a copy?
> 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.I don't see your point c)
> 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.
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.
pgsql-novice by date: