Обсуждение: Usage: pg_dump's text format
In short, I have not yet found a way to use pg_dump's text formatted file with psql to restore a database without generating some kind of error. There is always errors when dropping tables or dropping the database. I have searched the novice and general archives but have not found the answer to this.
In long, I use the following command to generate the dump file against a database named "mystore":
"pg_dump --clean --create --file=db_dump.txt --format p mystore"
The format of the dump file is as such (trimmed to keep length manageable):
\connect - my_admin
DROP INDEX "sys_mess_node_idx";
DROP TABLE "sag_to_sae";
DROP SEQUENCE "genset";
\connect - postgres
DROP INDEX "sys_mess_node_idx";
DROP TABLE "sag_to_sae";
DROP SEQUENCE "genset";
\connect - postgres
Drop Database "mystore";
Create Database "mystore";
Create Database "mystore";
\connect mystore postgres
\connect - my_admin
CREATE TABLE...
\connect - my_admin
CREATE TABLE...
<rest omitted for brevity>
To restore the database, I have tried:
"psql mystore < db_dump.txt"
And this fails at the "Drop Database "mystore"" command.
I have also tried:
"psql template1 < db_dump.txt"
Where the "DROP TABLE..." commands fail, but the database gets deleted prior to being re-created.
I know the problem is related to being or not being connected to the database one wants to restore. One cannot drop the database one is connected to. And if one is connected to another database, the "drop table" fail because said tables are not there.
Is there anyway to use the dump file wholesale in a cleaner manner? Is there no other way than using bits and pieces of it? Or hacking it before using it? I thought of using pg_restore, but found it is not meant for the text format. If I could somehow customize how the "\connect"'s are done, I think that would work.
Any advice or suggestion greatly welcomed. Thanks in advance,
JP
"JP Beaudry" <jbeaudry@cisco.com> writes: > In long, I use the following command to generate the dump file against a > database named "mystore": > "pg_dump --clean --create --file=db_dump.txt --format p mystore" > ... > To restore the database, I have tried: > "psql mystore < db_dump.txt" > And this fails at the "Drop Database "mystore"" command. --clean and --create are logically incompatible options. pg_dump versions newer than about 7.1 will tell you so ... I think you are overdue for an upgrade, but in the meantime don't use that combination. regards, tom lane