Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_upgrade

pg_upgrade

From
Загороднев Роман Евгеньевич
Date:

Hi.

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

Pg_upgrade version 10.10.

 

С уважением,

Роман Евгеньевич Загороднев

Главный системный администратор

НПФ «БУДУЩЕЕ»

+7(495)995-30-37 доб. 4590

Москва, Цветной бульвар, д.2, подъезд D

Re: pg_upgrade

From
Julien Rouhaud
Date:
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?

RE: pg_upgrade

From
Загороднев Роман Евгеньевич
Date:
FS - NTFS.
Used different version (10, 12) and result always the same. In log I see that links was created. But pg_upgrade copies
alldatabases. In destination data folder I see files, not links, and if no free space on disk for copy all data, error
appearwhile 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?
Canyou show the full output of pg_upgrade in verbose mode?
Attachment

Re: pg_upgrade

From
Julien Rouhaud
Date:
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
copiesall databases. In destination data folder I see files, not links, and if no free space on disk for copy all data,
errorappear 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?

Re: pg_upgrade

From
Juan José Santamaría Flecha
Date:
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 

RE: pg_upgrade

From
Загороднев Роман Евгеньевич
Date:

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:

 

С уважением,

Роман Загороднев

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> 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 

Attachment

Re: pg_upgrade

From
Juan José Santamaría Flecha
Date:


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

  
Attachment