Thread: More on migragting the server.

More on migragting the server.

From

David Siebert

Date:

26 September 2007, 15:47:34

I set up a test server using the latest 8.2 as suggest by the list and
did pg_dump of the old data base.
I created a new empty database with the same name an created a user with
the same name as was on the old server.
I then tried to do a restore using webmin just as a test and got errors.
I am seeing some reference to pgadmin.. I did use pgadminII to manage
the data base.
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 1255 18860 FUNCTION
pgadmin_get_rows(oid) phone
pg_restore: [archiver (db)] could not execute query: ERROR:  type
"pgadmin_table_cache" does not exist
    Command was: CREATE FUNCTION pgadmin_get_rows(oid) RETURNS
pgadmin_table_cache
    AS $_$SELECT DISTINCT ON(table_oid) * FROM pgadmin_tab...
pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.pgadmin_get_rows(oid) does not exist
    Command was: ALTER FUNCTION public.pgadmin_get_rows(oid) OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 9; 1255 18861 FUNCTION
pgadmin_get_sequence(oid) phone
pg_restore: [archiver (db)] could not execute query: ERROR:  type
"pgadmin_seq_cache" does not exist
    Command was: CREATE FUNCTION pgadmin_get_sequence(oid) RETURNS
pgadmin_seq_cache
    AS $_$SELECT DISTINCT ON(sequence_oid) * FROM pgadmi...
pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.pgadmin_get_sequence(oid) does not exist
    Command was: ALTER FUNCTION public.pgadmin_get_sequence(oid) OWNER
TO phone;
pg_restore: [archiver (db)] Error from TOC entry 837; 1259 18862 VIEW
pgadmin_databases phone
pg_restore: [archiver (db)] could not execute query: ERROR:  column
d.datpath does not exist
LINE 2: ....oid AS database_oid, d.datname AS database_name, d.datpath ...
                                                             ^
    Command was: CREATE VIEW pgadmin_databases AS
    SELECT d.oid AS database_oid, d.datname AS database_name, d.datpath
AS database_path, p...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_databases" does not exist
    Command was: ALTER TABLE public.pgadmin_databases OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 838; 1259 18877 VIEW
pgadmin_checks phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pg_relcheck" does not exist
    Command was: CREATE VIEW pgadmin_checks AS
    SELECT r.oid AS check_oid, r.rcname AS check_name, c.oid AS
check_table_oid, c.relname AS ...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_checks" does not exist
    Command was: ALTER TABLE public.pgadmin_checks OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 840; 1259 18914 VIEW
pgadmin_groups phone
pg_restore: [archiver (db)] could not execute query: ERROR:  column
pg_group.oid does not exist
LINE 2:     SELECT pg_group.oid AS group_oid, pg_group.groname AS gr...
                   ^
    Command was: CREATE VIEW pgadmin_groups AS
    SELECT pg_group.oid AS group_oid, pg_group.groname AS group_name,
pg_group.grosysid AS gro...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_groups" does not exist
    Command was: ALTER TABLE public.pgadmin_groups OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 841; 1259 18928 VIEW
