Adjust the size of default vmdk Vagrantfile disks (you can use this procedure to relocate + resize as well):

1) Shutdown the VM

2) VBoxManage.exe clonehd box-disk001.vmdk E:\Virtualbox_VMs\voracle9x-docker-sa1\voracle9x-docker-sa1-box-disk001.vdi --format VDI

C:\VirtualBox_VMs\voracle9x-docker-sa1_default_1763076112652_13435>"C:\Program Files\Oracle\VirtualBox"\VBoxManage.exe clonehd box-disk001.vmdk E:\Virtualbox_VMs\voracle9x-docker-sa1\voracle9x-docker-sa1-box-disk001.vdi --format VDI 0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100% Clone medium created in format 'VDI'. UUID: f09bc1a5-fdab-44b0-814b-cb599464562f

3) VBoxManage.exe modifyhd E:\Virtualbox_VMs\voracle9x-docker-sa1\voracle9x-docker-sa1-box-disk001.vdi --resize 76800

C:\VirtualBox_VMs\voracle9x-docker-sa1_default_1763076112652_13435>"C:\Program Files\Oracle\VirtualBox"\VBoxManage.exe modifyhd E:\Virtualbox_VMs\voracle9x-docker-sa1\voracle9x-docker-sa1-box-disk001.vdi --resize 76800 0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100% C:\VirtualBox_VMs\voracle9x-docker-sa1_default_1763076112652_13435>

4) Attach the new disk to VirtualBox media

5) Attach the new vdi disk in place of the existing vmdk (only disk sata0) attached to the VM using VBox.

6) Boot the VM using "vagrant up"

7) Run "fdisk -l" to list the partition table, we will see the partition table changed and new expanded size visible. in my case it expanded from 32GB to 75GB.

8) Run "cfdisk", in my case only 1 disk, so it picked up all the partitions from that disk

9) Choose /dev/sda3 (which corresponds to / FS) and use arrow key select resize, enter new size 74GB in my case; Write, Accept partition modification "yes"

10) Verify all the pvs, lvs, vgdisplay outputs to ensure the current sizes of the / FS is aligned.

11) Run "pvresize /dev/sda3" [partition corresponds to / FS]

12) Run "pvdisplay /dev/sda3" [partition corresponds to / FS] -- should display the extra space in free space.

13) Run "lvextend -l +100%FREE /dev/mapper/vg_main-lv_root" [vg corresponds to the / FS]

14) Run "xfs_growfs /" to accomadate the growth

15) Finally review the size allocated using  df -Th.

