I want to copy my database schema (just schema ;not data) from internal server to external server. The problem I am facing is entering passwords at every step. Even though the steps to copy are pretty simple, I am not able to generate a script to automate teh whole process.
What I have till now:
on internal server:
pg_dump -C -s --file=schema.txt {name}
scp schema.txt prakhar@{external server}:/home/prakhar
on external server:
dropdb {name}
createdb {name}
psql --file=schema.txt {name}
At each step I am prompted for password. I want to do two things: 1: Run the script from external server to fetch schema from internal ; or the other way around 2: Incorporate the password for both internal and external servers in a way the the script takes care of it for me.
I would recommend wrapping those commands in bash scripts, and in each one, prior to running the commands, add the following line:
export PGPASSWORD=<password>
Where is the password you want to use. This will export it as an environment variable which is available to the Postgres commands.
Here are other methods, including PGPASSWORD, to specify the Postgres password.
For *nix commands like scp, there are other options. One is sshpass. That would work well if you wanted to keep this all as a shell script.
Another option, and the one I would probably use for this sort of thing, would be to scrap the shell script wrapper and instead use something like Python's Fabric.
You can run commands using sudo, as well as commands on remote machines, as well as shell commands like the Postgres utility programs (you would want to set PGPASSWORD in the environment hash within Fabric for that).