Zayn Hamdan Blog

I have search how to do this and I’m writing this howto for my reference and maybe will help someone out there.
We have 2 servers, one for reporting and the other is database server. I need to dump data from database server across to reporting server.

I have set to database server on pg_hba.conf to MD5 authentication for only reporting server, so when I’m connecting from reporting server, I will be prompted for password. You may say, why don’t you set TRUST setting on your pg_hba.conf so you can connect from other server passwordless? Yes, it’s handy but it comes big risk. I want another server connect with password and I restrict only from one server.

So, how to do that? It’s simple, just issue this command before pg_dump command;

export PGPASSWORD='your postgres password'

trying to echo it by issue:

echo $PGPASSWORD

If it works, then you can issue command pg_dump with no password! Example:

pg_dump -h YOUR_IP -U postgres YOURDATABASE

You can also dump specific table by adding -t TABLENAME. For larger database or tables, you don’t want to wait until the dump process is done? You can use nohup command and ampersand in the end, example

nohup pg_dump -h YOUR_IP -U postgres YOURDATABASE &

Good Luck!