Menu
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?
821
Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. New!
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). pg_dump only dumps a single database.
The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump. Normally that is no big problem unless you have very high write activity in the database.
If you use the --clean option of pg_restore , the old tables will be dropped before the new ones are created. If you do not use the --clean option, you will get an error message that the table already exists, but pg_restore will continue processing unless you use the --exit-on-error option.
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
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