pgadmin_indexes phone
pg_restore: [archiver (db)] could not execute query: ERROR:  column
x.indislossy does not exist
LINE 2: ...t_userbyid(i.relowner) AS index_owner, CASE WHEN (x.indislos...
                                                             ^
    Command was: CREATE VIEW pgadmin_indexes AS
    SELECT i.oid AS index_oid, i.relname AS index_name, c.relname AS
index_table, pg_get_user...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_indexes" does not exist
    Command was: ALTER TABLE public.pgadmin_indexes OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 842; 1259 18956 VIEW
pgadmin_languages phone
pg_restore: [archiver (db)] could not execute query: ERROR:  column
l.lancompiler does not exist
LINE 2: ....oid AS language_oid, l.lanname AS language_name, l."lancomp...
                                                             ^
    Command was: CREATE VIEW pgadmin_languages AS
    SELECT l.oid AS language_oid, l.lanname AS language_name,
l."lancompiler" AS language_c...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_languages" does not exist
    Command was: ALTER TABLE public.pgadmin_languages OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 843; 1259 18972 VIEW
pgadmin_sequences phone
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax
error at or near "."
LINE 2: ...elacl AS sequence_acl, pgadmin_get_sequence(c.oid).sequence_...
                                                             ^
    Command was: CREATE VIEW pgadmin_sequences AS
    SELECT c.oid AS sequence_oid, c.relname AS sequence_name,
pg_get_userbyid(c.relowner) A...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_sequences" does not exist
    Command was: ALTER TABLE public.pgadmin_sequences OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 844; 1259 18993 VIEW
pgadmin_tables phone
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax
error at or near "."
LINE 2: ...END AS table_has_triggers, pgadmin_get_rows(c.oid).table_row...
                                                             ^
    Command was: CREATE VIEW pgadmin_tables AS
    SELECT c.oid AS table_oid, c.relname AS table_name,
pg_get_userbyid(c.relowner) AS table_o...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_tables" does not exist
    Command was: ALTER TABLE public.pgadmin_tables OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 845; 1259 19027 VIEW
pgadmin_users phone
pg_restore: [archiver (db)] could not execute query: ERROR:  column
pg_shadow.oid does not exist
LINE 2:     SELECT pg_shadow.oid AS user_oid, pg_shadow.usename AS u...
                   ^
    Command was: CREATE VIEW pgadmin_users AS
    SELECT pg_shadow.oid AS user_oid, pg_shadow.usename AS user_name,
pg_shadow.usesysid AS use...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"public.pgadmin_users" does not exist
    Command was: ALTER TABLE public.pgadmin_users OWNER TO phone;
pg_restore: [archiver (db)] Error from TOC entry 1031; 0 18724 TABLE
DATA pgadmin_desc phone
pg_restore: [archiver (db)] COPY failed: ERROR:  literal carriage return
found in data
HINT:  Use "\r" to represent carriage return.
CONTEXT:  COPY pgadmin_desc, line 5
pg_restore: [archiver (db)] Error from TOC entry 1041; 0 118970 TABLE
DATA phonecalls phone
pg_restore: [archiver (db)] COPY failed: ERROR:  literal carriage return
found in data
HINT:  Use "\r" to represent carriage return.
CONTEXT:  COPY phonecalls, line 204229
pg_restore: [archiver (db)] Error from TOC entry 1054; 0 0 ACL
pgadmin_get_rows(oid) phone
pg_restore: [archiver (db)] could not execute query: ERROR:  function
pgadmin_get_rows(oid) does not exist
    Command was: REVOKE ALL ON FUNCTION pgadmin_get_rows(oid) FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  function
pgadmin_get_rows(oid) does not exist
    Command was: REVOKE ALL ON FUNCTION pgadmin_get_rows(oid) FROM phone;
pg_restore: [archiver (db)] could not execute query: ERROR:  function
pgadmin_get_rows(oid) does not exist
    Command was: GRANT ALL ON FUNCTION pgadmin_get_rows(oid) TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1055; 0 0 ACL
pgadmin_get_sequence(oid) phone
pg_restore: [archiver (db)] could not execute query: ERROR:  function
pgadmin_get_sequence(oid) does not exist
    Command was: REVOKE ALL ON FUNCTION pgadmin_get_sequence(oid) FROM
PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  function
pgadmin_get_sequence(oid) does not exist
    Command was: REVOKE ALL ON FUNCTION pgadmin_get_sequence(oid) FROM
phone;
pg_restore: [archiver (db)] could not execute query: ERROR:  function
pgadmin_get_sequence(oid) does not exist
    Command was: GRANT ALL ON FUNCTION pgadmin_get_sequence(oid) TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1061; 0 0 ACL
pgadmin_databases phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_databases" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_databases FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_databases" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_databases TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1062; 0 0 ACL
pgadmin_checks phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_checks" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_checks FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_checks" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_checks TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1064; 0 0 ACL
pgadmin_groups phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_groups" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_groups FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_groups" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_groups TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1065; 0 0 ACL
pgadmin_indexes phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_indexes" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_indexes FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_indexes" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_indexes TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1066; 0 0 ACL
pgadmin_languages phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_languages" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_languages FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_languages" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_languages TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1067; 0 0 ACL
pgadmin_sequences phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_sequences" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_sequences FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_sequences" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_sequences TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1068; 0 0 ACL
pgadmin_tables phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_tables" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_tables FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_tables" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_tables TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1069; 0 0 ACL
pgadmin_users phone
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_users" does not exist
    Command was: REVOKE ALL ON TABLE pgadmin_users FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"pgadmin_users" does not exist
    Command was: GRANT ALL ON TABLE pgadmin_users TO PUBLIC;
pg_restore: [archiver (db)] Error from TOC entry 1076; 0 0 ACL
phonecalls_irecnum_seq phone
pg_restore: [archiver (db)] could not execute query: ERROR:  role "web"
does not exist
    Command was: GRANT UPDATE ON SEQUENCE phonecalls_irecnum_seq TO web;
pg_restore: [archiver (db)] Error from TOC entry 1077; 0 0 ACL
phonecalls phone
pg_restore: [archiver (db)] could not execute query: ERROR:  role "web"
does not exist
    Command was: GRANT SELECT,INSERT ON TABLE phonecalls TO web;
WARNING: errors ignored on restore: 46

Re: More on migragting the server.

From

Tom Lane

Date:

26 September 2007, 17:15:41

David Siebert <david@eclipsecat.com> writes:
> I set up a test server using the latest 8.2 as suggest by the list and
> did pg_dump of the old data base.
> I created a new empty database with the same name an created a user with
> the same name as was on the old server.
> I then tried to do a restore using webmin just as a test and got errors.

Hm, your old version was 7.1 right?  That predates the availability of
pg_depend information, which is what pg_dump must have to ensure that
it dumps objects in an order that has no forward references.  When
dumping from such an old server, modern pg_dump versions will use some
ordering heuristics that sort of mostly work, but a few problems are
to be expected.  In this example, for instance, it seems to have dumped
function pgadmin_get_rows(oid) before table pgadmin_table_cache, which
doesn't work.

What you'll need to do to get this reloaded is to manually modify the
load order.  pg_restore has some options that help you do that ---
basically you get a listing of the TOC (table of contents) of the
archive file, and then manually rearrange that listing, and then tell
pg_restore to restore in the manually specified order.  It will probably
take a bit of trial and error before you get it right, so I'd suggest
using pg_restore's -s option to not bother trying to load data until
you have a working load order.

Also, it might not be a bad idea to just omit the old pgAdmin objects
from the reload altogether, because they aren't going to be helpful
anyway for a modern pgAdmin.  Leave them out and then install a modern
pgAdmin release after you've successfully loaded your own stuff.
(The same goes for any other contrib or third-party stuff you might have
in there --- 7.1-era code is going to need replaced.)

            regards, tom lane