Обсуждение: Sequences not created, bug in pg_dump?
Hi, I'm creating a database dump excluding one table and found only the sequences created implicitly (using serial type) are created when I restore the dump. The command I use is: pg_dump -T table_to_be_excluded mydb I understand all related objects to the table to be excluded are not dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. -- Leonardo M. Ramé http://leonardorame.blogspot.com
On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: > > Hi, I'm creating a database dump excluding one table and found only the > sequences created implicitly (using serial type) are created when I > restore the dump. > > The command I use is: pg_dump -T table_to_be_excluded mydb > > I understand all related objects to the table to be excluded are not > dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. > What version of Postgres? If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they do not show up in the dump file? It works for me here on 9.3. -- Adrian Klaver adrian.klaver@aklaver.com
El 19/03/15 a las 13:09, Adrian Klaver escibió: > On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: >> >> Hi, I'm creating a database dump excluding one table and found only the >> sequences created implicitly (using serial type) are created when I >> restore the dump. >> >> The command I use is: pg_dump -T table_to_be_excluded mydb >> >> I understand all related objects to the table to be excluded are not >> dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. >> > > What version of Postgres? > > If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they > do not show up in the dump file? > > It works for me here on 9.3. > Yes that's the problem. The dump is performed using 9.3.5 on windows.
On 03/19/2015 10:02 AM, "Leonardo M. Ramé" wrote:
>
> El 19/03/15 a las 13:09, Adrian Klaver escibió:
>> On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote:
>>>
>>> Hi, I'm creating a database dump excluding one table and found only the
>>> sequences created implicitly (using serial type) are created when I
>>> restore the dump.
>>>
>>> The command I use is: pg_dump -T table_to_be_excluded mydb
>>>
>>> I understand all related objects to the table to be excluded are not
>>> dumpled, but why I don't get any CREATE SEQUENCE command in my dump?.
>>>
>>
>> What version of Postgres?
>>
>> If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they
>> do not show up in the dump file?
>>
>> It works for me here on 9.3.
>>
>
> Yes that's the problem. The dump is performed using 9.3.5 on windows.
I can not replicate:
aklaver@test=> create sequence test_seq;
aklaver@test=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+----------
public | CamelCap_Quoted | table | aklaver
public | app_sessions | table | aklaver
public | app_users | table | aklaver
public | app_users_vw | view | aklaver
public | app_val_session_vw | view | aklaver
public | camelcap_not_quoted | table | aklaver
public | float_test | table | postgres
public | ins_test | table | aklaver
public | mytable_is_not_readonly | table | aklaver
public | mytable_is_readonly | table | aklaver
public | on_duty | table | aklaver
public | on_duty_id_seq | sequence | aklaver
public | seq_counter | table | aklaver
public | t | table | postgres
public | t_id_seq | sequence | postgres
public | tasks | table | aklaver
public | tasks2 | table | aklaver
public | tasks_task_id_seq | sequence | aklaver
public | tbl_test | table | aklaver
public | test_seq | sequence | aklaver
/usr/local/pgsql93/bin/pg_dump -T app_sessions -U postgres -p 5452 test
> test_txt.sql
In test_txt.sql:
--
-- Name: test_seq; Type: SEQUENCE; Schema: public; Owner: aklaver
--
CREATE SEQUENCE test_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
Do you see any warnings/errors when you run the dump?
--
Adrian Klaver
adrian.klaver@aklaver.com
El 19/03/15 a las 14:13, Adrian Klaver escibió: > On 03/19/2015 10:02 AM, "Leonardo M. Ramé" wrote: >> >> El 19/03/15 a las 13:09, Adrian Klaver escibió: >>> On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: >>>> >>>> Hi, I'm creating a database dump excluding one table and found only the >>>> sequences created implicitly (using serial type) are created when I >>>> restore the dump. >>>> >>>> The command I use is: pg_dump -T table_to_be_excluded mydb >>>> >>>> I understand all related objects to the table to be excluded are not >>>> dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. >>>> >>> >>> What version of Postgres? >>> >>> If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they >>> do not show up in the dump file? >>> >>> It works for me here on 9.3. >>> >> >> Yes that's the problem. The dump is performed using 9.3.5 on windows. > > I can not replicate: > > aklaver@test=> create sequence test_seq; > > aklaver@test=> \d > List of relations > Schema | Name | Type | Owner > --------+-------------------------+----------+---------- > public | CamelCap_Quoted | table | aklaver > public | app_sessions | table | aklaver > public | app_users | table | aklaver > public | app_users_vw | view | aklaver > public | app_val_session_vw | view | aklaver > public | camelcap_not_quoted | table | aklaver > public | float_test | table | postgres > public | ins_test | table | aklaver > public | mytable_is_not_readonly | table | aklaver > public | mytable_is_readonly | table | aklaver > public | on_duty | table | aklaver > public | on_duty_id_seq | sequence | aklaver > public | seq_counter | table | aklaver > public | t | table | postgres > public | t_id_seq | sequence | postgres > public | tasks | table | aklaver > public | tasks2 | table | aklaver > public | tasks_task_id_seq | sequence | aklaver > public | tbl_test | table | aklaver > public | test_seq | sequence | aklaver > > > /usr/local/pgsql93/bin/pg_dump -T app_sessions -U postgres -p 5452 test > > test_txt.sql > > > In test_txt.sql: > > -- > -- Name: test_seq; Type: SEQUENCE; Schema: public; Owner: aklaver > -- > > CREATE SEQUENCE test_seq > START WITH 1 > INCREMENT BY 1 > NO MINVALUE > NO MAXVALUE > CACHE 1; > > Do you see any warnings/errors when you run the dump? > Sorry for answering too late, but the problem was solved by using pg_dump command line instead of doing the backup from pgAdmin. So I should change the subject of this thread, s/in pg_dump/in pg_admin/g :) Regards, Leonardo.
On 03/20/2015 10:21 AM, "Leonardo M. Ramé" wrote: > > > El 19/03/15 a las 14:13, Adrian Klaver escibió: >> On 03/19/2015 10:02 AM, "Leonardo M. Ramé" wrote: >>> >>> El 19/03/15 a las 13:09, Adrian Klaver escibió: >>>> On 03/19/2015 08:43 AM, "Leonardo M. Ramé" wrote: >>>>> >>>>> Hi, I'm creating a database dump excluding one table and found only >>>>> the >>>>> sequences created implicitly (using serial type) are created when I >>>>> restore the dump. >>>>> >>>>> The command I use is: pg_dump -T table_to_be_excluded mydb >>>>> >>>>> I understand all related objects to the table to be excluded are not >>>>> dumpled, but why I don't get any CREATE SEQUENCE command in my dump?. >>>>> >>>> >>>> What version of Postgres? >>>> >>>> If I follow you have CREATEd sequence(s) using CREATE SEQUENCE and they >>>> do not show up in the dump file? >>>> >>>> It works for me here on 9.3. >>>> >>> >>> Yes that's the problem. The dump is performed using 9.3.5 on windows. >> >> I can not replicate: >> >> aklaver@test=> create sequence test_seq; >> >> aklaver@test=> \d >> List of relations >> Schema | Name | Type | Owner >> --------+-------------------------+----------+---------- >> public | CamelCap_Quoted | table | aklaver >> public | app_sessions | table | aklaver >> public | app_users | table | aklaver >> public | app_users_vw | view | aklaver >> public | app_val_session_vw | view | aklaver >> public | camelcap_not_quoted | table | aklaver >> public | float_test | table | postgres >> public | ins_test | table | aklaver >> public | mytable_is_not_readonly | table | aklaver >> public | mytable_is_readonly | table | aklaver >> public | on_duty | table | aklaver >> public | on_duty_id_seq | sequence | aklaver >> public | seq_counter | table | aklaver >> public | t | table | postgres >> public | t_id_seq | sequence | postgres >> public | tasks | table | aklaver >> public | tasks2 | table | aklaver >> public | tasks_task_id_seq | sequence | aklaver >> public | tbl_test | table | aklaver >> public | test_seq | sequence | aklaver >> >> >> /usr/local/pgsql93/bin/pg_dump -T app_sessions -U postgres -p 5452 test >> > test_txt.sql >> >> >> In test_txt.sql: >> >> -- >> -- Name: test_seq; Type: SEQUENCE; Schema: public; Owner: aklaver >> -- >> >> CREATE SEQUENCE test_seq >> START WITH 1 >> INCREMENT BY 1 >> NO MINVALUE >> NO MAXVALUE >> CACHE 1; >> >> Do you see any warnings/errors when you run the dump? >> > > Sorry for answering too late, but the problem was solved by using > pg_dump command line instead of doing the backup from pgAdmin. So I > should change the subject of this thread, s/in pg_dump/in pg_admin/g :) Might want to bring this up here: http://www.postgresql.org/list/pgadmin-support/ > > Regards, > Leonardo. > > -- Adrian Klaver adrian.klaver@aklaver.com