Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Can someone help me with the most common stable replication mechanism for postgres that I can run at specific times of a day(know it sounds weird) postgres that can run over ssh?

Over ssh is because my machines are on linode with only one network interface – public and I don’t want to expose the port

share|improve this question
    
Do you mean that you have a second postgres server on which you want updated data from the main server to be loaded at specific times, or do you just want a backup to be stored somewhere so it can be re-loaded when necessary? –  Jenny D Aug 22 '12 at 12:59
    
@JennyD yes, I have another machine running which needs the data from the primary machine and not a backup –  Quintin Par Aug 23 '12 at 3:13

2 Answers 2

I use a script started from crontab: this script creates a ssh tunnel to the remote postgres host, starts a session to the remote postgres database using the local psql binary, then kills the tunnel once done.

For example:

# $REMOTE is the hostname of the remote postgres server
# this tunnel maps the local port 50432 to the remote port 5432 where the
#  remote postgres instance is listening
/usr/bin/ssh -nNT postgres@${REMOTE} -L 50432:localhost:5432 &
TUNNELPID=$!

# wait a little for the tunnel to establish itself
sleep 5

# execute the statements in load.sql against the remote database 'remotepgdb'
/usr/local/pgsql/bin/psql -q -h localhost -p 50432 -U remotepguser remotepgdb < ${SCRIPT_PATH}/load.sql

# kill the ssh tunnel
/usr/bin/kill ${TUNNELPID}

Before trying this technique, I suggest to make sure that the local psql client and the remote postgres database are exactly at the same version.

share|improve this answer
    
This is the right direction. Let me try this out. –  Quintin Par Aug 23 '12 at 3:13
    
I see that you have loading ${SCRIPT_PATH}/load.sql. Is there a way to directly pull from the local db, imitating replication? Without locks and all.. –  Quintin Par Aug 23 '12 at 6:47
    
You may want to investigate if dblink postgresql.org/docs/current/static/contrib-dblink-function.html fits your requirement. You could keep the ssh tunnel and write a stored procedure on the local db which opens a connection to the remote db and manipulates the data. See also postgresonline.com/journal/index.php?/archives/44-guid.html for examples of using dblink. –  sebastianopilla Aug 23 '12 at 8:14

You could also have a look at pgpool. We use it for continous replication of a postgres database, and if you keep an ssh-tunnel (or stunnel) up, you can have the data encrypted during transfer

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.