[ADMIN] postgres 9.0.4, pg_restore and indexes

[raghu ram]

I am trying to document how to recover a table that has been dropped by
using pg_restore.
puppet=# \d hosts
Table "public.hosts"
Column | Type |
Modifiers
-----------------+-----------------------------+----------------------------------------------------
id | integer | not null default
nextval('hosts_id_seq'::regclass)
name | character varying(255) | not null
ip | character varying(255) |
environment | text |
last_compile | timestamp without time zone |
last_freshcheck | timestamp without time zone |
last_report | timestamp without time zone |
updated_at | timestamp without time zone |
source_file_id | integer |
created_at | timestamp without time zone |
"hosts_pkey" PRIMARY KEY, btree (id)
"index_hosts_on_name" btree (name)
"index_hosts_on_source_file_id" btree (source_file_id)
I have a pg_dump produced file for the database, and doing pg_restore with
-bash-3.2$ pg_restore -Fc -l --schema public
/var/data/pgsql/backups/prodDB/20111017_puppet.pgdump | grep hosts
1566; 1259 1605899114 TABLE public hosts puppet
1567; 1259 1605899120 SEQUENCE public hosts_id_seq puppet
1937; 0 0 SEQUENCE OWNED BY public hosts_id_seq puppet
1938; 0 0 SEQUENCE SET public hosts_id_seq puppet
1920; 0 1605899114 TABLE DATA public hosts puppet
1885; 2606 1605899385 CONSTRAINT public hosts_pkey puppet
1886; 1259 1605899402 INDEX public index_hosts_on_name puppet
1887; 1259 1605899403 INDEX public index_hosts_on_source_file_id puppet
======
I can't create the primary key no matter what I do.
pg_restore -Fc -t hosts -j=2 --index=hosts_pkey --schema public -d
restore_tmp /var/data/pgsql/backups/prodDB/20111017_puppet.pgdump
restore_tmp=# \d hosts
Table "public.hosts"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
id | integer | not null
name | character varying(255) | not null
ip | character varying(255) |
environment | text |
last_compile | timestamp without time zone |
last_freshcheck | timestamp without time zone |
last_report | timestamp without time zone |
updated_at | timestamp without time zone |
source_file_id | integer |
created_at | timestamp without time zone |
restore_tmp=# \q
-bash-3.2$ pg_restore -Fc -s -t hosts -j=2 --index=index_hosts_on_name
--schema public -d restore_tmp
/var/data/pgsql/backups/prodDB/20111017_puppet.pgdump
pg_restore: [archiver (db)] Error from TOC entry 1566; 1259 1605899114
TABLE hosts puppet
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"hosts" already exists
CREATE TABLE hosts (
id integer NOT NULL,
name character varying(255) NOT NULL,
ip character varying(255),
...
WARNING: errors ignored on restore: 1
-bash-3.2$ pg_restore -Fc -s -t hosts -j=2
--index=index_hosts_on_source_file_id --schema public -d restore_tmp
/var/data/pgsql/backups/prodDB/20111017_puppet.pgdump
pg_restore: [archiver (db)] Error from TOC entry 1566; 1259 1605899114
TABLE hosts puppet
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"hosts" already exists
CREATE TABLE hosts (
id integer NOT NULL,
name character varying(255) NOT NULL,
ip character varying(255),
...
WARNING: errors ignored on restore: 1
-bash-3.2$ psql -d restore_tmp
psql (9.0.4)
Type "help" for help.
restore_tmp=# \d hosts
Table "public.hosts"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
id | integer | not null
name | character varying(255) | not null
ip | character varying(255) |
environment | text |
last_compile | timestamp without time zone |
last_freshcheck | timestamp without time zone |
last_report | timestamp without time zone |
updated_at | timestamp without time zone |
source_file_id | integer |
created_at | timestamp without time zone |
"index_hosts_on_name" btree (name)
"index_hosts_on_source_file_id" btree (source_file_id)
====
Could someone hit with me with a clue stick ? I've tried endless
combinations of ways to restore the whole table with all indexes and have
failed miserably. I must be missing something obvious.

Try with below work-around to restore only hosts table from compressed dump
file:

"pg_restore -t hosts -Fc
/var/data/pgsql/backups/prodDB/20111017_puppet.pgdump > hosts_plaindump" [
i.e you'll get a human-readable dump ]

psql -d restore_tmp -p 5432 -U postgres -f "\i hosts_plaindump"

--Raghu

[Tom Lane]

What I am I missing ? Or is pg_restore just not capable of restoring the full definition of a table ?

There isn't any provision for doing that automatically; it would be a
bit tough to determine exactly what the "full definition" consists of
in a way that makes everybody happy. (For instance, how about tables
that are targets of foreign key references? Functions that are depended
on by check constraints or indexes? And so on.)

What you can do is use the --list option to get a list of all the
objects in the dump, edit that down to what you want, then use the
--use-list option to restore the stuff in your abbreviated list.

regards, tom lane