**********************Sample output: Before: (base) [root@localhost ~]# pvdisplay   --- Physical volume ---   PV Name               /dev/sda3   VG Name               vg_main   PV Size               <36.00 GiB / not usable 0   Allocatable           yes (but full)   PE Size               4.00 MiB   Total PE              9215   Free PE               0   Allocated PE          9215   PV UUID               6X5HFj-GRnV-Idjh-BO95-cLUf-biD4-dFPP3z (base) [root@localhost ~]# pvdisplay /dev/sda3   --- Physical volume ---   PV Name               /dev/sda3   VG Name               vg_main   PV Size               <36.00 GiB / not usable 0   Allocatable           yes (but full)   PE Size               4.00 MiB   Total PE              9215   Free PE               0   Allocated PE          9215   PV UUID               6X5HFj-GRnV-Idjh-BO95-cLUf-biD4-dFPP3z (base) [root@localhost ~]# vgdisplay vg_main   --- Volume group ---   VG Name               vg_main   System ID   Format                lvm2   Metadata Areas        1   Metadata Sequence No  5   VG Access             read/write   VG Status             resizable   MAX LV                0   Cur LV                2   Open LV               2   Max PV                0   Cur PV                1   Act PV                1   VG Size               <36.00 GiB   PE Size               4.00 MiB   Total PE              9215   Alloc PE / Size       9215 / <36.00 GiB   Free  PE / Size       0 / 0   VG UUID               7VoDZZ-Qqta-yTHp-UDUc-Ovju-UFID-ms8Riy (base) [root@localhost ~]# lvdisplay /dev/vg_main/lv_root   --- Logical volume ---   LV Path                /dev/vg_main/lv_root   LV Name                lv_root   VG Name                vg_main   LV UUID                btd7vi-Qtwj-nsIw-0tqf-2ury-a160-SA966o   LV Write Access        read/write   LV Creation host, time localhost.localdomain, 2025-08-28 12:38:32 +0000   LV Status              available   # open                 1   LV Size                <32.00 GiB   Current LE             8191   Segments               1   Allocation             inherit   Read ahead sectors     auto   - currently set to     256   Block device           252:0 (base) [root@localhost ~]# df -Th Filesystem                  Type      Size  Used Avail Use% Mounted on devtmpfs                    devtmpfs  4.0M     0  4.0M   0% /dev tmpfs                       tmpfs     2.9G     0  2.9G   0% /dev/shm tmpfs                       tmpfs     1.2G   17M  1.2G   2% /run /dev/mapper/vg_main-lv_root xfs        32G   24G  8.9G  73% / /dev/sda2                   xfs       960M  217M  744M  23% /boot vagrant                     vboxsf    466G  404G   63G  87% /vagrant tmpfs                       tmpfs     593M  4.0K  593M   1% /run/user/1000 tmpfs                       tmpfs     593M  4.0K  593M   1% /run/user/0 (base) [root@localhost ~] After: (base) [root@localhost ~]# pvresize /dev/sda3   Physical volume "/dev/sda3" changed   1 physical volume(s) resized or updated / 0 physical volume(s) not resized (base) [root@localhost ~]# pvdisplay /dev/sda3   --- Physical volume ---   PV Name               /dev/sda3   VG Name               vg_main   PV Size               <74.00 GiB / not usable 16.50 KiB   Allocatable           yes   PE Size               4.00 MiB   Total PE              18943   Free PE               9728   Allocated PE          9215   PV UUID               6X5HFj-GRnV-Idjh-BO95-cLUf-biD4-dFPP3z (base) [root@localhost ~]# lvextend -l +100%FREE /dev/mapper/vg_main-lv_root   Size of logical volume vg_main/lv_root changed from <32.00 GiB (8191 extents) to <70.00 GiB (17919 extents).   Logical volume vg_main/lv_root successfully resized. (base) [root@localhost ~]# df -Th / Filesystem                  Type  Size  Used Avail Use% Mounted on /dev/mapper/vg_main-lv_root xfs    32G   24G  8.9G  73% / (base) [root@localhost ~]# xfs_growfs / meta-data=/dev/mapper/vg_main-lv_root isize=512    agcount=4, agsize=2096896 blks          =                       sectsz=512   attr=2, projid32bit=1          =                       crc=1        finobt=1, sparse=1, rmapbt=0          =                       reflink=1    bigtime=1 inobtcount=1 nrext64=0          =                       exchange=0 data     =                       bsize=4096   blocks=8387584, imaxpct=25          =                       sunit=0      swidth=0 blks naming   =version 2              bsize=4096   ascii-ci=0, ftype=1, parent=0 log      =internal log           bsize=4096   blocks=16384, version=2          =                       sectsz=512   sunit=0 blks, lazy-count=1 realtime =none                   extsz=4096   blocks=0, rtextents=0 data blocks changed from 8387584 to 18349056 (base) [root@localhost ~]# df -Th / Filesystem                  Type  Size  Used Avail Use% Mounted on /dev/mapper/vg_main-lv_root xfs    70G   24G   47G  34% / (base) [root@localhost ~]# ****************

Part 3: Steps to move data from a non partitioned table to partitioned table in postgresql

Objective: Steps to move a nonpartition table to partition table with indexes & referential constraints in postgresql

Notice unlike part 1 and 2, the referential constraint make a real code change. Kindly read the blog to understand further..

A) Table Setup:

primary table:

create table test_idxfk as
with t as
(
select generate_series(0,100) lvl
)
select t.lvl
,(mod(t.lvl,100)+date '1/1/2022') as dob
,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
,substr(md5(random()::text),15) as fn
,substr(md5(random()::text),15) as ln
,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE
from t;

alter table test_idxfk add constraint test_idxfk_lvl_pk primary key (lvl);

child table:

create table test_idxfk_child as
with t as
(
select generate_series(0,100) lvl
)
select t.lvl
,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as HOSTLCODE
from t;

alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);

analyze verbose test_idxfk;
analyze verbose test_idxfk_child;

Output:

