Re: Migration and snapshot of database

On Thu, May 30, 2013 at 5:49 PM, aarti sawant <aartisawant0(at)gmail(dot)com> wrote:
> I want to migrate physical server on to virtual machine
> My physical server consist of
> 1. Some Application.
> 2. PostgreSQL Database
>
> Here the system migrated from Physical machine will be replicated into two
>
> VM's.
> - One will be act as live machine, on which live operations will be
> performed.
> - Second will be test VM, where I can make changes in application and test
> for
>
> new features.
>
> Both VM need to have their own database. But two copies of database require
>
> twice the storage.
> I want to optimize for storage.
>
> Once the database is migrated to new machine, how I can use this database
> for
>
> both VM's without two independent copies.
> Test VM will have its own write operations, which will be ignored later.
>
> My approach
> Migration:
> -I am thinking of using pg_basebackup for online database backup from
> physical
>
> machine to new machine.
> -Is pg_barman or pg_rman is faster than pg_basebackup?
>
> Storage optimization:
> I want to use snapshot to optimize for storage.
> - Will take snapshot of original database and export the snapshot with
>
> read/write access to test VM.
>
> Since, I could not found anything within postgres, I plan to do it using
> file
>
> system or LVM.
> So with this method I can do my testing and delete the snapshot after
> testing
>
> is done without reflecting original database.
>
> Has anyone work on such scenario? What are problem faced in writable
>
> snapshot?
>

So, something like following?

1) Take a base backup of the cluster on the physical machine using
pg_basebackup.

2) Restore from it on the live VM and consider it the main cluster
instance from this point on.

3) Do something like: pg_start_backup('backup_label') -> take a
writable file system snapshot -> pg_stop_backup() -> copy
'backup_label' file to the snapshot.

4) Restore from the snapshot containing the backup_label file, use it
for testing, occasionally write to it and then discard the snapshot.

Since the snapshot is copy-on-write, the whole operation seems to
require less storage than otherwise (like a full base backup)
I am also eager to know if anybody has tried this and what are the
odds of this kind of setup being usable for usual master-slave
scenario possibly reducing cost of creating standbys?

--
Amit Langote