Default value of serial fields changes after restore

Question

Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serial fields default values are trimmed out.

For example:

CREATE TABLE testschema.testtable
(
   id serial, 
   name character varying(255), 
   CONSTRAINT pk_testtable PRIMARY KEY (id)
) 
WITH (
  OIDS = FALSE
)
;


SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value
FROM pg_attribute AS a
JOIN pg_class AS c ON a.attrelid = c.oid
JOIN pg_namespace AS n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE a.attnum > 0 
  AND n.nspname = 'testschema' 
  AND c.relname = 'testtable' 

The id's default_value is nextval('testschema.testtable_id_seq'::regclass).

After restore, default_value changes to nextval('testtable_id_seq'::regclass) and INSERT's start to fail as the sequence can´t be found on it's schema.

Backup
$ pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup dbname

Restore
$ pg_restore -U postgres -h localhost -l backup > backup.list
$ pg_restore -U postgres -h localhost --disable-triggers -O -d dbname -S postgres -Fc -L backup.list backup

Is this some backup/restore problem? What am I doing wrong? BTW, PostgreSQL version is 9.1.3 x64 running on Windows (dev machine), but can be reproduced on Linux too.

Answer 1

I ran the same process as you did, but got different results. Restoring the database using your command didn't work when trying to overwrite the same database because you didn't use the -c --clean option. Restoring to a new database with the same pg_restore command worked. Inserting new records was still possible however.

Here are the steps I took.

Create the database

createdb testdatabase

Create the schema

psql -d testdatabase -c 'create schema testschema;'

Create the table

psql -d testdatabase

CREATE TABLE testschema.testtable(
  id serial, name character varying(255), 
  CONSTRAINT pk_testtable PRIMARY KEY (id)
) with (OIDS=false);

NOTICE: CREATE TABLE will create implicit sequence "testtable_id_seq" for serial column "testtable.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_testtable" for table "testtable" CREATE TABLE

Insert some records

testdatabase=# insert into testschema.testtable (name) values('person1'),('person2');
INSERT 0 2

Backup the database

pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup testdatabase

Create the backup list file

pg_restore -U postgres -h localhost -l backup > backup.list

Relevant contents of backup.list

2639; 1262 16468 DATABASE - testdatabase postgres 163; 1259 16472 TABLE testschema testtable postgres 162; 1259 16470 SEQUENCE testschema testtable_id_seq postgres 2643; 0 0 SEQUENCE OWNED BY testschema testtable_id_seq postgres 2644; 0 0 SEQUENCE SET testschema testtable_id_seq postgres 2633; 2604 16475 DEFAULT testschema id postgres 2636; 0 16472 TABLE DATA testschema testtable postgres 2635; 2606 16477 CONSTRAINT testschema pk_testtable postgres

Restore the backup

pg_restore -U postgres -h localhost --disable-triggers -O -d testdatabase -S postgres -Fc -L backup.list backup

pg_restore sends back errors

pg_restore: [archiver (db)] could not execute query: ERROR: schema "testschema" already exists pg_restore: [archiver (db)] Error from TOC entry 163; 1259 16472 TABLE testtable postgres pg_restore: [archiver (db)] could not execute query: ERROR: relation "testtable_id_seq" already exists

Restore to another database

createdb testdatabase2
pg_restore -U postgres -h localhost --disable-triggers -O -d testdatabase2 -S postgres -Fc -L backup.list backup

Did not get any errors

Insert more records

psql -d testdatabase2

testdatabase2=# insert into testschema.testtable (name) values('person2'),('person3');
INSERT 0 2

View records

testdatabase2=# select * from testschema.testtable;

 id |  name   
----+---------
  1 | person1
  2 | person2
  3 | person2
  4 | person3

View the table structure for testschema.testtable

pg_dump --schema-only -t testschema.testtable testdatabase2

As you mentioned in your question, postgresql does rewrite the default value

ALTER TABLE ONLY testtable ALTER COLUMN id SET DEFAULT nextval('testtable_id_seq'::regclass);

Conclusion

Using your exact same commands, errors did occur when trying to restore over the same database, but when restoring to a new database, adding new records was possible.

You must add the -c or --clean, to drop existing objects so that they can be restored by pg_restore.

pg_restore -c -U postgres -h localhost --disable-triggers -O -d testdatabase -S postgres -Fc -L backup.list backup

Postgresql did rewrite the default value for your serial column as you mentioned in your question, but insertions were still possible.

Updated Answer

When using the query you provided, it is true I don't see the testschema in the search path after restoring the database.

testdatabase=# SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid JOIN pg_namespace AS n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum WHERE a.attnum > 0   AND n.nspname = 'testschema'  AND c.relname = 'testtable';
 attnum |  nspname   |  relname  |             default_value             
--------+------------+-----------+---------------------------------------
      1 | testschema | testtable | nextval('testtable_id_seq'::regclass)
      2 | testschema | testtable | 

But, when I use \d testschema.testtable I do see the testschema in the definition of NEXTVAL

testdatabase=# \d testschema.testtable;
                                    Table "testschema.testtable"
 Column |          Type          |                             Modifiers                             
--------+------------------------+-------------------------------------------------------------------
 id     | integer                | not null default nextval('testschema.testtable_id_seq'::regclass)
 name   | character varying(255) |

I found this on Postgresql's forums, which talks about the differences we are both seeing.

http://archives.postgresql.org/pgsql-admin/2009-06/msg00070.php