Re: postgres 9.0.4, pg_restore and indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, Oct 25, 2011 at 3:26 AM, Greg Williamson <gwilliamson39@xxxxxxxxx> 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 
 

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux