Thread: Sequences not created, bug in pg_dump?

Sequences not created, bug in pg_dump?

From

"Leonardo M. Ramé"

Date:

19 March 2015, 18:43:23

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

Re: Sequences not created, bug in pg_dump?

From

Adrian Klaver

Date:

19 March 2015, 19:09:37

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

Re: Sequences not created, bug in pg_dump?

From

"Leonardo M. Ramé"

Date:

19 March 2015, 20:02:23

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.

Re: Sequences not created, bug in pg_dump?

From

Adrian Klaver

Date:

19 March 2015, 20:14:12

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

Re: Sequences not created, bug in pg_dump?

From

"Leonardo M. Ramé"

Date:

20 March 2015, 20:22:06


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.

Re: Sequences not created, bug in pg_dump?

From

Adrian Klaver

Date:

20 March 2015, 22:13:50

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