postgres=# create table test_idxfk as
postgres-# with t as
postgres-# (
postgres(# select generate_series(0,100) lvl
postgres(# )
postgres-# select t.lvl
postgres-# ,(mod(t.lvl,100)+date '1/1/2022') as dob
postgres-# ,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
postgres-# ,substr(md5(random()::text),15) as fn
postgres-# ,substr(md5(random()::text),15) as ln
postgres-# ,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE
postgres-# from t;
SELECT 101
Time: 17.071 ms

postgres=# alter table test_idxfk add constraint test_idxfk_lvl_pk primary key (lvl);
ALTER TABLE
Time: 8.048 ms
postgres=#

postgres=# create table test_idxfk_child as
postgres-# with t as
postgres-# (
postgres(# select generate_series(0,100) lvl
postgres(# )
postgres-# select t.lvl
postgres-# ,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
postgres-# ,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as HOSTLCODE
postgres-# from t;
SELECT 101
Time: 8.698 ms

postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);
ALTER TABLE
Time: 6.412 ms
postgres=#

postgres=# \d+ test_idxfk*
                                        Table "public.test_idxfk"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 lvl     | integer |           | not null |         | plain    |             |              |
 dob     | date    |           |          |         | plain    |             |              |
 hosptl  | text    |           |          |         | extended |             |              |
 fn      | text    |           |          |         | extended |             |              |
 ln      | text    |           |          |         | extended |             |              |
 poscode | text    |           |          |         | extended |             |              |
Indexes:
    "test_idxfk_lvl_pk" PRIMARY KEY, btree (lvl)
Access method: heap
               Index "public.test_idxfk_lvl_pk"
 Column |  Type   | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
 lvl    | integer | yes  | lvl        | plain   |
primary key, btree, for table "public.test_idxfk"
postgres=#

postgres=# \d+ test_idxfk_child*
                                      Table "public.test_idxfk_child"
  Column   |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 lvl       | integer |           |          |         | plain    |             |              |
 hosptl    | text    |           |          |         | extended |             |              |
 hostlcode | text    |           |          |         | extended |             |              |
Foreign-key constraints:
    "test_idxfk_child_lvl_fk" FOREIGN KEY (lvl) REFERENCES test_idxfk(lvl)
Access method: heap
postgres=#

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test_idxfk%' order by relname;
      relname      | relpages | reltuples | relallvisible | relispartition
-------------------+----------+-----------+---------------+----------------
 test_idxfk        |        2 |       101 |             0 | f
 test_idxfk_child  |        1 |       101 |             0 | f
 test_idxfk_lvl_pk |        2 |       101 |             0 | f
(3 rows)
Time: 0.680 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test_idxfk%' order by relname;
     relname      | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
 test_idxfk       |        3 |          303 |       101 |         0 |         0 |        101 |                   0
 test_idxfk_child |        1 |          101 |       101 |         0 |         0 |        101 |                   0
(2 rows)
Time: 12.428 ms
postgres=#

B) In part 1 we already identified the partition key, hence we will proceed with hosptl column as our partition key.

C) Let us collect dependency diagram (ER), we have a table dependency now because of the introduction of the referential constraint.

D) Let us get the source table ddl

/usr/pgsql-14/bin/pg_dump --table='public.test_idxfk' --username=postgres --dbname=postgres --schema-only --file=/tmp/test_idxfk_05apr23.sql --verbose

-bash-4.2$ /usr/pgsql-14/bin/pg_dump --table='public.test_idxfk' --username=postgres --dbname=postgres --schema-only --file=/tmp/test_idxfk_05apr23.sql --verbose
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
...
pg_dump: creating TABLE "public.test_idxfk"
pg_dump: creating CONSTRAINT "public.test_idxfk test_idxfk_lvl_pk"

-bash-4.2$

Output:

-- TOC entry 245 (class 1259 OID 16735)
-- Name: test_idxfk; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.test_idxfk (
    lvl integer NOT NULL,
    dob date,
    hosptl text,
    fn text,
    ln text,
    poscode text
);

ALTER TABLE public.test_idxfk OWNER TO postgres;
--
-- TOC entry 4007 (class 2606 OID 16741)
-- Name: test_idxfk test_idxfk_lvl_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.test_idxfk
    ADD CONSTRAINT test_idxfk_lvl_pk PRIMARY KEY (lvl);

-- Completed on 2023-04-05 16:14:05 BST

E) Let us create the table...

CREATE TABLE public.test_part_idxfk (
    lvl integer,
    dob date,
    hosptl text,
    fn text,
    ln text,
    poscode text
) partition by list(hosptl);
CREATE TABLE public.test_part_idxfk_a partition of public.test_part_idxfk for values in ('AAAAAAAAAAAAAAAAAAAA');
CREATE TABLE public.test_part_idxfk_b partition of public.test_part_idxfk for values in ('BBBBBBBBBBBBBBBBBBBA');
CREATE TABLE public.test_part_idxfk_c partition of public.test_part_idxfk for values in ('CCCCCCCCCCCCCCCCCCCA');
CREATE TABLE public.test_part_idxfk_d partition of public.test_part_idxfk for values in ('DDDDDDDDDDDDDDDDDDDA');
CREATE TABLE public.test_part_idxfk_default partition of public.test_part_idxfk default;

