1

I'm trying to configure a PostgreSQL instance running on SERVER_A to connect to another PostgreSQL instance on SERVER_B. I'm running a shell script on SERVER_A that ultimately needs to connect and execute a pg_restore onto a database that resides on SERVER_B.

SERVER_B is on Amazon's AWS. Normally, to ssh into the machine, I would run the following commands:

# ssh -i .ssh/server-dev.pem root@dev.hostname.com
[root@dev]# ssh -i .ssh/dev1mac 0.0.0.0

...With the Postgres instance residing at 0.0.0.0

Can anyone help explain to me how I would use the above information to configure Postgres on SERVER_A? I have been looking into pg_hba.conf, but quite frankly I've never done this before and from the documentation, it's not clear to me how to achieve my desired result. Is there perhaps an easier way that would allow me to skip configuring Postgres altogether, and instead just run a series of commands from my shell script to achieve the restore?

I'd appreciate any help, thanks!

littleK
  • 129
  • 1
  • 11
  • What is the end goal ("desired result" you're trying to accomplish? -- If you just want to run `pg_restore` as a shell command on `SERVER_B` why not just send everything over SSH? – voretaq7 Aug 23 '12 at 15:43
  • The end goal is to run a pg_restore from a dump file on SERVER_A to a database on SERVER_B. I guess my question is, is it possible to program my shell script (on SERVER_A) to perform that pg_restore over SSH? If so, I could use some instruction on how to do that. – littleK Aug 23 '12 at 15:47

1 Answers1

3

If all you want to do is pg_restore a dump file currently on SERVER_A to the DB instance running on SERVER_B it's easy:

SERVER_A # cat dump_file | ssh SERVER_B pg_restore

(Include any necessary options to pg_restore, but don't specify a filename. Per the postgres manual, If [filename is] not specified, the standard input is used.)

(You could also scp the dump file over and then run the restore using the file on SERVER_B, but the above seems to be more in line with your goal of running one script on SERVER_A that does everything).

voretaq7
  • 79,879
  • 17
  • 130
  • 214
  • Thanks voretaq7, what's the best way to modify the above command considering that I have to SSH twice? Once to an intermediary server, and once more to the destination (SERVER_B)? – littleK Aug 23 '12 at 16:06
  • hmm... well obviously "Use ssh-agent and turn on agent forwarding" springs immediately to mind so you don't get password prompts in the way. For moving the data you could stick a [`cat`](http://www.freebsd.org/cgi/man.cgi?query=cat) in the middle (on your intermediary server), or set up an SSH tunnel (using `-L` or `-R` depending on which end you need to talk to) to avoid the extra jump. – voretaq7 Aug 23 '12 at 16:10
  • Thanks, does something like this look about right? cat dump_files/out.sql | ssh -i .ssh/server-dev.pem -vv root@dev.hostname.com -L 5454:0.0.0.0:5432 pg_restore -U postgres -h localhost -p 5454. – littleK Aug 23 '12 at 16:42
  • @littleK you've got two different solutions conflated there -- without knowing your environment better I cant tell you what specifically to type, but what you've got there will probably not work because your port forward goes nowhere. Try creating the chain in your environment (minus the actual pg_restore until you get to the machine you need to be on -- the best way to learn is to do :-) – voretaq7 Aug 23 '12 at 17:58
  • Thanks, I've was able to get it working. Now, I have to find a way to supply a Postgres database password for the remote server. My script currently exports PGPASSWORD for accessing the database on SERVER_A, any suggestions on the password for SERVER_B? – littleK Aug 24 '12 at 15:28