Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 35392B5DD26 for ; Mon, 24 Oct 2011 18:56:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 68696-01-4 for ; Mon, 24 Oct 2011 21:56:10 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from nm17-vm0.bullet.mail.sp2.yahoo.com (nm17-vm0.bullet.mail.sp2.yahoo.com [98.139.91.212]) by mail.postgresql.org (Postfix) with SMTP id 544D6B5DD53 for ; Mon, 24 Oct 2011 18:56:09 -0300 (ADT) Received: from [98.139.91.68] by nm17.bullet.mail.sp2.yahoo.com with NNFMP; 24 Oct 2011 21:56:09 -0000 Received: from [98.139.91.49] by tm8.bullet.mail.sp2.yahoo.com with NNFMP; 24 Oct 2011 21:56:09 -0000 Received: from [127.0.0.1] by omp1049.mail.sp2.yahoo.com with NNFMP; 24 Oct 2011 21:56:09 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 401108.61617.bm@omp1049.mail.sp2.yahoo.com Received: (qmail 83558 invoked by uid 60001); 24 Oct 2011 21:56:08 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1319493368; bh=qiImDi1iGxaBDmU8a4l4gW6XtlzWaS6Xd7hFzXaL2yw=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=kZK8wKXipUJ4HN0XD14LlBsUjwY5gBAYZt99kcrI2+jZ6EcMlIrgNUY5fEJRFQ1KnQZpB4CDmy7uy1ThcaNoFQCV5uC69QTMPAwRI7QrIgzpELdqwshOJD2tK49SqS7Rg+9/aB7CspVEy4SQo52VrMKSzXIRGLJUazgF5x0SJVs= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=FUA9qngPE9N3bvvjPVwgtbtfnsg6PiDxf8bRPzR8wa8x8ObS8Bkd+3lBeMyL1lYqNjmzWDZ2q23zGb7faIRSFITywDes6IGAuenhlKmVhHqJ8VDA7Sc8ktDKqgJQE/ew8wTxBDVWrO+HPjWx5Z7n58YJqUkOnY8ePVE9X8L2TUI=; X-YMail-OSG: SqqK4m0VM1kJdlI77uqyvnwaFeK_CtvbU1lXo_OFXyJzaP1 aiinoj9A0aD_r7GOVdmXGfJvu0nHbkQGnWbb9uUVWoIkaea8U9NymmxopzkN M2rUAVMsM0SSDqj2M5nnMCDtknx0f0Cbb8uN68xnmjSL.JDFmmOzVW8q_me6 k5zOZZjgmPiVHxpj7l6TCpfobMoyxFTvYVjySfBumXAuXqysX7F9agfx6h_e szCaMTDbQbIOlDVi3_XW5rI1aVSQxBcONytDfZZ62XLaa5WrTJZDtVQ3uwF_ 6mJgZ66QFPQgNNqU022eyooVHu9hnt1P2rius9SYY356aV3e_XAsqBX9i_2F fuBC83PXhvB.TuPV_fkJUFVVBcCZzjLtvxXOdgRO69g-- Received: from [206.80.5.2] by web46108.mail.sp1.yahoo.com via HTTP; Mon, 24 Oct 2011 14:56:08 PDT X-Mailer: YahooMailWebService/0.8.114.317681 Message-ID: <1319493368.35732.YahooMailNeo@web46108.mail.sp1.yahoo.com> Date: Mon, 24 Oct 2011 14:56:08 -0700 (PDT) From: Greg Williamson Reply-To: Greg Williamson Subject: postgres 9.0.4, pg_restore and indexes To: "pgsql-admin@postgresql.org" MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.152 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001, FREEMAIL_REPLYTO_END_DIGIT=1.151, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201110/104 X-Sequence-Number: 38556 I am trying to document how to recover a table that has been dropped by usi= ng pg_restore.=0A=0AThis is the table as it was originally:=0Apuppet=3D# \d= hosts=0A=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 Table "public.hosts"=0A=A0 =A0 =A0Column =A0 =A0 =A0| =A0 = =A0 =A0 =A0 =A0 =A0Type =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 Modifiers =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A---= --------------+-----------------------------+------------------------------= ----------------------=0A=A0id =A0 =A0 =A0 =A0 =A0 =A0 =A0| integer =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null default nextval('hosts_id_seq':= :regclass)=0A=A0name =A0 =A0 =A0 =A0 =A0 =A0| character varying(255) =A0 = =A0 =A0| not null=0A=A0ip =A0 =A0 =A0 =A0 =A0 =A0 =A0| character varying(25= 5) =A0 =A0 =A0|=A0=0A=A0environment =A0 =A0 | text =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0|=A0=0A=A0last_compile =A0 =A0| timestamp without ti= me zone |=A0=0A=A0last_freshcheck | timestamp without time zone |=A0=0A=A0l= ast_report =A0 =A0 | timestamp without time zone |=A0=0A=A0updated_at =A0 = =A0 =A0| timestamp without time zone |=A0=0A=A0source_file_id =A0| integer = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |=A0=0A=A0created_at =A0 =A0 =A0| t= imestamp without time zone |=A0=0AIndexes:=0A=A0 =A0 "hosts_pkey" PRIMARY K= EY, btree (id)=0A=A0 =A0 "index_hosts_on_name" btree (name)=0A=A0 =A0 "inde= x_hosts_on_source_file_id" btree (source_file_id)=0A=0A=0AI have a pg_dump = produced file for the database, and doing pg_restore with a -l seems to sho= w that it has what I need:=0A=0A-bash-3.2$ pg_restore -Fc -l --schema publi= c /var/data/pgsql/backups/prodDB/20111017_puppet.pgdump | grep hosts=0A1566= ; 1259 1605899114 TABLE public hosts puppet=0A1567; 1259 1605899120 SEQUENC= E public hosts_id_seq puppet=0A1937; 0 0 SEQUENCE OWNED BY public hosts_id_= seq puppet=0A1938; 0 0 SEQUENCE SET public hosts_id_seq puppet=0A1920; 0 16= 05899114 TABLE DATA public hosts puppet=0A1885; 2606 1605899385 CONSTRAINT = public hosts_pkey puppet=0A1886; 1259 1605899402 INDEX public index_hosts_o= n_name puppet=0A1887; 1259 1605899403 INDEX public index_hosts_on_source_fi= le_id puppet=0A=0A=3D=3D=3D=3D=3D=3D=0A=A0I can't create the primary key no= matter what I do.=0A=0Apg_restore -Fc -t hosts -j=3D2 --index=3Dhosts_pkey= --schema public -d restore_tmp /var/data/pgsql/backups/prodDB/20111017_pup= pet.pgdump=0A=0Arestore_tmp=3D# \d hosts=0A=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0Table "public.hosts"=0A=A0 =A0 =A0Column =A0 =A0 =A0| =A0 =A0 =A0 = =A0 =A0 =A0Type =A0 =A0 =A0 =A0 =A0 =A0 | Modifiers=A0=0A-----------------+= -----------------------------+-----------=0A=A0id =A0 =A0 =A0 =A0 =A0 =A0 = =A0| integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null=0A=A0name = =A0 =A0 =A0 =A0 =A0 =A0| character varying(255) =A0 =A0 =A0| not null=0A=A0= ip =A0 =A0 =A0 =A0 =A0 =A0 =A0| character varying(255) =A0 =A0 =A0|=A0=0A= =A0environment =A0 =A0 | text =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0|=A0=0A=A0last_compile =A0 =A0| timestamp without time zone |=A0=0A=A0la= st_freshcheck | timestamp without time zone |=A0=0A=A0last_report =A0 =A0 |= timestamp without time zone |=A0=0A=A0updated_at =A0 =A0 =A0| timestamp wi= thout time zone |=A0=0A=A0source_file_id =A0| integer =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 |=A0=0A=A0created_at =A0 =A0 =A0| timestamp without tim= e zone |=A0=0A=0Arestore_tmp=3D# \q=0A=0A=0AThese do manage to add the othe= r two indexes:=0A-bash-3.2$ pg_restore -Fc -s -t hosts -j=3D2 --index=3Dind= ex_hosts_on_name --schema public -d restore_tmp /var/data/pgsql/backups/pro= dDB/20111017_puppet.pgdump=0Apg_restore: [archiver (db)] Error while PROCES= SING TOC:=0Apg_restore: [archiver (db)] Error from TOC entry 1566; 1259 160= 5899114 TABLE hosts puppet=0Apg_restore: [archiver (db)] could not execute = query: ERROR: =A0relation "hosts" already exists=0A=A0 =A0 Command was:=A0= =0ACREATE TABLE hosts (=0A=A0 =A0 id integer NOT NULL,=0A=A0 =A0 name chara= cter varying(255) NOT NULL,=0A=A0 =A0 ip character varying(255),=0A=A0 =A0 = ...=0AWARNING: errors ignored on restore: 1=0A=0A-bash-3.2$ pg_restore -Fc = -s -t hosts -j=3D2 --index=3Dindex_hosts_on_source_file_id --schema public = -d restore_tmp /var/data/pgsql/backups/prodDB/20111017_puppet.pgdump=0Apg_r= estore: [archiver (db)] Error while PROCESSING TOC:=0Apg_restore: [archiver= (db)] Error from TOC entry 1566; 1259 1605899114 TABLE hosts puppet=0Apg_r= estore: [archiver (db)] could not execute query: ERROR: =A0relation "hosts"= already exists=0A=A0 =A0 Command was:=A0=0ACREATE TABLE hosts (=0A=A0 =A0 = id integer NOT NULL,=0A=A0 =A0 name character varying(255) NOT NULL,=0A=A0 = =A0 ip character varying(255),=0A=A0 =A0 ...=0AWARNING: errors ignored on r= estore: 1=0A-bash-3.2$ psql -d restore_tmp=0Apsql (9.0.4)=0AType "help" for= help.=0A=0Arestore_tmp=3D# \d hosts=0A=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0Table "public.hosts"=0A=A0 =A0 =A0Column =A0 =A0 =A0| =A0 =A0 =A0 =A0 = =A0 =A0Type =A0 =A0 =A0 =A0 =A0 =A0 | Modifiers=A0=0A-----------------+----= -------------------------+-----------=0A=A0id =A0 =A0 =A0 =A0 =A0 =A0 =A0| = integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null=0A=A0name =A0 = =A0 =A0 =A0 =A0 =A0| character varying(255) =A0 =A0 =A0| not null=0A=A0ip = =A0 =A0 =A0 =A0 =A0 =A0 =A0| character varying(255) =A0 =A0 =A0|=A0=0A=A0en= vironment =A0 =A0 | text =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|= =A0=0A=A0last_compile =A0 =A0| timestamp without time zone |=A0=0A=A0last_f= reshcheck | timestamp without time zone |=A0=0A=A0last_report =A0 =A0 | tim= estamp without time zone |=A0=0A=A0updated_at =A0 =A0 =A0| timestamp withou= t time zone |=A0=0A=A0source_file_id =A0| integer =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 |=A0=0A=A0created_at =A0 =A0 =A0| timestamp without time zo= ne |=A0=0AIndexes:=0A=A0 =A0 "index_hosts_on_name" btree (name)=0A=A0 =A0 "= index_hosts_on_source_file_id" btree (source_file_id)=0A=0A=0A=3D=3D=3D=3D= =0A=0ACould someone hit with me with a clue stick ? I've tried endless comb= inations of ways to restore the whole table with all indexes and have faile= d miserably. I must be missing something obvious.=0A=0AThanks,=0A=0AGreg WI= lliamson=0A