Default value of serial fields changes after restore 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 &gt; 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 &gt; 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.
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 &gt; 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