Postgres restoration from pg_dumpall: relation does not exist, invalid command \N

I ran pg_dumpall on a Linux server And I'm attempting to restore the resulting file on another linux server, running the same version of postgres (8.1.21-1.el5_5.1).

When I try to restore I get the following errors:

# psql --user=postgres -f pgbackup_dcs.sql postgres
[...]
psql:pgbackup_dcs.sql:10753: ERROR:  relation "sl_event" does not exist
psql:pgbackup_dcs.sql:10754: invalid command \N
psql:pgbackup_dcs.sql:10755: invalid command \N
psql:pgbackup_dcs.sql:10756: invalid command \N
psql:pgbackup_dcs.sql:10757: invalid command \N
psql:pgbackup_dcs.sql:10758: invalid command \N
psql:pgbackup_dcs.sql:10759: invalid command \N
[there are a lot of other errors following this point]

These lines in the dumpfile look like this (first line here is 10753)

COPY sl_event (ev_origin, ev_seqno, ev_timestamp, ev_minxid, ev_maxxid, ev_xip, ev_type, ev_data1, ev_data2, ev_data3, ev_data4, ev_data5, ev_data6, ev_data7, ev_data8) FROM stdin;
1       687653  2010-12-08 11:54:45.669861      36713740        36713741                SYNC    \N      \N      \N      \N      \N      \N      \N      \N
1       687654  2010-12-08 11:54:55.6757        36713769        36713770                SYNC    \N      \N      \N      \N      \N      \N      \N      \N
1       687655  2010-12-08 11:55:05.68132       36713796        36713797                SYNC    \N      \N      \N      \N      \N      \N      \N      \N

Am I doing something wrong with either the dump or the restore? How can I restore the DBs on the new s开发者_JAVA百科erver?

Is there a CREATE TABLE sl_event(....) in the backup? You try to copy content into a table that doesn't exist, it must be missing in the backup or it's created after the copy statement.

useing pg_dump to dump a single database a time. I think dump a single a single database a time is good idea. We useall don't need to dump system db like template0,postgres..

After a lot of hunting around I found some extra library files in the postgres library directory that were from Slony (xxid.sl and a few files with "slony" in the name), and after copying those to the destination server the import proceeded fine.

Is it possible you have a data-only backup? I.e. a pg_dumpall -a will decline to create your tables and will result in something like this. Beyond that my guess would be that if that is not the case, then you have an upstream error that is causing this,.

i found this fix and it worked

First drop/delete the database you want to restore too completely

su - postgres
createdb -T template0 dbname
psql -U username -d dbname -f dumpfile

The reason is that when that pg_dump creates a .dmp files relative to template0 which might be different from the the one used to create the database http://openacs.org/forums/message-view?message_id=148479