From: Greg Williamson <gwilliamson39@yahoo.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: postgres 9.0.4, pg_restore and indexes
Date: Mon, 24 Oct 2011 14:56:08 -0700 (PDT)
Message-ID: <1319493368.35732.YahooMailNeo@web46108.mail.sp1.yahoo.com> (raw)
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.
Thanks,
Greg WIlliamson
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gwilliamson39@yahoo.com
Subject: Re: postgres 9.0.4, pg_restore and indexes
In-Reply-To: <1319493368.35732.YahooMailNeo@web46108.mail.sp1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox