Обсуждение: PG equivalent of "mysqlhotcopy"?
Although mysqldump is the official solution, there's a fabulous perl script "mysqlhotcopy" that simply copies the data from MySQL tables in folders and restoring the data is as simple as copying the files back to their /var/lib/mysql/data location. I know about pg_dumpall, which creates a humongous SQL file, but is there something equivalent in the postgresql world, like a "pgsqlhotcopy" which copies data folders in a similar way as mysqlhotcopy? Thanks!
you can always try to write something on your own, using pitr and pg_start_backup(), rsync, and friends. http://www.postgresql.org/docs/8.3/static/continuous-archiving.html
> I know about pg_dumpall, which creates a humongous SQL file, but is > there something equivalent in the postgresql world, like a > "pgsqlhotcopy" which copies data folders in a similar way as > mysqlhotcopy? If you're lucky enough to be using a filesystem which supports atomic snapshotting of directories (LVM and ZFS, I believe), you could just take a snapshot of your entire Postgres data directory. See e.g. http://lethargy.org/~jesus/archives/92-PostgreSQL-warm-standby-on-ZFS-crack.html Note that mysqlhotcopy essentially works by forcibly locking all tables while the copy is going on -- for any moderately big and busy server, this is a huge problem.
Hi, Le 30 juil. 09 à 15:34, Phoenix Kiula a écrit : > I know about pg_dumpall, which creates a humongous SQL file, but is > there something equivalent in the postgresql world, like a > "pgsqlhotcopy" which copies data folders in a similar way as > mysqlhotcopy? Copying the raw data files of a live database only works under certain conditions with PostgreSQL, in short you have to tell it about what you're going to do for it to cooperate. That's pg_start_backup() function and friends. Now, the generic technique provided by PostgreSQL is called PITR, Point In Time Recovery. You'll find ready to go software to drive this, in order of personal preference : - walmgr from Skytools http://wiki.postgresql.org/wiki/Skytools#walmgr http://skytools.projects.postgresql.org/doc/walmgr.html https://developer.skype.com/SkypeGarage/DbProjects/SkyTools/WalMgr - pitrtools http://projects.commandprompt.com/public/pitrtools/ - pg_standby (read carefully the 8.3 era behavior of this one) it's in the contribs Those 3 solutions are packaged in debian and maybe some other distributions or OS. Regards, -- dim