pg_dump ignoring table sequence?

Question

I have been playing around with PostgreSQL lately, and am having trouble understanding how to backup and restore a single table.

I used pgadmin3 to backup a single table in my database, in order to copy it to a different server. When I try to do a pg_restore on the file, I get error messages saying that the sequence does not exist:

pg_restore: [archiver (db)] could not execute query: ERROR:  relation "businesses_id_seq" does not exist
    Command was: 
CREATE TABLE businesses (
    id integer DEFAULT nextval('businesses_id_seq'::regclass) NOT NULL,
    name character varyin...

It looks like the dump file did not include the sequence for my auto incrementing column. How do I get it to include that?

Solution

dumping by table only - will dump only the table. You need to dump the sequence separately in addition to the table.

If you dont know your sequence you can list it with \d yourtable in psql. You will see something in the row your sequence is on that looks like : nextval('yourtable_id_seq'::regclass')

Then from the command line, pgdump -t yourtable_id_seq

http://www.postgresql.org/docs/9.0/static/app-pgdump.html

Comments

[demersus]

Do you know if I can specify a comma after th -t flag? Like this: pgdump -t mytable, mytabl_id_seq

[Phil Hord]

No, but you can specify multiple -t switches. Like this: pgdump -t mytable -t mytabl_id_seq