pg_dump序列设置 [英] pg_dump setting of sequences

问题描述

我最近开始使用PostgreSQL作为后端数据库（强加给我）开发应用程序，而以前没有Postgres的经验。到目前为止，还算不错，但是现在我遇到了一个我找不到答案的问题。

I've recently started developing apps with PostgreSQL as backend DB (imposed on me) with no previous experience of Postgres. So far it hasn't been too bad, but now I run into a problem to which I cannot find answer for.

我创建了一个运行pg_dump命令的批处理脚本。服务器上的特定数据库。该批处理文件由pgAgent按计划执行。

I created a batch scripts that runs a pg_dump command for a particular database on the server. This batch file is executed on schedule by the pgAgent.

pg_dump本身似乎可以正常工作。所有数据库结构和数据都转储到文件中。但是，序列都设置为1。例如，对于表 tbl_departments ，序列转储如下所示：

The pg_dump itself seems to work ok. All the database structure and data are dumped to a file. However the sequences are all set to 1. For example for table tbl_departments the sequence dump looks like this:

CREATE SEQUENCE "tbl_departments_iID_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE "tbl_departments_iID_seq" OWNER TO postgres;

ALTER SEQUENCE "tbl_departments_iID_seq" OWNED BY tbl_departments."iID";

在此特定示例中，应将序列设置为以8开头，因为最后插入的记录具有iID = 7。

In this particular example the sequence should be set to start with 8, since the last inserted record has iID = 7.

如何使pg_dump设置每个表的下一个可用序列号？

How do I make the pg_dump set the sequence starting number the next one available for each table?

转储命令是：
％PGBIN％pg_dump -h 192.168.0.112 -U postgres -F p -b -v --inserts -f \\192.168.0.58\PostgresDB\backup ternalinternals_db.sql内部构件

The command for dump is: %PGBIN%pg_dump -h 192.168.0.112 -U postgres -F p -b -v --inserts -f "\\192.168.0.58\PostgresDB\backup\internals_db.sql" Internals

编辑：

我想我有找到了问题，尽管我仍然不知道该如何解决：
如果我打开pgAdmin并为tbl_departments生成CREATE脚本，它看起来像这样：

I think I have found the issue, although I still don't know how to resolve this: If I open pgAdmin and generate CREATE script for tbl_departments, it look like this:

CREATE TABLE tbl_departments
(
  "iID" serial NOT NULL, -- id, autoincrement
  "c150Name" character varying(150) NOT NULL, -- human readable name for department
  "bRetired" boolean NOT NULL DEFAULT false, -- if TRUE that it is no longer active
  "iParentDept" integer NOT NULL DEFAULT 0, -- ID of the parent department
  CONSTRAINT tbl_departments_pkey PRIMARY KEY ("iID")
)

pg_dump语句为：

The pg_dump statement is:

    CREATE TABLE tbl_departments (
        "iID" integer NOT NULL,
        "c150Name" character varying(150) NOT NULL,
        "bRetired" boolean DEFAULT false NOT NULL,
        "iParentDept" integer DEFAULT 0 NOT NULL
    );


    ALTER TABLE tbl_departments OWNER TO postgres;

    COMMENT ON TABLE tbl_departments IS 'list of departments';

    COMMENT ON COLUMN tbl_departments."iID" IS 'id, autoincrement';

    COMMENT ON COLUMN tbl_departments."c150Name" IS 'human readable name for department';


    COMMENT ON COLUMN tbl_departments."bRetired" IS 'if TRUE that it is no longer active';

    COMMENT ON COLUMN tbl_departments."iParentDept" IS 'ID of the parent department';

    CREATE SEQUENCE "tbl_departments_iID_seq"
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

        ALTER TABLE "tbl_departments_iID_seq" OWNER TO postgres;

        ALTER SEQUENCE "tbl_departments_iID_seq" OWNED BY tbl_departments."iID";

    INSERT INTO tbl_departments VALUES (1, 'Information Technologies', false, 0);
    INSERT INTO tbl_departments VALUES (2, 'Quality Control', false, 0);
    INSERT INTO tbl_departments VALUES (3, 'Engineering', false, 0);
    INSERT INTO tbl_departments VALUES (5, 'Quality Assurance', false, 0);
    INSERT INTO tbl_departments VALUES (6, 'Production', false, 2);

    ALTER TABLE ONLY tbl_departments
        ADD CONSTRAINT tbl_departments_pkey PRIMARY KEY ("iID");

SELECT pg_catalog.setval('"tbl_departments_iID_seq"', 1, false);

pg_dump将iID列设置为整数而不是串行，这会禁用自动增量。 setval也设置为1，而不是人们期望的7。

the pg_dump sets the iID column to integer rather than serial, which disabled the auto-incrementation. The setval is also set to 1 rather than 7 as one would expect.

当我打开前端应用程序并添加新部门时，它失败了，因为我是提供的信息是：新部门的名称，有效/无效（正确/错误），父部门的ID。 （如果没有父母，则为0）。

When I open the front-end application and go to add new department it fails because all I am providing is: name of new department, active/disabled (true/false), ID of parent dept. (0 if no parent).

我期望数据库自动创建新记录的主键iID，据我所知这是一个预期的基本

I am expecting for the new record primary key iID to be created automatically by the DB, which as far as I know is an expected basic feature of any RDBMS.

，因为pg_dump将序列转换为自动递增停止的整数。

because the pg_dump converts the serials to integers the auto-incrementation stops.

推荐答案

没有理由担心。
生成的SQL文件将恢复序列的当前值。
使用编辑器打开文件，然后查找 setval 。
应该有这样的行：

There is no reason for concern. The generated SQL file will restore current values of sequences. Open the file with an editor and look for setval. There should be lines like this:

SELECT pg_catalog.setval('test_id_seq', 1234, true);

如果找不到它们，则意味着INSERT命令设置了序列的正确值。

If you cannot find them it means that INSERT commands set the proper value of a sequence.

正如Craig所注意到的，在转储原始数据库时，序列的当前值必须等于1。您可能直接插入了 iID 值，而不使用了 default 。在这种情况下，不使用序列。

As Craig noticed, the current value of the sequence had to be equal to 1 at the time of dump of the original database. You have probably inserted iID values directly, not using default. In that case the sequence is not used.

因此，我建议从头开始，但是在两个数据库中：

Therefore I suggest start from the beginning, but in two databases:

• 像问题中那样进行sql转储，


• 创建新数据库，


• 在以下位置运行sql脚本在新数据库中，


• 检查两个数据库中相应的序列列是否具有相同的声明，


• 比较两个数据库中相应序列的当前值。

• make an sql dump like in the question,
• create a new database,
• run the sql script in the new database,
• check whether corresponding serial columns have the same declaration in both databases,
• compare current values of corresponding sequences in both databases.

这篇关于pg_dump序列设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！