Postgres move partition to archive table

Question

I have a main prod table which I am converting to partitions and am taking a deep dive into Postgres (v14) partitioning on an isolated test system.

On the test system I have two schemas a production named part_schema and an archival schema named archive_schema. When I move a partition from the production table to the archive table the partition moves and if I query the parent archive table for the date range in the newly attached partition I can see the data is there. Also PGAdmin shows the partition under the parent in schema archive_schema and removes it from part_schema. If however I try to query the partition itself I get error 'relation does not exist' because the schema is still the 'old' one. In act I can query it under the 'old' schema. I have to move that manually. All works well but I was not expecting this.

Did I miss something when moving the partition?

select count(*) 
from part_schema.mtp_202401;

select fact_id from part_schema.maintbl_part 
where f_timestamp >= '2024-01-01' and f_timestamp < '2024-01-05';

alter table part_schema.maintbl_part 
detach partition part_schema.mtp_202401;

-- this moves the partition to the archive parent but not the schema
alter table archive_schema.archtbl 
attach partition part_schema.mtp_202401 
for values from ('2024-01-01') to ('2024-02-01');

-- this gives error: 'relation does not exist'
select count(*) 
from archive_schema.mtp_202401;

-- this works!!!
select count(*) 
from part_schema.mtp_202401;

-- I have to do this
alter table part_schema.mtp_202401 
set schema archive_schema;

-- this now works
select count(*) 
from archive_schema.mtp_202401;

select * 
from archive_schema.archtbl

Update:

The question was altered by one of the editors and it changed the intention of what I am seeking with this post. I started my original post as "I would appreciate someone confirming what I am doing and seeing is correct."

The work I did got me to were I wanted to, I just thought it strange that I had to do it in two steps. I understand that UI's can be present an optimised, more organised view. Thank you to all who commented.

@GurupreetSinghBhatia That's already in there, third statement from the bottom. — Zegarek
– Zegarek, Commented Jun 12 at 17:25
what i want to explain is, that step c compulsorily need to be performed as part of moving this partition. — Gurupreet Singh Bhatia
– Gurupreet Singh Bhatia, Commented Jun 12 at 17:28
It isn't compulsory. The partition can remain where it is or be moved elsewhere entirely, it doesn't really matter. I believe OP's issue here was that pgAdmin made it look like it's getting moved when they change the parent table, but it's not. And there's no need to do that, other than to make its "location" the same as visually implied by pgAdmin's Object Explorer. — Zegarek
– Zegarek, Commented Jun 12 at 17:32

Frank Heikens · Accepted Answer · 2025-06-12 14:55:44Z

1

This is not related to pgAdmin. You are doing two very different things:

detaching and attaching a table to a parent table.
changing the schema, the namespace, of your partition.

-- detachting for the current parent:
alter table part_schema.maintbl_part 
detach partition part_schema.mtp_202401;

-- No, this does not MOVE anything, it only attaches the partition to a new parent
-- this moves the partition to the archive parent but not the schema
alter table archive_schema.archtbl 
attach partition part_schema.mtp_202401 
for values from ('2024-01-01') to ('2024-02-01');

-- Fortunately, it fails! It would be very strange if it wouldn't
-- this gives error: 'relation does not exist'
select count(*) 
from archive_schema.mtp_202401; -- table doesn't move magicly to different schemas

-- Of course, you have to do this when you want a table in a different schema
-- I have to do this
alter table part_schema.mtp_202401 
set schema archive_schema;

edited Jun 12 at 14:55

answered Jun 12 at 14:21

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zegarek Jun 12 at 14:26

I think OP's problem is purely pgAdmin behaviour. They see the partition seemingly move to another schema as a result of the parent table change, without altering its schema separately. That's just because pgAdmin hides partitions completely and only lists them under the parent without schema-qualifying the names so it does look like they get moved as a part of the operation, and only the properties dialogs reveal their actual location.

Frank Heikens Jun 12 at 14:38

@Zegarek I've seen other tools that also show all partitions under the main table. Just not sure about the schemas. We never move partitions between schemas; all partitions for all tables have their own schema "partitions". When a partition gets a different parent, nothing else changes.

Zegarek Jun 12 at 14:51

I've seen some too and it makes total sense. I'm trying to say my understanding of OP's problem is it's a pgAdmin UI/UX issue, its Object Explorer making it look like partitions are tied to the parent schema, which isn't the case and the fact is tucked away in properties. I also haven't seen much need to move partitions between schemas, mainly just tablespaces (downgrading older partitions to cheaper drives) and it looks like OP's only doing that to align things with his understanding of what pgAdmin's showing, so they likely don't really need that either

Zegarek · Accepted Answer · 2025-06-12 17:37:33Z

Did I miss something when moving the partition?

You're not missing anything, that's pgAdmin's behaviour. Automatic schema change as an integral part of alter table..attach partition does not take place and as you already established, it has to be done separately: _{(if you want your partitions located in the same schema as the parent table)}

alter table part_schema.mtp_202401 set schema archive_schema;

PgAdmin lists the partitions under the main table regardless of which schema the child is actually in, to declutter the regular/parent table list.

Their true schemas are listed in Partitions>Properties. Same if you r-click the parent table, open Properties from the drop-down list, then enter the Partitions tab: You can even move the partitions to another schema that's completely unrelated to either part_schema or archive_schema, as mentioned by @Frank Heikens, which may simplify things.

If you do \d+ archive_schema.archtbl in psql (also available as "PSQL Tool" in pgAdmin), before manually issuing alter table..set schema on the partition, you'll see it's also listed there with it's true, unchanged schema:

test=# \d+ test_main
                                  Partitioned table "public.test_main"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 p      | integer |           |          |         | plain   |             |              |
 id     | integer |           |          |         | plain   |             |              |
Partition key: LIST (p)
Partitions: schema2.test_p2 FOR VALUES IN (2),
            test_p1 FOR VALUES IN (1)

In my case PgAdmin hiding partition tables under their parent table in Object Explorer even makes schema2 look completely empty and devoid of tables, even though that's exactly where test_p2 partition is:

Fortunately or not, there's no setting in pgAdmin to fully schema-qualify the partition names in Object Explorer tree or unhide them in their schemas.

If you need all partitions to always move to their parent schema, you could technically construct an event trigger catching alter table statements that attempt to attach partition, and follow that by setting the schema appropriately. It would also make sense to catch create table..partition of.

Collectives™ on Stack Overflow

Postgres move partition to archive table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related