Re: Fastest way to duplicate a quite large database

Поиск
Список
Период
Сортировка
От John McKown
Тема Re: Fastest way to duplicate a quite large database
Дата
Msg-id CAAJSdjiC-44Q89-u4yj++z=oxdmA5zUDPXaDX6YHFwOj5DaSwA@mail.gmail.com
обсуждение исходный текст
Ответ на Fastest way to duplicate a quite large database  (Edson Richter <edsonrichter@hotmail.com>)
Ответы Re: Fastest way to duplicate a quite large database  (John R Pierce <pierce@hogranch.com>)
Список pgsql-general
Дерево обсуждения
On Tue, Apr 12, 2016 at 9:25 AM, Edson Richter <edsonrichter@hotmail.com> wrote:
Hi!

I have a database "Customer" with about 60Gb of data.
I know I can backup and restore, but this seems too slow.

Is there any other option to duplicate this database as "CustomerTest" as fast as possible (even fastar than backup/restore) - better if in one operation (something like "copy database A to B")?
I would like to run this everyday, overnight, with minimal impact to prepare a test environment based on production data.

​Hum, I don't know exactly how to do it, but on Linux, you could put the "Customer" database in a tablespace which resides on a BTRFS filesystem. BTRFS can do a quick "snapshot" of the filesystem and you can then set things for "incremental backup", as talked about here: https://btrfs.wiki.kernel.org/index.php/Incremental_Backup . From some reading, btrfs is a performance dog compared to others.
interesting take using various filesystems for PostgreSQL: http://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs
another on btrfs + PostgreSQL http://www.cybertec.at/2015/01/forking-databases-the-art-of-copying-without-copying/
<quote from above>
...
So we managed to take fork a 15 GB database in 6 seconds with only a small hiccup in performance. We are ready to start up the forked database.
...
<quote/>

I got a number of hits searching on "postgresql btrfs" using Google search.


Thanks,

--
Atenciosamente,

Edson Carlos Ericksson Richter


--
How many surrealists does it take to screw in a lightbulb? One to hold the griffon and one to fill the bathtub with brightly colored LEDs.

Maranatha! <><
John McKown

В списке pgsql-general по дате отправления:

Предыдущее
От: Edson Richter
Дата:
Сообщение: Re: Fastest way to duplicate a quite large database
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Freezing localtimestamp and other time function on some value