Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time.

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, types, and so on are not removed and still occupy space.

They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, because, unless a user has the SUPERUSER privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view the objects in his schema in the recycle bin using the following statement:

SELECT * FROM pg_catalog.pg_recyclebin
   WHERE namespace = to_regnamespace(CURRENT_SCHEMA)::oid;

Objects dropped by the DDL command are moved to the recycle bin. Dropped objects that are moved to the recycle bin can include the following types of objects:

• Tables
• Undos
• Indexes
• Constraints
• Types
• Functions

When you drop a tablespace, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. Likewise:

• When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
• When you drop a schema, any objects belonging to the schema are not placed in the recycle bin and any objects in the recycle bin are purged.

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

• A user drops a table, re-creates it with the same name, then drops it again.
• Two users have tables with the same name, and both users drop their tables.

The renaming convention is as follows:

pg_dropped_[object_type]_[object_id]

where:

• object_type is the object type name, eg: table, index, constraint, type, function.
• object_id is a unique identifier for this object, which makes the recycle bin name unique in current database.

You can query system catalog pg_recyclebin to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT objname, oldname FROM pg_catalog.pg_recyclebin
   WHERE namespace = to_regnamespace('hr')::oid;
       objname         |  oldname
-----------------------+-----------
pg_dropped_table_18762 | employees

You can also view the contents of the recycle bin using the GUI management tool like DBeaver, pgAdmin 4, expand the system schema pg_recyclebin under a specified database to view all the dropped objects.

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM pg_recyclebin.pg_dropped_table_18762;

If you decide that you are never going to restore an item from the recycle bin, then you can use the VACUUM statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

When you use the VACUUM statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from system catalog pg_recyclebin as shown in “Viewing and Querying Objects in the Recycle Bin”. The following hypothetical example purges the table hr.employees, which was renamed to pg_recyclebin.pg_dropped_table_18762 when it was placed in the recycle bin:

VACUUM pg_recyclebin.pg_dropped_table_18762;

You can achieve the same result with the following statement:

VACUUM hr.employees;

If you have the SUPERUSER privilege or you are the current database owner, then you can purge the entire recycle bin, and release space for objects, by using the following statement:

VACUUM;

You can also use the VACUUM statement to purge an index or undo from the recycle bin.

Use the CREATE TABLE … LIKE statement to recover objects from the recycle bin.

You should specify the name of the table in the recycle bin. The recycle bin name can be obtained from system catalog pg_recyclebin as shown in “Viewing and Querying Objects in the Recycle Bin”. To use the CREATE TABLE … LIKE statement, you need the SELECT privileges required to access the dropped table.

The following example restores employees table and assigns to it a new name:

CREATE TABLE hr.employees2 (LIKE pg_recyclebin.pg_dropped_table_18762 INCLUDING ALL);
INSERT INTO hr.employees2 SELECT * FROM pg_recyclebin.pg_dropped_table_18762;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of the employees table in the recycle bin and you want to recover the second version. You can query the recycle bin and then restore to the appropriate system-generated name, as shown in the following example. Including the drop time in the query can help you verify that you are restoring the correct table.

SELECT objname, oldname, droptime FROM pg_catalog.pg_recyclebin;

Result:

       objname         |  oldname  |      droptime
-----------------------+-----------+--------------------
pg_dropped_table_18762 | employees | 2016-02-05 21:05:52
pg_dropped_table_18924 | employees | 2016-02-05 21:25:13
pg_dropped_table_19510 | employees | 2016-02-05 22:05:53

Restore the dropped table:

CREATE TABLE hr.employees2 (LIKE pg_recyclebin.pg_dropped_table_18924 INCLUDING ALL);
INSERT INTO hr.employees2 SELECT * FROM pg_recyclebin.pg_dropped_table_18924;