Hologres:Table recycle bin

What gets recovered

Table data, properties, and indexes—including primary key (PK), clustering key, and segment key—are all restored. If a TTL was set, it remains active after recovery.

Before you recover

• If a table with the same name already exists in the schema, rename or drop it before running RECOVER. If the existing table has a PK or clustering key, move it to a different schema (renaming alone is not enough). See Example 4.

• If the schema the table belonged to has been deleted, recovery fails.

Partitioned tables

• Recovering a child table restores it as a standard table. Attach it to the parent table manually.

• Recovering a parent table restores both the parent and all child tables to their original partitioned structure. No manual re-attachment is needed.

• If dynamic partitioning was enabled on the parent table, re-enable it manually after recovery. See Dynamic partitioning.

Dynamic tables

• A recovered dynamic table becomes a standard table. Its data is queryable, but auto-refresh is disabled. To restore auto-refresh, recreate the dynamic table. See ALTER DYNAMIC TABLE.

• Recovering a base table does not recover its dependent dynamic tables.

Cascading drops

DROP TABLE <table_name> CASCADE also deletes dependent views, materialized views, and dynamic tables. Running RECOVER TABLE on that table recovers only the table itself—views, materialized views, and dependent dynamic tables must be recreated manually.

Example: A dynamic table depends on view1, and view1 depends on table1. Running DROP TABLE table1 CASCADE deletes both view1 and the dynamic table. After recovery, only table1 is restored. Recreate view1 and the dynamic table manually.

View tables in the recycle bin

SELECT * FROM hologres.hg_recyclebin;

Table owners see only their own deleted tables. Superusers see all tables in the recycle bin.

The query returns the following fields:

To find a specific table when the recycle bin contains many entries, filter by name or sort by drop time:

-- Find a table by name.
SELECT * FROM hologres.hg_recyclebin WHERE table_name = '<table_name>';

-- Sort by deletion time to find recently dropped tables.
SELECT * FROM hologres.hg_recyclebin ORDER BY drop_time DESC;

Check storage used by the recycle bin

-- Check the storage of a specific table (returns bytes).
SELECT hologres.hg_recyclebin_relation_size('<schema_name.table_name>');

-- Check with a human-readable unit.
SELECT PG_SIZE_PRETTY(hologres.hg_recyclebin_relation_size('<schema_name.table_name>'));

-- If multiple tables share the same schema and name, specify table_id.
SELECT hologres.hg_recyclebin_relation_size('<schema_name.table_name>', <table_id>);

Starting from V3.1.32, V3.2.12, and V4.0.2, Hologres also provides storage monitoring metrics for the recycle bin per database.

Set the retention period

By default, tables are retained for 1 day before the system automatically purges them. Only a superuser can change the retention period, and the change applies at the database level.

-- Set the retention period to 5 days.
ALTER DATABASE <db_name> SET hg_recyclebin_retention_days = 5;

Valid range: 1–10 days.

Purge tables

Purging permanently deletes a table from the recycle bin. It cannot be recovered afterward.

-- Purge a single table.
PURGE TABLE <table_name>;

-- Purge all tables in the recycle bin (superuser only).
CALL hologres.hg_purge_all_tables();

You can purge tables only. Dependent dynamic tables cannot be purged directly.

View the recycle bin

• Table owners can view only their own deleted tables.

• Superusers can view all tables in the recycle bin.

Drop, recover, and purge tables

In the SPM/SLPM permission models, only users who were in the Developer or Admin group before a table was moved to the recycle bin can manage it there. Users added to those groups afterward cannot manage the table.

When switching from SPM/SLPM to expert mode, only a superuser can recover or purge tables in the recycle bin.

Permission edge case

Consider this scenario: user1 owns schema1 and user2 owns schema1.table2.

-- user1 creates schema1 and grants CREATE permission to user2.
CREATE SCHEMA schema1;
GRANT CREATE ON SCHEMA schema1 TO "BASIC$user2";

-- user2 creates schema1.table2 and becomes the table owner.
CREATE TABLE schema1.table2(id INT);

-- user1 can drop schema1 and all its objects, including table2...
DROP SCHEMA schema1 CASCADE;
-- DROP CASCADES TO TABLE schema1.table2
-- DROP SCHEMA

-- ...but cannot query table2.
SELECT * FROM schema1.table2;
-- ERROR: permission denied for table table2

To recover table2, either user1 (who deleted it) or user2 (the table owner) can run RECOVER TABLE.

Example 1: Drop and recover a standard table

1. Create a table, insert data, and drop it.

   CREATE TABLE tbl1 (
     id INT NOT NULL)
   WITH (
       orientation = 'column',
       distribution_key = 'id',
       clustering_key = 'id',
       event_time_column = 'id');
   INSERT INTO tbl1 SELECT i FROM GENERATE_SERIES(1, 1000000) i;
   DROP TABLE tbl1;

2. Confirm the table is in the recycle bin.

   SELECT * FROM hologres.hg_recyclebin;

   table_id | schema_name | table_name |   table_owner   |    dropby   |        drop_time
   ---------+-------------+------------+-----------------+-------------+------------------------
         14 | public      | tbl1       | xx_developer    | 1365xxxxxxxx| 2025-04-17 19:23:10+08
   (1 row)

