From: | "Jeffrey Baker" <jwbaker(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | pg_dump fails to include sequences, leads to restore fail in any version |
Date: | 2008-06-17 16:53:13 |
Message-ID: | fd145f7d0806170953n380b6cadvfe14d6ca384da647@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a
database which contains sequences in any of 8.1, 8.2, or 8.3:
[...]
--
-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
mercado; Owner: prod
--
SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
'transaction_id'), 6736138, true);
--
-- Name: transaction; Type: TABLE; Schema: mercado; Owner: prod; Tablespace:
--
CREATE TABLE "transaction" (
transaction_id integer DEFAULT
nextval('transaction_transaction_id_seq'::regclass) NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);
[...]
2008-06-16 19:26:41 PDT ERROR: relation "transaction_transaction_id_seq"
does not exist
Why? Because pg_dump mysteriously omits all sequences:
think=# \d transaction_transaction_id_seq
Sequence "mercado.transaction_transaction_id_seq"
Column | Type
---------------+---------
sequence_name | name
last_value | bigint
increment_by | bigint
max_value | bigint
min_value | bigint
cache_value | bigint
log_cnt | bigint
is_cycled | boolean
is_called | boolean
think=# \ds
List of relations
Schema | Name | Type | Owner
---------+------------------------------------+----------+-------
mercado | account_account_id_seq | sequence | prod
mercado | account_stat_account_stat_id_seq | sequence | prod
mercado | category_category_id_seq | sequence | prod
mercado | category_stat_category_stat_id_seq | sequence | prod
mercado | country_country_id_seq | sequence | prod
mercado | country_stat_country_stat_id_seq | sequence | prod
mercado | dict_dict_id_seq | sequence | prod
mercado | expire_icon_expire_icon_id_seq | sequence | prod
mercado | expire_time_expire_time_id_seq | sequence | prod
mercado | fx_fx_id_seq | sequence | prod
mercado | icon_icon_id_seq | sequence | prod
mercado | item_icon_item_icon_id_seq | sequence | prod
mercado | item_item_id_seq | sequence | prod
mercado | item_stat_item_stat_id_seq | sequence | prod
mercado | transaction_transaction_id_seq | sequence | prod
(15 rows)
postgres(at)think:~$ pg_dump -s -n mercado think | grep CREATE\ SEQUENCE
postgres(at)think:~$
Therefore when the restore is attempted, the table using the sequence as
default value cannot be created.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-06-17 17:29:56 | Cleaning up cross-type arithmetic operators |
Previous Message | Kris Jurka | 2008-06-17 16:25:09 | Re: sh -> pl |