Re: Problem with dump importing. - Mailing list pgsql-general

On Tuesday 15 December 2009 8:34:09 am NetLevel Admins wrote:
> Hello,
>
> I created database dump on server version 8.3.7 and trying to import it to
> server v. 8.1.18, but i see errors.
>
> CREATE DOMAIN
>
> > dump.sql:20: ERROR: role "userarm" does not exist
> > dump.sql:26: ERROR: type "pg_catalog.gtsvector" does not exist
> > dump.sql:29: ERROR: role "userarm" does not exist
> > CREATE TYPE
> > dump.sql:42: ERROR: role "userarm" does not exist
> > CREATE TYPE
> > dump.sql:54: ERROR: role "userarm" does not exist
> > CREATE TYPE
> > dump.sql:67: ERROR: role "userarm" does not exist
> > dump.sql:80: ERROR: type "pg_catalog.tsvector" does not exist
> > dump.sql:83: ERROR: role "userarm" does not exist
> > dump.sql:89: ERROR: type "pg_catalog.tsquery" does not exist
> > dump.sql:92: ERROR: role "userarm" does not exist
> > dump.sql:98: ERROR: type "pg_catalog.tsvector" does not exist
> > dump.sql:101: ERROR: role "userarm" does not exist
> > CREATE DOMAIN
> > dump.sql:110: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:121: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:132: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:143: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:154: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:165: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:176: ERROR: role "userarm" does not exist
> > CREATE FUNCTION
> > dump.sql:187: ERROR: role "userarm" does not exist
> > dump.sql:213: ERROR: type "tsdebug" does not exist
> > dump.sql:216: ERROR: role "userarm" does not exist
> > CREATE OPERATOR
> > dump.sql:233: ERROR: role "userarm" does not exist
> > CREATE OPERATOR
> > dump.sql:250: ERROR: role "userarm" does not exist
> > CREATE OPERATOR
> > dump.sql:267: ERROR: role "userarm" does not exist
> > CREATE OPERATOR
> > dump.sql:286: ERROR: role "userarm" does not exist
> > CREATE OPERATOR
> > dump.sql:303: ERROR: role "userarm" does not exist
> > CREATE OPERATOR
> > dump.sql:320: ERROR: role "userarm" does not exist
> > SET
> > SET
> > CREATE TABLE
> > dump.sql:339: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:356: ERROR: role "userarm" does not exist
> > SET
> > CREATE TABLE
> > dump.sql:377: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:390: ERROR: role "userarm" does not exist
> > setval
> > 126
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:415: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:428: ERROR: role "userarm" does not exist
> > setval
> > 23
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:451: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:466: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:485: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:521: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:534: ERROR: role "userarm" does not exist
> > setval
> > 168
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:575: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:588: ERROR: role "userarm" does not exist
> > setval
> > 308
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:618: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:639: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:653: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:666: ERROR: role "userarm" does not exist
> > setval
> > 53
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:691: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:705: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:721: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:735: ERROR: role "userarm" does not exist
> > setval
> > 1
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:754: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:767: ERROR: role "userarm" does not exist
> > setval
> > 29
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:795: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:808: ERROR: role "userarm" does not exist
> > setval
> > 9
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:838: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:851: ERROR: role "userarm" does not exist
> > setval
> > 66
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:872: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:885: ERROR: role "userarm" does not exist
> > setval
> > 52
> >
> >
> > 1 запис(ь/и/ей) dump.sql:971: ERROR: unterminated quoted string at or
> > near "'
> > adp_always smallint DEFAULT 1 NOT NULL,
> >
> > adp_days integer DEFAULT 1 NOT NULL,
> >
> > adp_hours integer DEFAULT 0 NOT NULL,
> >
> > adp_mins integer DEFAULT 0 NOT NULL,
> >
> > adp_secs integer DEFAULT 0 NOT NULL,
> >
> > CONSTRAINT phpbb_forums_display_on_index_check CHECK ((display_on_index
> > >= 0)),
> >
> > CONSTRAINT phpbb_forums_display_subforum_list_check CHECK
> > ((display_subforum_list >= 0)),
> >
> > CONSTRAINT phpbb_forums_enable_icons_check CHECK ((enable_icons >= 0)),
> >
> > CONSTRAINT phpbb_forums_enable_indexing_check CHECK ((enable_indexing >=
> > 0)),
> >
> > CONSTRAINT phpbb_forums_enable_prune_check CHECK ((enable_prune >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_desc_options_check CHECK
> > ((forum_desc_options
> >
> > >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_last_post_id_check CHECK
> > ((forum_last_post_id
> >
> > >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_last_post_time_check CHECK
> > ((forum_last_post_time >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_last_poster_id_check CHECK
> > ((forum_last_poster_id >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_posts_check CHECK ((forum_posts >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_rules_options_check CHECK
> > ((forum_rules_options >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_style_check CHECK ((forum_style >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_topics_check CHECK ((forum_topics >= 0)),
> >
> > CONSTRAINT phpbb_forums_forum_topics_real_check CHECK ((forum_topics_real
> >
> > >= 0)),
> >
> > CONSTRAINT phpbb_forums_left_id_check CHECK ((left_id >= 0)),
> >
> > CONSTRAINT phpbb_forums_parent_id_check CHECK ((parent_id >= 0)),
> >
> > CONSTRAINT phpbb_forums_prune_days_check CHECK ((prune_days >= 0)),
> >
> > CONSTRAINT phpbb_forums_prune_freq_check CHECK ((prune_freq >= 0)),
> >
> > CONSTRAINT phpbb_forums_prune_next_check CHECK ((prune_next >= 0)),
> >
> > CONSTRAINT phpbb_forums_prune_viewed_check CHECK ((prune_viewed >= 0)),
> >
> > CONSTRAINT phpbb_forums_right_id_check CHECK ((right_id >= 0))
> >
> > );" at character 2885
> > dump.sql:974: ERROR: relation "public.phpbb_forums" does not exist
> > CREATE TABLE
> > dump.sql:989: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1005: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1021: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1034: ERROR: role "userarm" does not exist
> > setval
> > 18
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1082: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1095: ERROR: role "userarm" does not exist
> > setval
> > 10
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1120: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1133: ERROR: role "userarm" does not exist
> > setval
> > 4
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:1156: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1169: ERROR: role "userarm" does not exist
> > setval
> > 13694
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1201: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1214: ERROR: role "userarm" does not exist
> > setval
> > 9
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:1242: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1255: ERROR: role "userarm" does not exist
> > setval
> > 72
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1284: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1297: ERROR: role "userarm" does not exist
> > setval
> > 3
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1317: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1337: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1350: ERROR: role "userarm" does not exist
> > setval
> > 198
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1383: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1399: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1415: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1428: ERROR: role "userarm" does not exist
> > setval
> > 48215
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:1488: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1501: ERROR: role "userarm" does not exist
> > setval
> > 6464
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1551: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1564: ERROR: role "userarm" does not exist
> > setval
> > 1
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1587: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1600: ERROR: role "userarm" does not exist
> > setval
> > 3
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:1632: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1661: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1675: ERROR: role "userarm" does not exist
> > setval
> > 1
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1716: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1728: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1746: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1763: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1776: ERROR: role "userarm" does not exist
> > setval
> > 23
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1800: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1813: ERROR: role "userarm" does not exist
> > setval
> > 226
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:1844: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1857: ERROR: role "userarm" does not exist
> > setval
> > 6
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1879: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1892: ERROR: role "userarm" does not exist
> > setval
> > 12192
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:1917: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1932: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:1945: ERROR: role "userarm" does not exist
> > setval
> > 112345
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:1968: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:1984: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:2015: ERROR: role "userarm" does not exist
> > CREATE TABLE
> > dump.sql:2031: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:2045: ERROR: role "userarm" does not exist
> > setval
> > 1
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:2067: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:2080: ERROR: role "userarm" does not exist
> > setval
> > 94
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:2109: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:2122: ERROR: role "userarm" does not exist
> > setval
> > 12
> >
> >
> > 1 запис(ь/и/ей)
> >
> > CREATE TABLE
> > dump.sql:2150: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:2163: ERROR: role "userarm" does not exist
> > setval
> > 7
> >
> >
> > 1 запис(ь/и/ей) CREATE TABLE
> > dump.sql:2184: ERROR: role "userarm" does not exist
> > CREATE SEQUENCE
> > dump.sql:2197: ERROR: role "userarm" does not exist
> > setval
> > 591
> >
> >
> > 1 запис(ь/и/ей)
>
> What could be a problem and is there a solution?

Two problems. One, dumping backwards is problematic. See:

http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html

"Because pg_dump is used to transfer data to newer versions of PostgreSQL, the
output of pg_dump can be expected to load into PostgreSQL server versions newer
than pg_dump's version. pg_dump can also dump from PostgreSQL servers older
than its own version. (Currently, servers back to version 7.0 are supported.)
However, pg_dump cannot dump from PostgreSQL servers newer than its own major
version; it will refuse to even try, rather than risk making an invalid dump.
Also, it is not guaranteed that pg_dump's output can be loaded into a server of
an older major version — not even if the dump was taken from a server of that
version. Loading a dump file into an older server may require manual editing of
the dump file to remove syntax not understood by the older server."

Two, the role error messages are because you did not dump the roles which are
global to a database cluster and not tied to a specific database. To do so you
need to do something along the lines of:

pg_dumpall -g

See here for more detail

http://www.postgresql.org/docs/8.4/interactive/app-pg-dumpall.html

--
Adrian Klaver
aklaver@comcast.net