Output:
postgres=# CREATE TABLE public.test_part_idxfk (
postgres(#     lvl integer,
postgres(#     dob date,
postgres(#     hosptl text,
postgres(#     fn text,
postgres(#     ln text,
postgres(#     poscode text
postgres(# ) partition by list(hosptl);
CREATE TABLE
Time: 6.714 ms
postgres=# CREATE TABLE public.test_part_idxfk_a partition of public.test_part_idxfk for values in ('AAAAAAAAAAAAAAAAAAAA');
CREATE TABLE public.test_part_idxfk_default partition of public.test_part_idxfk default;CREATE TABLE
Time: 9.639 ms
postgres=# CREATE TABLE public.test_part_idxfk_b partition of public.test_part_idxfk for values in ('BBBBBBBBBBBBBBBBBBBA');
CREATE TABLE
Time: 9.963 ms
postgres=# CREATE TABLE public.test_part_idxfk_c partition of public.test_part_idxfk for values in ('CCCCCCCCCCCCCCCCCCCA');
CREATE TABLE
Time: 14.759 ms
postgres=# CREATE TABLE public.test_part_idxfk_d partition of public.test_part_idxfk for values in ('DDDDDDDDDDDDDDDDDDDA');
CREATE TABLE
Time: 8.914 ms
postgres=# CREATE TABLE public.test_part_idxfk_default partition of public.test_part_idxfk default;
CREATE TABLE
Time: 6.651 ms
postgres=#

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test%idxfk%' order by relname;
         relname         | relpages | reltuples | relallvisible | relispartition
-------------------------+----------+-----------+---------------+----------------
 test_idxfk              |        2 |       101 |             0 | f
 test_idxfk_child        |        1 |       101 |             0 | f
 test_idxfk_lvl_pk       |        2 |       101 |             0 | f
 test_part_idxfk         |        0 |        -1 |             0 | f
 test_part_idxfk_a       |        0 |        -1 |             0 | t
 test_part_idxfk_b       |        0 |        -1 |             0 | t
 test_part_idxfk_c       |        0 |        -1 |             0 | t
 test_part_idxfk_d       |        0 |        -1 |             0 | t
 test_part_idxfk_default |        0 |        -1 |             0 | t
(9 rows)
Time: 0.513 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test%idxfk%' order by relname;
         relname         | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
-------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
 test_idxfk              |        3 |          303 |       101 |         0 |         0 |        101 |                   0
 test_idxfk_child        |        1 |          101 |       101 |         0 |         0 |        101 |                   0
 test_part_idxfk         |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_a       |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_b       |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_c       |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_d       |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_default |        0 |            0 |         0 |         0 |         0 |          0 |                   0
(8 rows)
Time: 12.640 ms
postgres=#

F) Let us copy the data.

copy test_idxfk to '/tmp/test_idxfk_05apr23.dmp';
copy test_part_idxfk from '/tmp/test_idxfk_05apr23.dmp';

Output:
postgres=# copy test_idxfk to '/tmp/test_idxfk_05apr23.dmp';
COPY 101
Time: 0.971 ms

postgres=# copy test_part_idxfk from '/tmp/test_idxfk_05apr23.dmp';
COPY 101
Time: 2.772 ms
postgres=#

postgres=# analyze verbose test_idxfk;
INFO:  analyzing "public.test_idxfk"
INFO:  "test_idxfk": scanned 2 of 2 pages, containing 101 live rows and 0 dead rows; 101 rows in sample, 101 estimated total rows
ANALYZE
Time: 2.955 ms

postgres=# analyze verbose test_part_idxfk;
INFO:  analyzing "public.test_part_idxfk" inheritance tree
INFO:  "test_part_idxfk_a": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO:  "test_part_idxfk_b": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO:  "test_part_idxfk_c": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO:  "test_part_idxfk_d": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO:  analyzing "public.test_part_idxfk_a"
INFO:  "test_part_idxfk_a": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO:  analyzing "public.test_part_idxfk_b"
INFO:  "test_part_idxfk_b": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO:  analyzing "public.test_part_idxfk_c"
INFO:  "test_part_idxfk_c": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows
INFO:  analyzing "public.test_part_idxfk_d"
INFO:  "test_part_idxfk_d": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO:  analyzing "public.test_part_idxfk_default"
INFO:  "test_part_idxfk_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
ANALYZE
Time: 12.780 ms

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test%idxfk%' order by relname;
         relname         | relpages | reltuples | relallvisible | relispartition
-------------------------+----------+-----------+---------------+----------------
 test_idxfk              |        2 |       101 |             0 | f
 test_idxfk_child        |        1 |       101 |             0 | f
 test_idxfk_lvl_pk       |        2 |       101 |             0 | f
 test_part_idxfk         |       -1 |       101 |             0 | f
 test_part_idxfk_a       |        1 |        26 |             0 | t
 test_part_idxfk_b       |        1 |        26 |             0 | t
 test_part_idxfk_c       |        1 |        26 |             0 | t
 test_part_idxfk_d       |        1 |        23 |             0 | t
 test_part_idxfk_default |        0 |         0 |             0 | t
(9 rows)
Time: 0.711 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test%idxfk%' order by relname;
         relname         | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
-------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
 test_idxfk              |        4 |          404 |       101 |         0 |         0 |        101 |                   0
 test_idxfk_child        |        1 |          101 |       101 |         0 |         0 |        101 |                   0
 test_part_idxfk         |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_a       |        0 |            0 |        26 |         0 |         0 |         26 |                   0
 test_part_idxfk_b       |        0 |            0 |        26 |         0 |         0 |         26 |                   0
 test_part_idxfk_c       |        0 |            0 |        26 |         0 |         0 |         26 |                   0
 test_part_idxfk_d       |        0 |            0 |        23 |         0 |         0 |         23 |                   0
 test_part_idxfk_default |        0 |            0 |         0 |         0 |         0 |          0 |                   0
(8 rows)
Time: 12.508 ms
postgres=#

G) Let us add the primary key now.
Test the error - postgresql says you need to include the partition column in the primary key...

ALTER TABLE public.test_part_idxfk
    ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl);
Error:
postgres=# ALTER TABLE public.test_part_idxfk
postgres-#     ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "test_part_idxfk" lacks column "hosptl" which is part of the partition key.
Time: 2.294 ms
postgres=#

So the document is correct.

Now let us try again with primary key including the partition key column:

ALTER TABLE public.test_part_idxfk
    ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl,hosptl);
postgres=# ALTER TABLE public.test_part_idxfk
postgres-#     ADD CONSTRAINT test_part_idxfk_lvl_pk PRIMARY KEY (lvl,hosptl);
ALTER TABLE
Time: 32.099 ms

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test%idxfk%' order by relname;
           relname            | relpages | reltuples | relallvisible | relispartition
------------------------------+----------+-----------+---------------+----------------
 test_idxfk                   |        2 |       101 |             0 | f
 test_idxfk_child             |        1 |       101 |             0 | f
 test_idxfk_lvl_pk            |        2 |       101 |             0 | f
 test_part_idxfk              |       -1 |       101 |             0 | f
 test_part_idxfk_a            |        1 |        26 |             0 | t
 test_part_idxfk_a_pkey       |        2 |        26 |             0 | t
 test_part_idxfk_b            |        1 |        26 |             0 | t
 test_part_idxfk_b_pkey       |        2 |        26 |             0 | t
 test_part_idxfk_c            |        1 |        26 |             0 | t
 test_part_idxfk_c_pkey       |        2 |        26 |             0 | t
 test_part_idxfk_d            |        1 |        23 |             0 | t
 test_part_idxfk_d_pkey       |        2 |        23 |             0 | t
 test_part_idxfk_default      |        0 |         0 |             0 | t
 test_part_idxfk_default_pkey |        1 |         0 |             0 | t
 test_part_idxfk_lvl_pk       |        0 |         0 |             0 | f
(15 rows)
Time: 0.760 ms

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test%idxfk%' order by relname;
         relname         | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
-------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
 test_idxfk              |        4 |          404 |       101 |         0 |         0 |        101 |                   0
 test_idxfk_child        |        1 |          101 |       101 |         0 |         0 |        101 |                   0
 test_part_idxfk         |        0 |            0 |         0 |         0 |         0 |          0 |                   0
 test_part_idxfk_a       |        2 |           52 |        26 |         0 |         0 |         26 |                   0
 test_part_idxfk_b       |        2 |           52 |        26 |         0 |         0 |         26 |                   0
 test_part_idxfk_c       |        2 |           52 |        26 |         0 |         0 |         26 |                   0
 test_part_idxfk_d       |        2 |           46 |        23 |         0 |         0 |         23 |                   0
 test_part_idxfk_default |        2 |            0 |         0 |         0 |         0 |          0 |                   0
(8 rows)
Time: 13.192 ms

postgres=# select * from pg_indexes where tablename like 'test%idxfk%' order by tablename,indexname;
 schemaname |        tablename        |          indexname           | tablespace |                                                   indexdef
------------+-------------------------+------------------------------+------------+--------------------------------------------------------------------------------------------------------------
 public     | test_idxfk              | test_idxfk_lvl_pk            |            | CREATE UNIQUE INDEX test_idxfk_lvl_pk ON public.test_idxfk USING btree (lvl)
 public     | test_part_idxfk         | test_part_idxfk_lvl_pk       |            | CREATE UNIQUE INDEX test_part_idxfk_lvl_pk ON ONLY public.test_part_idxfk USING btree (lvl, hosptl)
 public     | test_part_idxfk_a       | test_part_idxfk_a_pkey       |            | CREATE UNIQUE INDEX test_part_idxfk_a_pkey ON public.test_part_idxfk_a USING btree (lvl, hosptl)
 public     | test_part_idxfk_b       | test_part_idxfk_b_pkey       |            | CREATE UNIQUE INDEX test_part_idxfk_b_pkey ON public.test_part_idxfk_b USING btree (lvl, hosptl)
 public     | test_part_idxfk_c       | test_part_idxfk_c_pkey       |            | CREATE UNIQUE INDEX test_part_idxfk_c_pkey ON public.test_part_idxfk_c USING btree (lvl, hosptl)
 public     | test_part_idxfk_d       | test_part_idxfk_d_pkey       |            | CREATE UNIQUE INDEX test_part_idxfk_d_pkey ON public.test_part_idxfk_d USING btree (lvl, hosptl)
 public     | test_part_idxfk_default | test_part_idxfk_default_pkey |            | CREATE UNIQUE INDEX test_part_idxfk_default_pkey ON public.test_part_idxfk_default USING btree (lvl, hosptl)
(7 rows)
Time: 2.620 ms
postgres=#

postgres=# \d+ test_part_idxfk
                                Partitioned table "public.test_part_idxfk"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 lvl     | integer |           | not null |         | plain    |             |              |
 dob     | date    |           |          |         | plain    |             |              |
 hosptl  | text    |           | not null |         | extended |             |              |
 fn      | text    |           |          |         | extended |             |              |
 ln      | text    |           |          |         | extended |             |              |
 poscode | text    |           |          |         | extended |             |              |
Partition key: LIST (hosptl)
Indexes:
    "test_part_idxfk_lvl_pk" PRIMARY KEY, btree (lvl, hosptl)
Partitions: test_part_idxfk_a FOR VALUES IN ('AAAAAAAAAAAAAAAAAAAA'),
            test_part_idxfk_b FOR VALUES IN ('BBBBBBBBBBBBBBBBBBBA'),
            test_part_idxfk_c FOR VALUES IN ('CCCCCCCCCCCCCCCCCCCA'),
            test_part_idxfk_d FOR VALUES IN ('DDDDDDDDDDDDDDDDDDDA'),
            test_part_idxfk_default DEFAULT

postgres=# \d+ test_part_idxfk_lvl_pk
       Partitioned index "public.test_part_idxfk_lvl_pk"
 Column |  Type   | Key? | Definition | Storage  | Stats target
--------+---------+------+------------+----------+--------------
 lvl    | integer | yes  | lvl        | plain    |
 hosptl | text    | yes  | hosptl     | extended |
primary key, btree, for table "public.test_part_idxfk"
Partitions: test_part_idxfk_a_pkey,
            test_part_idxfk_b_pkey,
            test_part_idxfk_c_pkey,
            test_part_idxfk_default_pkey,
            test_part_idxfk_d_pkey
Access method: btree

postgres=# \di+ test_part_idxfk_lvl_pk
                                                           List of relations
 Schema |          Name          |       Type        |  Owner   |      Table      | Persistence | Access method |  Size   | Description
--------+------------------------+-------------------+----------+-----------------+-------------+---------------+---------+-------------
 public | test_part_idxfk_lvl_pk | partitioned index | postgres | test_part_idxfk | permanent   | btree         | 0 bytes |
(1 row)
postgres=#

H) Let us now rename the table and index...

alter table test_idxfk rename to test_idxfk_old;
alter index test_idxfk_lvl_pk rename to test_idxfk_lvl_pk_old;

postgres=# alter table test_idxfk rename to test_idxfk_old;
ALTER TABLE
Time: 2.988 ms
postgres=# alter index test_idxfk_lvl_pk rename to test_idxfk_lvl_pk_old;
ALTER INDEX
Time: 2.745 ms
postgres=#
postgres=# \d+
                                                        List of relations
 Schema |              Name               |       Type        |  Owner   | Persistence | Access method |    Size    | Description
--------+---------------------------------+-------------------+----------+-------------+---------------+------------+-------------
..
 public | test_idxfk_child                | table             | postgres | permanent   | heap          | 16 kB      |
 public | test_idxfk_old                  | table             | postgres | permanent   | heap          | 24 kB      |
 Schema |             Name             |       Type        |  Owner   |          Table          | Persistence | Access method |    Size    | Description
--------+------------------------------+-------------------+----------+-------------------------+-------------+---------------+------------+-------------
 public | test_idxfk_lvl_pk_old        | index             | postgres | test_idxfk_old          | permanent   | btree         | 16 kB      |
            indexdef
------------+-------------------------+------------------------------+------------+--------------------------------------------------------------------------------------------------------------
 public     | test_idxfk_old          | test_idxfk_lvl_pk_old        |            | CREATE UNIQUE INDEX test_idxfk_lvl_pk_old ON public.test_idxfk_old USING btree (lvl)




Let us rename the partitioned table:

alter table test_part_idxfk rename to test_idxfk;
alter table test_part_idxfk_a rename to test_idxfk_a;
alter table test_part_idxfk_b rename to test_idxfk_b;
alter table test_part_idxfk_c rename to test_idxfk_c;
alter table test_part_idxfk_d rename to test_idxfk_d;

Output:

alter table test_part_idxfk_default rename to test_idxfk_default;
Time: 0.833 ms
postgres=# alter table test_part_idxfk rename to test_idxfk;
ALTER TABLE
Time: 2.950 ms
postgres=#  alter table test_part_idxfk_a rename to test_idxfk_a;
ALTER TABLE
Time: 3.892 ms
postgres=#  alter table test_part_idxfk_b rename to test_idxfk_b;
ALTER TABLE
Time: 8.017 ms
postgres=#  alter table test_part_idxfk_c rename to test_idxfk_c;
ALTER TABLE
Time: 8.148 ms
postgres=#  alter table test_part_idxfk_d rename to test_idxfk_d;
ALTER TABLE
Time: 4.647 ms
postgres=#  alter table test_part_idxfk_default rename to test_idxfk_default;
ALTER TABLE
Time: 1.826 ms
postgres=#

Let us rename the index:

alter index test_part_idxfk_lvl_pk rename to test_idxfk_lvl_pk;
alter index test_part_idxfk_a_pkey rename to test_idxfk_a_pkey;
alter index test_part_idxfk_b_pkey rename to test_idxfk_b_pkey;
alter index test_part_idxfk_c_pkey rename to test_idxfk_c_pkey;
alter index test_part_idxfk_d_pkey rename to test_idxfk_d_pkey;
alter index test_part_idxfk_default_pkey rename to test_idxfk_default_pkey;

Output:
postgres=# alter index test_part_idxfk_lvl_pk rename to test_idxfk_lvl_pk;
ALTER INDEX
Time: 2.022 ms
postgres=#  alter index test_part_idxfk_a_pkey rename to test_idxfk_a_pkey;
ALTER INDEX
Time: 1.796 ms
postgres=#  alter index test_part_idxfk_b_pkey rename to test_idxfk_b_pkey;
ALTER INDEX
Time: 3.128 ms
postgres=#  alter index test_part_idxfk_c_pkey rename to test_idxfk_c_pkey;
ALTER INDEX
Time: 2.552 ms
postgres=#  alter index test_part_idxfk_d_pkey rename to test_idxfk_d_pkey;
ALTER INDEX
Time: 2.542 ms
postgres=#  alter index test_part_idxfk_default_pkey rename to test_idxfk_default_pkey;
ALTER INDEX
Time: 2.303 ms
postgres=#

I) Let us test the constraint first in nonpart table for enforcement:

insert into test_idxfk_child values (101,'AAAAA','PPPPP');
postgres=# \d test_idxfk_child
           Table "public.test_idxfk_child"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 lvl       | integer |           |          |
 hosptl    | text    |           |          |
 hostlcode | text    |           |          |
Foreign-key constraints:
    "test_idxfk_child_lvl_fk" FOREIGN KEY (lvl) REFERENCES test_idxfk_old(lvl)
postgres=# insert into test_idxfk_child values (101,'AAAAA','PPPPP');
ERROR:  insert or update on table "test_idxfk_child" violates foreign key constraint "test_idxfk_child_lvl_fk"
DETAIL:  Key (lvl)=(101) is not present in table "test_idxfk_old".
Time: 0.924 ms
postgres=#

So it works fine.

J) Now let us drop and add the constraint back again to the partition table.

