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 > 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.

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/msg0007

StackExchange Database Administrators Q#21150, answer score: 4