Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2015
    Posts
    4

    Unanswered: PostgreSQL database backup

    Hello all,

    I am very new to PostgreSQL, working with MySQL and MSSQL in the past.

    I did managed to get my head around most of it for now, but I am having some issues with the backup and restore.

    I am executing a batch script that does a pg_dump of a database to a network location. The script completes with no problems, and the test restore was also successfull - or so I though.

    When I went to test the front-end app after restore everything crashed, and here is what I managed to find out.

    My pg_dump script is:

    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

    the sample extract from the dump file for table departments looks like this:
    Code:
    CREATE TABLE 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 departments OWNER TO postgres;
    
    COMMENT ON TABLE departments IS 'list of departments';
    
    COMMENT ON COLUMN departments ."iID" IS 'id, autoincrement';
    
    COMMENT ON COLUMN departments ."c150Name" IS 'human readable name for department';
    
    COMMENT ON COLUMN departments ."bRetired" IS 'if TRUE that it is no longer active';
    
    COMMENT ON COLUMN departments ."iParentDept" IS 'ID of the parent department';
    
    CREATE SEQUENCE "departments_iID_seq"
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;
    
    ALTER TABLE "departments_iID_seq" OWNER TO postgres;
    
    ALTER SEQUENCE "departments_iID_seq" OWNED BY departments."iID";
    
    INSERT INTO departments VALUES (1, 'Information Technologies', false, 0);
    INSERT INTO departments VALUES (2, 'Quality Control', false, 0);
    INSERT INTO departments VALUES (3, 'Engineering', false, 0);
    INSERT INTO departments VALUES (5, 'Quality Assurance', false, 0);
    INSERT INTO departments VALUES (6, 'Production', false, 2);
    
    
    SELECT pg_catalog.setval('"departments_iID_seq"', 1, false);
    
    ALTER TABLE ONLY departments 
       ADD CONSTRAINT departments_pkey PRIMARY KEY ("iID");
    Here is the creation script I get when I run pgAdmin CREAT script on that table:
    Code:
    CREATE TABLE 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 departments_pkey PRIMARY KEY ("iID")
    )
    As you can see the pg_dump script sets the iID type to integer not serial.
    If I go to front-end app and try to create new department, it fails because the insert query only provides name, active state (t/f), and ID of parent department. I am expecting for the DB to create a new autoincremented primary key ID (which as far as I know is or should be a basic feature of any RDBMS). PostgreSQL will autoincrement only is type is set to serial.

    This happens to every single table that has field with the type set to serial, and although it does restore the database structure and data it renders it pretty much useless.

    Does anyone knows how to resolve this?

    Any help is greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I have done a lot of (SQL) dumps and have never ever seen the setval() being incorrectly exported in the generated script.

    The only way that I can think of, how this has happened is that you inserted values into that column manually without letting the sequence do its job. If you supply a value for the id column then Postgres will store that and will not increment the sequence. Postgres will only increment the sequence if you let it do that - usually by not supplying the column in the insert statement or by using the DEFAULT keyword.

    So if you run
    Code:
    insert into departments (id, name, retired) values (1, 'foo', false);
    then you are bypassing the automatic sequence usage and you are telling Postgres "I know what I do, please don't use the sequence". This is equivalent to use "set identity_insert on" in SQL Server.

    If you want to take advantage of the automatic number generation use:
    Code:
    insert into departments (name, retired) values ('foo', false);
    or
    Code:
    insert into departments (id, name, retired) values (default, 'foo', false);
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Oct 2015
    Posts
    4
    Ok, I understand. In fact my dump is executed with --inserts option.

    However, if I ignore the ID's than I will run into data problems.

    Example scenario would be:

    Table_1 {ID,Name}

    ------------
    | 1 | aaa |
    ------------
    | 2 | bbb |
    ------------
    | 3 | ccc |
    ------------
    | 4 | ddd |
    ------------
    | 5 | eee |

    Table_2 {ID,value,tb1_id}

    -----------------------
    | 1 | somevalue | 1 |
    -----------------------
    | 2 | somevalue | 1 |
    -----------------------
    | 3 | somevalue | 4 |
    -----------------------
    | 4 | somevalue | 4 |
    -----------------------
    | 5 | somevalue | 5 |



    If I was to delete record with ID=4 from table_1, than on restore with automatic ID inserts, record with ID=5 would than receive ID=4. This in turn would cause the data in Table_2 to be incorect (tb1_id).


    How do I handle the backup and restore in order to preserver the ID and data integrity while making the automatic inserts work?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by psuplat View Post
    How do I handle the backup and restore in order to preserver the ID and data integrity while making the automatic inserts work?
    Your problem is not the backup and restore. Your problem is the way you inserted the rows in the first place.

    pg_dump only dumps the content of the database - and that is inconsistent in your case.

    You need to adjust the sequence before running pg_dump
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •