Re: file cloning in pg_upgrade and CREATE DATABASE

On 07/11/2018 19:03, Tom Lane wrote: > Peter Eisentraut writes: >> Committed, thanks. > > It seems unfortunate that there is no test coverage in the committed > patch. I realize that it may be hard to test given the filesystem > dependency, but how will we know if it works at all? You can use

Re: file cloning in pg_upgrade and CREATE DATABASE

On Wed, Nov 07, 2018 at 06:42:00PM +0100, Peter Eisentraut wrote: > -n is often used for something like "dry run", so it didn't go for that > here. I suspect the cloning will remain a marginal option for some > time, so having only a long option is acceptable. Good point. I didn't consider

Re: file cloning in pg_upgrade and CREATE DATABASE

Peter Eisentraut writes: > Committed, thanks. It seems unfortunate that there is no test coverage in the committed patch. I realize that it may be hard to test given the filesystem dependency, but how will we know if it works at all? regards, tom lane

Re: file cloning in pg_upgrade and CREATE DATABASE

On 19/10/2018 01:50, Michael Paquier wrote: > On Thu, Oct 18, 2018 at 11:59:00PM +0200, Peter Eisentraut wrote: >> New patch that removes all the various reflink modes and adds a new >> option --clone that works similar to --link. I think it's much cleaner now. > > Thanks Peter for the new

Re: file cloning in pg_upgrade and CREATE DATABASE

On Thu, Oct 18, 2018 at 11:59:00PM +0200, Peter Eisentraut wrote: > New patch that removes all the various reflink modes and adds a new > option --clone that works similar to --link. I think it's much cleaner now. Thanks Peter for the new version. + + {"clone", no_argument, NULL,

Re: file cloning in pg_upgrade and CREATE DATABASE

On 11/10/2018 16:50, Peter Eisentraut wrote: > On 10/10/2018 21:50, Bruce Momjian wrote: >>> I see. Peter is proposing to have a fourth mode, essentially >>> --transfer-mode=clone-or-copy. >> >> Uh, if you use --link, and the two data directories are on different >> file systems, it fails. No

Re: file cloning in pg_upgrade and CREATE DATABASE

On 10/10/2018 21:50, Bruce Momjian wrote: >> I see. Peter is proposing to have a fourth mode, essentially >> --transfer-mode=clone-or-copy. > > Uh, if you use --link, and the two data directories are on different > file systems, it fails. No one has ever asked for link-or-copy, so why > are we

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Oct 2, 2018 at 11:07:06PM -0300, Alvaro Herrera wrote: > On 2018-Oct-03, Michael Paquier wrote: > > > On Tue, Oct 02, 2018 at 10:35:02PM -0300, Alvaro Herrera wrote: > > > I'm not clear what interface are you proposing. Maybe they would just > > > use the clone-or-fail mode, and note

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Oct 02, 2018 at 11:07:06PM -0300, Alvaro Herrera wrote: > I see. Peter is proposing to have a fourth mode, essentially > --transfer-mode=clone-or-copy. Yes, a mode which depends on what the file system supports. Perhaps "safe" or "fast" could be another name, in the shape of the fastest

Re: file cloning in pg_upgrade and CREATE DATABASE

On 2018-Oct-03, Michael Paquier wrote: > On Tue, Oct 02, 2018 at 10:35:02PM -0300, Alvaro Herrera wrote: > > I'm not clear what interface are you proposing. Maybe they would just > > use the clone-or-fail mode, and note whether it fails? If that's not > > it, please explain. > > Okay. What I

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Oct 02, 2018 at 10:35:02PM -0300, Alvaro Herrera wrote: > I'm not clear what interface are you proposing. Maybe they would just > use the clone-or-fail mode, and note whether it fails? If that's not > it, please explain. Okay. What I am proposing is to not have any kind of automatic

Re: file cloning in pg_upgrade and CREATE DATABASE

On 2018-Oct-03, Michael Paquier wrote: > There could be an argument for having an automatic more within this > scheme, still I am not really a fan of this. When somebody integrates > pg_upgrade within an upgrade framework, they would likely test if > cloning actually works, bumping immediately

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Oct 02, 2018 at 02:31:35PM +0200, Peter Eisentraut wrote: > I can see the argument for that. But I don't understand where the > automatic mode fits into this. I would like to keep all three modes > from my patch: copy, clone-if-possible, clone-or-fail, unless you want > to argue against

Re: file cloning in pg_upgrade and CREATE DATABASE

