Wednesday, January 22, 2014

Exclude tables during pg_restore

25 comments
Let we have a database like:

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)


Continue reading →
Wednesday, January 15, 2014

Postgresql schema sizes

0 comments
In order to get schema sizes of a large database, I used the following sql:

select schemaname, pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s
from pg_stat_user_tables
group by schemaname

   schemaname   |    s  
----------------+---------
 schema1        | 11 GB
 temp           |   1088 kB
 schema2        | 51 GB
 schema3        | 179 GB
(4 rows)


Continue reading →

Labels