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 Database "mystore";
Create Database "mystore";
\connect mystore postgres
\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