3. Check the storage used.

   SELECT (hologres.hg_recyclebin_relation_size('tbl1')/1024)::text||'KB' AS hg_recyclebin_relation_size;

    hg_recyclebin_relation_size
   -----------------------------
                         1336KB
   (1 row)

4. Recover the table. Data, PK, and clustering key are all restored.

   RECOVER TABLE tbl1;

Example 2: Drop and recover a partitioned table

1. Create a partitioned table with child tables.

   CREATE TABLE tbl2_parent(id INT) PARTITION BY list (id);
   CREATE TABLE tbl2_child_1 PARTITION OF tbl2_parent FOR VALUES IN (1);
   CREATE TABLE tbl2_child_2 PARTITION OF tbl2_parent FOR VALUES IN (2);

2. Drop and recover a child table. After recovery, the child table becomes a standard table and must be re-attached to the parent.

   -- Drop the child table.
   DROP TABLE tbl2_child_1;
   
   -- Confirm it is in the recycle bin.
   SELECT * FROM hologres.hg_recyclebin;

   table_id | schema_name |  table_name  |   table_owner  |   dropby  |       drop_time
   ---------+-------------+--------------+----------------+-----------+------------------------
         16 | public      | tbl2_child_1 | xx_developer   | 1365xxxxx | 2025-04-17 19:33:30+08
   (1 row)

   -- Recover the child table.
   RECOVER TABLE tbl2_child_1;
   
   -- Verify its structure. It is now a standard table, not a partition.
   SELECT hg_dump_script('tbl2_child_1');

   The DDL output shows CREATE TABLE public.tbl2_child_1 without a partition clause, confirming the table is now a standard table.

3. Drop and recover the parent table. Both parent and child tables are restored to their original structure.

   -- Drop the parent table.
   DROP TABLE tbl2_parent CASCADE;
   
   -- Confirm both parent and child are in the recycle bin.
   SELECT * FROM hologres.hg_recyclebin;

   table_id | schema_name |  table_name  |  table_owner  |     dropby    |       drop_time
   ---------+-------------+--------------+---------------+---------------+------------------------
         17 | public      | tbl2_child_2 | xx_developer  | 1365xxxxxxxx  | 2025-04-17 19:41:04+08
         15 | public      | tbl2_parent  | xx_developer  | 1365xxxxxxxx  | 2025-04-17 19:41:04+08

   -- Recover the parent table. Child tables are automatically restored.
   RECOVER TABLE tbl2_parent;
   
   -- Verify the partition structure using a PSQL client.
   \d+ tbl2_parent;

   Partitioned table "public.tbl2_parent"
    Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
   --------+---------+-----------+----------+---------+---------+--------------+-------------
    id     | integer |           |          |         | plain   |              |
   Partition key: LIST (id)
   Partitions: tbl2_child_2 FOR VALUES IN (2)

Example 3: Drop a table permanently without the recycle bin

CREATE TABLE tbl1(id INT);
DROP TABLE tbl1 FORCE;

Example 4: Recover a table with a conflicting name

Scenario A: Name conflict without shared indexes

If the existing table has no PK or clustering key, rename it first, then recover.

-- Create and drop a table.
CREATE TABLE tbl6(id INT);
DROP TABLE tbl6;

-- Create a new table with the same name.
CREATE TABLE tbl6(id INT);

-- Recovery fails because the name conflicts.
RECOVER TABLE tbl6;
-- ERROR: Table public.tbl6 already exists

-- Rename the existing table, then recover.
ALTER TABLE tbl6 RENAME TO tbl6_rename;
RECOVER TABLE tbl6;

Scenario B: Name conflict with shared PK or clustering key

If the existing table has a PK or clustering key, renaming is not enough—move it to a different schema before recovering.

-- Create a table with a PK and clustering key, then drop it.
CREATE TABLE tbl1 (
    col1 INT,
    col2 INT,
    col3 INT,
    PRIMARY KEY (col1, col2)
)
WITH (clustering_key = 'col1');
DROP TABLE tbl1;

-- Recreate a table with the same name, PK, and clustering key.
CREATE TABLE tbl1 (
    col1 INT,
    col2 INT,
    col3 INT,
    PRIMARY KEY (col1, col2)
)
WITH (clustering_key = 'col1');

-- Recovery fails even after renaming, because the index name conflicts.
ALTER TABLE tbl1 RENAME TO tbl2;
RECOVER TABLE tbl1;
-- ERROR: relation "tbl1_pkey" already EXISTS IN SCHEMA "public"

-- Move the renamed table to a different schema, then recover.
CREATE SCHEMA test;
ALTER TABLE tbl2 SET SCHEMA test;
RECOVER TABLE tbl1;

Scenario C: Recover a specific table when multiple deleted tables share the same name

When the recycle bin contains multiple tables with the same name, use table_id to identify the one to recover.

-- Find the table_id from the recycle bin.
SELECT table_id, table_name, schema_name, drop_time
FROM hologres.hg_recyclebin
WHERE table_name = 'tbl1'
ORDER BY drop_time;

-- Recover the specific table by its table_id.
RECOVER TABLE tbl1 WITH (table_id = <table_id>);

Example 5: Purge a table from the recycle bin

CREATE TABLE tbl1(id INT);
DROP TABLE tbl1;

-- Permanently remove the table from the recycle bin.
PURGE TABLE tbl1;