Let we have a database like:
Take a dump of the database:
Now our task is to restore the database excluding table "t1":
Create a new database:
Restore schema only:
Drop table "t2":
Restore data:
Verify:
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
public | t1 | table | postgres
public | t2 | table | postgres
(3 rows)
testdb=# insert into t1 select c, c::text||'abc' as some_text from generate_series(1,10) as c;
INSERT 0 10
testdb=# insert into t2 select c, c::text||'abc' as some_text from generate_series(1,10) as c;
INSERT 0 10
testdb=# insert into t select c, c::text||'abc' as some_text from generate_series(1,10) as c;
testdb=# insert into t select c, (c::text||'abc')::bytea as some_text from generate_series(1,10) as c;
INSERT 0 10
Take a dump of the database:
pg_dump testdb -Fc -v > testdb.sqlc
Now our task is to restore the database excluding table "t1":
Create a new database:
create database testdb_new;
Restore schema only:
pg_restore -d testdb_new -s -v testdb.sqlc
pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating TABLE t
pg_restore: creating TABLE t1
pg_restore: creating TABLE t2
pg_restore: creating CONSTRAINT t1_pkey
pg_restore: creating CONSTRAINT t_pkey
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for TABLE t
pg_restore: setting owner and privileges for TABLE t1
pg_restore: setting owner and privileges for TABLE t2
pg_restore: setting owner and privileges for CONSTRAINT t1_pkey
pg_restore: setting owner and privileges for CONSTRAINT t_pkey
Drop table "t2":
postgres=# \c testdb_new
You are now connected to database "testdb_new" as user "postgres".
testdb_new=# drop table t1;
DROP TABLE
Restore data:
pg_restore -d testdb_new -a -v testdb.sqlc
pg_restore: connecting to database for restore
Password:
pg_restore: restoring data for table "t"
pg_restore: restoring data for table "t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2728; 0 35908 TABLE DATA t1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "t1" does not exist
Command was: COPY t1 (i, some_text) FROM stdin;
pg_restore: restoring data for table "t2"
pg_restore: setting owner and privileges for TABLE DATA t
pg_restore: setting owner and privileges for TABLE DATA t1
pg_restore: setting owner and privileges for TABLE DATA t2
WARNING: errors ignored on restore: 1
Verify:
testdb_new=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
public | t2 | table | postgres
(2 rows)
testdb_new=# select * from t;
i | nam
----+-------
1 | 1abc
2 | 2abc
3 | 3abc
4 | 4abc
5 | 5abc
6 | 6abc
7 | 7abc
8 | 8abc
9 | 9abc
10 | 10abc
(10 rows)
Worth reading! Our experts also have given detailed inputs about these trainings & courses! Presenting here for your reference. Do checkout
oracle sql training in chennai & enjoy learning more about it.
Sharing the same interest, Infycle feels so happy to share our detailed information about all these courses with you all! Python Training In Chennai & get to know everything you want to about software trainings.
Just the way I have expected. Your website really is interesting. ExcelR Data Analytics Course
Mua vé tại đại lý vé máy bay Aivivu, tham khảo
vé máy bay đi Mỹ Vietnam Airline
vé máy bay từ mỹ về việt nam bao nhiêu
vé máy bay quy nhơn sài gòn giá rẻ
đặt vé máy bay về hà nội
vé máy bay ra nha trang