Re: postgres 9.0.4, pg_restore and indexes

On Tue, Oct 25, 2011 at 3:26 AM, Greg Williamson <gwilliamson39(at)yahoo(dot)com>wrote:

> 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.
>
>
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