Drop routine:

select * from pg_constraint where conname='test_idxfk_child_lvl_fk';

postgres=# select * from pg_constraint where conname='test_idxfk_child_lvl_fk';
-[ RECORD 1 ]-+------------------------
oid           | 16749
conname       | test_idxfk_child_lvl_fk
connamespace  | 2200
contype       | f
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 16744 <<< cons rel id
contypid      | 0
conindid      | 16740 <<< cons pk idx id
conparentid   | 0
confrelid     | 16735 <<< cons pk rel id
confupdtype   | a
confdeltype   | a
confmatchtype | s
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {1}
confkey       | {1}
conpfeqop     | {96}
conppeqop     | {96}
conffeqop     | {96}
conexclop     |
conbin        |
Time: 0.681 ms
postgres=#

select reloid,relname,reltype from pg_class where oid in (16744,16740,16735) order by oid;

Output:

postgres=# select oid,relname,reltype from pg_class where oid in (16744,16740,16735) order by oid;
  oid  |        relname        | reltype
-------+-----------------------+---------
 16735 | test_idxfk_old        |   16737
 16740 | test_idxfk_lvl_pk_old |       0
 16744 | test_idxfk_child      |   16746
(3 rows)
Time: 0.529 ms
postgres=#

alter table test_idxfk_child drop constraint test_idxfk_child_lvl_fk;

