Re: postgres 9.0.4, pg_restore and indexes - Mailing list pgsql-admin

I am trying to document how to recover a table that has been dropped by using pg_restore.

This is the table as it was originally:
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 | 
Indexes:
    "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 a -l seems to show that it has what I need:

-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


These do manage to add the other two indexes:
-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 while PROCESSING TOC:
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
    Command was: 
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 while PROCESSING TOC:
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
    Command was: 
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 | 
Indexes:
    "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.