Обсуждение: Dump file created with pg_dump cannot be restored with psql
I have two installation of postgresql-server-9.2.4 on Gentoo. I try to just copy database from one to another. According to the documentation http://www.postgresql.org/docs/9.2/static/backup-dump.html I created dump file: psql -U <role> <database> > dumpfile.sql copied it to another machine, manually created the same role, database and schema (owned by user) as in the first machineand tried to restore: psql -f dumpfile.sql -U <role> <database> It produces a lot of errors starting from the first command in dumpfile: DROP TABLE "archive" CASCADE\g that produces error: ERROR: table "archive" does not exist The second comand is creation of this table: CREATE TABLE "archive" ( "ar_namespace" int NOT NULL DEFAULT '0', "ar_title" bytea NOT NULL DEFAULT '', "ar_text" bytea NOT NULL, "ar_comment" bytea NOT NULL, "ar_user" int CHECK ("ar_user" >= 0) NOT NULL DEFAULT '0', "ar_user_text" bytea NOT NULL, "ar_timestamp" bytea NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', "ar_minor_edit" smallint NOT NULL DEFAULT '0', "ar_flags" bytea NOT NULL, "ar_rev_id" int CHECK ("ar_rev_id" >= 0) DEFAULT NULL, "ar_text_id" int CHECK ("ar_text_id" >= 0) DEFAULT NULL, "ar_deleted" smallint CHECK ("ar_deleted" >= 0) NOT NULL DEFAULT '0', "ar_len" int CHECK ("ar_len" >= 0) DEFAULT NULL, "ar_page_id" int CHECK ("ar_page_id" >= 0) DEFAULT NULL, "ar_parent_id" int CHECK ("ar_parent_id" >= 0) DEFAULT NULL, "ar_sha1" bytea NOT NULL DEFAULT '', "ar_content_format" bytea DEFAULT NULL, "ar_content_model" bytea DEFAULT NULL ) ; it produces error: ERROR: invalid input syntax for type bytea and so on... a lot of error relation/table does not exist. In the initial database field "ar_title" in table "archive" has type "text", not "bytea". At the end I have only 45 tables of 51 in my new database. In the man page of pg_dump I can see option -c, --clean that as far as I understand should activate existance of DROP TABLEcommands in dump. I didn't add this option but have such commands. Why?
On 08/02/2013 05:03 PM, tot-to wrote: > I have two installation of postgresql-server-9.2.4 on Gentoo. > > I try to just copy database from one to another. > > According to the documentation http://www.postgresql.org/docs/9.2/static/backup-dump.html I created dump file: > psql -U <role> <database> > dumpfile.sql I would tend to doubt it, I would guess you actually used pg_dump. Could you show the exact command line you used? FYI a better source of documentation can be found here: http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html http://www.postgresql.org/docs/9.2/interactive/app-pgrestore.html > > copied it to another machine, manually created the same role, database and schema (owned by user) as in the first machineand tried to restore: > psql -f dumpfile.sql -U <role> <database> > > It produces a lot of errors starting from the first command in dumpfile: > DROP TABLE "archive" CASCADE\g > > that produces error: > ERROR: table "archive" does not exist > > The second comand is creation of this table: > CREATE TABLE "archive" ( > "ar_namespace" int NOT NULL DEFAULT '0', > "ar_title" bytea NOT NULL DEFAULT '', > "ar_text" bytea NOT NULL, > "ar_comment" bytea NOT NULL, > "ar_user" int CHECK ("ar_user" >= 0) NOT NULL DEFAULT '0', > "ar_user_text" bytea NOT NULL, > "ar_timestamp" bytea NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', > "ar_minor_edit" smallint NOT NULL DEFAULT '0', > "ar_flags" bytea NOT NULL, > "ar_rev_id" int CHECK ("ar_rev_id" >= 0) DEFAULT NULL, > "ar_text_id" int CHECK ("ar_text_id" >= 0) DEFAULT NULL, > "ar_deleted" smallint CHECK ("ar_deleted" >= 0) NOT NULL DEFAULT '0', > "ar_len" int CHECK ("ar_len" >= 0) DEFAULT NULL, > "ar_page_id" int CHECK ("ar_page_id" >= 0) DEFAULT NULL, > "ar_parent_id" int CHECK ("ar_parent_id" >= 0) DEFAULT NULL, > "ar_sha1" bytea NOT NULL DEFAULT '', > "ar_content_format" bytea DEFAULT NULL, > "ar_content_model" bytea DEFAULT NULL > ) ; > > it produces error: > ERROR: invalid input syntax for type bytea > > and so on... a lot of error relation/table does not exist. > > In the initial database field "ar_title" in table "archive" has type "text", not "bytea". > > At the end I have only 45 tables of 51 in my new database. > > In the man page of pg_dump I can see option -c, --clean that as far as I understand should activate existance of DROP TABLEcommands in dump. I didn't add this option but have such commands. Why? Probably depends on the switches you gave to pg_dump and how you wrote them out. That is why the exact command you gave to create the dump is necessary. > > -- Adrian Klaver adrian.klaver@gmail.com
Oh, sorry. I mixed up dumps... I am migrating from mysql and by mistake I tried to apply dump from "mysqldump --compat=postgresql" that was named very similarto dump of finally converted database produced by pg_dump (for the purpose of copy from test to main server). Bashcomletitions and then bash history (when I was reproducing problem before mailing here) made malicious joke on me. :) Sorry for inattention. And thank you for your answer. I found my mistake when I was looking for the exact command. On Fri, 02 Aug 2013 17:15:28 -0700 Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 08/02/2013 05:03 PM, tot-to wrote: > > I have two installation of postgresql-server-9.2.4 on Gentoo. > > > > I try to just copy database from one to another. > > > > According to the documentation http://www.postgresql.org/docs/9.2/static/backup-dump.html I created dump file: > > psql -U <role> <database> > dumpfile.sql > > I would tend to doubt it, I would guess you actually used pg_dump. > Could you show the exact command line you used? > > > FYI a better source of documentation can be found here: > > http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html > http://www.postgresql.org/docs/9.2/interactive/app-pgrestore.html > > > > > copied it to another machine, manually created the same role, database and schema (owned by user) as in the first machineand tried to restore: > > psql -f dumpfile.sql -U <role> <database> > > > > It produces a lot of errors starting from the first command in dumpfile: > > DROP TABLE "archive" CASCADE\g > > > > that produces error: > > ERROR: table "archive" does not exist > > > > The second comand is creation of this table: > > CREATE TABLE "archive" ( > > "ar_namespace" int NOT NULL DEFAULT '0', > > "ar_title" bytea NOT NULL DEFAULT '', > > "ar_text" bytea NOT NULL, > > "ar_comment" bytea NOT NULL, > > "ar_user" int CHECK ("ar_user" >= 0) NOT NULL DEFAULT '0', > > "ar_user_text" bytea NOT NULL, > > "ar_timestamp" bytea NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0', > > "ar_minor_edit" smallint NOT NULL DEFAULT '0', > > "ar_flags" bytea NOT NULL, > > "ar_rev_id" int CHECK ("ar_rev_id" >= 0) DEFAULT NULL, > > "ar_text_id" int CHECK ("ar_text_id" >= 0) DEFAULT NULL, > > "ar_deleted" smallint CHECK ("ar_deleted" >= 0) NOT NULL DEFAULT '0', > > "ar_len" int CHECK ("ar_len" >= 0) DEFAULT NULL, > > "ar_page_id" int CHECK ("ar_page_id" >= 0) DEFAULT NULL, > > "ar_parent_id" int CHECK ("ar_parent_id" >= 0) DEFAULT NULL, > > "ar_sha1" bytea NOT NULL DEFAULT '', > > "ar_content_format" bytea DEFAULT NULL, > > "ar_content_model" bytea DEFAULT NULL > > ) ; > > > > it produces error: > > ERROR: invalid input syntax for type bytea > > > > and so on... a lot of error relation/table does not exist. > > > > In the initial database field "ar_title" in table "archive" has type "text", not "bytea". > > > > At the end I have only 45 tables of 51 in my new database. > > > > In the man page of pg_dump I can see option -c, --clean that as far as I understand should activate existance of DROPTABLE commands in dump. I didn't add this option but have such commands. Why? > > Probably depends on the switches you gave to pg_dump and how you wrote > them out. That is why the exact command you gave to create the dump is > necessary. > > > > > > >
For that matter, for the first time we tried enforcing some of the "rules" of CFs this time, and I'd like to hear if people think that helped. I think he merit of "fast promote" is - allowing quick connection by skipping checkpoint and its demerit is - taking little bit longer when crash-recovery ----- gilroy -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dump-file-created-with-pg-dump-cannot-be-restored-with-psql-tp5766148p5766174.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.