Output:

postgres=# alter table test_idxfk_child drop constraint test_idxfk_child_lvl_fk;
ALTER TABLE
Time: 4.431 ms

postgres=# select * from pg_constraint where conname='test_idxfk_child_lvl_fk';
 oid | conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin
-----+---------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-------------+-----------+-------------+-------------+---------------+------------+-------------+--------------+--------+---------+-----------+-----------+-----------+-----------+--------
(0 rows)
Time: 0.981 ms

postgres=#

Add routine:

alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);
postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl);
ERROR:  there is no unique constraint matching given keys for referenced table "test_idxfk"
Time: 0.571 ms
postgres=#

postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl) references test_idxfk(lvl,hosptl);
ERROR:  number of referencing and referenced columns for foreign key disagree
Time: 0.545 ms
postgres=#

Stumpped!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

So an ASIS datamodel change with just partition change cant happen!
We have to adjust the foriegn key column to include the hosptl code as well, which is a big surprise!!

If you dont have this hostpl column in child table, the situation is more worse!

alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl,hosptl) references test_idxfk(lvl,hosptl);

Output:

postgres=# alter table test_idxfk_child add constraint test_idxfk_child_lvl_fk foreign key (lvl,hosptl) references test_idxfk(lvl,hosptl);

ALTER TABLE

