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