pg_upgrade

On Wed, Dec 25, 2019 at 3:17 PM Загороднев Роман Евгеньевич
<re.zagorodnev@npff.ru> wrote:

Hi.

On windows pg_upgrade.exe ignores –k (--link) options and try to copy all data.

Pg_upgrade version 10.10.

This should not be happening. On windows pg_upgrade uses
CreateHardLinkA()
(https://docs.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-createhardlinka?redirectedfrom=MSDN)
if -k / --link option is used. The page mentions that only ntfs
filesystem is supported, is this what you're using? Can you show the
full output of pg_upgrade in verbose mode?

FS - NTFS.
Used different version (10, 12) and result always the same. In log I see that links was created. But pg_upgrade copies all databases. In destination data folder I see files, not links, and if no free space on disk for copy all data, error appear while upgrade.

-----Original Message-----
From: Julien Rouhaud [mailto:rjuju123@gmail.com]
Sent: Wednesday, December 25, 2019 5:31 PM
To: Загороднев Роман Евгеньевич <re.zagorodnev@npff.ru>
Cc: pgsql-bugs@postgresql.org
Subject: Re: pg_upgrade

On Wed, Dec 25, 2019 at 3:17 PM <re.zagorodnev@npff.ru> wrote:

Hi.

On windows pg_upgrade.exe ignores –k (--link) options and try to copy all data.

Pg_upgrade version 10.10.

This should not be happening. On windows pg_upgrade uses
CreateHardLinkA()
(https://docs.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-createhardlinka?redirectedfrom=MSDN)
if -k / --link option is used. The page mentions that only ntfs filesystem is supported, is this what you're using? Can you show the full output of pg_upgrade in verbose mode?

On Wed, Dec 25, 2019 at 4:26 PM Загороднев Роман Евгеньевич
<re.zagorodnev@npff.ru> wrote:

FS - NTFS.
Used different version (10, 12) and result always the same. In log I see that links was created. But pg_upgrade copies all databases. In destination data folder I see files, not links, and if no free space on disk for copy all data, error appear while upgrade.

Unfortunately, I have no idea what encoding you used, so I can't read
the file. I tried some windows encoding but no luck, can you send the
file in utf8 or mention which encoding you're using? Could you also
specify the full pg_upgrade command line you're using?

On Wed, Dec 25, 2019 at 5:03 PM Julien Rouhaud <rjuju123@gmail.com> wrote:

On Wed, Dec 25, 2019 at 4:26 PM Загороднев Роман Евгеньевич
<re.zagorodnev@npff.ru> wrote:

FS - NTFS.
Used different version (10, 12) and result always the same. In log I see

that links was created. But pg_upgrade copies all databases. In destination
data folder I see files, not links, and if no free space on disk for copy
all data, error appear while upgrade.

Unfortunately, I have no idea what encoding you used, so I can't read
the file. I tried some windows encoding but no luck, can you send the
file in utf8 or mention which encoding you're using? Could you also
specify the full pg_upgrade command line you're using?

Hard links are not easy to distinguish from regular files in Windows.
If you identify a file path from a relation in a user database, like so:

test=# select pg_relation_filepath(c.relname::text) from pg_class c
test-# join pg_namespace n on n.oid = c.relnamespace
test-# where n.nspname not in ('information_schema', 'pg_catalog') and
c.relkind = 'r' limit 1;
pg_relation_filepath
----------------------
base/16384/16385
(1 row)

And then check the links of this file:

C:\> fsutil hardlink list %PGDATA%/base/16384/16385

You should see two locations. Can you please confirm this?

Regards,

Juan José Santamaría Flecha

Thanks for reply.
I performed some tests: fresh install windows server 2016 vm, postgres 9.4 and 12 install. One db on 9.4.
Upgrade command:
SET PGPASSWORD=postgres
C:\PostgreSQL\12\bin\pg_upgrade.exe -b "C:\PostgreSQL\9.4\bin" -B "C:\PostgreSQL\12\bin" -d "C:\PostgreSQL\9.4\data" -D "C:\PostgreSQL\12\data" -k -v -U postgres

Pg_upgrade output:
… linking "C:/PostgreSQL/9.4/data/base/16393/12092" to "C:/PostgreSQL/12/data/base/16410/2613"
linking "C:/PostgreSQL/9.4/data/base/16393/12092_fsm" to "C:/PostgreSQL/12/data/base/16410/2613_fsm"
linking "C:/PostgreSQL/9.4/data/base/16393/12094" to "C:/PostgreSQL/12/data/base/16410/2683"
linking "C:/PostgreSQL/9.4/data/base/16393/16394" to "C:/PostgreSQL/12/data/base/16410/16394"
linking "C:/PostgreSQL/9.4/data/base/16393/16394_fsm" to "C:/PostgreSQL/12/data/base/16410/16394_fsm"
linking "C:/PostgreSQL/9.4/data/base/16393/16401" to "C:/PostgreSQL/12/data/base/16410/16401"
linking "C:/PostgreSQL/9.4/data/base/16393/16401_fsm" to "C:/PostgreSQL/12/data/base/16410/16401_fsm"

Fsutil shows only one location:
C:\>fsutil hardlink list C:\PostgreSQL\12\data\base\16410\1249_vm
\PostgreSQL\12\data\base\16410\1249_vm

C:\>fsutil hardlink list C:\PostgreSQL\12\data\base\16410\113
\PostgreSQL\12\data\base\16410\113

In windows hard links created with mklink. Hardlink can be created only for files.
On the same server I create hardlink for file:
C:\Temp>mklink /H link_pg.cmd c:\Scripts\pg_upgrade.cmd
Hardlink created for link_pg.cmd <<===>> c:\Scripts\pg_upgrade.cmd

And fsutil shows two locations:
C:\Temp>fsutil hardlink list link_pg.cmd
\Scripts\pg_upgrade.cmd
\Temp\link_pg.cmd

In properties of file link_pg.cmd Size on disk are 0.
File properties in data directory:
[cid:image003.jpg@01D5BF1F.DC6314F0]

С уважением,
Роман Загороднев
From: Juan José Santamaría Flecha [mailto:juanjo.santamaria@gmail.com]
Sent: Friday, December 27, 2019 3:41 PM
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Загороднев Роман Евгеньевич <re.zagorodnev@npff.ru>; pgsql-bugs@postgresql.org
Subject: Re: pg_upgrade

On Wed, Dec 25, 2019 at 5:03 PM Julien Rouhaud <rjuju123@gmail.com<mailto:rjuju123@gmail.com>> wrote:
On Wed, Dec 25, 2019 at 4:26 PM Загороднев Роман Евгеньевич
<re.zagorodnev@npff.ru<mailto:re.zagorodnev@npff.ru>> wrote:

FS - NTFS.
Used different version (10, 12) and result always the same. In log I see that links was created. But pg_upgrade copies all databases. In destination data folder I see files, not links, and if no free space on disk for copy all data, error appear while upgrade.

Unfortunately, I have no idea what encoding you used, so I can't read
the file. I tried some windows encoding but no luck, can you send the
file in utf8 or mention which encoding you're using? Could you also
specify the full pg_upgrade command line you're using?

Hard links are not easy to distinguish from regular files in Windows.
If you identify a file path from a relation in a user database, like so:

test=# select pg_relation_filepath(c.relname::text) from pg_class c
test-# join pg_namespace n on n.oid = c.relnamespace
test-# where n.nspname not in ('information_schema', 'pg_catalog') and c.relkind = 'r' limit 1;
pg_relation_filepath
----------------------
base/16384/16385
(1 row)

And then check the links of this file:

C:\> fsutil hardlink list %PGDATA%/base/16384/16385
You should see two locations. Can you please confirm this?

Regards,

Juan José Santamaría Flecha

On Mon, Dec 30, 2019 at 12:46 PM Загороднев Роман Евгеньевич <
re.zagorodnev@npff.ru> wrote:

Fsutil shows only one location:

C:\>fsutil hardlink list C:\PostgreSQL\12\data\base\16410\1249_vm

\PostgreSQL\12\data\base\16410\1249_vm

C:\>fsutil hardlink list C:\PostgreSQL\12\data\base\16410\113

\PostgreSQL\12\data\base\16410\113

Not every relation is linked during the upgrade, the catalog of the greater
version is new and independent from the older version, as you are testing.

So some overhead is expected, you will need available disk space to fit
that.

Regards,

Juan José Santamaría Flecha