Time: 5.872 ms

postgres=#

postgres=# select * from pg_constraint where conname='test_idxfk_child_lvl_fk';
-[ RECORD 1 ]-+------------------------
oid           | 16801
conname       | test_idxfk_child_lvl_fk
connamespace  | 2200
contype       | f
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 16744
contypid      | 0
conindid      | 16789
conparentid   | 0
confrelid     | 16756
confupdtype   | a
confdeltype   | a
confmatchtype | s
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {1,2}
confkey       | {1,3}
conpfeqop     | {96,98}
conppeqop     | {96,98}
conffeqop     | {96,98}
conexclop     |
conbin        |
Time: 0.483 ms

postgres=# select oid,relname,reltype from pg_class where oid in (16744,16740,16735) order by oid;
  oid  |        relname        | reltype
-------+-----------------------+---------
 16735 | test_idxfk_old        |   16737
 16740 | test_idxfk_lvl_pk_old |       0
 16744 | test_idxfk_child      |   16746
(3 rows)
Time: 1.295 ms

postgres=#
Test : FK is in enforcement mode
postgres=# insert into test_idxfk_child values (101,'AAAAA','PPPPP');
ERROR:  insert or update on table "test_idxfk_child" violates foreign key constraint "test_idxfk_child_lvl_fk"
DETAIL:  Key (lvl, hosptl)=(101, AAAAA) is not present in table "test_idxfk".
Time: 0.854 ms
postgres=#

Probably a stackover flow suggestion needs to be sought for the condition we faced.

Update:

Even in oracle it is the same situation, a partition table should include its partition key column in the primary key. So when we decide to perform a parition on the table, we have to give this due consideration as a data model change.

Thanks