Thread: postgres 9.0.4, pg_restore and indexes

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
allindexes and have failed miserably. I must be missing something obvious. 

Thanks,

Greg WIlliamson

Raghu Ram wrote:

<snip>

>
>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-readabledump ] 
>


Did you actually try this ?

This does not include any references to indexes, constraints or sequences, all of which it might be useful to recreate.

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

TIA,

Greg WIlliamson

Greg Williamson <gwilliamson39@yahoo.com> writes:
> 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