Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I have two machines:

  • Remote, Ubuntu server, example.com, database: staging (owner user2, pass2), passwordless login for user1 (sudoer) via ssh
  • Local, Ubuntu, localhost, database: local (owner user3, pass3)

The question is:

  • How to import data and structure of staging database to local (and vice versa) in one command/script?
  • How to set up the permissions?

These are not production databases, so I don't need real time sync. I just need to automate the psql tasks (usually done by go to phppgadmin, export the data to file, import the data from file via psql, all from Local machine).

The best solution would be database owner/password independent, because I have few pairs more of such a databases. I have a root access on both machines and I can install any additional required users accounts/software.

share|improve this question
1  
Either use the new replication features in 9.x or perform regular backup from production and restore to your staging server. – Alex Holst Nov 16 '10 at 17:29

1 Answer

up vote 1 down vote accepted

Might be simple enough as just scripting the database dumping and then transferring the file to the other database server and then restoring:

The 'pg_dump' utility found with the PostgreSQL Server can be utilized by Database Administrators for taking dumps or backups of databases into a file. The syntax of pg_dump is :

pg_dump databasename -f outputfile -i -x -O -R -S username

I am not a fan completely of this script http://www.wisdombay.com/articles/pg_dmp.sh but it should work just fine for you.

The reason for my caution is - because it houses your login/passwords

Now simply scp to the backup server and run a cron job to import the databases.

If stuck - let me know.

share|improve this answer
Thanks. I've seen this script before, but I think plain text passwords are not a good solution at all. Maybe some setup in sudoers file to run this script is needed. But I think, when I already have a passwordless access with ssh, I might run some commands just as a parameter for ssh. But what commands? ;) – takeshin Nov 16 '10 at 20:20
1  
Use .pgpass to store your password, that's safe. postgresql.org/docs/current/interactive/libpq-pgpass.html – Frank Heikens Nov 18 '10 at 13:05

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.