pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres 9.0.4, pg_restore and indexes
4+ messages / 3 participants
[nested] [flat]
* postgres 9.0.4, pg_restore and indexes
@ 2011-10-24 21:56 Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Greg Williamson @ 2011-10-24 21:56 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres 9.0.4, pg_restore and indexes
@ 2011-10-24 23:33 raghu ram <raghuchennuru@gmail.com>
parent: Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: raghu ram @ 2011-10-24 23:33 UTC (permalink / raw)
To: Greg Williamson <gwilliamson39@yahoo.com>; +Cc: pgsql-admin
On Tue, Oct 25, 2011 at 3:26 AM, Greg Williamson <gwilliamson39@yahoo.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres 9.0.4, pg_restore and indexes
@ 2011-10-25 00:41 Greg Williamson <gwilliamson39@yahoo.com>
parent: raghu ram <raghuchennuru@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Greg Williamson @ 2011-10-25 00:41 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Raghu Ram wrote:
<snip>
>
>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 ]
>
Did you actually try this ?
This does not include any references to indexes, constraints or sequences, all of which it might be useful to recreate.
What I am I missing ? Or is pg_restore just not capable of restoring the full definition of a table ?
TIA,
Greg WIlliamson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres 9.0.4, pg_restore and indexes
@ 2011-10-25 01:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2011-10-25 01:10 UTC (permalink / raw)
To: Greg Williamson <gwilliamson39@yahoo.com>; +Cc: pgsql-admin
Greg Williamson <gwilliamson39@yahoo.com> writes:
> What I am I missing ? Or is pg_restore just not capable of restoring the full definition of a table ?
There isn't any provision for doing that automatically; it would be a
bit tough to determine exactly what the "full definition" consists of
in a way that makes everybody happy. (For instance, how about tables
that are targets of foreign key references? Functions that are depended
on by check constraints or indexes? And so on.)
What you can do is use the --list option to get a list of all the
objects in the dump, edit that down to what you want, then use the
--use-list option to restore the stuff in your abbreviated list.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-25 01:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-24 21:56 postgres 9.0.4, pg_restore and indexes Greg Williamson <gwilliamson39@yahoo.com>
2011-10-24 23:33 ` raghu ram <raghuchennuru@gmail.com>
2011-10-25 00:41 ` Greg Williamson <gwilliamson39@yahoo.com>
2011-10-25 01:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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