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