On 28/09/2018 07:19, Michael Paquier wrote: > +static bool cloning_ok = true; > + > +pg_log(PG_VERBOSE, "copying \"%s\" to \"%s\"\n", > + old_file, new_file); > +if (cloning_ok && > +!cloneFile(old_file, new_file, map->nspname, map->relname, true)) > +{ > +

Re: file cloning in pg_upgrade and CREATE DATABASE

On Fri, Sep 28, 2018 at 02:19:53PM +0900, Michael Paquier wrote: > Thanks for the rebase. At the end I got my hands on only an APFS using > a mac. I ran a test with an instance holding a database with pgbench at > scaling factor 500, which gives close to 6.5GB. The results are nice on > my

Re: file cloning in pg_upgrade and CREATE DATABASE

On Thu, Sep 27, 2018 at 11:10:08PM +0200, Peter Eisentraut wrote: > On 26/09/2018 08:44, Michael Paquier wrote: >> Could you rebase once again? I am going through the patch and wanted to >> test pg_upgrade on Linux with XFS, but it does not apply anymore. > > attached Thanks for the rebase. At

Re: file cloning in pg_upgrade and CREATE DATABASE

On 26/09/2018 08:44, Michael Paquier wrote: > On Sat, Sep 01, 2018 at 07:28:37AM +0200, Peter Eisentraut wrote: >> rebased patch, no functionality changes > > Could you rebase once again? I am going through the patch and wanted to > test pg_upgrade on Linux with XFS, but it does not apply

Re: file cloning in pg_upgrade and CREATE DATABASE

Hi Peter, On Sat, Sep 01, 2018 at 07:28:37AM +0200, Peter Eisentraut wrote: > rebased patch, no functionality changes Could you rebase once again? I am going through the patch and wanted to test pg_upgrade on Linux with XFS, but it does not apply anymore. Thanks, -- Michael signature.asc

Re: file cloning in pg_upgrade and CREATE DATABASE

rebased patch, no functionality changes -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >From 5e0f60e9cf182063f2f711251430d79282be1f93 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 1 Sep 2018

Re: file cloning in pg_upgrade and CREATE DATABASE

On 17.07.18 08:58, Michael Paquier wrote: > Hm... I am wondering if we actually want the "auto" mode where we make > the option smarter automatically. I am afraid of users trying to use it > and being surprised that there is no gain while they expected some. I > would rather switch that to an

Re: file cloning in pg_upgrade and CREATE DATABASE

On Wed, Jun 06, 2018 at 11:58:14AM -0400, Peter Eisentraut wrote: > > The setting always requires the use of relinks. If > they are not supported, the pg_upgrade run > will abort. Use this in production to limit the upgrade run time. > The setting auto uses reflinks when available, >

Re: file cloning in pg_upgrade and CREATE DATABASE

On Fri, Jul 13, 2018 at 10:22:21AM +0200, Peter Eisentraut wrote: > On 13.07.18 07:09, Thomas Munro wrote: >> TIL that Solaris 11.4 (closed) ZFS supports reflink() too. Sadly, >> it's not in OpenZFS though I see numerous requests and discussions... > > I look forward to your FreeBSD patch then.

Re: file cloning in pg_upgrade and CREATE DATABASE

On Wed, Feb 21, 2018 at 4:00 PM, Peter Eisentraut wrote: > - XFS has (optional) reflink support. This file system is probably more > widely used than Btrfs. > > - Linux and glibc have a proper function to do this now. > > - APFS on macOS supports file cloning. TIL that Solaris 11.4 (closed) ZFS

Re: file cloning in pg_upgrade and CREATE DATABASE

On Wed, Jun 6, 2018 at 11:58 AM, Peter Eisentraut wrote: > --reflink={always,auto,never}. (This option name is adapted from GNU ... > The setting always requires the use of relinks. If Is it supposed to be relinks or reflinks? The two lines above don't agree. -- Robert Haas EnterpriseDB:

Re: file cloning in pg_upgrade and CREATE DATABASE

I have made a major revision of this patch. I have removed all the changes to CREATE DATABASE. That was too contentious and we got lost in unrelated details there. The real benefit is for pg_upgrade. Another point was that for pg_upgrade use a user would like to know beforehand whether

Re: file cloning in pg_upgrade and CREATE DATABASE

I think we have raised a number of interesting issues here which require more deeper consideration. So I suggest to set this patch to Returned with feedback. Btw., I just learned that copy_file_range() only works on files on the same device. So more arrangements will need to be made for that.

Re: file cloning in pg_upgrade and CREATE DATABASE

On 3/26/18 02:15, Michael Paquier wrote: > f8c183a has introduced the optimization that your patch is removing, > which was discussed on this thread: > https://www.postgresql.org/message-id/flat/4B78906A.7020309%40mark.mielke.cc Note that that thread is from 2010 and talks about creation of a

Re: file cloning in pg_upgrade and CREATE DATABASE

On Sun, Mar 25, 2018 at 09:33:38PM -0400, Peter Eisentraut wrote: > On 3/21/18 22:38, Michael Paquier wrote: >> At least on Linux it is possible to rely on sync_file_range which is >> called via pg_flush_data, so it seems to me that we ought to roughly >> keep the loop working on FLUSH_DISTANCE,

Re: file cloning in pg_upgrade and CREATE DATABASE

On 3/23/18 13:16, Bruce Momjian wrote: > Also, it would be nice if users could easily know if pg_upgrade is going > to use COW or not because it might affect whether they choose --link or > not. Right now it seems unclear how a user would know. Can we have > pg_upgrade --check perhaps output

Re: file cloning in pg_upgrade and CREATE DATABASE

On 3/21/18 22:38, Michael Paquier wrote: > At least on Linux it is possible to rely on sync_file_range which is > called via pg_flush_data, so it seems to me that we ought to roughly > keep the loop working on FLUSH_DISTANCE, and replace the calls of > read/write by copy_file_range. copyfile is

Re: file cloning in pg_upgrade and CREATE DATABASE

I think this documentation change: + leaving the old cluster untouched. At present, this is supported on Linux - would be better by changing "untouched" to "unmodified". Also, it would be nice if users could easily know if pg_upgrade is

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Mar 20, 2018 at 10:55:04AM -0400, Peter Eisentraut wrote: > On 3/19/18 22:58, Michael Paquier wrote: >> - Extend copy_file so as it is able to use fcopyfile. > > fcopyfile() does not support cloning. (This is not documented.) You are right. I have been reading the documentation here to

Re: file cloning in pg_upgrade and CREATE DATABASE

.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services From bd8fe105f6b1c64098e344c4a7d0fc9c94d2e31d Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pete...@gmx.net> Date: Tue, 20 Mar 2018 10:21:47 -0400 Subject: [PATCH v2] Use file cloning in pg_upgrade and CREATE

Re: file cloning in pg_upgrade and CREATE DATABASE

On Mon, Mar 19, 2018 at 04:14:15PM +0900, Michael Paquier wrote: > Two other things I have noticed as well: > 1) src/bin/pg_rewind/copy_fetch.c could benefit from similar speed-ups I > think when copying data from source to target using the local mode of > pg_rewind. This could really improve

Re: file cloning in pg_upgrade and CREATE DATABASE

On Mon, Mar 19, 2018 at 04:06:36PM +0900, Michael Paquier wrote: > Any backend-side callers of copy_file() would not benefit from > copyfile() on OSX. Shouldn't all that handling be inside copy_file(), > similarly to what your patch actually does for pg_upgrade? I think that > you should also

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Feb 20, 2018 at 10:00:04PM -0500, Peter Eisentraut wrote: > Some new things have happened since then: > > - XFS has (optional) reflink support. This file system is probably more > widely used than Btrfs. Btrfs is still in development, there are I think no many people who would use it in

Re: file cloning in pg_upgrade and CREATE DATABASE

On 2/21/18 18:57, Tomas Vondra wrote: > Nice improvement, of course. How does that affect performance on the > cloned database? If I understand this correctly, it essentially enables > CoW on the files, so what's the overhead on that? It'd be unfortunate to > speed up CREATE DATABASE only to get

Re: file cloning in pg_upgrade and CREATE DATABASE

On 02/21/2018 04:00 AM, Peter Eisentraut wrote: > ... > > Some example measurements: > > 6 GB database, pg_upgrade unpatched 30 seconds, patched 3 seconds (XFS > and APFS) > > similar for a CREATE DATABASE from a large template > Nice improvement, of course. How does that affect performance

Re: file cloning in pg_upgrade and CREATE DATABASE

On Tue, Feb 20, 2018 at 10:00 PM, Peter Eisentraut wrote: > Some example measurements: > > 6 GB database, pg_upgrade unpatched 30 seconds, patched 3 seconds (XFS > and APFS) > > similar for a CREATE DATABASE from a large template > > Even if you don't have a file

file cloning in pg_upgrade and CREATE DATABASE

Here is another attempt at implementing file cloning for pg_upgrade and CREATE DATABASE. The idea is to take advantage of file systems that can make copy-on-write clones, which would make the copy run much faster. For pg_upgrade, this will give the performance of --link mode without