table_delete_using_oid_from_pg_class_restore

table_delete_using_oid_from_pg_class_restore

postgres@db:~/playground/demo$ pg_ctl -D data -l logfile start
waiting for server to start.... done
server started
postgres@db:~/github$ cd pagila
postgres@db:~/github/pagila$ ls -lrt
total 8292
-rw-rw-r-- 1 postgres postgres    5304 Jul  7 18:21 README.md
-rw-rw-r-- 1 postgres postgres    1100 Jul  7 18:21 LICENSE.txt
-rw-rw-r-- 1 postgres postgres 3034559 Jul  7 18:21 pagila-data.sql
-rw-rw-r-- 1 postgres postgres   52458 Jul  7 18:21 pagila-schema.sql
-rw-rw-r-- 1 postgres postgres 5388598 Jul  7 18:21 pagila-insert-data.sql
postgres@db:~/github/pagila$ psql -f pagila-schema.sql
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE TYPE
ALTER TYPE
CREATE DOMAIN
ALTER DOMAIN
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE SEQUENCE
ALTER TABLE
SET
SET
CREATE TABLE
ALTER TABLE
CREATE FUNCTION
ALTER FUNCTION
CREATE AGGREGATE
ALTER AGGREGATE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
postgres@db:~/github/pagila$ psql -f pagila-data.sql
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
COPY 200
COPY 109
COPY 600
COPY 603
COPY 16
COPY 2
COPY 599
COPY 6
COPY 1000
COPY 5462
COPY 1000
COPY 4581
COPY 2
COPY 16044
COPY 1157
COPY 2312
COPY 5644
COPY 6754
COPY 182
COPY 0
 setval
--------
    200
(1 row)

 setval
--------
    605
(1 row)

 setval
--------
     16
(1 row)

 setval
--------
    600
(1 row)

 setval
--------
    109
(1 row)

 setval
--------
    599
(1 row)

 setval
--------
   1000
(1 row)

 setval
--------
   4581
(1 row)

 setval
--------
      6
(1 row)

 setval
--------
  32098
(1 row)

 setval
--------
  16049
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
      2
(1 row)

postgres@db:~$ play
postgres@db:~/playground$ cd demo

# just checking pagilla backup running fine before tampering

postgres@db:~/playground/demo$ pg_dumpall > data.backup
postgres@db:~/playground/demo$ less data.backup
postgres@db:~/playground/demo$

-- for demo i just backedup pg_class data to a temo table
postgres@db:~/playground/demo$ psql
psql (14beta1)
Type "help" for help.

postgres=# create table pg_class_backup as ( select * from pg_class );
SELECT 502
postgres=# \dt
                    List of relations
 Schema |       Name       |       Type        |  Owner
--------+------------------+-------------------+----------
 public | actor            | table             | postgres
 public | address          | table             | postgres
 public | category         | table             | postgres
 public | city             | table             | postgres
 public | country          | table             | postgres
 public | customer         | table             | postgres
 public | film             | table             | postgres
 public | film_actor       | table             | postgres
 public | film_category    | table             | postgres
 public | inventory        | table             | postgres
 public | language         | table             | postgres
 public | payment          | partitioned table | postgres
 public | payment_p2020_01 | table             | postgres
 public | payment_p2020_02 | table             | postgres
 public | payment_p2020_03 | table             | postgres
 public | payment_p2020_04 | table             | postgres
 public | payment_p2020_05 | table             | postgres
 public | payment_p2020_06 | table             | postgres
 public | pg_class_backup  | table             | postgres
 public | rental           | table             | postgres
 public | staff            | table             | postgres
 public | store            | table             | postgres
(22 rows)

postgres=# select oid from pg_class where relna
relname       relnamespace  relnatts
postgres=# select oid,relname from pg_class where relname in ('actor', 'address', 'category', 'city');
  oid  | relname
-------+----------
 16460 | address
 16468 | city
 16419 | actor
 16427 | category
(4 rows)

postgres=# delete from pg_class where oid in (16460, 16468, 16419, 16427);
DELETE 4
postgres=# \q
postgres@db:~/playground/demo$ pg_dumpall > data_with_oid_del.backup
pg_dump: error: query failed: ERROR:  cache lookup failed for relation 16460
pg_dump: error: query was: SELECT tableoid, oid, conname, confrelid, conindid, pg_catalog.pg_get_constraintdef(oid) AS condef FROM pg_catalog.pg_constraint WHERE conrelid = '16407'::pg_catalog.oid AND conparentid = 0 AND contype = 'f'
pg_dumpall: error: pg_dump failed on database "postgres", exiting
postgres@db:~/playground/demo$ psql
psql (14beta1)
Type "help" for help.

postgres=# insert into pg_class select * from pg_class_backup where oid in (16460, 16468, 16419, 16427);
INSERT 0 4
postgres=# vacuum (full,analyze);
VACUUM
postgres=# \q
postgres@db:~/playground/demo$ pg_dumpall > data_with_oid_del_and_restored.backup




also, from an external backup
# the backup restored on db on port 5433
postgres@db:~/playground/demo$ psql -p 5433
psql (14beta1)
Type "help" for help.

postgres=# copy (select * from  pg_class where oid in (16460, 16468, 16419, 16427)) to '/tmp/tables.backup';
COPY 4
postgres=# \q

-- then restore the copied data to the original db which has oids deleted
postgres@db:~/playground/demo$ psql -p 5432
psql (14beta1)
Type "help" for help.

postgres=# create table pg_class_backup as (select * from pg_class);
SELECT 498
postgres=# copy pg_class_backup from '/tmp/tables.backup';
COPY 4
postgres=# insert into pg_class select * from pg_class_backup where oid in (16460, 16468, 16419, 16427);
INSERT 0 4
postgres=# \q
postgres@db:~/playground/demo$ pg_dumpall -p 5432 > deleted_backup
-- completed fine


then
postgres=# select relname from pg_class where oid in (16460, 16468, 16419, 16427);
 relname
----------
 address
 city
 actor
 category
(4 rows)

postgres=# drop table address cascade;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to view customer_list
drop cascades to view sales_by_store
drop cascades to view staff_list
drop cascades to constraint customer_address_id_fkey on table customer
drop cascades to constraint staff_address_id_fkey on table staff
drop cascades to constraint store_address_id_fkey on table store
DROP TABLE
postgres=# drop table city cascade;
DROP TABLE
postgres=# drop table actor cascade;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to view actor_info
drop cascades to view film_list
drop cascades to view nicer_but_slower_film_list
drop cascades to constraint film_actor_actor_id_fkey on table film_actor
DROP TABLE
postgres=# drop table category cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to view sales_by_film_category
drop cascades to constraint film_category_category_id_fkey on table film_category
DROP TABLE

# completes fine after tables drop
pg_dumpall -p 5432 > deleted_backup